1 record becomes 2 when displayed

Chris_Evans

Hi - I have the following code on my site to retrieve some news threads from my database, it works fine except that it makes one mysql record become 2 posts on the main page. Now I've discovered that the problem is when I have two tables, which is what I need... here's how I realised:

[b]Works:[/b]
				<?php
				$result = mysql_query("SELECT * FROM news order by id desc limit 15")
				or die("Failed to connect to the database.");
				While ($row = mysql_fetch_array($result))
				{
				?>
					<tr>
					<td class="newstitle"><? echo $row['title']; ?></td>
					</tr><tr>
					<td class="main"><? echo $row['body']; ?></td>
					</tr><tr>
					<td class="newsfooter">Posted by: <i><a href="http://www.fateam.com/forum/profile.php?mode=viewprofile&u=<? echo $row['user_id']; ?>"><? echo $row['postby']; ?></a></i> on <? echo $row['date']; ?> </td>
					</tr>
				<?
				}
				mysql_free_result($result);
				?>

[b]Need this one but doesn't work, notice that it has two tables in the "FROM" section:[/b]
				<?php
				$result = mysql_query("SELECT * FROM news, phpbb_users order by id desc limit 15")
				or die("Failed to connect to the database.");
				While ($row = mysql_fetch_array($result))
				{
				?>
					<tr>
					<td class="newstitle"><? echo $row['title']; ?></td>
					</tr><tr>
					<td class="main"><? echo $row['body']; ?></td>
					</tr><tr>
					<td class="newsfooter">Posted by: <i><a href="http://www.fateam.com/forum/profile.php?mode=viewprofile&u=<? echo $row['user_id']; ?>"><? echo $row['postby']; ?></a></i> on <? echo $row['date']; ?> </td>
					</tr>
				<?
				}
				mysql_free_result($result);
				?>

I've been twiddling my thumbs, any ideas?

Thanks.

sarahk

the joys of sql

when you don't tell it how to link two tables together it gives you every different combination - in your case there are 2

so you need

$sql = "SELECT * FROM news, phpbb_users where news.id = phpbb_users.id order by news.id desc limit 15"
$result = mysql_query($sql);

Only I don't have any idea of how you plan to join news and phpbb_users or what column they have in common.

As you don't filter on phpbb_users I wonder about the value you get from adding it to the query. Are you just trying to avoid two hits to the database?

Chris_Evans

				$result = mysql_query("SELECT * FROM news, phpbb_users WHERE news.postby = phpbb_users.username order by news.id desc limit 15")
				or die("Failed to connect to the database.");

There, great! Thanks. Basically I'm linking phpb_users.username to news.postby - that way I can get the user_id from the phpbb_users table which means I can link to the profile! 🙂

Thanks,

Chris 😃