[RESOLVED] Query multiple tables - follow on...

TobyRT

Hiya

I'm trying to run a query across three tables on my site - jobs, news, and articles. Each table has different field names - I've run a successful UNION query, but it hasn't allowed me to extract the fields I want - I keep getting column number errors.

I found a very useful post from Leatherback saying to put the results into an array http://www.phpbuilder.com/board/showthread.php?t=10305050&highlight=union+fields:

I've come up with the following on the basis of that, but I keep getting "mysql_num_rows(): supplied argument is not a valid MySQL result resource" as an error. Could anyone advise why please? I'm not getting any SQL errors.

$search = $_GET['search'];
$newsquery = $connector->query("SELECT id, title, UNIX_TIMESTAMP(dateadded) AS dateadded, article FROM news WHERE article LIKE '%$search%'") or die ("Error in Query: $query.".mysql_error());;
$jobsquery = $connector->query("SELECT id, company, jobtitle, jobdesc, contact, UNIX_TIMESTAMP(dateadded) AS dateadded, UNIX_TIMESTAMP(closingdate) AS closingdate, oxford FROM jobs WHERE jobdesc LIKE '%$search%' ORDER BY dateadded DESC") or die ("Error in Query: $query.".mysql_error());;
$articlequery = $connector->query("SELECT id, title, subtitle, article, author, UNIX_TIMESTAMP(dateadded) AS dateadded FROM inprint WHERE article LIKE '%$search%' ORDER BY dateadded DESC") or die ("Error in Query: $query.".mysql_error());;}
$i=0;
while($row = mysql_fetch_array($newsquery))
{ 
$all_records[$i] = array( 'title' => $row['title'], 'dateadded' =>$row['dateadded'], 'article' =>$row['article']); 
}
$i=1;
while($row = mysql_fetch_array($jobsquery))
{ 
$all_records[$i] = array( 'company' => $row['company'], 'jobtitle' =>$row['jobtitle'], 'jobdesc' =>$row['jobdesc'], 'contact' =>$row['contact'], 'dateadded' =>$row['dateadded'], 'closingdate' =>$row['closingdate'] ); 
}
$i=2;
while($row = mysql_fetch_array($articlequery))
{ 
$all_records[$i] = array( 'title' => $row['title'], 'subtitle' =>$row['subtitle'], 'article' =>$row['contact'], 'author' =>$row['author'], 'dateadded' =>$row['dateadded'],); 
}
if (mysql_num_rows($allrecords[0]) > 0){
echo 'News Articles Found';}
if (mysql_num_rows($allrecords[1]) > 0){
echo 'Jobs Articles Found';}
if (mysql_num_rows($allrecords[2]) > 0){
echo 'InPrint Articles Found';}

kakki

first :
you used "$all_records[$i]" in the upper part and
"$allrecords[2]" in the second part
second:
you dont add $row to the array, only your interpreted array,
i think that can be one reason for the errors ( there is no resultset in that allrecords-aray )
third:
is that workin properly??
i think,that you add only the LAST row of a query to $all_records
( cause of $all_records[$i] with $i being fixed in that while-loop )
if you want to get the amount of entries why dont you just use count(*) on the sql - side

TobyRT

Sorry kakki,

You've completely lost me there - could you show me what you actually mean?

I can't use COUNT(*) because i don't actually want to get the number of results - I'm only using this as a test - the 'News Articles Found' bit is just a test to see if the queries are working (and they're obviously not!)

Of course, if you could suggest a way of searching 3 tables with different numbers of fields and field names, I'd be very grateful - this just seems to be the best method i've found so far!

kakki

 $search = $_GET['search'];
$newsquery = $connector->query("SELECT id, title, UNIX_TIMESTAMP(dateadded) AS dateadded, article FROM news WHERE article LIKE '%$search%'") or die ("Error in Query: $query.".mysql_error());;
$jobsquery = $connector->query("SELECT id, company, jobtitle, jobdesc, contact, UNIX_TIMESTAMP(dateadded) AS dateadded, UNIX_TIMESTAMP(closingdate) AS closingdate, oxford FROM jobs WHERE jobdesc LIKE '%$search%' ORDER BY dateadded DESC") or die ("Error in Query: $query.".mysql_error());;
$articlequery = $connector->query("SELECT id, title, subtitle, article, author, UNIX_TIMESTAMP(dateadded) AS dateadded FROM inprint WHERE article LIKE '%$search%' ORDER BY dateadded DESC") or die ("Error in Query: $query.".mysql_error());;}
$i=0;
while($row = mysql_fetch_array($newsquery))
{ 
$all_news[$i] = array( 'title' => $row['title'], 'dateadded' =>$row['dateadded'], 'article' =>$row['article']); 
}
$i=1;
while($row = mysql_fetch_array($jobsquery))
{ 
$all_jobs[] = array( 'company' => $row['company'], 'jobtitle' =>$row['jobtitle'], 'jobdesc' =>$row['jobdesc'], 'contact' =>$row['contact'], 'dateadded' =>$row['dateadded'], 'closingdate' =>$row['closingdate'] ); 
}
$i=2;
while($row = mysql_fetch_array($articlequery))
{ 
$all_article[] = array( 'title' => $row['title'], 'subtitle' =>$row['subtitle'], 'article' =>$row['contact'], 'author' =>$row['author'], 'dateadded' =>$row['dateadded'],); 
}
if (sizeof($all_news) > 0){
echo 'News Articles Found';}
if (sizeof($all_articles) > 0){
echo 'Jobs Articles Found';}
if (sizeof($all_jobs) > 0){
echo 'InPrint Articles Found';}

OR:
you do a union and glue several different queries together to get one resultset....
but beware of the cols, you have to add static values to the cols, that are not in the table
SELECT whatever, NULL,NULL FROM jobs
UNION
SELECT NULL, whatever,NULL FROM articles
UNION
SELECT NULL,NULL,whatever FROM news

so you get ALL in one table, the cols, that are not available on jobs,new.... are set to NULL

TobyRT

Ahhh...now that's some cunning coding!

In the end I got around it in a similar way with three different queries (as above) and then just did a mysql_num_rows on each (as I do in every other one of my queries!)

It worked, although I suspect yours may be cleaner.

Thanks for the tips though - I think I'll try and implement this in one of my next adventures!