adding drop down lists in PHP..

chezzer · 19-07-2009 9:39pm #1

How do i add a drop down list on a webpage, where the values
are from a DB ...

i'm trying to use PHP ...

<HTML>
<HEAD>

<body background = "backgr.jpg">

<form action="add_match_DB.php" method="post">
<?php

$sql2 = mysql_query("SELECT Name from players");



?>

<select>
  <option> <?php mysql_fetch_assoc($sql2) ?></option>
 
</select>

ron_darrell · 19-07-2009 10:13pm

You've the right general idea just you need to tweak it slightly. The problem is simple enough. An option tag has text (so that the client can see what it is that they are choosing) and a value (which can be the same as the text or is some other unique piece of data that identifies the item the user has selected. This is the data that is sent to the server when the form is submitted).

<HTML>
<HEAD>
<body background = "backgr.jpg">
<form action="add_match_DB.php" method="post">
<?php
$sql2 = mysql_query("SELECT Name from players");
?>
<select id="myDDL" name="myDDL">
<?php
while($row = mysql_fetch_array($sql2)){
echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
} 
?>
</select>
<input type="submit" value="Submit" />
</form>

Try that and see how you get on

-RD

chezzer · 19-07-2009 10:40pm

Thanks a lot for the suggestion, still no joy that just gives a blank
result ...

I'm hacking here ... i need to get a good php coding book ...

Webmonkey · 19-07-2009 10:57pm

Are you sure you are getting results back from that query?

laugh · 20-07-2009 7:14am

Webmonkey wrote: »

Are you sure you are getting results back from that query?

I'm fairly novice myself but isn't the connection missing?

Webmonkey · 20-07-2009 9:10am

It is missing alright but I'd assume he has all that stuff done already, plus selecting the db. We'll see soon enough

chezzer · 20-07-2009 9:44am

Sorry lads ... yes the connection is there ... just not included in the code i posted .. and the Query does return data .. as i tested it in the SQL editor ... at work now .. ill post more later...

any recommendations on a good PHP book, i know there are good web tutorials but i prefer learning from books...

Webmonkey · 20-07-2009 9:48am

I would do a [php]echo "Number of Rows returned: ".mysql_num_rows($sql2);[/php] after you do the mysql_query just to confirm rows coming back because from what I can see from the code posted above, it should work.
You should turn on full error reporting as well - you never know, you might not even be successfully connecting to the database.

laugh · 20-07-2009 10:50am

chezzer wrote: »

any recommendations on a good PHP book, i know there are good web tutorials but i prefer learning from books...

http://www.sitepoint.com/books/phpmysql4/

If you like learning by example rather than theory.

chezzer · 20-07-2009 12:29pm

Thanks !! this book looks good ...

cheers..

chezzer · 20-07-2009 6:12pm

Webmonkey wrote: »

I would do a [php]echo "Number of Rows returned: ".mysql_num_rows($sql2);[/php] after you do the mysql_query just to confirm rows coming back because from what I can see from the code posted above, it should work.
You should turn on full error reporting as well - you never know, you might not even be successfully connecting to the database.

hmm ... so it can't regognise the data ... as it's giving me this when i try that ....

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\add_match.php on line 46
Number of Rows returned:

Webmonkey · 20-07-2009 6:14pm

It's probably not even getting connected to the database.

Have you tried a "or die("")" when you try connect and select the database.

I bet if you go view source you will see errors within the <select> tags too in the source but they don't render to the browser screen.

Try this directly after the mysql_query line:

[php]echo 'Error: '.mysql_error();[/php]

chezzer · 20-07-2009 9:54pm

oops!!!

you are right ... this outputs "No Database selected" ...

i left out ...

mysql_select_db('mysql',$con);

chezzer · 21-07-2009 8:24am

So now ...

<HTML>
<HEAD>
<body background = "backgr.jpg">
<form action="add_match_DB.php" method="post">
<?php
$sql2 = mysql_query("SELECT Id,Name from players");
?>
<select id="myDDL" name="myDDL">
<?php
while($row = mysql_fetch_array($sql2)){
echo "<option value=\"".$row[1]."\">".$row[1]."</option>";
} 
?>
</select>
<input type="submit" value="Submit" />
</form>

