Drop Down Box display the first element of an array twice from an MySQL query...

[darkenroyce]

Hi Guys

I am creating a sports database and one of the features involves create drop down dialogue boxes that retrieve data from MySQL tables and provides them as <option> within <select></select>. I am using PHP for this and I'm fairly new at the programming language. I have got the dialogue to work with respect to retrieving the data from MySQL table but it only displays the data from first row/first column of the table twice. I want the foreach loop to iterate down the rows of the first column until it reaches the end of these rows in the MySQL table. Below is the code I used to do it. Please help me to find where I went wrong:

<td><select>
    <?php
 
    $conn = mysql_connect("localhost", "root") or die(mysql_error());
 
    //database selected
 
    mysql_select_db("database", $conn) or die(mysql_error());
 
    $state = "SELECT player_name FROM table1";
 
    $query = mysql_query($state, $conn);    
 
    $queryColumn = mysql_fetch_array($query);
 
    $aData = $queryColumn;
 
    foreach( $aData as $test ){
    echo '<option value="' . $aData . '">' . $test . '</option>';
    }
 
    ?>
    </select></td>
 

Thanks for your help much appreciated! I've been stuck on this for a while now.

[RoryClapham]

An easier way to do it would be to use a while loop to retrieve the information from the database such as:
[PHP]
<td><select>

<?php

$conn = mysql_connect("localhost", "root") or die(mysql_error());

//database selected

mysql_select_db("database", $conn) or die(mysql_error());

$state = "SELECT player_name FROM table1";

$query = mysql_query($state, $conn);

