mysql:connection OK but response error

Question

hi all..this is my code:

    $Line = mysql_real_escape_string(postVar("showline"));
    $Model = mysql_real_escape_string(postVar("showmodel"));
    $NIK = mysql_real_escape_string(postVar("showNIK"));

          $sql ="SELECT NIK,Line,Model FROM inspection_report";
          $sql.="WHERE NIK='".$NIK."' AND Model LIKE '%".$Model."%' AND Line='".$Line."'";
          $sql.="ORDER BY Inspection_datetime DESC LIMIT 0 , 30";

$dbc=mysql_connect(_SRV, _ACCID, _PWD) or die(_ERROR15.": ".mysql_error());
mysql_select_db("qdbase") or die(_ERROR17.": ".mysql_error());
$res=mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  // submit SQL to MySQL and error trap.
$num=mysql_affected_rows();
$objJSON=new mysql2json();
print(trim($objJSON->getJSON($res,$num,'aaData',false)));

mysql_free_result($res);

at firebugs shows that connection to process page ok...but at response show error.. where is my fault?

Answer 1

I am assuming that is PHP.

Add the command echo $sql; after your lines above. I bet your query is malformed, i.e. no space between the end of the FROM clause and the WHERE. Same with ORDER BY. Happens all the time ;)

Answer 2

What Jason has said is good and will show you where the error is, which looks like a lack of spaces in the line breaks. Add a space before WHERE and another before ORDER

Answer 3

I have found a lot easier to write and read my SQL statements by declaring the SQL String within a single set of quotes as in:

$sql ="SELECT NIK,Line,Model FROM inspection_report
WHERE NIK='$NIK' AND Model LIKE '%$Model%' AND Line='$Line'
ORDER BY Inspection_datetime DESC LIMIT 0 , 30";

This method will also solve your problem with missing spaces between lines.

Answer 4

As stated in other answers, you're lacking spaces in your query:

$sql = "SELECT .... inspection_report";
$sql .= "WHERE NIK=..."
etc...

will generate a query string:

SELECT ... inspection_reportWHERE NIK=...
                           ^^--- problem is here

Notice the lack of a space before the WHERE clause. You have to either modify your string concatenation statements to explicitly include the space:

$sql = "SELECT ... inspection_report";
$sql .= " WHERE NIK=..."
         ^---notice the space here

or use alternative syntax to build the string. For multi-line string assignments, it's generally always preferable to use HEREDOCs, unless you need to concatenate function call results or constants into the string:

$sql = <<<EOL
SELECT ... inspection report
WHERE NIK=...
EOL;

PHP will honor the line breaks inside the heredoc, and MySQL will silently treat them as spaces, preserving the integrity of your query.