Nested mySQL queries create issue with validity of result index?

[jerrygarciuh]

Hi all,
I am iterating through a result set to generate a second set of queries but
no matter what I do I get the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource

even though if I echo the query to the browser and cut and paste it into the
command line I get valid results.

Do I have to store the first result in an array before doing the second set?

TIA,
jg

while ($i < mysql_num_rows($result)) {
$PIDs[$id]['ClientName'] = $client;
$PIDs[$id]['ProjectName'] = mysql_result($result, $i, 'Name');
$PIDs[$id]['PID'] = mysql_result($result, $i, 'PID');
$thisPID = $PIDs[$id]['PID'];
$PIDresult = ("SELECT * from hours WHERE PID = '$thisPID'") or
die(mysql_error());
$j=0;
while ($j < mysql_num_rows($PIDresult)) {
$PIDs[$id]['PID']['Comments'] = mysql_result($PIDresult, $j,
'Comments');
$j++;
}
$i++;
}
}

[Jon Kraft]

jerrygarciuh <de*****@no.spam.nolaflash.com> wrote:

Hi all,
I am iterating through a result set to generate a second set of queries
but no matter what I do I get the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource
Hi Jerry,

You should call mysql_num_rows() only once.

while ($i < mysql_num_rows($result)) {
$NumRows_1 = mysql_num_rows($result);
while ($i < $NumRows_1) {
$PIDs[$id]['ClientName'] = $client;
$PIDs[$id]['ProjectName'] = mysql_result($result, $i, 'Name');
$PIDs[$id]['PID'] = mysql_result($result, $i, 'PID');
$thisPID = $PIDs[$id]['PID'];
$PIDresult = ("SELECT * from hours WHERE PID = '$thisPID'") or
die(mysql_error());
$j=0;
while ($j < mysql_num_rows($PIDresult)) {

$NumRows_2 = mysql_num_rows($PIDresult)
while ($j < $NumRows_2) {

[snip]

HTH;
JOn

[jerrygarciuh]

Jon,

Thanks for the reply. Calling mysql_num_rows in the way you suggest still
produces the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource

My adjusted code is below. I am thinking the Good way must be to store what
I need in a throwaway array and not nest the queries.
Thanks for any advice!

jg

foreach ($clients as $id => $client) {
$i = 0;
$result = mysql_query("SELECT PID, Name FROM projects WHERE ID = '$id'");
$NumRows_1 = mysql_num_rows($result);
while ($i < $NumRows_1) {
$PIDs[$id]['ClientName'] = $client;
$PIDs[$id]['ProjectName'] = mysql_result($result, $i, 'Name');
$PIDs[$id]['PID'] = mysql_result($result, $i, 'PID');
$thisPID = $PIDs[$id]['PID'];
$PIDresult = ("SELECT * from hours WHERE PID = '$thisPID'") or
die(mysql_error());
$j=0;
$NumRows_2 = mysql_num_rows($PIDresult);
while ($j < $NumRows_2) {
$PIDs[$id]['PID']['Comments'] = mysql_result($PIDresult, $j,
'Comments');
//make an array of Session info and add to $PIDs
$j++;
}
$i++;
}
}

"Jon Kraft" <jo*@jonux.co.uk> wrote in message
news:bj************@ID-175424.news.uni-berlin.de...

jerrygarciuh <de*****@no.spam.nolaflash.com> wrote:

Hi all,
I am iterating through a result set to generate a second set of queries
but no matter what I do I get the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource

Hi Jerry,

You should call mysql_num_rows() only once.

while ($i < mysql_num_rows($result)) {

$NumRows_1 = mysql_num_rows($result);
while ($i < $NumRows_1) {

$PIDs[$id]['ClientName'] = $client;
$PIDs[$id]['ProjectName'] = mysql_result($result, $i, 'Name');
$PIDs[$id]['PID'] = mysql_result($result, $i, 'PID');
$thisPID = $PIDs[$id]['PID'];
$PIDresult = ("SELECT * from hours WHERE PID = '$thisPID'") or
die(mysql_error());
$j=0;
while ($j < mysql_num_rows($PIDresult)) {

$NumRows_2 = mysql_num_rows($PIDresult)
while ($j < $NumRows_2) {

[snip]

HTH;
JOn

[Jon Kraft]

jerrygarciuh <de*****@no.spam.nolaflash.com> wrote:

Jon,

Thanks for the reply. Calling mysql_num_rows in the way you suggest still
produces the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource

My adjusted code is below. I am thinking the Good way must be to store
what I need in a throwaway array and not nest the queries.

foreach ($clients as $id => $client) {
$i = 0;
$result = mysql_query("SELECT PID, Name FROM projects WHERE ID =
'$id'"); $NumRows_1 = mysql_num_rows($result);

Then the error message means your query fails and there is no valid result
set. Try this to check on the SQL error:

$sql = "SELECT PID, Name FROM projects WHERE ID = '$id'";
$result = mysql_query($sql) or die (mysql_error()."".$sql);

HTH;
JOn

[jerrygarciuh]

Jon,

Well a very odd thing happened. I had been echoing the query and running it
from the command line and getting results but I went ahead and changed to
your format of setting up the query string as a scalar and inserting it as
such and lo and behold the error disappeared.

Thanks!

jg
"Jon Kraft" <jo*@jonux.co.uk> wrote in message
news:bj************@ID-175424.news.uni-berlin.de...

jerrygarciuh <de*****@no.spam.nolaflash.com> wrote:

Jon,

Thanks for the reply. Calling mysql_num_rows in the way you suggest still produces the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource

My adjusted code is below. I am thinking the Good way must be to store
what I need in a throwaway array and not nest the queries.

foreach ($clients as $id => $client) {
$i = 0;
$result = mysql_query("SELECT PID, Name FROM projects WHERE ID =
'$id'"); $NumRows_1 = mysql_num_rows($result);

Then the error message means your query fails and there is no valid result
set. Try this to check on the SQL error:

$sql = "SELECT PID, Name FROM projects WHERE ID = '$id'";
$result = mysql_query($sql) or die (mysql_error()."".$sql);

HTH;
JOn

[Andy Hassall]

On Sun, 7 Sep 2003 20:50:58 -0500, "jerrygarciuh"
<de*****@no.spam.nolaflash.com> wrote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource $PIDresult = ("SELECT * from hours WHERE PID = '$thisPID'") or
die(mysql_error());

You're missing a mysql_query() around the SQL string.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[jerrygarciuh]

OMG, laughing very hard at myself just now.

Thanks!

jg

"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:du********************************@4ax.com...

On Sun, 7 Sep 2003 20:50:58 -0500, "jerrygarciuh"
<de*****@no.spam.nolaflash.com> wrote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource

$PIDresult = ("SELECT * from hours WHERE PID = '$thisPID'") or
die(mysql_error());

You're missing a mysql_query() around the SQL string.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)