PHP: drop down list and mysql

[Sridhar_Rao]

I am just starting.. .I am facing difficulty in solving a simple issue. Please help me with code.

There is a table with names of individuals and their addresses. Say, the column 1 is name, column 2 is add1, column 3 is add2.

I want a simple program in php that would display the ordered list of individual names in a drop down list.When the user selects one of them and submits it via a form, mysql should locate the record that matches the name and display the address (add1 and add2).

simple, but I am not getting it right.

I am getting the code to display the drop down list from the table, but when selected, only half of it is getting posted. Say, the name is Anthony Gonzalves in the list, when clicked, only Anthony gets passed and hence it does not match any record in the table. What is happening?

[amitava82]

Post your code.

[victor_rambo]

You are facing the issue because you are not using any PRIMARY KEY. Its always good to have a ID field, that is is the primary key and auto increments.
Create a ID field and use it as primary key, and also set it to auto-increment.

<form action="address.php" method="post">
   <select name="name">
   <!-- The value parameter is the ID of the person-->
      <option value="1">Anthony Golsalves</option>
      <option value="2">Gary Hill</option>
      <option value="3">Arvind McBill</option>
      <option value="4">Berill Shaw</option>
   </select>
   </input type="submit" value="OK" />
</form>

PHP Code:

<?php
$id=mysql_real_escape_string($_POST['id']);//assign sanitized data to a variable
$sql="SELECT name,add1,add2 FROM persons WHERE id='$id'";//self explanatory. See how we are using the id field here
$resource=mysql_query($sql) or die('Error fetching data from persons table: '.mysql_error());
//now iterate over the single result set
$name=mysql_result($resource,0,'name');
$add1=mysql_result($resource,0,'add1');
$add2=mysql_result($resource,0,'add2');

echo "The person $name resides at <br />Add 1: $add1 <br />Add 2: $add2";
?>

I suggest that you go through few tutorials before starting with databases. I received my first PHP-MySQL tutorial from this site and I feel that it shall be evry helpful to you too. There was some problem with the site in recent past, hence I never suggested it to anybody. But now, its back. I personally feel that its much better than w3schools.com or tizag.com

[Sridhar_Rao]

Here is the code:

$alln = @mysql_query('select name from detail order by name desc');
//this initializes the query

if (isset($_POST['button'])){ //checks if item from list is posted

$select=mysql_real_escape_string($_POST['select']); //name selected from list is stored in $select

echo ($select).'</br>'; //I used this to check the value being stored in $select

$result=mysql_query("select * from detail where name='$select'");
if (mysql_num_rows($result) == 0) {
die ("No rows found!"); //only this got displayed
}

while($row=mysql_fetch_assoc($result)) { //codes to display the address
echo ($row['Add1']);
echo ($row['Add2]);
}

mysql_free_result($result);
mysql_close();
} else {
?>
<form name="form1" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<select name="select"> //the form
<?php
while($nm=mysql_fetch_array($alln)){
echo ("<option value=".$nm['name'].">".$nm['name']."</option>"); //works fine; all full names gets displayed
}
?>
</select>
<input type="submit" name="button" value="Submit" />
</form>
<?php
}
?>

What is wrong with this code? I added mysql_real_escape_string after the previous post, yet it doesn't work.

[victor_rambo]

First of all, remove the error output suppression operators(@) from line 1. If there is any error there, it won't be displayed.

Also, use [php] tags, it makes it easier to read.

[Sridhar_Rao]

that is ok, sql is working fine...there is no error..as you can see that the names from the table are getting displayed in the pull down list.
when the name is selected from the list, it is not getting sent fully..here is the problem.

Lets look at this code:

PHP Code:

<form name="form1" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> 
<select name="select">  
 <?php
    while($nm=mysql_fetch_array($alln)){
    echo ("<option value=".$nm['name'].">".$nm['name']."</option>");  //works fine; all full names gets displayed
}
?> 
</select>

[victor_rambo]

Also, field names and array keys are case-sensitive. If your field name is 'add2', then $row['Add2'] will return NULL.
The best thing you can do here is:

PHP Code:

while($row=mysql_fetch_assoc($result)) { //codes to display the address
print_r($row);
} 


Try this thing: put the following code at the top of your script and see what it tells:

PHP Code:

<?php die(print_r($_POST));?>

[Sridhar_Rao]

OK, but the key issue here is...why is that full name is not being sent across the form? help me solve this puzzle.


This is the only I output I get

Array ( ) 1

[victor_rambo]

just to clarify, i hope ther are no quotation marks in the name!

Originally Posted by Sridhar_Rao

OK, but the key issue here is...why is that full name is not being sent across the form? help me solve this puzzle.


This is the only I output I get

you sure you used the print_r() function and not echo or print()?

[Sridhar_Rao]

The full name Anthony Gonzalves gets displayed in the list, when I select it and submit the form, I check what is being sent across.

$select=$_POST['select'];
echo ($select);

I get to see only Anthony. Why is this happening so? I even tried including mysql_real_escape_string but that didn't help.

Whether it is print or echo, there has been no change, I tried with both..yet no difference.

[victor_rambo]

Got the culprit:

PHP Code:

echo ("<option value=\"".$nm['name']."\">".$nm['name']."</option>"); 


You missed on the quotes that define that the value attribute.

[amitava82]

First of all, it's a very bad practice to use label as value for select or any other form elements. Use INT key. For example,

PHP Code:

<form action="<?php $_SERVER['PHP_SELF'];?>" method="get"><select name="select">
  <?php
do {  
?>
  <option value="<?php echo $row_Recordset1['id']?>"><?php echo $row_Recordset1['name']?></option>
  <?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
  $rows = mysql_num_rows($Recordset1);
  if($rows > 0) {
      mysql_data_seek($Recordset1, 0);
      $row_Recordset1 = mysql_fetch_assoc($Recordset1);
  }
?>

So, use unique ID field of your table and pass the ID as value to retrieve details from table. It should work.

And avoid echoing html.

[Sridhar_Rao]

Oh my goodness, such a petty mistake and hell lot of unwanted trouble. Thanks for pointing it out. Yeah, now it is fine.

By the way, are there any freeware PHP editors that can detect such syntax mistakes? I am using notepad++ to edit PHP files.

[victor_rambo]

^ most will not because of bakwards compatibility issues. hence, always validate your (X)HTML

[Sridhar_Rao]

OK, thanks.. thanks to amitava82 too for the good points.

One last question. How do I get to display the titles of the columns too. Say I have 15-20 columns besides the address. How do I display them with the results without hard coding all the column names?

[victor_rambo]

PHP Code:

$resource=mysql_query($sql);
$single_row=mysql_fetch_assoc();
$field_names_array=array_keys($single_row);//now iterate this array. 


You could also use mysql_list_fields() but it shall list all fields and their info, not just restricted to your selection.

[Sridhar_Rao]

Is there a possibility of selectively excluding certain fields?

[victor_rambo]

Yes, you can select names of those fields which you are selecting in your SELECT query using the 1st method I suggested. If yoy want to even filter these, then you must create a array of those to be excluded and then filter the main array.

[toofan]

Rohan and Amitava keep up the good work.

[jaya]

while($row=mysql_fetch_assoc($result)) { //codes to display the address
echo ($row['Add1']);
echo ($row['Add2]);
}

Quites missing in echo ($row['Add2]);