mysql_fetch_array error. limit switch the problem?

KaitoTST

so i'm trying to add an "auto-increment" for my table for every value that has id=1, but the message

"Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in"

keeps on popping up. So now i'm stuck on what to do. I think maybe the limit is the thing causing my problems. Please help. Thanks =)

Code:

$id=1;
$con=mysql_connect("localhost","databse","asdf");
if (!$con) die('Could not connect:'.mysql_error());
mysql_select_db("database", $con);
$numrow=mysql_query("SELECT COUNT(pid) FROM pictures WHERE id='$id'");
while($row=mysql_fetch_array($numrow)) {
   $totalrow=$row['COUNT(pid)'];}
   for($idid=0; $idid<=($totalrow-1); $idid++) {
      $ppid=mysql_query("SELECT * FROM pictures WHERE id='$id' ORDER BY pid LIMIT '$idid',1");
      $rows=mysql_fetch_object($ppid); }
while($rows=mysql_fetch_array($ppid)) {
   mysql_query("UPDATE pictures SET idid='$idid' WHERE id='$id' AND pid='$rows[pid]'");
mysql_close($con);

jkurrle

This probably isn't related to your problem, but it appears that you are missing an ending curly bracket to your last WHILE statement.

bradgrafelman

Can you give us more information about what you're trying to do? Some sample data would be helpful as well.

The reason I ask is because your SQL queries appear to be grossly unoptimized (I immediately cringe whenever I see a query being called within the loop of another query). If you can give us more information about your DB structure and what you're trying to do, it's very likely that you can reduce all that code down into one SQL query. Not only would it be more efficient, but it'd be a lot easier to read/edit the code, too.

jkurrle

I'm going to do a slight modification to your code. Do you still get the same error message when you run this code?

$id=1; 
$con=mysql_connect("localhost","databse","asdf") 
  or die('Could not connect:'.mysql_error()); 
mysql_select_db("database", $con); 

$numrow=mysql_query("SELECT COUNT(pid) AS totalrow FROM pictures WHERE id='$id'"); 
$row=mysql_fetch_assoc($numrow);
extract($row);

for($idid=0; $idid<=($totalrow-1); $idid++) { 
      $ppid=mysql_query("SELECT * FROM pictures WHERE id='$id' ORDER BY pid LIMIT '$idid',1"); 
      $rows=mysql_fetch_object($ppid); }

while($rows=mysql_fetch_array($ppid))  

   mysql_query("UPDATE pictures SET idid='$idid' WHERE id='$id' AND pid='$rows[pid]'"); 

mysql_close($con);

jkurrle

Looking at the rewritten code, I am wondering if you are running into a problem fetching data from the same results set twice in a row. I've tried doing that and found that the results set gets exhausted (emptied) from the fetch.

Making a copy of the results set ($results2 = $results) doesn't fix the problem, as the copy is only a symbolic link to the original, not a real copy. So when you exhaust one, you exhaust the other.

I cannot say this for sure, as you are doing a mysql_fetch_object, followed by mysql_fetch_array and I'm not sure how the two interact with each other. It is possible that when running the mysql_fetch_object, you exhaust the results set. Then when you run mysql_fetch_array against the same results set, there's nothing to fetch against.

Something for you to explore...