query with SQL UNION - help

[leegold2]

I'm trying to get this to work, it will compile with "php -l" but the
search is broken. What's wrong? I have MYSQL 4.1. Thanks



$result = mysql_query("SE LECT p.page_url AS url, p.title AS title,
p.descrip AS descrip, p.page_id AS id
FROM page p, word w, occurrence o
WHERE p.page_id = o.page_id AND
w.word_id = o.word_id AND
w.word_word LIKE '%$keyword%'")

UNION
(SELECT p.page_url AS url, p.title AS title,
p.descrip AS descrip, p.page_id AS id
FROM page p
WHERE p.descrip LIKE '%$keyword%'");

GROUP BY p.page_id
ORDER BY p.page_id DESC" );

[Andy Hassall]

Re: query with SQL UNION - help

On Sun, 17 Oct 2004 20:18:39 GMT, leegold2 <leegold@nospam .net> wrote:
[color=blue]
>I'm trying to get this to work, it will compile with "php -l" but the
>search is broken. What's wrong?[/color]

Define broken. Errors? (Which ones?) Wrong results? (What did you expect? What
data do you have?)

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Steve]

Re: query with SQL UNION - help

leegold2 wrote:[color=blue]
> I'm trying to get this to work, it will compile with "php -l" but the
> search is broken. What's wrong? I have MYSQL 4.1. Thanks
>
>
>
> $result = mysql_query("SE LECT p.page_url AS url, p.title AS title,
> p.descrip AS descrip, p.page_id AS id
> FROM page p, word w, occurrence o
> WHERE p.page_id = o.page_id AND
> w.word_id = o.word_id AND
> w.word_word LIKE '%$keyword%'")
>
> UNION
> (SELECT p.page_url AS url, p.title AS title,
> p.descrip AS descrip, p.page_id AS id
> FROM page p
> WHERE p.descrip LIKE '%$keyword%'");
>
> GROUP BY p.page_id
> ORDER BY p.page_id DESC" );[/color]

I can't remember which version of Mysql that unions are finally
supported. it may be that yours is too old.

However, will a variable name in single quoted in double quotes expand
to it's value? I don't think it will. You man need to catenate a few
substrings together to get it to work.

Steve

[Sadara]

Re: query with SQL UNION - help

Steve wrote:[color=blue]
> leegold2 wrote:
>[color=green]
>> I'm trying to get this to work, it will compile with "php -l" but the
>> search is broken. What's wrong? I have MYSQL 4.1. Thanks
>>
>>
>>
>> $result = mysql_query("SE LECT p.page_url AS url, p.title AS title,
>> p.descrip AS descrip, p.page_id AS id
>> FROM page p, word w, occurrence o
>> WHERE p.page_id = o.page_id AND
>> w.word_id = o.word_id AND
>> w.word_word LIKE '%$keyword%'")
>>
>> UNION
>> (SELECT p.page_url AS url, p.title AS title,
>> p.descrip AS descrip, p.page_id AS id
>> FROM page p
>> WHERE p.descrip LIKE '%$keyword%'");
>>
>> GROUP BY p.page_id
>> ORDER BY p.page_id DESC" );[/color]
>
>
> I can't remember which version of Mysql that unions are finally
> supported. it may be that yours is too old.[/color]
the UNION statement is available in MySQL from version 4.0.0 onwards.
[color=blue]
> However, will a variable name in single quoted in double quotes expand
> to it's value? I don't think it will. You man need to catenate a few
> substrings together to get it to work.[/color]
"LIKE '%$keyword%'" should work fine.
[color=blue]
>
> Steve[/color]
sadara