Page 1 of 1

get mysql results

Posted: Mon Nov 19, 2007 11:23 pm
by bimo
Let me preface this question by saying that I haven't tried to connect to a db in almost a year and have hardly been doing any php.

With that out of the way, I'm ashamed to admit it, but I can't find my results. I want to dynamically populate a select list. I'd like to know if anyone sees what this bonehead (me) is missing. Here's my code:

Code: Select all

<option id="gen_sel_def1" default="true">~ select ~</option>
				<?php
				$querySG = "SELECT * from genre_name order by genre_name_id";				
				$results4 = mysql_query($querySG) or die('Query failed: ' . mysql_error());
				$numRows = mysql_num_rows($results4);
					print("numrows = ".count($numRows));
				$resArray = mysql_fetch_array($results4);
				if ($numRows <= 0) {
					echo "<h1>No rows found, nothing to print so I am exiting</h1>";
					exit;
				}		
				else {	
					print_r($resArray);
					foreach($resArray as $option) {		
						print_r($option);
						print("<option value='".$option['0']." name='".$option['1']."'>".$option."</option>\n");
					}		
					print_r($subgenre);
					print("<option>count = ".count($subgenre)."</option>");
					/*while($subgenre) {
						print("<option value='".$subgenre['genre_name_id']." name='".$subgenre['name']."'>".$subgenre['name']."</option>\n");
					}*/
				}
				?>			
				
			</select>
The connect is farther up the page (do I have to reconnect/select the db?) and when I run the query through mysql querybrowser, it works perfectly.

Thanks,

b

Posted: Tue Nov 20, 2007 11:05 am
by pickle
Ok, a few things:
  • The code snippet you've shown doesn't have an opening <select> tag.
  • If you do have an opening <select> tag higher up, then anything you output between the opening & closing <select> tags will NOT be visible on the screen unless it's inside an <option> tag. For example:

    Code: Select all

    <select>
        Hi Mom!
        <option>
           Mom
        </option>
    </select>
    Hi Mom! won't be visible.
  • Your result set is $results4. You're looping through $resArray however.
  • You're trying to use $option both as a scalar value and as an array - that won't work well.
You'll want to re-organize your code to something like this (very pseudo-code)

Code: Select all

$results = query('blah blah');
if(num_rows == 0)
{
   //no rows
}
else
{
  //open select
  while($row = mysql_fetch_assoc($results))
  {
    //show option
  }
  //close select
}
Note I used mysql_fetch_assoc() not mysql_fetch_array(). mysql_fetch_assoc() allows you to refer to column names by name & will survive a DB column re-ordering.

Posted: Tue Nov 20, 2007 9:06 pm
by bimo
Thanks. That snippet is contained in a select tag. Maybe being overzealous in my attempt at brevity, I didn't include it. Sorry.

And I had tried mysql_fetch assoc, too. Thanks for the reorganization, though. After the holiday I will try it out.

b

Posted: Tue Nov 20, 2007 10:15 pm
by bimo
Thanks, I got it to work. geez, I must have been pretty tired. sloppy mistakes...