Forums
This topic is locked
PHP and MySql
03 Feb 2005 15:08:20 A B posted:
hi guysjus need some help, i am new to php and sql and wanted to display some simple data
basically i have a php page where i would like to select a record from a drop-down list, these records are stored in the database i have created,
i then would like to display the relevant data assoicated with this record in some text box fields, currently only the last name in my database appers in the form, i think im also missing code for the drop down box,
here is the dh.php code :
<html>
<body bgcolor="#99CCFF">
<?php
// open a connection to the database
// if you have set a username and password it needs to go in here
// $conn = mysql_connect("localhost", "username", "password"
;$conn = mysql_connect("localhost", "root", ""
;// pick a database to use
mysql_select_db("dh",$conn);
// create a query
$sqlQuery = "select * from staff";
// send the query to the DB and catch the return
$result = mysql_query($sqlQuery, $conn) or die(mysql_error());
// Loop over the results and print them out
while ($row = mysql_fetch_array($result)) {
print("<tr>"
;$staff_no = $row['staff_no'];
$fname = $row['fname'];
$lname = $row['lname'];
$supervisor_staff_no = $row['supervisor_staff_no'];
$position_ = $row['position_'];
$sex = $row['sex'];
$dob = $row['dob'];
$salary = $row['salary'];
$branch_no = $row['branch_no'];
$street = $row['street'];
$city = $row['city'];
$postcode= $row['postcode'];
print("</tr>"
;}
print("</table>"
;?>
<body>
<h1 align="center">Dh</h1>
<h2 align="center">Staff form</h2>
<br>
<br>
<br>
<br>
<br>
<br>
<form>
Staff Number
<select name="Staff number">
<?
while ($row = mysql_fetch_array($result))
{
extract ($row);
}
?>
echo <option value='$staff_no'><? $staff_no ?>
</select>
<input type="submit" name="Submit" value="Submit">
</form>
<table border="1" width = 100% length = 100%>
<tr>
<td>
<table cellspacing="12" bgcolor="#3399CC" width = 100% length = 80%>
<tr><td>Full name</td><td align="center"></td> <td><input type="text" name="Fullname" value= <? echo ($fname.$lname)?> > </td>
<td>Branch Number</td><td align="center"> </td> <td><input type="text" name="Branch Number" value= <? echo ($branch_no)?> ></td><tr>
<td>Sex</td><td align="center"> </td> <td><input type="text" name="Sex" value= <? echo ($sex)?> ></td>
<td>Branch Address</td><td align="center"></td> <td><input type="text" name="Branch Address" value= <? echo ($street.$city.$postcode)?> ></td></tr>
<tr><td>DOB</td><td align="center"> </td> <td><input type="text" name="DOB" value= <? echo ($dob)?> ></td>
<td>Telephone Number</td><td align="center"> </td> <td><input type="text" name="Telephone Number" value= <? echo ($fname.$lname)?> ></td> </tr>
<tr></tr>
<tr><td>Position</td><td align="center"> </td> <td><input type="text" name="Position" value= <? echo ($position_)?> ></td> </tr>
<tr><td>Salary</td><td align="center"> </td> <td><input type="text" name="Salary" value= <? echo ($salary)?> ></td> </tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<tr><td>Please Enter details where applicable: </td><td align="center"></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<tr><td>Supervisor name</td><td align="center"></td> <td><input type="text" name="Supervisor name"></td>
<td>Manager Start Date</td><td align="center"> </td> <td><input type="text" name="Manager Start Date"></td><tr>
<td>Manager Bonus</td><td align="center"> </td> <td><input type="text" name="Manager Bonus"></td>
</table>
</td>
</tr>
</table>
</body>
</html>
the tables im using are :
-- =========
-- BRANCH
-- =========
DROP TABLE Branch;
CREATE TABLE Branch
(branch_no CHARACTER(4) PRIMARY KEY,
street CHARACTER VARYING(25),
city CHARACTER VARYING(15),
postcode CHARACTER(10) UNIQUE, -- ALTERNATE KEY
mgr_staff_no CHARACTER(5)); -- FOREIGN KEY
-- ===========
-- TELEPHONE
-- ===========
DROP TABLE Telephone;
CREATE TABLE Telephone
(tel_no CHARACTER VARYING(13) PRIMARY KEY,
branch_no CHARACTER(4)); -- FOREIGN KEY
-- ===========
-- STAFF
-- ===========
DROP TABLE Staff;
CREATE TABLE Staff
(staff_no CHARACTER(5) PRIMARY KEY,
fname CHARACTER VARYING(20),
lname CHARACTER VARYING(20),
position_ CHARACTER VARYING(20),
sex CHARACTER,
dob DATE,
salary DECIMAL(9,2),
supervisor_staff_no CHARACTER(5), -- FOREIGN KEY
branch_no CHARACTER(4)); -- FOREIGN KEY
-- ===========
-- MANAGER
-- ===========
DROP TABLE Manager;
CREATE TABLE Manager
(staff_no CHARACTER(5) PRIMARY KEY, -- FOREIGN KEY
mgr_start_date DATE,
bonus INTEGER);
hope it makes sense! thanks!
Replies
Replied 03 Feb 2005 20:11:25
03 Feb 2005 20:11:25 Chris Charlton replied:
It looks like your <option> parts for your dropdown (select) is actually <i>outside</i> of your <i>while</i> loop. Was that intentional? At quick glance, looks like the <i>echo</i> is not even within the PHP.
<pre id=code><font face=courier size=2 id=code><select name="Staff number">
<? while ($row = mysql_fetch_array($result)) {
extract ($row); } ?>
echo <option value='$staff_no'><? $staff_no ?>
</select></font id=code></pre id=code>
<pre id=code><font face=courier size=2 id=code><select name="Staff number">
<? while ($row = mysql_fetch_array($result)) {
extract ($row); } ?>
echo <option value='$staff_no'><? $staff_no ?>
</select></font id=code></pre id=code>