Userlist.php

[Sp1d3r]

Hello all, in the userlist.php (for v2) how would I change

$q=$db->query("SELECT u.*,g.* FROM users u LEFT JOIN gangs g ON u.gang=g.gangID ORDER BY $by $ord LIMIT $st,100");

So that I can show information from the userstats table?

I tried different things and can't find away with my current knowledge....

Thanks!

Sp1d3r

www.chaosrelic.com

[Sp1d3r]

Re: Userlist.php

BUMP

 

Just to clarify, I want to be able to pull the stats from the userstats table to display in the userlist.php tables.

Thanks again.

 

Sp1d3r

www.chaosrelic.com

[Dave]

Re: Userlist.php

$q=$db->query("SELECT u.*,g.*,us.* FROM users u LEFT JOIN gangs g ON u.gang=g.gangID LEFT JOIN userstats us ON us.userid=u.userid ORDER BY $by $ord LIMIT $st,100");

Not tested but should work

[Sp1d3r]

Re: Userlist.php

Thanks for the replay wicked but I got the same error I was getting when i tried to change it.

QUERY ERROR: Column 'userid' in order clause is ambiguous

Query was SELECT u.*,g.*,us.* FROM users u LEFT JOIN gangs g ON u.gang=g.gangID LEFT JOIN userstats us ON us.userid=u.userid ORDER BY userid ASC LIMIT 0,100

Sp1d3r

www.chaosrelic.com

[Dave]

Re: Userlist.php

Its something to do with the bit at the end of the query... im to tired to check atm ill check tommorow.

[Sp1d3r]

Re: Userlist.php

 

Its something to do with the bit at the end of the query... im to tired to check atm ill check tommorow.

i lied to me!!

Everyone else this is a bump

Sp1d3r

www.chaosrelic.com

[Magictallguy]

Re: Userlist.php

 

$sql = sprintf("SELECT u.*, g.gangPREF, us.* " .
"FROM users u " .
"LEFT JOIN gangs g ON (u.gang = g.gangID) " .
"LEFT JOIN userstats us ON (u.userid = us.userid) " .
"ORDER BY %s %s LIMIT %u, 100", $by, $ord, $st);

$q = $db->query($sql);

[Sp1d3r]

Re: Userlist.php

Thank Magictallguy but I got the same error as above.

 

QUERY ERROR: Column 'userid' in order clause is ambiguous

Query was SELECT u.*, g.gangPREF, us.* FROM users u LEFT JOIN gangs g ON (u.gang = g.gangID) LEFT JOIN userstats us ON (u.userid = us.userid) ORDER BY userid ASC LIMIT 0, 100

The code is original v2 coding just FYI so theres no missing/special pieces if that helps.

Sp1d3r

www.chaosrelic.com

[Magictallguy]

Re: Userlist.php

 

Thank Magictallguy but I got the same error as above.

 

QUERY ERROR: Column 'userid' in order clause is ambiguous

Query was SELECT u.*, g.gangPREF, us.* FROM users u LEFT JOIN gangs g ON (u.gang = g.gangID) LEFT JOIN userstats us ON (u.userid = us.userid) ORDER BY userid ASC LIMIT 0, 100

The code is original v2 coding just FYI so theres no missing/special pieces if that helps.

Sp1d3r

www.chaosrelic.com

Ah, I see why now too..

It's selecting the order by from the users link and that is selecting userid. userid exists in both the users and the userstats table - and that's why you're getting an error.. I can't think of a simple way to fix at the moment. Way too much on my mind.

[AlabamaHit]

Re: Userlist.php

could it be this

ORDER BY userid ASC LIMIT 0, 100

?

maybe

ORDER BY u.userid ASC LIMIT 0, 100

[Magictallguy]

Re: Userlist.php

 

could it be this

ORDER BY userid ASC LIMIT 0, 100

?

maybe

ORDER BY u.userid ASC LIMIT 0, 100

CE is so friggin' slow -.-

Your method would disable the users ability to change the order clause..

Try using:

$sql = sprintf("SELECT u.*, g.gangPREF, us.* " .
"FROM users u " .
"LEFT JOIN gangs g ON (u.gang = g.gangID) " .
"LEFT JOIN userstats us ON (u.userid = us.userid) " .
"ORDER BY u.%s %s LIMIT %u, 100", $by, $ord, $st);

$q = $db->query($sql);

[Sp1d3r]

Re: Userlist.php

 

$sql = sprintf("SELECT u.*, g.gangPREF, us.* " .

"FROM users u " .

"LEFT JOIN gangs g ON (u.gang = g.gangID) " .

"LEFT JOIN userstats us ON (u.userid = us.userid) " .

"ORDER BY u.%s %s LIMIT %u, 100", $by, $ord, $st);

$q = $db->query($sql);

Thanks that works.

What I was doing before you helped me was instead of using

us.*

I just used us.XXX where XXX is the field in the table.

Is there anything wrong with doing that? Just curious but the code you provided did work

+1

Sp1d3r

www.chaosrelic.com

[Magictallguy]

Re: Userlist.php

 

$sql = sprintf("SELECT u.*, g.gangPREF, us.* " .

"FROM users u " .

"LEFT JOIN gangs g ON (u.gang = g.gangID) " .

"LEFT JOIN userstats us ON (u.userid = us.userid) " .

"ORDER BY u.%s %s LIMIT %u, 100", $by, $ord, $st);

$q = $db->query($sql);

Thanks that works.

What I was doing before you helped me was instead of using

us.*

I just used us.XXX where XXX is the field in the table.

Is there anything wrong with doing that? Just curious but the code you provided did work

+1

Sp1d3r

www.chaosrelic.com

Nothing wrong with that at all. But, as I don't know which parts of the table you wish to display, I selected all.

If you wish to speed up your code while using a MySQL query, only select what you need from a table instead of everything