Turning a mysql column into an array and using the array in a dropdown

Question

Long time listener, first time caller. I'm having trouble with pulling a column called "Rep_IP" from a mysql table called "roster", turning it into an array, and then using that array to populate a dropdown in html. I've tried several suggestions listed here as well as other places and I'm not having any luck. The page shows up just fine but the dropdown has no options to select. I figured I would see if one of you could tell me what I am doing wrong here.

<html>
<body>

<form action="insert.php" method="post">
<p>Rep ID:</p>

<?php
$con = mysql_connect("localhost", "root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("rep_stats", $con);
$query = "SELECT Rep_ID FROM roster";

$result = mysql_query($query) or die ("no query");

$result_array = array();
echo "$query"
while($row = mysql_fetch_assoc($result))
{
$result_array[] = $row;
}
?>
<select name="Rep_ID">
<?php

foreach($result_array as $rep)
{
echo "<option value=" . $rep['Rep_ID'] . ">" . $rep['Rep_ID'] . "</option>";
}   

?>
</select>
Issues Handled: <input type="number" name="IssuesHandled">
Hours Worked: <input type="number" step="any" name="HoursWorked">
<input type="submit">
</form>

</body>
</html>

As you can see, the drop down is part of a form that is used to create an entry in a new table as well. I don't know if that makes a difference but I figured I would point it out.

1. A semicolon is missing at the end of line echo "$query". 2. Putting values in an array is an unnecessary step. Render your options while traversing the resultset like Ian suggested. And please, don't use mysql_* functions for new code. They are are deprecated as of PHP 5.5.0. Instead learn about prepared statements and use either PDO or MySQLi. — peterm
– peterm, Commented Jan 20, 2013 at 17:38
Personally, I like to pretend that the guy doing the mysql and the guy doing the php are two separate people. The mysqler simply hands the phper an array that they can use in whatever they like. So for me, the array building step's perfectly fine. — Strawberry
– Strawberry, Commented Jan 20, 2013 at 18:41
Start with adding a ; after this line echo "$query". B.T.W. why would you like to echo the query. Or is this just to test the code? I think that this will result in trouble for your FORM tag. — Mr. Radical
– Mr. Radical, Commented Jan 20, 2013 at 18:52

Insyte · Accepted Answer · 2013-01-20 17:30:54Z

1

Try this.

<select name="Rep_ID">
  <?php
  while($row = mysql_fetch_assoc($result))
  {
      echo "<option value=" . $row['Rep_ID'] . ">" . $row['Rep_ID'] . "</option>";
  }
  ?>
  </select>

edited Jan 20, 2013 at 17:30

answered Jan 20, 2013 at 17:18

Insyte

2,2382 gold badges20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mr. Radical · Accepted Answer · 2013-01-20 22:46:56Z

0

Try this:

<?php


function select_list(){
$host = "host";
$user = "user";
$password = "password";
$database = "database";

$link = mysqli_connect($host, $user, $password, $database);
IF (!$link)
{
echo ('Could not connect');
}
ELSE {
$query = "SELECT Rep_ID FROM roster";

$result = mysqli_query($link, $query);

while($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
    echo "<option value=" . $row['Rep_ID'] . ">" . $row['Rep_ID'] . "</option>";
}  

}
mysqli_close($link);
}

$begin_form = 
<<< EODBEGINFORM
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>Insert data </title></head>
<body>

<form action="insert.php" method="post" name="form1">
<p>Rep ID:</p>

<select name="reps" form="form1">
EODBEGINFORM;


$end_form =
<<< EODENDFORM
</select><br>
Issues Handled: <input type="text" size="12" name="IssuesHandled"><br>
Hours Worked: <input type="text"  size="12" name="HoursWorked"><br>
<input type="submit" name="submit" value="submit">
</form>
</body>
</html>
EODENDFORM;


echo $begin_form;
echo select_list();
echo $end_form;
?>

You will notice that I have used MYSQLI_ istead of MYSQL_ the reason is that this better for new code, see the comments above.

I debugged your code and ran I to a lot of problems.:-( The main problem was:
echo "$query" Your forgot the semicolon at the end of the line.

Good luck with you project.

edited Jan 20, 2013 at 22:46

answered Jan 20, 2013 at 22:15

Mr. Radical

1,8381 gold badge20 silver badges30 bronze badges

2 Comments

Kevin Over a year ago

Thanks. This worked perfectly. As for all the errors, I am a noob to mysql and I appreciate the constructive feedback. I'm changing all my other code to mysqli now.

Mr. Radical Over a year ago

@Kevin I am glad that I have been able to help you. Could you accept my answer by marking it with a check mark. You can find it on the left side of my answer. Thank you in advance.

Collectives™ on Stack Overflow

Turning a mysql column into an array and using the array in a dropdown

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related