UNION parse error (and dynamic UNION)

Databases
1

Hi All,

I’m having trouble with a UNION.

Here is my code:

$result = mysqli_query($link, 'SELECT * FROM table_1 WHERE MATCH(column) AGAINST('$keyword1*' IN BOOLEAN MODE)
	UNION 
SELECT * FROM table_2 WHERE MATCH(column) AGAINST('keyword2*' IN BOOLEAN MODE)';

I keep getting a parse error - but can’t figure out what it is. Any ideas?

As a second question - is it possible to build a dynamic UNION query? I think this would be optimal but I’m not sure that this is possible.
I ask because I will need to add one more AND clause to the query above from a set of checkboxes the user can submit with this form.

Thanks in advance - hope it is clear.

2

Try enclosing the sub-queries in brackets

3

I tried with the query in brakcets '(SELECT * …
but still not working - getting a parse error. Should I break out the query to

$sql = 'SELECT...

Rather than having it part of a $result ?

4 
$result = mysqli_query($link, 'SELECT * FROM table_1 WHERE MATCH(column) AGAINST('$keyword1*' IN BOOLEAN MODE) 
    UNION  
SELECT * FROM table_2 WHERE MATCH(column) AGAINST('keyword2*' IN BOOLEAN MODE)';

Is keyword2 meant to be a variable? If so the parse error will be down to the ommition of the $ at the start of keyword2

5

to find the real error, run your query outside of php

i’ll bet the tables have different layouts

6

swto, try:

$sql="
    SELECT
        *
    FROM
        table_1
    WHERE
        MATCH(column) AGAINST('$keyword1*' IN BOOLEAN MODE)
        
    UNION
    
    SELECT
        *
    FROM
        table_2 WHERE MATCH(column) AGAINST('$keyword2*' IN BOOLEAN MODE)";

$result = mysqli_query($link,$sql);

@Rudy, just reading though the OP again, I think it’s a PHP error and not a MySQL error. In the OP the first part of the query (starting with the ’ that marks the start of it) was:

'SELECT * FROM table_1 WHERE MATCH(column) AGAINST('$keyword1*' IN BOOLEAN MODE)

Now as soon as PHP reaches the first unescaped ’ it assumes that it has reached the end of the query string and so is not expecting to find the $keyword, it was expecting to find a )

The OP could instead have used " at the start and end of the query string.

7

I got it working - though missed some of your great suggestions while working on it.

The final working code is:

$result = mysqli_query($link,
 
"(SELECT * FROM table_1 WHERE MATCH($field1) AGAINST('$keyword1*' IN BOOLEAN MODE))
 
UNION 

(SELECT * FROM table_2 WHERE MATCH($field2) AGAINST('$keyword2*' IN BOOLEAN MODE))");

All the parenthesis were messing me up and not using " at beginning and end instead of ’ (as SpacePhoenix said). The results are now being displayed correctly.

My problem for another day relates to the actions possible with the results. Since the records are from different tables - how do I ensure that a download link, for example, pulls from the correct table? I’ll have to have some fun with making the table_names dynamic in the script.

Thanks for your suggestions and help, Cheers.

8

why do you have separate tables if they have the same layout?

a single table would eliminate quite a few of these problems

9

the tables contain data in different languages…

10

Is the content of the two tables basically the same apart from being in different languages? If so you could store it all in one table with a new field in the table for a language code that denotes what language the data in the row is in.

11

I suppose I could try that but it might get confusing when the database becomes larger. There are also right to left languages for the database, which would add to the confusion.

I think I’ll plow along and see how it goes. The database is small right now until the functionality is there, so I could re-consider if the coding gets too complicated.

Thanks -