CODEWITHSUNDEEP
phpmysql
Rhecil Codes
Rhecil Codes

Reputation: 541

Building SQL query with _POST parameters

I'm trying to build a dynamic query and am getting an SQL syntax error. The following query statement works correctly when typed out explicitly:

$sql = "SELECT * FROM English WHERE submitdate = '2015-09-30'";

However, if I try build the same query with _POST parameters, I get a syntax error, even though the error shows the correct query as above:

if ($_POST["lang"] === "de") :
  $query_lang = 'SELECT * FROM German';
elseif ($_POST["lang"] === "en") :
  $query_lang = 'SELECT * FROM English';
else :
  $query_lang = 'SELECT * FROM English, German';
endif;

if ( !empty($_POST["date"]) || $_POST["date"] != "all") :
  $query_date = ' WHERE submitdate = \''. $_POST["date"] . '\'';
else :
  $query_date = '';
endif;

$sql = '"' . $query_lang . $query_date . '"';

The error message is 

Error: "SELECT * FROM English WHERE submitdate = '2015-09-30'"<br>
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 
'"SELECT * FROM English WHERE submitdate = '2015-09-30'"' at line 1

Upvotes: 1

Views: 62

Answers (3)

xlecoustillier
xlecoustillier

Reputation: 16351

If $_POST["lang"] is not set or different than 'en' and 'de', you end up querying for

SELECT * FROM English, German WHERE submitdate = '2015-09-30'

This will raise an error as submitdate is ambiguous between both tables.

EDIT: This is not the answer you're looking for, for now. I leave it here as it will become relevant when you'll have fixed what farugi87 or RiggsFolly suggest.

What you can do in this case is to use UNION:

SELECT * FROM
(SELECT * FROM English 
UNION SELECT * FROM German) t
WHERE submitdate ...

This way, you don't have to change anything to the rest of your query, as long as the English and German tables share the same structure.

If you can, you could also consider merging both tables into one, adding a lang column. You could then query:

SELECT *
FROM thisNewTable
WHERE lang = 'en' -- or lang = 'de' or no filter at all in case of 'all'
AND submitdate = ...

Upvotes: 0

RiggsFolly
RiggsFolly

Reputation: 94642

You are making your string concatenation more difficult than it needs to be.

Simplify it like this and you will remove the accidental extra quotes around the query.

if ( !empty($_POST["date"]) || $_POST["date"] != "all") :
  $query_date = " WHERE submitdate = '{$_POST["date"]}'";
else :
  $query_date = '';
endif;

$sql = $query_lang . $query_date;

Upvotes: 0

frarugi87
frarugi87

Reputation: 2880

I think you are writing

"SELECT * FROM English WHERE submitdate = '2015-09-30'"

Instead of

SELECT * FROM English WHERE submitdate = '2015-09-30'

Try putting this

$sql = $query_lang . $query_date;

and see if it works

Upvotes: 2

Related Questions