By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,664 Members | 1,669 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,664 IT Pros & Developers. It's quick & easy.

Nested mySQL queries create issue with validity of result index?

P: n/a
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++;
}
}
Jul 16 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Jul 16 '05 #2

P: n/a
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

Jul 16 '05 #3

P: n/a
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
Jul 16 '05 #4

P: n/a
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

Jul 16 '05 #5

P: n/a
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)
Jul 16 '05 #6

P: n/a
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)

Jul 16 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.