...

View Full Version : Select Field Populated By MYSQL DB



stevenmw
01-22-2012, 05:13 AM
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
$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
01-22-2012, 05:20 AM
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
$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:


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
01-22-2012, 05:32 AM
Thanks. I'll work on some revisions.

stevenmw
01-22-2012, 06:38 AM
I came up with a very basic revision for test purposes.



<?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
01-22-2012, 07:22 AM
I came up with a very basic revision for test purposes.



<?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
01-22-2012, 09:00 AM
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
01-22-2012, 09:58 PM
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

:thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum