Select from database based on a mysql populated dropdown list

ElPed · January 7, 2012, 9:53am

Hello again,

I have managed to populate a dropdown list with information from one of my Tables on my MySQL database. Could anyone give me some advice on how to now select an item from the dropdown list and use it in a SELECT statemant? The code that populates the table is shown below:-

<?php      
            
$server="localhost";
$username="root";
$password="";
$link=mysql_connect($server, $username, $password) or die ("Cannot connect to mysql server: ".mysql_error());
            
$dbname = 'golf_society';
mysql_select_db($dbname, $link) or die ("Cannot connect to database: ".mysql_error());
            
$query="SELECT competitionname,id, competitiondate FROM competition order by competitiondate";

$result = mysql_query ($query);
echo "<select name=competitionname value=' '>";


while($drop=mysql_fetch_array($result)){

//data stored in $drop
echo "<option value=$drop[id]>$drop[competitionname] $drop[competitiondate]</option>";

}
echo "</select>";
// Close list box 
?>echo "</select>";
// Close list box 
?>

Thanks a lot

system · January 7, 2012, 10:08am

To some extent it depends on whether the <select> is part of a form submission or if you want an onchange event handler to send the selected option value to a server side script as an ajax request or not.

In either case, the principle is much the same. You need to send the selected option value as a GET or POST to a server side script which then uses the sent value as input to an sql select query. Your server side script then process the returned query results accordingly.

If this is for a real life application then you will have to handle the validation and security issues related to the sent value but for now you probably want to KISS :), so don’t worry about security/validation for now.

ElPed · January 7, 2012, 10:23am

Thanks for that Max,

I wanted to do something along the lines of:-

$result = mysql_query ("select * from competition where competitionname = ‘the selected value from the dropdown list’ ");

system · January 7, 2012, 10:29am

Yes that is the psuedo code for the eventual query, but how do you want to send the selected option value to the server side script? Is your<select> actually part of a form which is submitted to a server side script or do you want to send the selected value to the server side script as soon as an option is selected?

ElPed · January 7, 2012, 11:04am

I would like to click on the list - select a competition name and then that to return the other information from the table. I suppose what I’m saying is can this value be used directly in the SELECT statement,and if so how?

I can do it if I create a form and populate the form manually, but I can’t get it when the dropdown list is populated directly from MySQL:-(

Cups · January 7, 2012, 1:19pm


echo "<select name='competitionname'>";
foreach(mysql_fetch_array($result) as $drop){

//data stored in $drop
echo "<option value=" . $drop['id'] .">" . $drop['competitionname'] . " " . $drop['competitiondate'] . "</option>";

}
echo "</select>";

Try something like that, watch the quotes – php does not expand variables inside double quotes when the var is an array – hence many prefer to return an object instead.

mysql_fetch_object()


echo "<option value=$drop->id>$drop->competitionname $drop->competitiondate</option>";

or you will also see {} s used to clearly de-mark where vars start and end


echo "<option value={$drop->id}>{$drop->competitionname} {$drop->competitiondate}</option>";

ElPed · January 7, 2012, 2:50pm

Cups,

Thank you very much for your response,

When I use the existing code I get the following dropdown:-

Inserting your first code I get :-

For the 2nd and third examples of code I get a dropdown list with no content!

Cups · January 7, 2012, 5:46pm

What does the result of adding:


$rows = mysql_fetch_array($result);

var_dump($rows)

give you? Anything?

ElPed · January 8, 2012, 2:53pm

Sorry Cups, I’m affraid not.

I don’t want to take up too much more of your time but if you can bear with me:–

I have two files

tester.php - which populates the dropdown list from the database

index3.php - which tries to select all the competition details from the database

tester.php

<?php

$server=“localhost”;
$username=“root”;
$password=“”;
$link=mysql_connect($server, $username, $password) or die ("Cannot connect to mysql server: ".mysql_error());

$dbname = ‘golf_society’;
mysql_select_db($dbname, $link) or die ("Cannot connect to database: ".mysql_error());

$query=“SELECT competitionname, id, competitiondate FROM competition order by competitiondate”;

$result = mysql_query ($query) or die (‘error submitting’);

echo “<select name=‘competitionname’>”;
while($drop=mysql_fetch_array($result)){

//data stored in $drop
echo “<option value=$drop[id]>$drop[competitionname] $drop[competitiondate]</option>”;

}
echo “</select>”;
?>
<input type=“submit” value=“Submit”>
</form>
</body>
</html>

index3.php

?php
$competition = $_POST[‘competitionname’];

$server=“localhost”;
$username=“root”;
$password=“”;
$link=mysql_connect($server, $username, $password) or die ("Cannot connect to mysql server: ".mysql_error());

$dbname = ‘golf_society’;
mysql_select_db($dbname, $link) or die ("Cannot connect to database: ".mysql_error());

$result = mysql_query(“SELECT id, competitionname, competitiondate FROM competition where competitionname = ‘$competition’ order by competitiondate”);

echo “<table border=‘0’ align=‘center’>”;
echo “<tr><th>id</th> <th>name</th> <th>date</th> <th>start</th></tr>”;
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo “<tr><font size=‘2’><td align=‘justify’>”;
echo $row[‘id’];
echo “</td><td align=‘justify’>”;
echo $row[‘competitionname’];
echo “</td><td align=‘center’>”;
echo $row[‘competitiondate’];
echo “</td><td align=‘center’>”;
echo $row[‘starttime’];
echo “</td><td align=‘center’>”;

echo "&lt;/td&gt;&lt;/tr&gt;";

}
echo “</table>”;
?>

Basically tester.php acts as required and displays a dropdown list with the competitions listed, but when I select one and submit it, all that is displayed in index3.php is the table with no results.

It seems that the selected value is not getting passed to the index.php file!

Am I making a mistake with the <select name=‘competitionname’>

Cups · January 8, 2012, 4:06pm

Both files must be in the same folder in this case as the action of the form is “tester3.php” NOT “…/tester3.php” or anything like that.

Making a tester, smart move!

Even better, reduce your tester down and do some “debugging by checking” as you go.

In effect, you’d start with a blank script and write these code blocks one at a time proving that what you just wrote actually does work.

It is the only way to divide and conquer between errors in HTML (or JS) / PHP / MYSQL


<?php 

// is anything being passed to this page at all?
// if not it might be a badly formed HTML issue

var_dump($_POST);
echo '<hr />';


$competition = $_POST['competitionname']; 
echo $competition . '<hr />';


$server="localhost";
$username="root";
$password="";
$link=mysql_connect($server, $username, $password) or die ("Cannot connect to mysql server: ".mysql_error());

$dbname = 'golf_society';
mysql_select_db($dbname, $link) or die ("Cannot connect to database: ".mysql_error());


// build up your query string into a variable first
$qry = "SELECT id, competitionname, competitiondate FROM competition where competitionname = '$competition' order by competitiondate";

//then echo that variable, copy it paste it into your database and see if you have any matching data
echo $qry . ' <hr />';

$result = mysql_query($qry);

// keeps getting the next row until there are no more to get
// dont too much care about your html tables, do that kind of
// tidying up when you have the whole thing running smoothly

while($row = mysql_fetch_array( $result )) {

var_dump( $row );

} 
?>

ElPed · January 9, 2012, 2:55am

Thanks very much for that, I’ll give it a try.

Thanks for all your help.

ElPed · January 9, 2012, 5:12am

Cups,

Thanks a lot, I now havie it returning the required values.

Working from your last post I was able to see that it was the id of the Competition which was being sent to the index3.php page.

I altered my code to read:-

$result = mysql_query(“SELECT id, competitionname, competitiondate FROM competition where id = ‘$competition’ order by competitiondate”);

and it works.

I need to fiddle about now with the formatting etc. but once again thanks for all your hard work.

Cups · January 9, 2012, 10:19am

Great, glad we helped you out there.

To take my point on a stage, when developing something like that postback handler, always do as I say, move slowly proving that things are happening as you expect them.

I went through a stage where I had files develop with a debug flag at the top of them, it might help you…


<?php
// first line of your file
// easy to find and turn off

$debug = 1; // or 0 or false if you prefer

// some scripting


if($debug){
var_dump($_POST);
echo '<hr />';
}

// more scripting building up a query string and so on

if($debug){
var_dump($sql);
echo '<hr />';
}

// and so on

I mean, you will soon tire of it, but it does give you a bit of a “safety net” till you have the real basics nailed into your brain.