Search & Pagination (2 tables)

[scottlpool2003]

I need to pull info from 2 tables using LIKE but also paginate the results.

Table 1: publication
Search by tags
Need ID to search table 2

Table 2: publication_issue
publication_id = publication.id

<?php

$query = ("SELECT * 
 FROM publication WHERE tags LIKE '%news%'");
  $result = mysql_query($query) or die(mysql_error());
   while($row = mysql_fetch_array($result)){
    $id = $row['id'];



$queryb = ("SELECT *
 FROM publication_issue WHERE publication_id = '$id' LIMIT 0, 10");
  $resultb = mysql_query($queryb) or die(mysql_error());
   while($rowb = mysql_fetch_array($resultb)){
    $idb = $rowb[id];
    echo "$idb<br />";
   } }

?>

This code pulls out everything in the db and doesn't limit the way I want it to. All I need is help to revise this code so that the limit works.

Please help! :-)

[smantscheff]

You have to join the two tables:

select * from publication p, publication_issue i
where i.publication_id = p.id
and p.tags like '%news'
order by something
limit x,y

[scottlpool2003]

Thanks for helping.

I had used similar code but couldn't get it to work and am having the same problem with your code:

Column 'id' in order clause is ambiguous

$query = ("select id
from publication
inner join publication_issue on publication.id=publication_issue.publication_id
where publication.tags='%news%' ORDER BY id LIMIT 0,10");
 $result = mysql_query($query) or die(mysql_error());
   while($row = mysql_fetch_array($result)){
    $title = $row['title'];
    echo "$title<br />";
}

[pritaeas]

publication.tags='%news%'

You need to use LIKE as smantscheff showed you.

[smantscheff]

And you have to qualify the column id with the table name:

select publication.id ...