Query failed

[sagisgirl]

hi...im a newbi in php..so i really in need of help..

i got an error like this :

Query failed: 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 1
SELECT * FROM md_tenant WHERE tenantID =

and here are my codes:

$query_recTenantID = "SELECT * FROM md_tenant WHERE tenantID = ".$tenantID;
$rs_recTenantID = mysql_query($query_recTenantID) or die ('Query failed: ' . mysql_error(). "<br />\n $query_recTenantID");
while($recTenantID = mysql_fetch_array($rs_recTenantID))
    { echo $recTenantID['tenantID']; }

can someone help me to solve this error..

[veedeoo]

Your codes looks ok. Make sure $tentID is defined or have value otherwise it will give you an undefined variable tenantID, and sql syntax error .

[sagisgirl]

oohh..ok.. but i already defined the variable $tenantID..

$tenantID = $_REQUEST['tid'];

and it also have value...

[broj1]

When you use values from forms in your query best practices are:

• check for existence of values
• validate entered values for correct type/value

• escape the values to prevent entering bad characters (like ')

  // check if there is a value in the request
  if(isset($_REQUEST['tid']) && !empty($_REQUEST['tid'])) {

      // cast to integer if you are expecting integer
      // escape if you are expecting string
      // $tenantID = (int) $_REQUEST['tid'];
      $tenantID = mysql_real_escape_string($_REQUEST['tid']);
  
      // then use the value in query
      $query_recTenantID = "SELECT * FROM md_tenant WHERE tenantID = ".$tenantID;
  
      // display the rows
      ...
  

  }

[veedeoo]

Are you sure it has value? Because AFAIK, error like that is related to the where clause when it is not wrapped with single quotes.

For example, lets use your query above
$query_recTenantID = "SELECT * FROM md_tenant WHERE tenantID = ".$tenantID;

Scenario 1: Table md_tenant don't exist : error -> Query failed: Table 'YourDatabaseName.md_tenant' doesn't exist

Scenario 2: Column tenantID don't exist: error -> Query failed: Unknown column 'tenantID' in 'where clause'

Scenarion 3: everything are fine and does exists, but tenantID have no value in where clause: error -> Query failed: 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 1

Quick solution for scenario 3 to prevent blank and no value to cause query error. Wrap your where clause value with single quotes like this. This will NOT create a line 1 error, because blank is a value that does not even exist and will NOT return anything, but without error.

    $query_recTenantID = "SELECT * FROM md_tenant WHERE tenantID = '". $tenantID ."'";

[sagisgirl]

thank you so much for your help.
it worked fine now...ok now i get it..so every where clause,must be wrap with single quotes.?
once again, thank you so much to broj1 and veedeoo... :D