List Box Control

[mickorourke]

I need to select record from database for editing
when a selection is made in the List box the three text boxs will display name, email, age, for that record I am trying the code below but its not working could someone have a look
Thanks Mick

<?php

$connection = mysql_connect ("localhost", "root", "mick");
if ($connection == false){
echo mysql_errno().": ".mysql_error()."<BR>";
exit;
}

$query = ("SELECT id,fullname FROM email_info ");
$result = mysql_db_query ("test_database", $query);

if ($result){
{
echo "<SELECT NAME='fullname]'>";
while ($myrow = mysql_fetch_array($result))
{
echo "<OPTION VALUE=\"".$myrow["id"]."\">".
$myrow["fullname"]."</OPTION> ";
}
echo "</SELECT>";
}
$query = ("SELECT fullname,email_address,age FROM email_info ");
$result = mysql_db_query ("test_database", $query);
echo "<table border=1>";
echo "<tr><td><b>Full Name</b></td><td> <b>Email Address</b></td></tr>";

$numOfRows = mysql_num_rows ($result);
for ($i = 0; $i < $numOfRows; $i++){
$name = mysql_result ($result, $i, "fullname");
$email = mysql_result ($result, $i, "email_address");
$email = mysql_result ($result, $i, "age");

echo " <tr><td><input size=\"50\" value=\"$name\" name= \"1\"></td>";
echo " <tr><td><input size=\"50\" value=\"$email\" name= \"2\"></td>";
echo " <tr><td><input size=\"50\" value=\"$age\" name= \"3\"></td>";

}
echo "</table>";
}
else{
echo mysql_errno().": ".mysql_error()."<BR>";
}

mysql_close ();

?>

[jatar_k]

Welcome to WebmasterWorld mickorourke,

what exactly are they selecting from the list box? I don't see a where clause being used in your queries so I don't understand what you are basing your select on.

this chunk of code is overly verbose as well

$numOfRows = mysql_num_rows ($result);
for ($i = 0; $i < $numOfRows; $i++){
$name = mysql_result ($result, $i, "fullname");
$email = mysql_result ($result, $i, "email_address");
$email = mysql_result ($result, $i, "age");

echo " <tr><td><input size=\"50\" value=\"$name\" name= \"1\"></td>";
echo " <tr><td><input size=\"50\" value=\"$email\" name= \"2\"></td>";
echo " <tr><td><input size=\"50\" value=\"$age\" name= \"3\"></td>";

that could all be written like so

while ($row = mysql_fetch_array($result)) {
echo '<tr><td><input size="50" value="',$row['name'],'" name="1"></td>';
echo '<tr><td><input size="50" value="',$row['email'],'" name="2"></td>';
echo '<tr><td><input size="50" value="',$row['age'],'" name="3"></td>';
}

much simpler ;)

[mickorourke]

THANKS For the reply jatar_k
For the three text boxs I need to display for editing the record selected in the list box
I am just starting out with php the code I provided was copied from somewhere else
The drop down listbox and the three text boxs are from the same table
What I want to do is make a selection of "fullname" from the listbox and have the records for that entry show in the three text boxs,
have another look please in the mean time I will read up on where clause,
Thanks again Mick

[jatar_k]

so does this logic show what you want to do

show a page with listbox of names
user selects a name
user submits form

use fullname to select data from database
load fullname, email and age into next form
user edits values
user submits 2nd form
new data is saved to the database

[mickorourke]

thanks jatar_k
this sounds right
but do i need two forms?
MICK

[jatar_k]

you could reload the same page if you like, I just don't like that functionality. I always split them up, you don't need to though.

[mickorourke]

so can you help me out with the code
Mick

[jatar_k]

some general tips

don't use mysql_db_query just use mysql_query and you don't need to pass the connection to these functions unless you have more than one open, otherwise it assumes the last opened connection.
as I previously showed, use a loop with mysql_fetch_array as opposed to using mysql_result

as I am going through this code the braces are mismatched so a bunch of things probably wouldn't work

I am not a big fan of writing code for people but this is a good example to use for teaching purposes

