reusing mysql query results

mark1000

I'm creating a data entry form where a user can submit multiple entries. Each entry uses a pulldown menu. I'm using a MySQL table to store options for a pulldown menu in each row of the form. However, I only seem to be able to use the results of the table query to populate the first pulldown menu. The subsequent pulldown menus are not populated. I've tried using the reset() command, but I get an error.

Help? I assume that a pointer is moving to the end of the query results, but I don't know how to reset it to the beginning. I really hope I don't have to run the query multiple times.

The relevant code snippets look like this:

$querycompanies = "Select * from companies";
$resultcompanies = mysql_query($querycompanies);

.....table and form header information.......
print "<OPTION value=\"\"></option>\n";
while ($row = mysql_fetch_array($resultcompanies))
{
print "<OPTION value=\"$row[id]\">$row[company]</option>\n";
}

print "<OPTION value=\"\"></option>\n";
while ($row = mysql_fetch_array($resultcompanies))
{
print "<OPTION value=\"$row[id]\">$row[company]</option>\n";
}

........table and form closing information.........

In this example, the first pulldown is populated, and the second one isn't.

I've done numerous Google and PHPBuilder searches, and I've looked through the PHP books I have, but I can't find what I'm looking for.

Thanks!

Mark

steadyguy

Since mysql_fetch_array() returns both and indexed and associative array, you can use both the extract() technique, or for/foreach.

$results = mysql_fetch_array($resultHandle);
$rows = mysql_num_rows($resultHandle);
print '<select name="Choices">';
for ($i = 0; $i < $rows; $i++) {
    print "<option value=\"$rows[$i]\">$rows[$i]</option>";
}
print '</select>';
//and again!
print '<select name="Choices">';
foreach ($rows as $value) {
    print "<option value=\"$value\">$value</option>";
}
print '</select>';

mark1000

Hi, and thanks for replying!

If I move your result line into the loop, like this:
$rows = mysql_num_rows($resultcompanies);
for ($i = 0; $i < $rows; $i++) {
$result = mysql_fetch_array($resultcompanies);
print "<option value=\"$result[0]\">$result[2]</option>";
}

The code works, but only once. (I couldn't get it to work the way you had it. Maybe I did something wrong?) As I have it now, there is the same problem as before. I should point out that while I only have two pulldown menus in my example, there may be a dozen or so in the real app, so using the index one time and the values a second time still leaves me with the same problem.

The table I'm querying looks like this:

id company
1 A Company Name
2 Another Company Name
3 A Third Company Name

The table id is being used as a foreign key in another table, so I need the id field to populate the "value" area and the company name to populate the "display" area of each entry in the pulldown.

Any idea why I can't just reset the array? I get an error that says "Passed variable is not an array or object."

Any help would be appreciated. Or, am I going about this the wrong way?

Thanks!

Mark

mark1000

OK, after rephrasing my own question a couple of times in the search box, I found a post that answered it. The command I've been looking for is

mysql_data_seek ($mysqlresultid, 0)

Thanks to Peter Lovatt for posting this answer a couple of years ago. 🙂

Thanks, all.

Mark