Quick PHP + MySQL Query help!

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • dirtysouth
    Confirmed User
    • Jul 2003
    • 2613
    #1

    Quick PHP + MySQL Query help!

    I need a little help with a query. It's for a site that allows users to post up their original audio file (or "track"). Users can listen, vote, etc. The problem is in outputting the data from 2 tables in the DB.

    Here's the deal:

    table1 - "tracks"
    table2 - "users"

    "tracks" and "users" have a common column "username".

    The query so far:

    $query = "SELECT id, name, username, title, hitCount, timestamp FROM tracks ORDER BY id DESC LIMIT $offset, $rowsPerPage";

    The above outputs all the tracks that have been posted and pages it to 10 results per page. Everything is fine with that. What I need is a join that will allow me to output the users' profile image which is in table2 in a column called "thumb".



    Any help is greatly appreciated! I've fucked with this for days. Also I haven't been around much so I don't know if anyone still goes to Web Q&A so please forgive the posting in here.
    no sig
    Tags: None
    • darksoul
      Confirmed User
      • Apr 2002
      • 4997
      #2
      Code:
      SELECT a.thumb as thumb,id,name,username,title,hitCount,timestamp FROM tracks,users as a WHERE username=a.username ORDER BY id DESC LIMIT $offset, $rowsPerPage;
      1337 5y54|)m1n: 157717888
      BM-2cUBw4B2fgiYAfjkE7JvWaJMiUXD96n9tN
      Cambooth

        Comment

        • dirtysouth
          Confirmed User
          • Jul 2003
          • 2613
          #3
          Originally posted by darksoul
          Code:
          SELECT a.thumb as thumb,id,name,username,title,hitCount,timestamp FROM tracks,users as a WHERE username=a.username ORDER BY id DESC LIMIT $offset, $rowsPerPage;
          Query failed, BUT I think it's because I didn't give you enough info.

          table1 - tracks

          id
          name
          TYPE
          size
          path
          username *
          title
          hitCount
          timestamp

          table2 - users

          username *
          password
          userid
          userlevel
          email
          timestamp
          website
          about
          hometown
          postal
          thumb

          Thanks for helping.
          no sig

            Comment

            • darksoul
              Confirmed User
              • Apr 2002
              • 4997
              #4
              whats the error message ?
              1337 5y54|)m1n: 157717888
              BM-2cUBw4B2fgiYAfjkE7JvWaJMiUXD96n9tN
              Cambooth

                Comment

                • PHP-CODER-FOR-HIRE
                  Confirmed User
                  • Nov 2006
                  • 1090
                  #5
                  Dude instead of selecting 90% of the fields, just do the whole thing for the sake of simplicity:

                  SELECT * FROM table WHERE username=...

                    Comment

                    • dirtysouth
                      Confirmed User
                      • Jul 2003
                      • 2613
                      #6
                      Originally posted by darksoul
                      whats the error message ?
                      Error, query failed

                      From:

                      $result = mysql_query($query) or die('Error, query failed');


                      PHP-CODER-FOR-HIRE:

                      I've tried that but I've added them that way to help me understand what's going on.
                      no sig

                        Comment

                        • darksoul
                          Confirmed User
                          • Apr 2002
                          • 4997
                          #7
                          Originally posted by dirtysouth
                          Error, query failed

                          From:

                          $result = mysql_query($query) or die('Error, query failed');
                          I hope that is a joke.
                          I asked about the error message the query is producing not
                          some random message you chose to display when it fails.
                          1337 5y54|)m1n: 157717888
                          BM-2cUBw4B2fgiYAfjkE7JvWaJMiUXD96n9tN
                          Cambooth

                            Comment

                            • dirtysouth
                              Confirmed User
                              • Jul 2003
                              • 2613
                              #8
                              Originally posted by darksoul
                              I hope that is a joke.
                              I asked about the error message the query is producing not
                              some random message you chose to display when it fails.
                              Sorry about that.

                              Column: 'username' in field list is ambiguous
                              no sig

                                Comment

                                • darksoul
                                  Confirmed User
                                  • Apr 2002
                                  • 4997
                                  #9
                                  Code:
                                  select a.thumb,b.* FROM users as a, tracks as b
WHERE a.username=b.username
ORDER BY id DESC LIMIT $offset, $rowsPerPage;
                                  1337 5y54|)m1n: 157717888
                                  BM-2cUBw4B2fgiYAfjkE7JvWaJMiUXD96n9tN
                                  Cambooth

                                    Comment

                                    • dirtysouth
                                      Confirmed User
                                      • Jul 2003
                                      • 2613
                                      #10
                                      Originally posted by darksoul
                                      Code:
                                      select a.thumb,b.* FROM users as a, tracks as b
WHERE a.username=b.username
ORDER BY id DESC LIMIT $offset, $rowsPerPage;
                                      You da' man. Thanks, I appreciate that.
                                      no sig

                                        Comment

                                        • grumpy
                                          Too lazy to set a custom title
                                          • Jan 2002
                                          • 9870
                                          #11
                                          just goto www.mysql.net . Have fun
                                          Don't let greediness blur your vision | You gotta let some shit slide
                                          icq - 441-456-888

                                            Comment

                                            Previous template Next
                                            Working...