MySql select from multiple tables problem (without joins)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    MySql select from multiple tables problem (without joins)

    I have a weird problem and I can't figure it out.
    I have this select :
    PHP Code:
    $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:
    PHP Code:
    $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?
    Last edited by cedry2k; 16.05.13, 20:53.

    #2
    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

    Comment


      #3
      Originally posted by cedry2k View Post
      I have a weird problem and I can't figure it out.
      I have this select :
      PHP Code:
      $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:
      PHP Code:
      $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")
      Last edited by just_m3.; 17.05.13, 07:53.
      This is ten percent luck, twenty percent skill
      Fifteen percent concentrated power of will
      Five percent pleasure, fifty percent pain

      And a hundred percent reason to remember the name!

      Comment


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

        PHP Code:
        $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...

        Comment


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

          Comment


            #6
            It is in Tutorials from 10.01.13.
            http://coding-talk.com/f19/php-and-m...tension-22715/
            <!DOCTYPE html PUBLIC "-//WAPFORUM.RS

            Comment


              #7
              Thank you! Great tutorial!

              Comment


                #8
                You are welcome, im glad that its useful.
                <!DOCTYPE html PUBLIC "-//WAPFORUM.RS

                Comment

                Working...
                X