Bad syntax causing errors for MySQL query

Question

I'm trying to make some PHP that creates a query based on what it gets from the URL. I'm pretty sure I'm close to the code required to finish it but I can't get my syntax right. The idea is, it loops through all the companies in the URL ?companies=bla,bla,bla (which I stored in an array) and then for each one it echos part of the SQL statement for $query. I know my code is going to be bad. I am totally aware of that but I can't debug it without the right syntax. If someone could correct my syntax I'd highly appreciate it!

Thanks

$companies = $_GET['companies'];
$companiesArray = explode(',', $companies);
$companiesArrayLength = count($companiesArray);

$query = $db->query(
    for ($i = 0; $i < $companiesArrayLength; $i++) {
        echo "SELECT * FROM " . $companiesArray[$i] . " UNION ALL";
    }
    //echo " ORDER BY timestamp DESC LIMIT 50";
);

do you have a companies table, if yes, how does it look like? — huysentruitw
– huysentruitw, Commented Jun 3, 2013 at 11:20
@h2ooooooo I am aware of that, I haven't added code to prevent it yet but I am aware of what needs to be done to prevent it. — jskidd3
– jskidd3, Commented Jun 3, 2013 at 11:21

huysentruitw · Accepted Answer · 2013-06-03 11:26:17Z

1

So your last sub-query should not end with UNION ALL, can you do something like this:

$parts = array();

foreach ($companiesArray as $company)
    $parts[] = "SELECT * FROM " . $company;

$query = implode(" UNION ALL ", $parts) . " ORDER BY timestamp DESC LIMIT 50";

answered Jun 3, 2013 at 11:26

huysentruitw

28.2k10 gold badges95 silver badges141 bronze badges

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

Comments

Andrius Naruševičius · Accepted Answer · 2013-06-03 11:22:16Z

1

So your code will end up being

SELECT * FROM a UNION ALL
...
SELECT * FROM k UNION ALL

and the final UNION ALL is not needed, that's why your are getting the error. Remove it so it looks like:

SELECT * FROM a UNION ALL
...
SELECT * FROM k

and it should execute. Make sure the column count in each table is the same too!

answered Jun 3, 2013 at 11:22

Andrius Naruševičius

8,6289 gold badges53 silver badges83 bronze badges

1 Comment

jskidd3 Over a year ago

Thanks for this! It didn't fully answer my question as my PHP syntax was off but you certainly corrected my query! :)

oezi · Accepted Answer · 2013-06-03 11:23:27Z

1

you can't echo into a function-parameter (query() in this case). instead, build up a string and execute the whole query afterwards:

$querystring = "";
for ($i = 0; $i < $companiesArrayLength; $i++) {
    $querystring .= "SELECT * FROM " . $companiesArray[$i];
    // no UNION ALL after the last part
    if($i != $companiesArrayLength-1){
        $querystring .= " UNION ALL";
    }
}
$querystring .= " ORDER BY timestamp DESC LIMIT 50";

$query = $db->query($querystring);

answered Jun 3, 2013 at 11:23

oezi

51.9k10 gold badges102 silver badges119 bronze badges

5 Comments

jskidd3 Over a year ago

This answers my question perfectly, along with Andrius' query correction. Thanks very much.

oezi Over a year ago

you're welcome. please remember to mark the most helpful post as answer by clicking the tick-mark to the left of it when your question is solved.

jskidd3 Over a year ago

Absolutely! I just have to wait for that timer :P

huysentruitw Over a year ago

@jskidd3: I would prefer to use foreach and implode, it will result in cleaner code

jskidd3 Over a year ago

@WouterHuysentruit Only just seen your answer, sorry. Yours is the better one in my opinion so you win the best answer.

Nikhil Agrawal · Accepted Answer · 2013-06-03 11:42:56Z

1

Try it :

$sqlQuery = NULL;

for ($i = 0; $i < $companiesArrayLength; $i++) {
  if ($i != $companiesArrayLength-1 ){
    $sqlQuery .= " SELECT * FROM " . $companiesArray[$i] . " UNION ALL ";
   } else {
    $sqlQuery .= " SELECT * FROM " . $companiesArray[$i] ;
   }
}

$sqlQuery .= " ORDER BY timestamp DESC LIMIT 50";

$query = $db->query($sqlQuery);

Thanks.

answered Jun 3, 2013 at 11:42

Nikhil Agrawal

2851 gold badge4 silver badges13 bronze badges

Comments

Pierre de LESPINAY · Accepted Answer · 2013-06-03 11:24:30Z

0

Maybe you could do

foreach ($companiesArray as $company) {
    $sqls[] = "SELECT * FROM ".$company;
}
$sql = implode(" UNION ALL ", $sqls);
$query = $db->query($sql);

But, as you said, it's very dirty

answered Jun 3, 2013 at 11:24

Pierre de LESPINAY

46.4k62 gold badges222 silver badges318 bronze badges

Collectives™ on Stack Overflow

Bad syntax causing errors for MySQL query

5 Answers 5

Comments

1 Comment

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related