GoFuckYourself.com - Adult Webmaster Forum - Quick PHP + MySQL Query help!

GoFuckYourself.com - Adult Webmaster Forum (https://gfy.com/index.php)

- Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)

- - Quick PHP + MySQL Query help! (https://gfy.com/showthread.php?t=699309)

dirtysouth

01-25-2007 10:11 AM

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. :winkwink:

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 (Post 11789054)

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. :pimp

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 (Post 11789102)

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 (Post 11789180)

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 (Post 11789214)

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. :upsidedow

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 (Post 11789271)

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. :thumbsup

grumpy

01-25-2007 11:50 AM

just goto www.mysql.net . Have fun

All times are GMT -7. The time now is 11:39 AM.