Quick PHP + MySQL Query help! - GoFuckYourself.com

dirtysouth · 01-25-2007, 10:11 AM

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.

darksoul · 01-25-2007, 10:20 AM

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;

dirtysouth · 01-25-2007, 10:28 AM

Quote:

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.

darksoul · 01-25-2007, 10:34 AM

whats the error message ?

PHP-CODER-FOR-HIRE · 01-25-2007, 10:35 AM

Dude instead of selecting 90% of the fields, just do the whole thing for the sake of simplicity:

SELECT * FROM table WHERE username=...

dirtysouth · 01-25-2007, 10:50 AM

Quote:

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.

darksoul · 01-25-2007, 10:55 AM

Quote:

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.

dirtysouth · 01-25-2007, 11:01 AM

Quote:

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

darksoul · 01-25-2007, 11:05 AM

Code:

select a.thumb,b.* FROM users as a, tracks as b
WHERE a.username=b.username
ORDER BY id DESC LIMIT $offset, $rowsPerPage;

dirtysouth · 01-25-2007, 11:08 AM

Quote:

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.

grumpy · 01-25-2007, 11:50 AM

just goto www.mysql.net . Have fun

01-25-2007, 10:11 AM	#1
dirtysouth Confirmed User Join Date: Jul 2003 Location: Mobtown Posts: 2,613	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

01-25-2007, 10:20 AM	#2
darksoul Confirmed User Join Date: Apr 2002 Location: /root/ Posts: 4,997	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

01-25-2007, 10:34 AM	#4
darksoul Confirmed User Join Date: Apr 2002 Location: /root/ Posts: 4,997	whats the error message ? __________________ 1337 5y54\|)m1n: 157717888 BM-2cUBw4B2fgiYAfjkE7JvWaJMiUXD96n9tN Cambooth

01-25-2007, 10:35 AM	#5
PHP-CODER-FOR-HIRE Confirmed User Industry Role: Join Date: Nov 2006 Posts: 1,090	Dude instead of selecting 90% of the fields, just do the whole thing for the sake of simplicity: SELECT * FROM table WHERE username=... __________________ Hire a PHP Coder

01-25-2007, 11:05 AM	#9
darksoul Confirmed User Join Date: Apr 2002 Location: /root/ Posts: 4,997	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

01-25-2007, 11:50 AM	#11
grumpy Too lazy to set a custom title Join Date: Jan 2002 Location: Holland Posts: 9,870	just goto www.mysql.net . Have fun __________________ Don't let greediness blur your vision \| You gotta let some shit slide icq - 441-456-888