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($r esult, $i, 'Name');
$PIDs[$id]['PID'] = mysql_result($r esult, $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($P IDresult, $j,
'Comments');
$j++;
}
$i++;
}
}

[Jon Kraft]

Re: Nested mySQL queries create issue with validity of result index?

jerrygarciuh <designs@no.spa m.nolaflash.com > wrote:
[color=blue]
> 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[/color]

Hi Jerry,

You should call mysql_num_rows( ) only once.

[color=blue]
> while ($i < mysql_num_rows( $result)) {[/color]

$NumRows_1 = mysql_num_rows( $result);
while ($i < $NumRows_1) {
[color=blue]
> $PIDs[$id]['ClientName'] = $client;
> $PIDs[$id]['ProjectName'] = mysql_result($r esult, $i, 'Name');
> $PIDs[$id]['PID'] = mysql_result($r esult, $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)) {[/color]

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

[snip]

HTH;
JOn

[jerrygarciuh]

Re: Nested mySQL queries create issue with validity of result index?

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("SE LECT 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($r esult, $i, 'Name');
$PIDs[$id]['PID'] = mysql_result($r esult, $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($P IDresult, $j,
'Comments');
//make an array of Session info and add to $PIDs
$j++;
}
$i++;
}
}





"Jon Kraft" <jon@jonux.co.u k> wrote in message
news:bjhj5c$jdv 2f$3@ID-175424.news.uni-berlin.de...[color=blue]
> jerrygarciuh <designs@no.spa m.nolaflash.com > wrote:
>[color=green]
> > 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[/color]
>
> Hi Jerry,
>
> You should call mysql_num_rows( ) only once.
>
>[color=green]
> > while ($i < mysql_num_rows( $result)) {[/color]
>
> $NumRows_1 = mysql_num_rows( $result);
> while ($i < $NumRows_1) {
>[color=green]
> > $PIDs[$id]['ClientName'] = $client;
> > $PIDs[$id]['ProjectName'] = mysql_result($r esult, $i, 'Name');
> > $PIDs[$id]['PID'] = mysql_result($r esult, $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)) {[/color]
>
> $NumRows_2 = mysql_num_rows( $PIDresult)
> while ($j < $NumRows_2) {
>
> [snip]
>
> HTH;
> JOn[/color]

[Jon Kraft]

Re: Nested mySQL queries create issue with validity of result index?

jerrygarciuh <designs@no.spa m.nolaflash.com > wrote:
[color=blue]
> 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("SE LECT PID, Name FROM projects WHERE ID =
> '$id'"); $NumRows_1 = mysql_num_rows( $result);[/color]

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($sq l) or die (mysql_error(). "".$sql);

HTH;
JOn

[jerrygarciuh]

Re: Nested mySQL queries create issue with validity of result index?

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" <jon@jonux.co.u k> wrote in message
news:bji2lp$imq a7$1@ID-175424.news.uni-berlin.de...[color=blue]
> jerrygarciuh <designs@no.spa m.nolaflash.com > wrote:
>[color=green]
> > Jon,
> >
> > Thanks for the reply. Calling mysql_num_rows in the way you suggest[/color][/color]
still[color=blue][color=green]
> > 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("SE LECT PID, Name FROM projects WHERE ID =
> > '$id'"); $NumRows_1 = mysql_num_rows( $result);[/color]
>
> 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($sq l) or die (mysql_error(). "".$sql);
>
> HTH;
> JOn[/color]

[Andy Hassall]

Re: Nested mySQL queries create issue with validity of result index?

On Sun, 7 Sep 2003 20:50:58 -0500, "jerrygarci uh"
<designs@no.spa m.nolaflash.com > wrote:
[color=blue]
>Warning: mysql_num_rows( ): supplied argument is not a valid MySQL result
>resource[/color]
[color=blue]
> $PIDresult = ("SELECT * from hours WHERE PID = '$thisPID'") or
>die(mysql_erro r());[/color]

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

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

[jerrygarciuh]

Re: Nested mySQL queries create issue with validity of result index?

OMG, laughing very hard at myself just now.

Thanks!

jg



"Andy Hassall" <andy@andyh.co. uk> wrote in message
news:dujplvojcj 3c2512udlggpios q1jcg092k@4ax.c om...[color=blue]
> On Sun, 7 Sep 2003 20:50:58 -0500, "jerrygarci uh"
> <designs@no.spa m.nolaflash.com > wrote:
>[color=green]
> >Warning: mysql_num_rows( ): supplied argument is not a valid MySQL result
> >resource[/color]
>[color=green]
> > $PIDresult = ("SELECT * from hours WHERE PID = '$thisPID'") or
> >die(mysql_erro r());[/color]
>
> You're missing a mysql_query() around the SQL string.
>
> --
> Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)[/color]