mltple table query Error: Query was empty

[hgreenesmith]

Hi,

I'm working on a select statement to retrieve data, which I know(data exists) is in the MySQL DB.

Here is the part I'm working on:

function funGetRecipeIngredientsAsTable() {
 
$recipeID = $_SESSION['sesRecipeID'];
 
$sqlString = "SELECT u.unitName, m.measureAbbrev, i.ingredientName
FROM Recipe r, RecipeIngredients ri, Units u, Measurements m, i.Ingredients
WHERE r.recipeID = ri.recipeID
AND ri.unitID = u.unitID
AND ri.measureID = Measurements.measureID
AND ri.ingredientID = i.ingredientID
AND ri.recipeID = ".$recipeID.";";
 
$ingredientDataResult = mysql_query($sqlString);
 
echo "sql string is: ".$ingredientDataResult ."<br/>";
echo "recipe ID is: " . $recipeID ."<br/>";
 
if (!mysql_query($ingredientDataResult)) {
  die('Error: ' . mysql_error());
  }

(have some other code here, but that's not the issue)
I have a connection to the DB because all my other functions are working

When I run the function I get this:

sql string is:
recipe ID is:
Error: Query was empty

It's acting as if it doesn't know what my $recipeID is, and it also isn't telling me what the value of $sqlString is either. I have set recipeID session variable on another page, and on this page I'm retrieving it, yet it's empty.

I've played with the last where statement so many times, maybe I've ended up with something totally wrong. When I test it in MySQL, I replace all the variable stuff with a number and it retrieves all the data I need to see.

Hoping someone can help me.

[Atli]

Hey.

Are you sure you have started the session on that page (with session_start)?
In any case, you should verify the ID before trying to put it into the query. You can use the isset function on the $_SESSION['sesRecipeID'] element to do that.

and it also isn't telling me what the value of $sqlString is either

You are printing the wrong variable. Your echo is printing the result of the mysql_query call, rather than the query string, which will never print anything other than FALSE or a resource ID.

[hgreenesmith]

Thank you for responding! Yes, I have the session_start at the top of my page.

I just did an isset and it returned my else statement, so that's one issue I will have to check.

As far as printing the wrong variable... What else can I print? I want to see the results, not the actual string. If I put the $sqlString in there all I'm going to get is the select statement.

Am I not thinking clearly? From my code, can you tell me what to print out? I'm confused.

Thank you again for your help! : )

[Atli]

Ahh I see.

You need to use the mysql_fetch_array, or one of it's siblings, to fetch the data from the result set. The mysql_query function only returns a resource ID; a pointer to a location where the actual data MySQL returned is stored. To fetch it, you need the mysql_fetch functions.

For example, to simply dump the data from a query into a table:

<?php
mysql_connect(...);
mysql_select_db(...);
 
$sql = "SELECT `col1`, `col2` FROM `myTable`";
$result = mysql_query($sql) or die(mysql_error());
 
echo '<table>';
while($row = mysql_fetch_assoc($result)) 
{
    echo '<tr>';
    echo "<td>{$row['col1']}</td>";
    echo "<td>{$row['col2']}</td>";
    echo '</tr>';
}
echo '</table>';
 
mysql_free_result($result);
mysql_close($result);
?>

[hgreenesmith]

actually that is very similar to what I already have. Maybe I have something out of order... after my if/die statement I wrote this, but since it wasn't even pulling anything, I assumed it was higher up in my select statement.

after my select statement my intent is to set the variables and then dump them into the table which already exists. I'm calling the function at the bottom of that table just before the closing tag to make the table grow as ingredients get entered and saved in the DB. That's why you don't see table tags in this code.

//SET SESSION VARIABLES
$_SESSION['sesUnitName'] = $unitName;
$_SESSION['sesMeasureName'] = $measureAbbrev;
$_SESSION['sesIngredientName'] = $IngredientName;
//$_SESSION['recipeID'] = $recipeID;
 
 
$ingredientDataResult = mysql_query($sqlString);
 
echo "sql string is: ".$sqlString ."<br/>";
 
//echo "recipe ID is: " . $recipeID ."<br/>";
 
if (!mysql_query($ingredientDataResult))
  {
  die('Error: ' . mysql_error());
  }
 
echo "<tr>Ingredients Added</tr>
<tr>
<th colspan='2'></th>
<th>Unit Name</th>
<th>Measurement</th>
<th>Ingredient</th>
</tr>";
 
while($row = mysql_fetch_array($ingredientDataResult))
  {
  echo "<tr>";
  echo "<td  colspan='5'>You have added: </td>";
  echo "<td>" . $row['sesUnitName'] . "</td>";
  echo "<td>" . $row['sesMeasureName'] . "</td>";
  echo "<td>" . $row['sesIngredientName'] . "</td>";
  echo "</tr>";
  }

So for my original thought in this reply; do I have this out of order? By the way, I've tried both the fetch_array and the fetch_assoc and neither did any good.

[Atli]

The only problem in there (assuming the $sqlString variable is defined earlier) is the IF clause on lines #14-17. You are doing a mysql_query call on the return value of a previous mysql_query call, which will always fail.

If your intent is to verify that the first call returned a valid result, then you can just remove the second call and just test the variable itself.

if (!$ingredientDataResult) {
    die("Error: " . mysql_error());
}

A mysql_query call always returns either FALSE or a resource ID. A resource ID is evaluated as TRUE, so this test will return FALSE (and thus execute the die command) only if the query failed.

[hgreenesmith]

Ok. I've just redone the or die method and now I finally have a clue. This is my new error.
mysql Error: 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 '' at line 7

It seems my syntax is either wrong or it's misleading because it doesn't know what else to say. So it's one of two things or both.
1-get the session variable working
2-syntax is wrong on the last line of the select statement.

I have a really odd question.

My recordID is set up as a $recipeID = "{$_GET["recipeID"]}";

If I've set it up as this, can I turn around and set it as a session variable on another page? The reason I'm asking is because on another page it tells me what the recordID is, but on this php page where I'm trying to retrieve it as a session variable and it won't.

[Atli]

Try putting this in a new PHP document in the same directory your other PHP files are, and check to see if it prints you session variable.

<?php
session_start();
header('content-type: text/plain');
print_r($_SESSION);
?>

Note, this works best in one of the modern browsers. (Firefox/Chrome/Opera... basically everything except IE.)

If that doesn't print your sesRecipeID variable, can you post the code that sets the variable?

[hgreenesmith]

I am using the latest v of firefox at the moment and after trying your example I got three errors. could not send session cookies(which I don't have any) could not send session cache limiter. Both of these are referencing line 1
line 2 was Cannot modify header information - headers already sent

//Set the variable
$_SESSION['sesRecipeID'] = $recipeID;
 
//retrieve session variables for loginID, recipeID, recipeTitle
$recipeID = $_SESSION['sesRecipeID'];
$recipeTitle = $_SESSION['sesRecipeTitle'];
 
//echo '<br>Record inserted, ID:  ' .$recipeID;
// Redirect back to the recipe page
 header("Location: addRecipe.php?recipeID=$recipeID");

This code is on a separate php for processing just the id and the title, it then redirects back to the page where data gets entered.

$recipeID = "{$_GET["recipeID"]}";
$recipeTitle = "{$_GET["recipeTitle"]}";
 
//generated code from start logic to connect to db
$link = mysql_connect('hgreenesmith1.startlogicmysql.com', 'hgreenesmith1', 'heather1!'); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 
mysql_select_db(recipes);
$sqlString = "SELECT recipeTitle, categoryID, temp, bakeTime 
                            FROM Recipe
                            WHERE Recipe.recipeID = $recipeID";
 
if(!$_GET["recipeID"] == ""){
$recipeTitleQuery = @mysql_query($sqlString);
$rows = mysql_fetch_array($recipeTitleQuery);
}
    if($rows >= 0){
        $recipeTitle = $rows[0];
        $categoryID = $rows[1];
    }
    else {
        $recipeTitle = "txtRecipeTitle";
        $categoryID = "selCategories";    
    }
 
 

These two groups of code work just fine.

I really appreciate all your help. Thank you!

[Atli]

I am using the latest v of firefox at the moment and after trying your example I got three errors. could not send session cookies(which I don't have any) could not send session cache limiter. Both of these are referencing line 1
line 2 was Cannot modify header information - headers already sent

That means there was something before the code I posted. It has to be the only thing in the output. Even a white-space before the <?php tag will result in this error.

[hgreenesmith]

So sorry about that. I was so tired, I never noticed that the beginning of the tag had a space in front of it.

Here is what the test page says.

Array
(
)

I've never used that code before, so I don't know exactly what it's doing, but I'm guessing that IF there is anything in a session, my loginID should have at least been there...

I went back real quick and created three variables and then I set them. When I viewed this page again, I got this:

Array
(
    [sesLoginName] => Heather
    [sesRecipeID] => 1
    [sesRecipeTitle] => Potato Soup
)

Is this how it should look?
I then went to another page to see if I could retrieve these and when I echo them, they are all empty. I'm not able to retrieve their values for some reason. I will continue googling. I'm sure I'm missing some little key element I've forgotten to do.

[hgreenesmith]

Since I now know the issue is not mysql, and actually a php challenge, this subject can be closed/resolved. I am working on making a class that will house the session variables and I will make that page required on all the other pages. Once I get that done, if I run into any problems I might post another thread under the php heading.

Thank you again for all your help!