Checking if record is a value in mysql statement

[jackr1909]

Hi everyone,

 

i am trying to select the max(id) from a table where a record in that table matches another record, for example. Something along these lines, but it isn't working. Any help would be greatly appreciated

 

<?php
include("dbconnect.php"); //DB CONNECTION
//2 query's are needed to account for both possibilites
$result = mysql_query("SELECT * FROM friends WHERE adder = 'jack'"); //adder is the person who adds the friend
$result2 = mysql_query("SELECT * FROM friends WHERE adder = 'jack'"); //adde is the person who is added
while($row = mysql_fetch_array($result)){
$frienda = $row['addee'];
}
while($row = mysql_fetch_array($result2)){
$friendb = $row['adder'];
}
$finalquery = mysql_query("SELECT max(id) FROM links WHERE username = '$frienda' OR username = '$friendb'");
while($row = mysql_fetch_array($result)){
echo $row['0'];
}
?>

 

Thanks for the help,

 

Jack

[joel24]

couldn't you combine it all into one query?

$result = mysql_query("SELECT f.*, (SELECT max(id) FROM links l WHERE l.username = f.addee OR l.username = f.adder) AS maxValue
FROM friends f WHERE f.adder = 'jack' OR f.addee='jack'");

[jackr1909]

Thankyou so much,

 

is there anyway that i could order results, so that i could get the second highest value, etc., etc.

Thanks again,

 

Jack

[joel24]

second highest value?! i.e. max-1.. something like this should work

$result = mysql_query("SELECT f.*, (
SELECT max(id) FROM links l 
WHERE (l.username = f.addee OR l.username = f.adder) AND id < (SELECT max(id) FROM links l2 WHERE (l2.username = f.addee OR l2.username = f.adder)
) AS maxValue
FROM friends f WHERE f.adder = 'jack' OR f.addee='jack'");

 

//edit - misread your query

if you want to get the max, 2nd, 3rd etc (multiple values) you will have to perform a separate query;

SELECT `id` FROM links WHERE adder = 'jack' OR addee='jack' ORDER BY `id` desc

[jackr1909]

I'm getting syntax error, in regards to your edit, adder and addee are in a different table, the first reply was exactly what i wanted.

 

Thanks,

 

Jack

[joel24]

can you please copy+paste the error?

[jackr1909]

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS maxValue FROM friends f WHERE f.adder = 'jack' OR f.addee='jack'

[jackr1909]

Dont worry, i got the desc bit. ( added a limit to id FROM POSTS and changed max(id) to id

SELECT f.*, (SELECT id FROM posts l WHERE l.username = f.addee OR l.username = f.adder  ORDER BY id DESC LIMIT 1,1) AS maxValue
FROM friends f WHERE f.adder = 'jackr1909' OR f.addee='jackr1909' ORDER BY maxValue DESC

 

Thanks very much,

 

Jack

[jackr1909]

Hi everyone, i have this code:

 

SELECT f.*, (SELECT id FROM posts l WHERE l.username = f.addee OR l.username = f.adder  ORDER BY id DESC LIMIT $select1,1) AS maxValue
FROM friends f WHERE f.adder = '$_SESSION[username]' OR f.addee='$_SESSION[username]'

 

 

and from friends in the WHERE, i would also like to check that a column 'xyz' = 'yes'. I have tried to simply add it to the end of the query, but i'm getting errors (not MySQL, just nothing is showing)

 

And help would be much appreciated,

 

Thanks,

 

Jack

[dzelenika]

I think that you should put OR in parenthesis:

 

SELECT f.*, (SELECT id FROM posts l WHERE l.username = f.addee OR l.username = f.adder  ORDER BY id DESC LIMIT $select1,1) AS maxValue

FROM friends f WHERE (f.adder = '$_SESSION[username]' OR f.addee='$_SESSION[username]') AND ...

[fenway]

Don't start a new thread for questions on the same query.