UNION parse error (and dynamic UNION)

[swto]

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.

[SpacePhoenix]

Try enclosing the sub-queries in brackets

[swto]

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 ?

[SpacePhoenix]

$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

[r937]

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

i'll bet the tables have different layouts

[SpacePhoenix]

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.

[swto]

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.

[r937]

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

a single table would eliminate quite a few of these problems

[swto]

the tables contain data in different languages...

[SpacePhoenix]

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.

[swto]

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 -