SQL Syntax Error

PHP
1

Hi - I’m getting an error all of a sudden, can somebody point me to the issue please?

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 ‘-1%’’ LIMIT 18446744073709551615’ at line 1

// begin Recordset
$suggestParam__colours = '-1';
if (isset($_GET['suggest1_choice'])) {
  $suggestParam__colours = $_GET['suggest1_choice'];
}
$query_colours = sprintf("SELECT colour_name FROM colours WHERE colour_name LIKE '%s'", GetSQLValueString($suggestParam__colours . "%", "text"));
$colours = $autocomplete->SelectLimit($query_colours) or die($autocomplete->ErrorMsg());
$totalRows_colours = $colours->RecordCount();
// end Recordset
2

It’s going to be hard to diagnose without knowing what most of these functions DO… “GetSQLValueString” is not a default function.

3

What is the result of

var_dump($query_colours);

Make sure you put it after the query line.

1 Like
4

Try this:

echo '<h5>' .   GetSQLValueString($suggestParam__colours) .'</h5>':

$query_colours = "
SELECT
   colour_name
FROM
  colours
WHERE
  colour_name
LIKE
  '%'" 
  .  GetSQLValueString($suggestParam__colours)
  .'%'
. '  text'
;

echo '<h5>' .$query_colours .'</h5>':

Edit:
Also try copying and pasting the SQL statement into PhpMySql and if it does not work then note the errors.

5

The error suggests to me that your $_GET variable is not being provided to the query, hence the value of -1 in the error message. But looking closely at the error message (presuming you’ve copy/pasted it, rather than typing it out) I’m not sure why it has no closing single-quote after the -1% string that you inserted.

From what I can gather, getSQLValueString() is a Dreamweaver function that escapes data, and one reference to it suggests that it uses mysql_real_escape_string() to do its work. If your server has recently been upgraded to PHP7, that function probably won’t be there any more and might be contributing.

As the function appears to add single-quotes around the value when you specify text as the type, is there any need for them to be in your query string as well?

6

You get query - SELECT colour_name FROM colours WHERE colour_name LIKE ‘-1%’ ?
try use " instead ’

SELECT colour_name FROM colours WHERE colour_name LIKE “-1%”

Please give feedback

7

Thanks all, it was the GetSQLValueString function which had been inadvertently deleted from the page

$query_colours = sprintf("SELECT colour_name FROM colours WHERE colour_name LIKE '%s'", GetSQLValueString($suggestParam__colours . "%", "text"));
$colours = $autocomplete->SelectLimit($query_colours) or die($autocomplete->ErrorMsg());
$totalRows_colours = $colours->RecordCount();
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.