I have added 'Id' in the SELECT stmt, yet the name is still displayed
as i use the 2nd element in the array, im thinking
to send the Id (integer) value to the next page - through the submit button, would this be done through the 'name of the DDL'.row[0] ??
or similar ??

Webmonkey · 21-07-2009 8:34am

The ID there would be $row[0].

You should use the ID for the value of the option and the name as the user friendly name.
Only the value will be sent via the $_GET or $_POST. You can easily retrieve back the name by doing a SQL lookup, "SELET Name from players where ID = ".$_POST;

You should not be using actual names for doing anything besides displaying the option on screen. It should all be done via the unique ID of the row underneath.

chezzer · 21-07-2009 9:52am

Yes I have allready set up the DB and tables, tables are linked with keys (ints)
so as to make it more dynamic .. it was the php/HTML i was a little sketchy on ... the DB stuff is grand ...

Webmonkey · 21-07-2009 9:59am

sometimes it might be nicer to use mysql_fetch_assoc instead as you get an associtive array so you can go $row and $row - often easier to read too and less errors.

best luck with it.

chezzer · 21-07-2009 9:29pm

im passing the values , problem is they are the names ... .$_POST;

i want to post the Id's ....

Webmonkey · 21-07-2009 9:31pm

hmm. This should pass the value as the ID:

[php]
<HTML>
<HEAD>
<body background = "backgr.jpg">
<form action="add_match_DB.php" method="post">
<?php
$sql2 = mysql_query("SELECT Id,Name from players");
?>
<select id="myDDL" name="myDDL">
<?php
while($row = mysql_fetch_array($sql2)){
echo "<option value=\"".$row[0]."\">".$row[1]."</option>";
}
?>
</select>
<input type="submit" value="Submit" />
</form>
[/php]

I don't see how the actual names get passed instead if you use $row[0] for the value of the option.

chezzer · 21-07-2009 9:50pm

ok thanks ...

funny enough this wont return an id either ..

echo "Temp value :". $_POST['myDDL']; -- this displays name correctly

echo "Temp value 2 :". $_POST['myDDL2']; -- so does this...


$get_ID = "SELECT Id from tennis_players WHERE Name ='$_POST['myDDL']'";    -- this gives parse error..

echo "ID is : ". $get_ID;

the parse error is :
Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in C:\wamp\www\add_match_DB.php on line 23

Webmonkey · 21-07-2009 9:56pm

Try this

[php]
<HTML>
<HEAD>
<body background = "backgr.jpg">
<form action="add_match_DB.php" method="post">
<?php
$sql2 = mysql_query("SELECT Id,Name from players");
?>
<select id="myDDL" name="myDDL">
<?php
while($row = mysql_fetch_assoc($sql2)){
echo '<option value="'.$row.'">'.$row.'</option>';
}
?>
</select>
<input type="submit" value="Submit" />
</form>
[/php]

And for the other error if you like try the old way:

[php]$get_ID = "SELECT Id from tennis_players WHERE Name = '".$_POST."'";
[/php]

chezzer · 21-07-2009 11:12pm

ok all working ... now almost finished ...

but date is coming up as 0000

on the DB it is type date ... im sure it's some sort of
type mismatch with HTML ...

i have
<B>Date : </B> <input type="date" name="Date" /><br><br>

in the form for the date ...

Maybe it'll be best add a pop up calendar ...

Webmonkey · 22-07-2009 8:22am

chezzer wrote: »

ok all working ... now almost finished ...

but date is coming up as 0000

on the DB it is type date ... im sure it's some sort of
type mismatch with HTML ...

i have
<B>Date : </B> <input type="date" name="Date" /><br><br>

in the form for the date ...

Maybe it'll be best add a pop up calendar ...

The format of the date is probably YYYY-MM-DD - so you will have to enter it as this afaik. Easy enough to change around the format with PHP before inserting the data. MySQL has useful functions for changing the format of the date when doing a SELECT statement

chezzer · 22-07-2009 10:28am

hehe cheers .. i just read this on the mysql online docs....

yeah i can add a php function to mess around with the format...

cheers!

adding drop down lists in PHP..

Comments