Dealing with a mysql UNION query

[excelsior9]

Ok, I'm not sure what's wrong here. Basically, I'm executing this UNION query:

$query = "SELECT id,title,authorcomments,author,keywords,date_format(postdate, '%W %M %D, %Y') AS readable_date FROM artwork
WHERE authorcomments LIKE '%$trimmed%' OR title LIKE '%$trimmed%' OR keywords LIKE '%$trimmed%'
UNION SELECT id,title,poemtext,authorcomments,author,keywords,date_format(postdate, '%W %M %D, %Y') AS readable_date FROM poems
WHERE authorcomments LIKE '%$trimmed%' OR title LIKE '%$trimmed%' OR keywords LIKE '%$trimmed%' ORDER BY postdate DESC";


And I THINK that bit's fine... but when I try and deal with it with these PHP functions:

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);


I get this error message:


Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/littlehe/public_html/etcetera2/search.php on line 83


Any ideas what's wrong?

 

[jpadie]

something is wrong in your query or connection.

if you do this instead you should receive a meaningfull error message.

$numresults=mysql_query($query);
if (!$numresults){
 die (mysql_error());
}

 

[excelsior9]

Thanks - Turned out the SELECT queries have to access the same number of columns (though this is a little odd, since the tables might be completely different, no?)

Anyway, I can deal with that, but there is just one small problem that still occurs. If I include the ORDER BY postdate DESC clause in the mysql query, I get the error "unknown column 'postdate' in 'order' clause", but obviously there IS a postdate column being queried - for both SELECTs.

How can I get it to accept an order clause??

 

[jpadie]

sounds like a mysql issue, not php.

 

[ingresman]

It's not an issue , it's how SQL works.
A union essentialy runs 2 (or more ) queries and appends one to the other with any sorting done by an order by clause.
Your first query specifies 6 columns, the second one 7 columns which is why MYSQL doesn't like that bit. You might have to include a dummy column called poemtext between title and authorcomments in the first query try something like title,"" as poemtext,authorcomments.
Second issue on the order by is you are using postdate in the clause which is the original name of the column but you have overridden this by specifying readable_date as the name of the column. Change postdate to readable_date and all should work as expected.
Tell ushow you get on !

 

[jpadie]

my apologies. i was not intending to infer that mysql was behaving in an incorrect fashion. just that the question had become about mysql and not about php and it was therefore outside the scope of this forum.

 

[ingresman]

no worries !! I know exactly what you meant and I agree that it does belong in the mysql forum. I've also seen thw quality of your other replies and know where your coming from. It looks like excelsior9 didn't post it there so I decided to answer it here.
How many times do you do a search for an issue on google and you find one that matches your exact issue , you click on the link and you get a non answered question?!, I just like to provide a tidy answer for those coming after us!
A pet gripe of mine is when someone issues an request for help, sorts it themself and posts back saying fixed it with no indication of what they did. Not saying that excelsior9 is going to do that but it does happen.

 

[jpadie]

great ! thanks for posting back.