An easy one for you

[Anon]

I'm stuck and kicking myself. I know it is easy but I need to complete the following statement:

select all from perscriptions_temp where user_id = user_id and rp_id = THE LARGEST

Code so far:

$sql_select = "select * from perscriptions_temp where user_id ='$user_id' and rp_id ="THE LARGEST";

Any help appreciated I'm stupid, I think this is easy.

[matto]

... and rp_id=(select max(rp_id) from perscriptions_temp)

this should work ...

hth

[Anon]

thanks

[Anon]

That didn't work, I've tried the code below too, but that doesn't work either!?!

$sql_select = "select * from perscriptions_temp where user_id ='$user_id' and max(rp_id)='$user_id'";

[matto]

depends on your table structure ...

select * from perscriptions_temp where user_id ='$user_id' and max(rp_id)='$user_id'

first, max() must be in a sub-select (as I posted).
second, max() only works with number fields:

for example:
select name from product where price=(select max(price) from product)

this will get the product(s) with the highest price. note that price is a number value.

[Anon]

This should work I think but it doesn't! Table name is perscriptions_temp which has columns rp_id and user_id. I want to find all the data in the row where the max rp_id equals the user_id of the current user.

My code:

$sql_select = "select max(rp_id) from perscriptions_temp where user_id='$user_id'";
$max_rp_id = mysql_query ($sql_select);
$sql_select2 = "select * from perscriptions_temp where rp_id='$max_rp_id' and user_id='$user_id'";
$data = mysql_query ($sql_select2);
$row = mysql_fetch_array($data);

		$rp_id = $row["rp_id"];
		$gp_no = $row["gp_no"];
		$des_1 = $row["des_1"];
		$des_2 = $row["des_2"];
		$des_3 = $row["des_3"]; etc....