Help with my MYSQL Query

[smonkcaptain]

Hey all.

 

Below is the MYSQL query i'm using, however getting 'mysql_fetch_row(): supplied argument is not a valid MySQL result resource' error.

 

$sqllens = mysql_query("SELECT AND SHOW COLUMNS FROM `members` WHERE field=\'Lens\' AND username LIKE '$username'");

 

Could someone correct the Query to do the same thing, but stop the error?

[trq]

$sqllens = mysql_query("SELECT AND SHOW COLUMNS FROM `members` WHERE field='Lens' AND username LIKE '$username'");

[smonkcaptain]

I'm still getting the error. It's stating that the error is on the line

while ($row = mysql_fetch_row($sqllens))

from:

 

 <?php $sqllens = mysql_query("SELECT AND SHOW COLUMNS FROM `members` WHERE field='Lens' AND username LIKE '$username'");
while ($row = mysql_fetch_row($sqllens))
{
   echo '<select name="lens" style="margin-left:10px; width: 200px; font-size:11px"/>';
   echo "<option value=\"0\">Please Select...</option>";   
   foreach(explode("','",substr($row[1],6,-2)) as $v)
   {
     print "<option value=\"".$v."\"";
     if(isset($_POST['Lens']) && $_POST['Lens']==$v)
         print ' selected';
     print ">$v</option>";
   }
   echo '</select>';} ?>

[trq]

You need always check your query succeeded and returns some records before you try and use it. You can also catch any errors and debug them a little better.

 

<?php
$sql = "SELECT AND SHOW COLUMNS FROM `members` WHERE field='Lens' AND username LIKE '$username'";
if ($result = mysql_query($sql))) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_row($result)) {
      echo '<select name="lens" style="margin-left:10px; width: 200px; font-size:11px"/>';
      echo "<option value=\"0\">Please Select...</option>";   
      foreach (explode("','",substr($row[1],6,-2)) as $v) {
        print "<option value=\"".$v."\"";
        if (isset($_POST['Lens']) && $_POST['Lens']==$v) {
          print ' selected';
          print ">$v</option>";
        }
      }
      echo '</select>';
    }
  } else {
    // no results found.
  }
} else {
  trigger_error(mysql_error() . "<br />" . $sql);
}
?>

 

If your expecting more than one result your html is going to be foobar'd. If your not expecting more than one result, you don't need the while loop and should probably put a LIMIT clause in your query.

[smonkcaptain]

Thanks for the replies.

 

I've the code you've provided above, and i'm still getting this error:

 

Notice: 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 'AND SHOW COLUMNS FROM `members` WHERE field='Lens' AND username LIKE 'Jimmy Leam' at line 1

SELECT AND SHOW COLUMNS FROM `members` WHERE field='Lens' AND username LIKE 'Jimmy Leaman' in E:\domains\j\jimmyleaman.net\user\htdocs\photo_upload.php on line 449

 

There is meant to be more than one result.

 

This is grabbing 'SET' variables from my MYSQL table and displaying it in a drop down menu. It's searching the database of it 'Lens' field for the current member in session.

 

 

<?php
session_start();
$username = $_SESSION['username'];
?>

<?php
$sql = ("SELECT AND SHOW COLUMNS FROM `members` WHERE field='Lens' AND username LIKE '$username'");
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_row($result)) {
      echo '<select name="lens" style="margin-left:10px; width: 200px; font-size:11px"/>';
      echo "<option value=\"0\">Please Select...</option>";   
      foreach (explode("','",substr($row[1],6,-2)) as $v) {
        print "<option value=\"".$v."\"";
        if (isset($_POST['Lens']) && $_POST['Lens']==$v) {
          print ' selected';
          print ">$v</option>";
        }
      }
      echo '</select>';
    }
  } else {
    // no results found.
  }
} else {
  trigger_error(mysql_error() . "<br />" . $sql);
}
?>

 

[trq]

Sorry, I never really looked at the query obviously.

 

$sql = ("SELECT * FROM `members` WHERE field='Lens' AND username LIKE '$username'");

[smonkcaptain]

Hmmm i'm now getting this error:

 

Notice: Unknown column 'field' in 'where clause'

SELECT * FROM `members` WHERE field='Lens' AND username LIKE 'Jimmy Leaman' in E:\domains\j\jimmyleaman.net\user\htdocs\photo_upload.php on line 449

 

 

[smonkcaptain]

Without the

"username LIKE '$username'"

it works fine, however with it i'm getting the error posted above.

 

 

[fenway]

That's because you're using a reserved keyword as a field name -- don't do that.