MySql select from multiple tables problem (without joins)

[cedry2k]

I have a weird problem and I can't figure it out.
I have this select :

$select = mysql_query("SELECT user_id, torrent_id, torrentname FROM wishlist WHERE user_id = '".$USER['id']."'") or die (mysql_error());
while ($row = mysql_fetch_assoc($select)){
echo "<a href='details.php?id=".$row['torrent_id']."'>".$row['torrentname']."</a><br/>";
} 

So far so good, but the problem is when I'm trying to add a second table in the select, like this:

$select = mysql_query("SELECT wishlist.user_id as uid, wishlist.torrent_id as wtid, wishlist.torrentname as wtn, categories.id FROM wishlist, categories WHERE wishlist.user_id = '".$USER['id']."'") or die (mysql_error());
while ($row = mysql_fetch_assoc($select)){
echo "<a href='details.php?id=".$row['wtid']."'>".$row['wtn']."</a><br/>";
} 

Now the problem is that instead of 4 rows that it should show in the mysql_fetch_assoc, show those 4 rows again and again and so on...
What I am doing wrong?

[something else]

you have not told which row to select on categories - so it will print over and over again due to it selecting everything in categories

Maybe you need to use a JOIN ?

also mysql_fetch_assoc() is deprecated as of php 5.5.0 so may cause you headaches in the future

[just_m3.]

I have a weird problem and I can't figure it out.
I have this select :

$select = mysql_query("SELECT user_id, torrent_id, torrentname FROM wishlist WHERE user_id = '".$USER['id']."' LIMIT 4") or die (mysql_error());
while ($row = mysql_fetch_assoc($select)){
echo "<a href='details.php?id=".$row['torrent_id']."'>".$row['torrentname']."</a><br/>";
} 

So far so good, but the problem is when I'm trying to add a second table in the select, like this:

$select = mysql_query("SELECT wishlist.user_id as uid, wishlist.torrent_id as wtid, wishlist.torrentname as wtn, categories.id FROM wishlist, categories WHERE wishlist.user_id = '".$USER['id']."'") or die (mysql_error());
while ($row = mysql_fetch_assoc($select)){
echo "<a href='details.php?id=".$row['wtid']."'>".$row['wtn']."</a><br/>";
} 

Now the problem is that instead of 4 rows that it should show in the mysql_fetch_assoc, show those 4 rows again and again and so on...
What I am doing wrong?

You are using while loop you might try limit query to 4 results .

like so :

mysql_query("SELECT user_id, torrent_id, torrentname FROM wishlist WHERE user_id = '".$USER['id']."' LIMIT 0,4")

[cedry2k]

Thank you! The problem is solved now, and here is my solution:

$a = "
SELECT torrents.id as tid, torrents.name as tname, torrents.size, torrents.added, torrents.description, torrents.times_completed, torrents.category as tcat, wishlist.torrent_id as wtid, wishlist.user_id
FROM torrents
LEFT JOIN wishlist
ON torrents.id=wishlist.torrent_id WHERE wishlist.user_id = ".$USER['id']."
";

$c = mysql_query($a) or die (mysql_error());

while ($row = mysql_fetch_assoc($c)){
$name = mysql_query("SELECT categories.id, categories.small_image as smi, torrents.category, torrents.id, wishlist.torrent_id FROM categories, torrents, wishlist WHERE categories.id = torrents.category AND torrents.id =".$row['wtid']) or die (mysql_error());
while ($res = mysql_fetch_assoc($name)){
$category_icon = "<img src='".$res['smi']."'>";
}
    
echo "<a href='details.php?id=".$row['tid']."'>".$row['tname']."</a>".$category_icon."<br/>";
} 

@something else: What do you suggest to use instead of mysql_fetch_assoc?
I searched on php.net and found that mysql_fetch_object is deprecated and also mysql_fetch_array is deprecated...

[something else]

MYSQLI - there needs to be a tutorial on here for it, but it is all over at php.net

[arnage]

It is in Tutorials from 10.01.13.
http://coding-talk.com/f19/php-and-m...tension-22715/

[cedry2k]

Thank you! Great tutorial!

[arnage]

You are welcome, im glad that its useful.