//Make sure the database connection worked and the $query variable has a value
if($query){
while($row = mysql_fetch_row($query){
//Use a while loop, while mysql_fetch_row can still fetch a new row do the following:
echo '<option value="' . $row[0] . '">' . $row[0] . '</option>';
//$row is set as an array with each index holding a column
}
}

?>
</select></td>
[/PHP]

[ronverdonk]

Hi Guys

I am creating a sports database and one of the features involves create drop down dialogue boxes that retrieve data from MySQL tables and provides them as <option> within <select></select>. I am using PHP for this and I'm fairly new at the programming language. I have got the dialogue to work with respect to retrieving the data from MySQL table but it only displays the data from first row/first column of the table twice. I want the foreach loop to iterate down the rows of the first column until it reaches the end of these rows in the MySQL table. Below is the code I used to do it. Please help me to find where I went wrong:

<td><select>
    <?php
    $conn = mysql_connect("localhost", "root") or die(mysql_error());
    //database selected
    mysql_select_db("database", $conn) or die(mysql_error());   
    $state = "SELECT player_name FROM table1";      
    $query = mysql_query($state, $conn);    
    $queryColumn = mysql_fetch_array($query);
    $aData = $queryColumn;
    foreach( $aData as $test ){
    echo '<option value="' . $aData . '">' . $test . '</option>';
    }
    ?>
    </select></td>
 

Thanks for your help much appreciated! I've been stuck on this for a while now.

The reason that your script goes wrong is that you:

1. fetch only one row $queryColumn from the result (you only have one mysql_fetch_xxx and not within a loop)

2. assign that row ($queryColumn) to another variable ($aData) and display the entire row (consists of 1 column only) as the option statement value and the content of the column the option statement text.

Ronald

[flexsingh]

The reason that your script goes wrong is that you:

1. fetch only one row $queryColumn from the result (you only have one mysql_fetch_xxx and not within a loop)

2. assign that row ($queryColumn) to another variable ($aData) and display the entire row (consists of 1 column only) as the option statement value and the content of the column the option statement text.

Ronald

Hello there,

I've been stuck on this problem to, could you see if im missing something in my code please. I put in the "while loop" but it returned me no values, and when I take it out I only get the first value and it repeats. I know I am close just something not there. If you could provide a example to your solution before that would be a huge help.

[PHP]<html>
<head>
</head>

<?php
$db_name="project"; // Database name
$tbl_name="bcourt"; // Table name

// Connect to server and select database.
$conn = mysql_connect($_SESSION['host'], $_SESSION['username'], $_SESSION['password'])or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
?>

<body background="main background1.jpg" link="blue" vlink="blue">

<table width="350" border="1" align="center" cellpadding="0" cellspacing="1">
<tr>
<td><form name="Update Network Gaming form" method="post" action="updatebcourt_ac.php">
<table width="100%" border="1" cellspacing="1" cellpadding="3">
<tr>
<td colspan="3" align="center"><strong><font face="sans-serif, Arial" font color="white">Insert Data Into mySQL Database </strong></td>
</tr>
<tr>
<td><font face="sans-serif, Arial" font color="white">Member No</td>
<td><font color="white">:</td>
<td><input name="Member_No" type="int" id="Member_No"></td>
</tr>
<tr>
<td><font face="sans-serif, Arial" font color="white">Court No</td>
<td><font color="white">:</td>
<td><input name="Court_No" type="varchar" id="Court_No" value="Bcourt"></td>
</tr>

<tr>
<td><font face="sans-serif, Arial" font color="white">Time Slot No</td>
<td><font color="white">:</td>
<td><select>

<?php
$db_name="project"; // Database name
$tbl_name="bcourt"; // Table name

// Connect to server and select database.
$conn = mysql_connect($_SESSION['host'], $_SESSION['username'], $_SESSION['password'])or die("cannot connect");
mysql_select_db("$db_name", $conn)or die("cannot select DB");

$time = "SELECT Time_Slot_No FROM $tbl_name WHERE Member_No = 'null'";

$query = mysql_query($time, $conn);

$queryColumn = mysql_fetch_array($query);

$slot = $queryColumn;

//while($queryColumn=mysql_fetch_array($query)){
foreach( $slot as $test ){
echo '<option value="' . $slot . '">' . $test . '</option>';
}
?>
</select></td>
</tr>

<td colspan="3" align="center"><input type="submit" name="Submit" value="Continue"></td>
</tr>
</table>
</form>
</td>
</tr>
</table>



</body>
</html>[/PHP]

I do not get any errors just duplicated values which in this case is "0". My timeslots are just "0,1,2,3,4". Even if I order in DESC order it still only shows "0".

Thank you in advanced for any help.

[ronverdonk]

flexsingh: do not hijack this thread!!
My comments on the OP's code also apply exactly to your code, so .... adapt it (why did you comment out the while statement, look at that).

Ronald

[flexsingh]

flexsingh: do not hijack this thread!!
My comments on the OP's code also apply exactly to your code, so .... adapt it (why did you comment out the while statement, look at that).

Ronald

Sorry didnt mean to do that, I didn't understand your responce. If you could just be alittle bit more clearer please, i'm still alittle new at the coding.

Sorry again.

[ronverdonk]

In this piece of code here:
1. You select from the database.
2. Then you fetch only 1 row from the result.
3. You assign the result row to another variable.
4. That 1 result is stored into 1 option statement, i.e. the result row $slot in the value part and the column $test in the text part of the option statement.
[php]
$time = "SELECT Time_Slot_No FROM $tbl_name WHERE Member_No = 'null'";
$query = mysql_query($time, $conn);
$queryColumn = mysql_fetch_array($query);
$slot = $queryColumn;
//while($queryColumn=mysql_fetch_array($query)){
foreach( $slot as $test ){
echo '<option value="' . $slot . '">' . $test . '</option>';
}
?>
</select></td>
[/php]You do not read and process any more rows from the database. So the above code should be something like[php]
$time = "SELECT Time_Slot_No FROM $tbl_name WHERE Member_No = 'null'";
$query = mysql_query($time, $conn); // do the query
while($queryColumn=mysql_fetch_assoc($query)){ // fetch each row in the result set
$val=$queryColumn['Time_Slot_No']; // make it simpler
echo "<option value='$val'>$val</option>"; // construct option statement
}
?>
</select></td>[/php]It is exactly as member RoryClapham alread said in an earlier post no 2 in this thread. but somehow that post was neither read nor followed.

Ronald

[flexsingh]

In this piece of code here:
1. You select from the database.
2. Then you fetch only 1 row from the result.
3. You assign the result row to another variable.
4. That 1 result is stored into 1 option statement, i.e. the result row $slot in the value part and the column $test in the text part of the option statement.
[php]
$time = "SELECT Time_Slot_No FROM $tbl_name WHERE Member_No = 'null'";
$query = mysql_query($time, $conn);
$queryColumn = mysql_fetch_array($query);
$slot = $queryColumn;
//while($queryColumn=mysql_fetch_array($query)){
foreach( $slot as $test ){
echo '<option value="' . $slot . '">' . $test . '</option>';
}
?>
</select></td>
[/php]You do not read and process any more rows from the database. So the above code should be something like[php]
$time = "SELECT Time_Slot_No FROM $tbl_name WHERE Member_No = 'null'";
$query = mysql_query($time, $conn); // do the query
while($queryColumn=mysql_fetch_assoc($query)){ // fetch each row in the result set
$val=$queryColumn['Time_Slot_No']; // make it simpler
echo "<option value='$val'>$val</option>"; // construct option statement
}
?>
</select></td>[/php]It is exactly as member RoryClapham alread said in an earlier post no 2 in this thread. but somehow that post was neither read nor followed.

Ronald

Im really sorry I must have missed it. Was just stressful and frustrating it didn't work after a long time. I'll read more carfully next time. Thank you for helping me and sorry again.

[ronverdonk]

Okay, does it work now as you want it to?

ROnald

[flexsingh]

Okay, does it work now as you want it to?

ROnald

Yes it does thank you for all the help :)

[ronverdonk]

You are welcome. See you again next time.

Now back to the original poster: darkenroyce. Sorry for the intrusion but did you also solve your problem?

Ronald