Trying to populate a drop-down list with PHP & MySQL, mysql_fetch_assoc() error

Question

I am trying to get a drop-down list to display column data containing countries ('level_4') but takes the value of the primary key ('id') for the form submission. I am migrating from an openoffice base form, so I've already written a working sql query. This is my attempt to migrate to a webform, and I'm having difficulty with PHP syntax.

35<li>
36      <?php 
37      $server="********";
38      $username="********";
39      $password="********";
40      $database="mtmg";
41      
42      $connection = mysql_connect($server, $username, $password) or die('Could not connect'.mysql_error());
43      mysql_select_db($database, $connection) or die("Cannot select db.");
44      
45      $sql="SELECT 'level_4','id' FROM 'mtmg'.'geography'";
46      $result=mysql_query($sql, $connection);
47      
48      echo '<label for="geography">Geography</label>';
49      echo '<select  id="geography" name="geography">';
50      
51      while ($row = mysql_fetch_assoc($result)) {echo '<option value="'.$row['level_4'].'">'.$row['level_4'].'</option>';}
52      echo mysql_error();
53
54      echo '</select>';
55      ?>
56</li>

Nothing happens on the web form, but I get the following message in the source code:

<li> 
        <label for="geography">Geography</label><select  id="geography" name="geography"><br /> 
<b>Warning</b>:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in <b>/f5/user_name/public/index.php</b> on line <b>51/b><br /> 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''mtmg'.'geography'' at line 1</select>       </li>

What am I doing wrong?

Isn't the table empty? Try mysql_num_rows() to check the results. — Damien
– Damien, Commented May 20, 2011 at 13:28

moteutsch · Accepted Answer · 2011-05-20 13:26:00Z

1

The error is saying that your query is bad. Change

$sql="SELECT 'level_4','id' FROM 'mtmg'.'geography'";

to

$sql="SELECT level_4, id FROM geography";

answered May 20, 2011 at 13:26

moteutsch

3,8334 gold badges31 silver badges35 bronze badges

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

Comments

Rob Bailey · Accepted Answer · 2011-05-20 13:26:01Z

0

I think your quotes are wrong. You should either be using the backtick (`) when specifying your database tables and columns or nothing:

45      $sql="SELECT `level_4`,`id` FROM `mtmg`.`geography`";
46      $result=mysql_query($sql, $connection);

If that doesn't solve the problem, try creating the database connection and then just making sure there is something coming out of your result:

47      die("<pre>".print_r($result)."</pre>");

hope that helps.

answered May 20, 2011 at 13:26

Rob Bailey

1,79715 silver badges19 bronze badges

Collectives™ on Stack Overflow

Trying to populate a drop-down list with PHP & MySQL, mysql_fetch_assoc() error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related