let's look at the agreed upon logic first

1. show a page with listbox of names
2. user selects a name
3. user submits form
4. use fullname to select data from database
5. load fullname, email and age into next form
6. user edits values
7. user submits 2nd form
8. new data is saved to the database

steps 1, 2 and 3 are for our first view of the page

it looks like your code will work for this

$connection = mysql_connect ("localhost", "root", "mick"); 
if (!$connection) { 
 echo mysql_errno().": ".mysql_error().'<br>'; 
 exit; 
} 
mysql_select_db('test_database'); 
$query = 'SELECT id,fullname FROM email_info '; 
$result = mysql_query($query); 
if ($result) { 
 echo '<select name="fullname">'; 
 while ($myrow = mysql_fetch_array($result)) { 
 echo '<option value="',$myrow["id"],'">',$myrow['fullname'],'</option> '; 
 } 
 echo "</select>"; 
}

you could add an error handler in an else if you like or other debugging mechanisms on your query function

I made a few changes to this.
1. you don't need to have $connection == false, you can just put!$connection which means if the variable contains a value of false, the statement becomes true and it will echo your errors.
2. I changed to mysql_query and added a call to mysql_select_db [php.net] after the connection. You could also test the return value on that to make sure it was successful.
3. array elements use single quotes around them so I changed (eg) $myrow["id"] to $myrow['id']
4. there was a stray brace in your select box name which would cause some problems trying to reference that var later
5. you didn't need the parentheses here
$query = ("SELECT id,fullname FROM email_info ");
6. I made your html tags lower case
7. I swapped the quotes on a bunch of this to remove the need for escaping double quotes. Please read about quoting strings [php.net]
8. I swapped the concatenation operator for commas in your echo'ed strings. There is no reason to have php concatenate it before it echo's it, just use commas and let it spit it out as it goes. you can read up on the different echo formats [php.net]

the only thing you will need to add to the above code is a proper form tag and a submit button. I would use this scripts own name in the action portion of the form tag. Stay away from using things such as $_SERVER['PHP_SELF']. you should also read up on PHP Security [webmasterworld.com]

so now we've covered the first three points let's look at the rest

first we need to understand what we have to work with now.

we should have the id of the selected name submitted from the last part of the form which will be available in the $_POST [php.net] superglobal array

we need to use that to construct our select query so we can edit the information for that person

I pull POST vars into local variables and test them (I won't cover that here, read the thread about PHP Security above) and then construct my queries in vars and then pass them to the mysql_query function. This makes debugging much easier and is very good practice. You were already doing it but I wanted to make a special note as to why ;)

$passedid = $_POST['id'];
$query = "SELECT fullname,email_address,age FROM email_info WHERE id=" . $passedid;
$result = mysql_query($query);

you can add in debugging code if this doesn't work such as echo'ing $query and adding an 'or die' after the mysql_query function

then we start our table

echo "<table border=1>";
echo "<tr><td><b>Full Name</b></td><td> <b>Email Address</b></td></tr>";

since we are using an id to select this row then it should only ever return a single result (unless you have duplicate keys but that is a db construction problem) so we should be able to just use an if statement as opposed to a loop

if ($row = mysql_fetch_array($result)) {
echo '<tr><td><input size="50" value="',$row['name'],'" name="1"></td>';
echo '<tr><td><input size="50" value="',$row['email'],'" name="2"></td>';
echo '<tr><td><input size="50" value="',$row['age'],'" name="3"></td>';
}

you could add an error handler in the else if you like

then we close our table

echo "</table>";

so that looks good, we now have to put these two together and make sure that our form spans all of the elements in both states. We also need to have a different action on the form for this step as we need to update the database. You will run into reload problems, among other things, if you do that all in the same script. This step should be posted to a processing script that has no output and then can redirect using header once completed to a page that says "data has been updated" or something like that

how about I leave it at that for now and you give the rest a shot and see how you get along. If you run into trouble, just post :)

additional reference
our own PHP Library [webmasterworld.com] - there are many threads about interacting with databases among other things