SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UNION parse error (and dynamic UNION)

    Hi All,

    I'm having trouble with a UNION.

    Here is my code:
    PHP 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. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Try enclosing the sub-queries in brackets
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried with the query in brakcets '(SELECT * ....
    but still not working - getting a parse error. Should I break out the query to

    Code:
    $sql = 'SELECT...
    Rather than having it part of a $result ?

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    PHP 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)'
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    to find the real error, run your query outside of php

    i'll bet the tables have different layouts
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    swto, try:

    PHP Code:
    $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:

    PHP Code:
    '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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it working - though missed some of your great suggestions while working on it.

    The final working code is:

    PHP Code:
    $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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why do you have separate tables if they have the same layout?

    a single table would eliminate quite a few of these problems
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the tables contain data in different languages...

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  11. #11
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 -


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •