Session in WHERE clause

[davidcriniti]

Hi everyone,

 

Not sure if the error is with the php or the mysql but something is not working in a query I have to try and determine if a given user has attained a badge in a website I'm developing for my school.

 

I've created the variable $badge_id and now I"m looking to find if the user has that badge. The user is defined by the session - $_SESSION['member_id'] -> but I'm not sure if that can be used in the WHERE clause?

 

Here is my code. Any advice is appreciated greatly.

 

Thanks,

Dave

 

 $badge_acquired_yes_no = mysql_query("SELECT * 
FROM tbl_badges_acquired, tbl_badges WHERE $badge_id = tbl_badges_acquired.badge_id &&  member_id =" . $_SESSION['member_id'] . "")
or die(mysql_error());



while($row1 = mysql_fetch_array($badge_acquired_yes_no))


{
$badge_image = $row1['badge_image'];



}

[Jessica]

Did you get a mysql error? Did you echo the query and run it in mysql? What makes you think it's not working?

 

PS: MySQL uses AND. 

[davidcriniti]

Hi Jessica,

 

To add some context to my initial explanation, the badges are associated with quizzes in the database.

So some quizzes have badges and some don't.

 

If I set $quiz_id to a quiz which has a badge, I get no error. However, if I set $quiz_id to a quiz which doesn't have a badge. I get the following 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 '= tbl_badges_acquired.badge_id AND member_id =1' at line 2

 

Here is all the code I have on my page:

 

 

<?php
session_start();
if (empty($_SESSION['userName'])){
header("Location: login_required.php");
die();
}else{
}

?>

 

 

<?php


include 'mysql_connect.php';


 ////////////SEE IF USER HAS BADGE

   $quiz_id = 3454;





$badge_info = mysql_query("SELECT quiz_id, badge_id, badge_name, badge_criteria, badge_image, badge_message 
FROM tbl_quiz_titles, tbl_badges WHERE $quiz_id = badge_quiz_id ORDER BY badge_id DESC LIMIT 0,1")
or die(mysql_error());



// keeps getting the next row until there are no more to get


$badge_rows = mysql_num_rows($badge_info);


while($row = mysql_fetch_array( $badge_info )) {

      $badge_name = $row['badge_name'];
     $badge_criteria = $row['badge_criteria'];
  $badge_id = $row['badge_id'];


echo $row['badge_id'];
 echo $row['badge_name'];

}


   $badge_acquired_yes_no = mysql_query("SELECT * 
FROM tbl_badges_acquired, tbl_badges WHERE $badge_id = tbl_badges_acquired.badge_id AND  member_id =" . $_SESSION['member_id'] . "")
or die(mysql_error());



while($row1 = mysql_fetch_array($badge_acquired_yes_no))


{
$badge_image = $row1['badge_image'];



}

 ///////

  echo  $row1['badge_image'] ;


echo $_SESSION['member_firstname'] ;


echo $_SESSION['member_id'];



  ?>

[Jessica]

Well. Did you READ the error?

[davidcriniti]

Yes, but I'm not sure how to fix the syntax?

[Jessica]

Don't put an empty variable in your SQL.

 

If you echo the entire SQL string you would see where it's wrong.

[davidcriniti]

A question then about "Don't put an empty variable in your SQL".

 

Currently, with this page in test mode, I'm just playing around with different variables.

$quiz_id = 4, for example, doesn't provide the error, because that quiz id has a badge associated with it.

 

However, when this page gets to a stage where it's live, some quizzes will have a badge associated with them, and others won't.

 

I guess they would then be empty variables?

 

If that is the case, should I then encase the whole $badge_acquired_yes_no mysql statement in an if statement??

 

eg: If $quiz_id = "" { ....

 

?

[Jessica]

What makes sense logically?

[davidcriniti]

well my previous post seemed to be logical to me, aside from the fact I'd said '$quiz_id = "... when it should have read "$quiz_id !=" ...

 

....so I tried that code, and ended up with a similar problem.

i) When I use a quiz_id that is matched to a badge id, it works ok, except the badge name is not echoed.

ii) When I use d a quiz_id that is not matched to a badge id, I get the following 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 '= tbl_badges_acquired.badge_id AND member_id =1' at lineI'm not sure where

 

I've tried to rejig the syntax numerous different ways, but nothing seems to work.

 

Any suggestions?

 

You did mention in your post above "If you echo the entire SQL string you would see where it's wrong.". How do I echo the entire SQL string?

 

This is my current code:

 

 

 

 

<?php

session_start();
if (empty($_SESSION['userName'])){
header("Location: login_required.php");
die();
}else{
}


include 'mysql_connect.php';


////////////SEE IF USER HAS BADGE

 $quiz_id = 43334;

$badge_info = mysql_query("SELECT quiz_id, badge_id, badge_name, badge_criteria, badge_image, badge_message
FROM tbl_quiz_titles, tbl_badges WHERE $quiz_id = badge_quiz_id ORDER BY badge_id DESC LIMIT 0,1")
or die(mysql_error());



// keeps getting the next row until there are no more to get


$badge_rows = mysql_num_rows($badge_info);


while($row = mysql_fetch_array( $badge_info )) {

$badge_name = $row['badge_name'];
$badge_criteria = $row['badge_criteria'];
$badge_id = $row['badge_id'];


echo "Badge ID: " . $row['badge_id'] . "<br/>";
echo "Badge name: " . $row['badge_name'] . "<br/>";
}


if ($quiz_id != "")
{
$badge_acquired_yes_no = mysql_query("SELECT *
FROM tbl_badges_acquired, tbl_badges WHERE $badge_id = tbl_badges_acquired.badge_id AND member_id =" . $_SESSION['member_id'] . "")
or die(mysql_error());



while($row1 = mysql_fetch_array($badge_acquired_yes_no))


{
$badge_image = $row1['badge_image'];
}

///////

echo "Badge image: " . $row1['badge_image'] . "<br/>" ;

}



echo "Member firstname: " . $_SESSION['member_firstname'] . "<br/>" ;


echo $_SESSION['member_id'];



 ?>

Edited February 8, 2013 by davidcriniti