SQL statement possible cause of error?

jamesc00

Hi,

I am trying to execute the following query:

$QUERY1 = mysqli_prepare($db1->dbh, "SELECT Database, SS_type, YTD2009 from '$table1' where SS_type = '$ss_type'
UNION
SELECT Database, SS_type, YTD2010 from '$table2' where SS_type = '$ss_type' UNION
SELECT Database, SS_type, YTD2011 from '$table3' where SS_type = '$ss_type' GROUP by Database");

and warning/error I am seeing at the terminal is:

PHP Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given....

which is coming from the following code:

if(!mysqli_stmt_execute($QUERY1)){
$db1->showerror();
} else{
mysqli_stmt_store_result($QUERY1);
mysqli_stmt_bind_result($QUERY1, $Database, $SS_type, $YTD2009, $YTD2010, $YTD2011);
print<<<STARTHTML
...
...

The values I am using are:
$table1 = "2009Db1_SS_vendor1"
$table2 = "2010Db1_SS_vendor1"
$table3 = "2011Db1_SS_vendor1"
$ss_type = "Total Searches Run"

There appears to be a problem with my above SQL statement. Any suggestions on how to address it? Thanks.

Weedpacket

if $QUERY1 is false then there was an error compiling the SQL statement. Use [man]mysqli_error[/man] to find out what it was.

jamesc00

It appears my problem is passing PHP variables into the SQL statements. I also tried the following:

"SELECT * from '$table1' where SS_type = '$ss_type'"

and the error I am receiving is:

Sql 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 ''2009Db1_SS_vendor1' where SS_type = 'Total Searches Run'' at line 1

What is the proper syntax for passing PHP variables into the SQL statements?

jamesc00

I have updated my code to:

$table1 = mysqli_real_escape_string($connection, $table1);
$ss_type = mysqli_real_escape_string($connection, $ss_type);

$mySQL = "SELECT * from '$table1' where SS_type = '$ss_type'";

$result = mysql_query($mySQL,$connection) or exit("Sql Error: ".mysql_error());

and the error I am now getting is:

Sql 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 ''' where SS_type = ''' at line 1

Let me know what I am doing wrong? Thx.

Weedpacket

The mistake is in the SQL itself:

...from '2009Db1_SS_vendor1' where ...

You're trying to select from a string, not a table; if you need to quote the name of the table (because of the leading year prefix), then

...from `2009Db1_SS_vendor1` where ...

Speaking of prefix: I'd like to ask why the year is part of the table name instead of a column in the table, but if I asked that I'd find myself asking the same thing about the "vendor1" part....