How do i get rows from two database tables into the same while loop?

Question

I have a feeling I've done this before but I can't remember exactly how it's done. This is the while loop so far:

include ('includes/DbCon.php');
$dir = 'images/photo/';                
$query = "select * from news";
$result = $mysqli->query ($query);
echo "<table border='0' class='newsTable' style='width:70%;margin:0em auto;'>";
echo "<tr stlye='display:block;margin:0em auto;'><th>Date</th><th>Headline</th><th>Body</th><th>Image</th></tr>";

while ($row = $result->fetch_assoc())
{
echo "<tr><td>"; 
echo date('j/M/y', strtotime($row['newsDate']));
echo "</td><td>";
echo "<a href='newsItem.php?id=".$row['id']."'>".$row['headline']."</a>";
echo "</td><td>";
echo "<a href='newsItem.php?id=".$row['id']."'>" . $row['body'] . "</a>";
echo "</td><td>";
echo "<p><img src=".$dir.$row['image']." width='100' height='100' alt=''>  </p>";
echo "</td></tr>";
echo "<th colspan=\"4\"><hr width=\"90%\"></th>";
}
echo "</table>";
$mysqli->close();

I tried making the select statement:

select * from news, reviews

But it only brought the items from the reviews table.

I tried looking up the php manual but couldn't find it. I have to set up all the variables for the second table (reviews), don't I?

Look up joins, if you wanna pull data from more than one table. — Mubashar Abbas
– Mubashar Abbas, Commented Aug 20, 2015 at 5:54
The tables are very similar. Both have these columns: id, headline, body, image. Reviews table has one extra column: link. Both tables have a date column: newsDate, reviewsDate. — tdrsam
– tdrsam, Commented Aug 20, 2015 at 6:12

Meenesh Jain · Accepted Answer · 2015-08-20 06:06:15Z

1

Your query is correct

passing multiple table name (comma separated ) bring data of all the table, but if your table have common columns then its hard to recognize them

Best approach is JOIN and HERE

other than that if you are using comma separated approach,

then see a example if you have two tables city and state

pass the column name rather than * so to execute query faster
separate duplicate column and same column with table names

query would be like this

 SELECT ct.city_name,ct.state_id,ct.is_capital,
        st.name,st.zoom,st.is_state, 
        ct.lat as ct_lat,st.lat as st_lat 
 FROM `city` as ct, 
      `state` as st

in your case it will be something like news and review and their column name

example of join

 SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
             ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

answered Aug 20, 2015 at 6:06

Meenesh Jain

2,5262 gold badges21 silver badges29 bronze badges

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

6 Comments

tdrsam Over a year ago

Thanks. I'll try this soon and let you know.

tdrsam Over a year ago

I tried a join but it puts the records from the second table into the same rows as the records from the first table when it's all joined in the new table. What I'd like is to have the records from both tables have separate rows and have NULL in any fields that aren't populated.

Meenesh Jain Over a year ago

left join and right join are used for what you want to achieve it leave the columns blank

tdrsam Over a year ago

When I use left or right join, it still won't give the records their own rows on the new table. Could I maybe add a new id to the new table and put each record from the two tables on a separate row with a new id.

tdrsam Over a year ago

The workaround I used for this was to alter the database table and store all the records in one table instead of two. So, I just had to change the database structure and I don't need a join or union now as there's only one table.

|

Collectives™ on Stack Overflow

How do i get rows from two database tables into the same while loop?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related