MYSQL syntax error

Question

HI everyone i tried for 3 days and i'm not able to solve this problem. This is the codes and i have went through it again and again but i found no errors. I tried at a blank page and it worked but when i put it inside the calendar it has the syntax error. Thanks a million for whoever who can assist.

/** QUERY THE DATABASE FOR AN ENTRY FOR THIS DAY !!  IF MATCHES FOUND, PRINT THEM !! **/
$testquery = mysql_query("SELECT orgid FROM sub WHERE userid='$userid'");
while($row4 = mysql_fetch_assoc($testquery))
{
  $org = $row4['orgid'];
  echo "$org<br>";
  $test2 = mysql_query("SELECT nameevent FROM event WHERE `userid`=$org AND EXTRACT(YEAR FROM startdate)='2010' AND EXTRACT(MONTH FROM startdate)='08' AND EXTRACT(DAY FROM startdate)='15'") or die(mysql_error());
  while($row5=mysql_fetch_assoc($test2))
  {
    $namethis = $row5['nameevent'];
    $calendar.=$namethis;
  }
}

Answer 1

First question: what calendar are you talking about?

And here are my 2-cents: does the EXTRACT function returns a string or a number? Are the "backticks" (userid) really in your query? Try to strip them off. Bye!

Answer 2

It's a guess, given that you haven't provided the error message you're seeing, but I imagine that userid is a text field and so the value $org in the WHERE clause needs quotes around it. I say this as the commented out testquery has quotes around the userid field, although I appreciate that it works on a different table. Anyway try this:

SELECT nameevent FROM event WHERE userid='$org' AND EXTRACT(YEAR FROM startdate)='2010' AND EXTRACT(MONTH FROM startdate)='08' AND EXTRACT(DAY FROM startdate)='15'

In such cases it's often useful to echo the sql statement and run it using a database client

Answer 3

First step in debugging problems like this, is to print out the acutal statement you are running. I don't know PHP, but can you first build up the SQL and then print it before calling mysql_query()?

EXTRACT() returns a number not a character value, so you don't need the single quotes when comparing EXTRACT(YEAR FROM startdate) = 2010, but I doubt that this would throw an error (unlike in other databases) but there might be a system configuration that does this.

Another thing that looks a bit strange by just looking at the names of your columns/variables: you are first retrieving a column orgid from the user table. But you compare that to the userid column in the event table. Shouldn't you also be using $userid to retrieve from the event table?

Also in the first query you are putting single quotes around $userid while you are not doing that for the userid column in the event table. Is userid a number or a string? Numbers don't need single quotes.

Answer 4

Any of the mysql_* functions can fail. You have to test all the return values and if one of them indicates an error (usually when the function returns false) your script has to handle it somehow.

---

E.g. in your query

mysql_query("SELECT orgid FROM sub WHERE userid='$userid'")

you mix a parameter into the sql statement. Have you assured that this value (the value of $userid) is secure for this purpose? see http://en.wikipedia.org/wiki/SQL_injection

---

You can use a JOIN statement two combine your two sql queryies into one.

---

see also:

• http://docs.php.net/mysql_error
• http://docs.php.net/mysql_real_escape_string
• http://www.w3schools.com/sql/sql_join.asp

  ---

Example of rudimentary error handling:

$mysql = mysql_connect('Fill in', 'the correct', 'values here');
if ( !$mysql ) { // some went wrong, error hanlding here
  echo 'connection failed. ', mysql_error();
  return;
}

$result = mysql_select_db('dbname', $mysql);
if (!$result ) {
  echo 'select_db failed. ', mysql_error($mysql);
  return;
}

// Is it safe to use $userid as a parmeter within an sql statement?
// see http://docs.php.net/mysql_real_escape_string
$sql = "SELECT orgid FROM sub WHERE userid='$userid'";
$testquery  = mysql_query($sql, $mysql);
if (!$testquery  ) {
  echo 'query failed. ', mysql_error($mysql), "<br />\n";
  echo 'query=<pre>', $sql, '</pre>';
  return;
}