ten120963 Posted July 29, 2012 Share Posted July 29, 2012 I have a database table with 32 records in it. I have written PHP code to cycle thru the records, and add the name field from each record into a pull down menu box. So far, I have tried 'select name from users order by name asc' and just 'select name from users', and each time, only 31 of the 32 names are displayed. Has anyone had this happen? $query = "SELECT name FROM users ORDER BY name ASC"; // run the query if ($r = mysql_query($query, $dbc)) { // retrieve every record and add to a dropdown box while ($row = mysql_fetch_array($r)) { print "<option value=\" {$row['name']} \"> {$row['name']} </option>"; } // query did not run } else { print '<p style="color: red;">Could not retrieve the data because:<br />' . mysql_error($dbc) . '.</p><p>The query being run was: ' . $query . '</p>'; } // end of query if print "</select></td>"; Link to comment Share on other sites More sharing options...
margaux Posted July 29, 2012 Share Posted July 29, 2012 Have you tried running this query directly through something like phpmyadmin to ensure that it is not one of your d/b records causing the problem? 1 Link to comment Share on other sites More sharing options...
ten120963 Posted July 30, 2012 Author Share Posted July 30, 2012 Sorry, I should have mentioned that in the first post. I did run it thru phpMyAdmin and Sequel Pro, and received all 32 records. Link to comment Share on other sites More sharing options...
Larry Posted July 30, 2012 Share Posted July 30, 2012 There's nothing obviously wrong in your code. How do you know only 31 of 32 items are being shown? Is the same one consistently not being shown or does it change? What value(s) isn't been shown? Link to comment Share on other sites More sharing options...
ten120963 Posted July 30, 2012 Author Share Posted July 30, 2012 It's always the first one that is not shown. If I sort by name, it's the first one alphabetically that is not shown. If I do not sort, then it's the first record in the database that is now shown. Link to comment Share on other sites More sharing options...
margaux Posted July 30, 2012 Share Posted July 30, 2012 There could be an issue with your html. I ran your code on one of my local databases and all records were returned. Presumably you've used print_r to see if all 32 rows are being returned? Link to comment Share on other sites More sharing options...
ten120963 Posted July 30, 2012 Author Share Posted July 30, 2012 How would I use print_r in the code above? I tried after the 'while ($row = mysql_fetch_array($r)) {' but did not see anything different on the page. Link to comment Share on other sites More sharing options...
margaux Posted July 30, 2012 Share Posted July 30, 2012 You could insert print_r($r); after your if statement which is a quick way to show how many rows are returned by your query. Link to comment Share on other sites More sharing options...
Larry Posted July 30, 2012 Share Posted July 30, 2012 You sure you don't have another one of these: $row = mysql_fetch_array($r) before the while loop? 1 Link to comment Share on other sites More sharing options...
ten120963 Posted July 31, 2012 Author Share Posted July 31, 2012 That could be it, because I do have two of '$row = mysql_fetch_array($r)' in my code. I am trying to create a form that has a table with a pull down menu of these 32 users, then a pull down menu with values in one row, then the same two pull down menus in the row below before i close the table. So, I am using the statement above two times to get the data twice. Would using $row2 and $q to get the data the second time solve the problem, or is there a more efficient way to get the data once to populate 2 different drop downs? Link to comment Share on other sites More sharing options...
margaux Posted July 31, 2012 Share Posted July 31, 2012 Everytime you call mysql_fetch_array, it returns the current row and moves its internal pointer to the next row. So the second time you use it, it will be starting at the second row. For your requirements, you could either repeat the d/b query or store the query results in an array which you can access as you need to e.g. $names = array(); // create a variable to hold the query results $query = "SELECT name FROM users ORDER BY name ASC"; $r = mysql_query($query, $dbc); while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) { $names[] = $row; // add the row in to the results array } Then you can loop through $names for your various dropdown menu options. 1 Link to comment Share on other sites More sharing options...
ten120963 Posted August 2, 2012 Author Share Posted August 2, 2012 My code: $names = array(); // create a variable to hold the query results $query = "SELECT name FROM users ORDER BY name ASC"; // run the query if ($r = mysql_query($query, $dbc)) { // retrieve every record and add to a dropdown box while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) { $names[] = $row; // add the row in to the results array } // query did not run } else { print '<p style="color: red;">Could not retrieve the data because:<br />' . mysql_error($dbc) . '.</p><p>The query being run was: ' . $query . '</p>'; } // end of query if mysql_close($dbc); // close the connection // set up combo box print '<tr><td>Name:</td><td><select name="name"'; foreach ($names as $num => $tname) { print "<option value=\"$num\">$tname</option>"; } print "</select></td>"; I get a dropdown box filled with the word 'Array' several times. Link to comment Share on other sites More sharing options...
HartleySan Posted August 2, 2012 Share Posted August 2, 2012 That's because with the way you've written your code, $names is an array of arrays (in other words, a multidimensional, or in this case, 2D array). The following code is grabbing multiple rows from the DB, which are each arrays in and of themselves: while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) { $names[] = $row; // add the row in to the results array } For example, if the first set of row values returned from the DB were ['Doug', 22, 'Chocolate chip'] and the second set of row values returned were ['Jenny', 19, 'Strawberry'], then your $names 2D array would have the following structure: $names[0][0] = 'Doug'; $names[0][1] = 22; $names[0][2] = 'Chocolate chip'; $names[1][0] = 'Jenny'; $names[1][1] = 19; $names[1][2] = 'Strawberry'; In that case, to actually output the names, which are the first values in each of the respective rows returned from the DB, you'd have to rewrite the line outputting the value to the HTML as follows: print "<option value=\"$num\">$tname[0]</option>"; Please note that this applies even when you only return one field/column from the DB (which seems to be the case with your SQL query), as $row is still an array (with only one element in it). 2 Link to comment Share on other sites More sharing options...
ten120963 Posted August 2, 2012 Author Share Posted August 2, 2012 Thanks to everyone for the great suggestions. I will try to figure it out from here. Link to comment Share on other sites More sharing options...
Recommended Posts