SELECT * FROM table A WHERE xxx="1" from table B, order DESC from table A - HELP

[rrgerman]

Ok this is my question, i have 2 tables… one named ratings and the orher one named users…

I am trying to display pictures with high ratings… but i just want to display them if the user is verified, the problem is that the verified option is in table users, and the ratings are in table ratings… how can i make this happen???

This is what i came up with but obviously doesnt work, i need somehow specify that “verified” is from USERS and then i dont know if i need to specify that the ORDER BY DESC is from ratings? :-X :-\ 8) :o ???

[php]$getitem1 = mysql_query(“SELECT * FROM ratings WHERE verified=‘yes’ ORDER BY total_value DESC LIMIT 0,1”)[/php]

[smokeys_13]

Hi there,

Try the following:

SELECT r.* FROM ratings AS r INNER JOIN users AS u ON u.`id`= r.`user_id` WHERE u.`verified`='yes' ORDER BY r.`total_value`DESC LIMIT 0,1

[rrgerman]

Hey! Thanks for replying!

Holly snap! that was some weird syntax but hella accurate!!, i just had to make a minor adjustment (of course, because you didnt know the name of my rows on both tables, otherwise you wouldve nailed it!) but it works like a charm!! thank you man! im impressed ;D

this is the final coding:

SELECT r.* FROM ratings AS r INNER JOIN users AS u ON u.username= r.id WHERE u.verified=‘yes’ ORDER BY r.total_value DESC LIMIT 0,1

[smokeys_13]

Glad to have helped. If you want to know more about the more complex select statements you can check out the following links (examples are further down the pages).

http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/join.html

[rrgerman]

by the way i have another question for you…

you can see my members profile by typing www.sitename.com/userinfo.php?user=USERNAME-HERE

as you know, i have on users table that "verified’ section which is either ‘yes’ or ‘no’,

is there any way to inser that here:

[php]/* Requested Username error checking */
$req_user = trim($_GET[‘user’]);
if(!$req_user || strlen($req_user) == 0 ||
!eregi("^([0-9a-z])+$", $req_user) ||
!$database->usernameTaken($req_user)){
die(“Username not registered”);
}

		/* Display requested user information */
		$req_user_info = $database->getUserInfo($req_user);?>[/php]

so if the user is not verified, the profile cant be seen?

i kinda got it going but trust me, it is the craziest and weirdest way to do it, dont even know how it works but oh well… im wondering if it can be done the right way?

[rrgerman]

i will check those sites for sure! :), hope you can help me with my last question ;D

[smokeys_13]

I’m afraid I’m not familiar with the framework/class you are making use of. I would (before the output of data) put something like this:

[php]$sql = “SELECT
verified
FROM
users
WHERE
username=”.$req_user."
AND verified=‘yes’
LIMIT 1";
$query = mysql_query($sql);
if(mysql_num_rows($query) > 0)
{
//output the data
}
else
{
//user not verified
}[/php]

[rrgerman]

Hmmm i got this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/13/8078813/html/profile.php on line 99

Line 99 is if(mysql_num_rows($query) > 0)

[rrgerman]

Ok i kind of got it my friend, i just need a lil more help

I put all the users as verified (as yes) on my table and then i put this code

$sql = “SELECT * FROM users WHERE verified=‘yes’ LIMIT 1”;
$query = mysql_query($sql);
if(mysql_num_rows($query) > 0)
{

and it was displaying the users, but then, i put verifies=‘no’ and its not displaying, so i just need to add to that code something like:

select * from users where verified=‘yes’ from username limit 1,

obviously that doesnt make any sense hhahahaha but you know

[rrgerman]

I GOT IT!!! ;D ;D ;D ;D ;D

[php]mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME) or die(mysql_error());
$sql = “SELECT * FROM users WHERE verified =‘no’ AND username = ‘$req_user’ LIMIT 1”;
$query = mysql_query($sql);
if(mysql_num_rows($query) > 0)
{
die(“User not approved yet”);
}[/php]

Again! thank you so much!!!