Building SQL query with _POST parameters

Question

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

Is it get same pattern '2015-09-30' when you var_dump $_POST["date"] — Janaka
– Janaka, Commented Oct 2, 2015 at 10:06
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 — Rhecil Codes
– Rhecil Codes, Commented Oct 2, 2015 at 10:07

frarugi87 · Accepted Answer · 2015-10-02 10:08:34Z

2

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

answered Oct 2, 2015 at 10:08

frarugi87

2,8801 gold badge23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rhecil Codes Over a year ago

Doh! Can't believe I didn't consider that - *facepalm - thank you everyone.

RiggsFolly · Accepted Answer · 2015-10-02 10:10:35Z

0

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;

answered Oct 2, 2015 at 10:10

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Comments

xlecoustillier · Accepted Answer · 2015-10-02 10:45:14Z

0

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 = ...

edited Oct 2, 2015 at 10:45

answered Oct 2, 2015 at 10:06

xlecoustillier

16.4k15 gold badges64 silver badges88 bronze badges

1 Comment

Rhecil Codes Over a year ago

Thanks. You are quite right. Why can't both tables be queried for the same date?

Collectives™ on Stack Overflow

Building SQL query with _POST parameters

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related