Displaying items from multiple tables on your webpage using php while loop

Question

I am trying to dsiplay data from 2 different tables from my mysql database using a while loop.

Currently I can display the data from 1 table and limit the results to 3. I then want to display the first 5 records from another table. If I join the tables I can only display the same number of items from both using LIMIT?

I am using a while loop to display the content from a table called item, using the following code;

$query"); $result2 = @mysql_query($query, $connection) or die ("Unable to perform query
$query");

<?php
while($row= mysql_fetch_array($result))
{
?>
<?php echo $row['item'] ?>
<?php
}
?>

If I start another loop for the data from the next table called movie, however the data is not displayed using the following code;

<?php
while($row= mysql_fetch_array($result2))
{
?>
<?php echo $row['title'] ?>
<?php
}
?>

What is the best way to display the data from the 2 tables? Many Thanks

If you join you can limit to 8 which would be 5+3. But yes, there is only one LIMIT clause per SELECT query. — hakre
– hakre, Commented Jun 21, 2011 at 17:17

Dirk · Accepted Answer · 2011-06-21 17:25:24Z

3

I don't know if you forgot to paste a bit of code, but this should work:

<?php
$query =  "select * from item order by date, time asc limit 0,3";
$result = mysql_query($query);
while($row= mysql_fetch_array($result))
{
   echo $row['item'];
}

$query2 = "select * from movie limit 0,5";
$result2 = mysql_query($query2);    
while($row= mysql_fetch_array($result2))
{
   echo $row['movie'];
}
?>

You may be able to do it with one SQL Query too:

SELECT i.item, m.movie
FROM (SELECT * FROM item ORDER BY date, time ASC LIMIT 0,3) i,
     (SELECT * FROM movie limit 0,5) m

Then in php:

<?php
while($row= mysql_fetch_array($result))
{
   echo $row['item'];
   echo $row['movie'];
}
?>

But that depends on how you want to format the output.

answered Jun 21, 2011 at 17:25

Dirk

3,1032 gold badges37 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Displaying items from multiple tables on your webpage using php while loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related