do a select on two tables and the result allows access to fields of these two selected tables

[os7]

Im doing a search system and Im having some problems.

I need to search in two tables (news and pages), I already had sucess doing my search system for just one table, but for two tables its not easy to do.

I already use a select statment with two tables using UNION because I want to show number of search results, that is number of returned rows of my first sql statment.

But now I need to do a select statment that allows me to acess all fields of my news table and all fields of my pages table.

I need to acess in my news table this fields: id, title, content, link, date, nViews

I need to acess in my pages table this fields: id, title, content, link

Im trying to do this also with UNION, but in this case Im not having any row returning.

Do you see what I have wrong in my code?

 

<?php
//first I get my $search keyword
$search = $url[1];

$pdo = connecting();
//then I want to show number of returned rows for keyword searched
$readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ?
UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?");
$readALL->bindValue(1,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(2,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(3,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(4,"%$search%", PDO::PARAM_STR);
$readALL->execute();
//I show number of returned rows
echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>';
//If dont return any rows I show a error message
if($readALL->rowCount() <=0){
echo 'Sorry but we didnt found any result for your keyword search.';
}
else{
//If return rows I want to show, if it is a page result I want to show title and link that I have in my page table
//if it is a news result I want to show title and link that I have in my news table and also date of news
echo '<ul class="searchlist">';
$readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4
UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4");
$readALL2->bindValue(1, '1');
$readALL2->bindValue(2, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(3, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(4, "%$search%", PDO::PARAM_STR);
$readALL2->execute();

while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){
echo '<li>';
echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>';
echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>';
//if it is a news result I also want to show date on my list
//echo '<span id="date">'.$result['date'].'</span>';
echo '</li>';
}
echo ' </ul>';
//but how can I do my select statement to have access to my news table fields and my page table fields??
}
?>

[requinix]

You can't do a UNION when the columns mismatch.

 

Do two separate queries and get two separate counts. You can sum the counts and display the results all together if you want, but IMO it would make more sense to show the two resultsets separately: one set of news results, one set of page results.

[os7]

Thanks for your tip requinix. I already have this working as you said. But now Im having a problem with my pagination system for my search results and I dont see how to fix this.

 

If my search system was only for news table, I call my Paginator function like this:

 

Paginator('news', "WHERE status = '1' AND (title LIKE '%$search%' OR content LIKE '%$search%') ORDER BY date DESC", $max, $link, $page);

 

But now do you see how I can call my function Paginator not only to my news table but also to my pages table? I dont see a way how can I do my select statment for this two tables here.

 

function Paginator($table, $cond, $max, $link, $pag){

   $pdo = start();

   $read = $pdo->prepare("SELECT * FROM {$table} {$cond}");

   $read->bindParam(1,$table,PDO::PARAM_STR);

   $read->bindParam(2,$cond,PDO::PARAM_STR);

   $read->execute();

   $total = $read->rowCount();

   if($total > $max){

        //I do my pagination 

    }

}

Edited July 15, 2014 by os7

[requinix]

Again: two separate queries. Which means two separate calls to your Paginator function.

 

Then when someone moves to the next page of one set of results, you don't even have to bother with the other query. And with the space you save you could actually fit twice the results.

[os7]

Ok thank you for your help. I´ll try that!