Select Field Populated By MYSQL DB

[stevenmw]

I'm writing a script, and part of that script has to pull data out of a database and display it in an html select field.

Below is my code with. It all works fine. My issue is that instead of displaying all of the database table rows inside of ONE select field, a new select field is generated for every table row.

How can I alter the code to display all of the rows in ONE SINGLE select field? Any help is much appreciated.

PHP Code:

<?php
$username="...";
$password="...";
$database="...";
$host=".....";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM news_cats";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) {
$f1=mysql_result($result, $i, "cat");
echo "<select>";
echo "<option name=\"cat\">$f1</option>";
echo "</select>";
$i++;
}
?>

[BluePanther]

Originally Posted by stevenmw

I'm writing a script, and part of that script has to pull data out of a database and display it in an html select field.

Below is my code with. It all works fine. My issue is that instead of displaying all of the database table rows inside of ONE select field, a new select field is generated for every table row.

How can I alter the code to display all of the rows in ONE SINGLE select field? Any help is much appreciated.

PHP Code:

<?php
$username="...";
$password="...";
$database="...";
$host=".....";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM news_cats";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) {
$f1=mysql_result($result, $i, "cat");
echo "<select>";
echo "<option name=\"cat\">$f1</option>";
echo "</select>";
$i++;
}
?>

You've approached this...strangely. Why are you using mysql_result, instead of mysql_fetch_assoc with a while loop for example? Kind of like this:

PHP Code:

while($info = mysql_fetch_assoc($result)){
    // Do something for this row
} 


Means you don't have to keep a counter or anything. Also, it makes much more sense to just select cat in your query instead of retrieving all the fields for that table.

Regardless, your problem is your select tag echo is inside your while statement - so it gets echo'd every time you loop.

[stevenmw]

Thanks. I'll work on some revisions.

[stevenmw]

I came up with a very basic revision for test purposes.

PHP Code:

<?php
$username="...";
$password="...";
$database="...";
$host="....";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM news_cats";
$result=mysql_query($query);
echo "<select>";
while ($row = mysql_fetch_assoc($result)) {
echo "<option>";
echo $row["cat"];
echo "</option>";
}
echo "</select>";
?>

Works great, thanks.

[BluePanther]

Originally Posted by stevenmw

I came up with a very basic revision for test purposes.

PHP Code:

<?php
$username="...";
$password="...";
$database="...";
$host="....";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM news_cats";
$result=mysql_query($query);
echo "<select>";
while ($row = mysql_fetch_assoc($result)) {
echo "<option>";
echo $row["cat"];
echo "</option>";
}
echo "</select>";
?>

Works great, thanks.

I would suggest changing your query to SELECT cat FROM news_cats - no need to retrieve all the extra information if all you're using is the one field. Always select the fields you're going to use, and only use the * if you're going to use every single one

[stevenmw]

Thanks for the tip. I'm going to be using the id field soon so I just called it all at once.

Thanks again.

[BluePanther]

Originally Posted by stevenmw

Thanks for the tip. I'm going to be using the id field soon so I just called it all at once.

Thanks again.

SELECT id,cat FROM news_cats