nonNested sql in MySQL. -problems.

Anon

Hi, I'm trying to work my wy around the fact that you don't have support for nested select in MySQL. I have tried with variables but can't get it to work for me. What am I doing wrong?

<?php
include("mydb.inc");
$mydb=new db;
$mydb->connect();
$mydb->connect_db();
$sql1="select max(PersonID) from Person";
$mydb->query($sql1);
$no=mysql_num_rows($mydb->qid);
$row=$mydb->fetcharray();
do{
$row=$mydb->fetcharray();
printf("<TR><TD>%s</TD>", $row["PersonID"]);
$i++;
}
while($i<$no);
$sql="select * from Person where PersonID = $sql1" ;
$mydb->query($sql);
$no=mysql_num_rows($mydb->qid);
$row=$mydb->fetcharray();
printf("<TD>%s </TD>", $row["FirstName"]);
printf("<TD>%s </TD>", $row["LastNAme"]);
$mydb->close();
?>

Grateful for any help.

Mitz.

mp96brbj

From what I can see, when you write:

$sql="select * from Person where PersonID = $sql1" ;

...$sql1 is still the query you defined, so what you're really saying is this:

$sql="select * from Person where PersonID = select max(PersonID) from Person" ;

You would need to insert the value of $row["PersonID"] instead of $sql1 in $sql.

However, I think you could still fix this with one single query, if you went about it like this instead:

SELECT * FROM Person ORDER BY PersonID DESC LIMIT 1;

Anon

Hi,

Thanks, I understand that my question was unclear. This was a test to learn how to use variables to get around the fact that you can't use subqueries in MySQL.

I do have problems finding the single result of " select max(PersonID) from Person; " and put it in a variable.

:-)

Mitz.

mp96brbj

I'n not completely familiar with MySQL connections using objects. Given that the code you supplied was correct, though, the single result should be available in $row["PersonID"].

If it's the same to you, you could try a different approach to MySQL connections, mentioned in a PHP/MySQL article at webmonkey.com:
http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial4.html

I'm not saying that this is better - it's just the way I'm used to doing it.