Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: Problem with UNION in mysql 3.x

    Hi everybody!

    I got a big problem with this...
    I have this 2 queries ($query1 and $query2):

    $query1 = "SELECT Name, Autor, Typ, Dateiname FROM dokumente
    WHERE ";
    $i = 0;
    while ($i<$number) //while there are still array elements to go
    {
    $query1 .= "$searchtype LIKE '%$splitsearchterm[$i]%' AND ";

    $i++; //increment $i
    }

    #remove the extra " AND" from the end
    $query1 = substr($query1,0,-4);

    $query2 = "SELECT at.Arbeitsname, ut.Name, at.Type, at.filename
    FROM users_table ut, arbeiten_table at
    WHERE at.AuthorID=ut.ID AND ";
    if ($searchtype==Name)
    {
    $searchtype=Arbeitsname;
    }

    else
    {
    $searchtype=Name;
    }

    $i = 0;
    while ($i<$number) //while there are still array elements to go
    {
    $query2 .= "$searchtype LIKE '%$splitsearchterm[$i]%' AND ";

    $i++; //increment $i
    }

    #remove the extra " AND" from the end
    $query2 = substr($query2,0,-4);

    Then I made a UNION of them in mysql 4.x like this and it worked:

    $query = "($query1)
    UNION
    ($query2)
    ORDER BY Name";

    Now I have to change the script for mysql 3.x and I wanted to use the temprary table trick like this:

    $query = "CREATE TEMPORARY TABLE temp_union ".$query1."
    INSERT INTO temp_union ".$query2."
    SELECT * FROM temp_union ORDER BY Name";
    DROP TABLE temp_union";

    and it didn't work. This is the echo of the query and the error that I get:

    CREATE TEMPORARY TABLE temp_union SELECT Name, Autor, Typ, Dateiname FROM dokumente WHERE Name LIKE '%cad%' INSERT INTO temp_union SELECT at.Arbeitsname, ut.Name, at.Type, at.filename FROM users_table ut, arbeiten_table at WHERE at.AuthorID=ut.ID AND Arbeitsname LIKE '%cad%' SELECT * FROM temp_union ORDER BY Name
    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /vdsk/projekte.d/DET-wissdb/dokumenten_suche1.php on line 100

    Can someone please tell what is wrong with the query?
    Why doesn't it work?
    Is there any other solution for this?
    Thanx for everything!

    Greetings,

    Nacho.

  2. #2
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    You may use ; or \g to separate your query
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

Posting Permissions

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