don't know what is wrong

[agnivohneb]

I have looked at this again and again. I have copied directly from other parts of the site so it is exact but I keep getting errors. I just need another set of eyes to have a look at my code.

php:

<?php require_once( 'mysql.php' ); $sql = mysql_query("SELECT * FROM `people` WHERE `status` = '1';"); while ($person = mysql_fetch_array($sql)) {         $sql = mysql_query("SELECT * FROM `people` ORDER BY RAND() WHERE `status` = '1' AND `id` != '" . $person['id'] . "' LIMIT 1;");         $target = mysql_fetch_array($sql);         $sql = mysql_query("UPDATE `people` SET `target` = '" . $target['id'] . "', `status` = '2' WHERE `id` =" . $person['id'] . " LIMIT 1 ;"); } ?>

This is the error I get

Error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\htdocs\durhamnetworks\shadow\admin\assigntarget.php on line 8 Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\htdocs\durhamnetworks\shadow\admin\assigntarget.php on line 6

Any help would be great.

[btiffin]

What's in $sql after the SELECT?

And from http://ca.php.net/manual/en/function.mysql-query.php

code:

A SQL query     The query string should not end with a semicolon.

This is not my domain expertise; so I could be totally out to lunch.
Cheers

[Dan]

I blive the error means that $sql is not vaild, witch means that mysql_query failed in some way.

To debug i sugest adding a check to see if $sql is vaild befor you use it and if not output the error like so:

code:

.......... $sql = mysql_query("SELECT * FROM `people` WHERE `status` = '1';"); if (!$sql) {     die('Invalid query: ' . mysql_error()); } ............

P.S. @btiffin I have never seen a SQL querry fail becues it has a ; at the end so i think that is unlikey.

[Brightguy]

WHERE should come before ORDER. Also, I doubt you want to be redefining $sql inside your loop. (And $sql is a misnomer - mysql_query returns a boolean or a handle to a mysql result.) Also I don't know what you're doing exactly, but you might want to rethink that loop.

[jeffgreco13]

What exactly are you trying to accomplish with that loop?? Apparently you're calling on random entries within people to change their status=2.

You have a ton of quotations going on there when actually you do not need to quote the table and table cell names. It will clean things up a bit.

Brightguy's right about $sql too... you redefine it 3 times in that statement... yet when the loop re-executes, the last assignment to $sql:

php:

$sql = mysql_query("UPDATE `people` SET `target` = '" . $target['id'] . "', `status` = '2' WHERE `id` =" . $person['id'] . " LIMIT 1 ;");

will be used in:

php:

while ($person = mysql_fetch_array($sql))

And yep you guessed it. You cannot fetch an array from an UPDATE query.

.... Soooo tell us what you want to accomplish with this loop and we'll help you find an easier way.

[CyberGeek]

Hello,

In regards to the initial problem is because of the first SQL statement. Remove the semi-colon from the end. Also, note that if your "status" column within the table holds a integer, you do not require the single quotations around the one.

Secondly - Your while loop will not work for to long. Your initial SQL statement is setting a resource pointer to the variable "$sql". You are using this resource pointer while looping unfortunately, you then reassign the same sql pointer variable, $sql, to hold two different resource ids within the while loop. Now, I also see that in your third sql statement, "UPDATE ..." you have "LIMIT 1". My question to you is, is it column named "id" your primary key? If so, all primary keys should be unique. I recommend the following code or a similar version.

code:

$rIdOne = mysql_query("SELECT * FROM `people` WHERE `status` = 1"); if( $rIdOne ) {         while( $person = mysql_fetch_array($resourceIdOne) )         {                 $rIdTwo = mysql_query("SELECT * FROM `people` WHERE `status` = 1 AND `id` != " . $person['id'] . " ORDER BY RAND() LIMIT 1");                 if( $target = mysql_fetch_array($rIdTwo) )                 {                         // I have removed your "limit 1"                         mysql_query("UPDATE `people` SET `target` = " . $target['id'] . ", `status` = 2 WHERE `id` = " . $person['id'] . ");                 }         } } else {         echo 'Initial SQL query failed.';         exit; }

[jeffgreco13]

keep in mind the semi-colon being referred to that needs to be removed is the one at the end of each query but within the quotations.

Syntax requires a semi-colon at the very end of the statement.

[CyberGeek]

Correct.

[agnivohneb]

Thanks guys (and possibly girls), I got it to work. I decided that I am not going to assign at random but instead in order. No biggy.
Here is my new code.

php:

<?php require_once( 'mysql.php' ); $sql = mysql_query("SELECT * FROM `people` WHERE `status` = '1';"); while ($person = mysql_fetch_array($sql)) {         $sql2 = mysql_query("SELECT * FROM `people` WHERE `status` = '1' AND `id` != '" . $person['id'] . "' LIMIT 1;");         $target = mysql_fetch_array($sql2);         $sql3 = mysql_query("UPDATE `people` SET `target` = '" . $target['id'] . "', `status` = '2' WHERE `id` =" . $person['id'] . " LIMIT 1 ;"); } $sql = mysql_query("SELECT * FROM `people` WHERE `status` = '2' LIMIT 1;"); $target = mysql_fetch_array($sql); $sql = mysql_query("SELECT * FROM `people` WHERE `status` = '1' LIMIT 1;"); $person = mysql_fetch_array($sql); $sql = mysql_query("UPDATE `people` SET `target` = '" . $target['id'] . "', `status` = '2' WHERE `id` =" . $person['id'] . " LIMIT 1 ;"); header ("Location: index.php"); exit(); ?>

[jeffgreco13]

agnivohneb @ Wed Jul 30, 2008 5:31 pm wrote:

Thanks guys (and possibly girls), I got it to work. I decided that I am not going to assign at random but instead in order. No biggy.
Here is my new code.

php:

<?php require_once( 'mysql.php' ); $sql = mysql_query("SELECT * FROM `people` WHERE `status` = '1';"); while ($person = mysql_fetch_array($sql)) {         $sql2 = mysql_query("SELECT * FROM `people` WHERE `status` = '1' AND `id` != '" . $person['id'] . "' LIMIT 1;");         $target = mysql_fetch_array($sql2);         $sql3 = mysql_query("UPDATE `people` SET `target` = '" . $target['id'] . "', `status` = '2' WHERE `id` =" . $person['id'] . " LIMIT 1 ;"); } $sql = mysql_query("SELECT * FROM `people` WHERE `status` = '2' LIMIT 1;"); $target = mysql_fetch_array($sql); $sql = mysql_query("SELECT * FROM `people` WHERE `status` = '1' LIMIT 1;"); $person = mysql_fetch_array($sql); $sql = mysql_query("UPDATE `people` SET `target` = '" . $target['id'] . "', `status` = '2' WHERE `id` =" . $person['id'] . " LIMIT 1 ;"); header ("Location: index.php"); exit(); ?>

Ok you've once again got me confused... is your 'id' column in your table the primary key? Usually the 'id' column is set as the primary key, auto-increment, not null. If that is your case then once again there is a lot of uselessness going on. Before I get into it tell me if I'm right. If the 'id' column in the given table is your primary key and being used to uniquely identify each entry then I'll help you out with some changes..

If I'm wrong and it works then I'm happy you fixed your problem.