Query returns nothing in page, need help with proper code.

[DruX]

(MySQL version is 5.1.30)

 

I am trying to set up a page that will show my website user's "favorite foods" to them, of which are stored in the database. I need the page to print all rows from the table that apply to the logged in user.

 

Here is my test table, it is called "foods". For example, if "tom" is logged in, show a list of all his "food" items.

 

I am trying to use this code to display the list in the user's page:

<?php 
$id = $_SESSION['username'];
$foods_query = mysql_query('SELECT food FROM foods
  WHERE username = $id'); 
echo $foods_query; 
?>

When I test, it returns blank. No message, no error. As if there is nothing in the table.

Is there a specific code I have to enter in order to print to the page? Or is this code all wrong?

 

-DruX

 

[jackpf]

How's it going to give you an error when you've got no error handling?

 

Put or die(mysql_error()) after the query.

[DruX]

Alright, it says:

Unknown column '$id' in 'where clause'

I also tried to enter the username in the code, "tom":

<?php 
$foods_query = mysql_query('SELECT food FROM foods
  WHERE username = acid'); 
echo $foods_query; 
?>

Returns same result, so it should not a problem with $_SESSION['username'].

 

-DruX

[jackpf]

Try putting quotes around it

[trq]

Variables are not interpolated within single quotes.

 

<?php 
$id = $_SESSION['username'];
$foods_query = mysql_query("SELECT food FROM foods
  WHERE username = '$id'"); 
echo $foods_query; 
?>

[jackpf]

Oh yeah, didn't even notice that lol. Time for jack to go to bed...

[DruX]

I think this was definitely a step in the right direction...

 

Tried both

<?php $id = $_SESSION['username']; $foods_query = mysql_query("SELECT * FROM foods
  WHERE username = '$id'") or die(mysql_error()); echo $foods_query; ?>

and

<?php $id = $_SESSION['username']; $foods_query = mysql_query('SELECT * FROM foods
  WHERE username = "$id"') or die(mysql_error()); echo $foods_query; ?>

They both echo "Resource id #6 ". This is a new one for me  :-\

 

I think its something more in depth, but at least its printing words now 

 

-DruX

[jackpf]

Yeah, that's not how you retreive stuff from a db. The variable containing the query is just a resource pointer.

 

You may want to look up a tutorial or something.

 

You need to call the results with something like mysql_fetch_array().

[trq]

A simple example.....

 

<?php 
$id = $_SESSION['username'];
$sql = "SELECT food FROM foods WHERE username = '$id'";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result) {
    $row = mysql_fetch_assoc($result);
    echo $row['food'];
  } else {
    echo "No match found";
  }
} else {
  // query failed.
  // handle error
} 
?>

[DruX]

<?php 
$id = $_SESSION['username']; 
$result = mysql_query("SELECT * FROM foods
WHERE username = '$id'") or die(mysql_error()); 
$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['food']. " - ". $row['category'];
?>

returns "peaches - fruits"

This code seems to like me...somewhat. Now I just need it to print all the rows for the username, not just the first one it finds in the DB. Like this for user "acid":

peaches - fruits

bananas - fruits

 

-DruX

 

P.S. Also tried mysql_fetch_assoc($result), but I couldn't get anything but a parse error.

[trq]

If your expecting more than one result you need a loop.

 

<?php 
$id = $_SESSION['username'];
$sql = "SELECT food, category FROM foods WHERE username = '$id'";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result) {
    while ($row = mysql_fetch_assoc($result)) {
      echo $row['food'] . ' - ' . $row['category'];
    }
  } else {
    echo "No match found";
  }
} else {
  // query failed.
  // handle error
} 
?>

 

Trust me. You should get in the habbit of checking results first and also trapping errors.