0

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! :-)

3
Contributors
4
Replies
7
Views
4 Years
Discussion Span
Last Post by smantscheff
0

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
0

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 />";
}
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.