Looping a MySQL query

NoFear

Greetings,
Is there anything functionally wrong with this code? There must be, 'cuz I keep getting errors:


for($i=0; $i<$numberofrows; $i++){
    $updatequery = "update ".$queryrows[$i]." set value = 5";
    $updateresult = mysql_query($updatequery);

$valuequery = "select gangname, value from ".$queryrows[$i];
$valueresult = mysql_query($valuequery);
$valuerows = mysql_fetch_array($valueresult);

echo "<b>Number of rows returned after update: ".$valuerows."<br><br></b>";
echo "<b>Value of first result: ".$valuerows[0]."<br><br></b>";
echo "\$i= ".$i."<br><br>";
}

I want to run an updating mysql query from an array of tables.

When I run this code, it seems that the first loop goes ok, then I get this error message:

Notice: Undefined offset: 1 in C:\Program Files\Apache Group\Apache2\htdocs\Test Stuff\individualtabletest.php on line 23

Notice: Undefined offset: 1 in C:\Program Files\Apache Group\Apache2\htdocs\Test Stuff\individualtabletest.php on line 27

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\Test Stuff\individualtabletest.php on line 29
Number of rows returned after update:

Value of first result:

$i= 1

diego25

My guess is that $queryrows is wrong. Maybe it's out of scope or something? Where is it coming from? Did you try doing a print_r on $queryrows?

Diego

NoFear

Originally posted by diego25
My guess is that $queryrows is wrong. Maybe it's out of scope or something? Where is it coming from? Did you try doing a print_r on $queryrows?

Diego

This is my entire code. I believe $queryrows is ok, because the FIRST LOOP prints out how it is supposed to. When the loop swings around to go again, I get the error messages that I posted above. It's really weird, but it's also essential for me to figure this problem out, as it's an integral part to what I'm programming.

$query = "select username from user_info";
$result = mysql_query($query);
$queryrows = mysql_fetch_row($result);
$numberofrows = mysql_num_rows($result);
echo "<b>Number of rows from initial query: ".$numberofrows."<br><br></b>";
echo "<b>Value of first result: ".$queryrows[0]."<br><br><b>";

for($i=0; $i<$numberofrows;){
    $updatequery = "update ".$queryrows[$i]." set value = 5";
    $updateresult = mysql_query($updatequery);

$valuequery = "select gangname, value from ".$queryrows[$i];
$valueresult = mysql_query($valuequery);
$valuerows = mysql_fetch_array($valueresult);

echo "<b>Number of rows returned after update: ".$valuerows."<br><br></b>";
echo "<b>Value of first result: ".$valuerows[0]."<br><br></b>";
echo "\$i= ".$i."<br><br>";
$i++;
}

Note that the errors (lines 23 and 27) are the lines in the code for "$updatequery =" and "$valuequery ="

What IS an undefined offset, anyway? I've searched the net all night and I can't find any documentation on this error, anywhere. Just pages where this error has occured.

diego25

Well, if you're trying to select username, then how many columns will the result have? 1, right? So when you do this:
$queryrows = mysql_fetch_row($result);

$queryrows will only have 1 column, which has index 0. Therefore in your loop, when $i is 0, $queryrows[$i] is fine. But when $i is 1, $queryrows[$i] is wrong because there's only one column.

Diego

NoFear

Correct, there is only one column, but there are 3 rows being returned (and more later on). These rows being returned are the names of tables that I'm trying to loop through to update.

When I output the number of rows on screen from this:
$queryrows = mysql_fetch_row($result);
It prints out that there are 3 rows. This should mean that I can call the values for $queryrows[0-2], and it will return the ROWS, not the columns, correct? This is what I was trying to do, and it's not working. Or maybe I'm just confusing my MySQL commands.... Any idea what I can change to do this correctly?

diego25

No, incorrect.
This:
$queryrows = mysql_fetch_row($result);
will fetch one row at a time. And since each row contains only one column, it will only have $queryrows[0] defined.

check out http://www.php.net/mysql_fetch_row

Diego

NoFear

Originally posted by diego25
No, incorrect.
This:
$queryrows = mysql_fetch_row($result);
will fetch one row at a time. And since each row contains only one column, it will only have $queryrows[0] defined.

check out http://www.php.net/mysql_fetch_row

Diego

Ok then.
Now that I understand that, what function can I use to gather the rows that were returned from a MySQL query, instead of the columns?

NoFear

I've figured out how to make the code work how I want it to, and I'll post the source when I get home from work today. Needless to say, it was quite different from what I posted above.