for loop not updating interaction var

MySQL database
#1

i’m trying to update a confirmation column in my database with an $i var from a for loop.

[php]

<?php include("../include/connect_db.php"); $nRecords = mysql_num_rows($query) + 100; for ($i = 100; $i <= $nRecords; $i++) { mysql_query("UPDATE debtor_payments SET `confirmation`= '$i' WHERE `confirmation`=0") or die(mysql_error()); } ?>

[/php]

I don’t want to do it manually since we talking about 500+ records.
the loop runs and set all records confirmation to 100 which it should be 100,101,102,103 and so on.

#2

anybody ? i still need help with this.

#3

Could you include the query-part? Basically looking to debug mysql_num_rows($query) to see how many rows are there. If num rows return null / 0 then you will get the result you are describing.

#4

Hello,

First things first. Since you really didn’t state what the issue with the for statement I’m taking a guess that the query isn’t working properly and I think I know why. You have quotes around the columns in your table where you shouldn’t. Try this query:

[php]mysql_query(“UPDATE debtor_payment SET confirmation = ‘$i’ WHERE confirmation = ‘0’”)
or die(mysql_error());[/php]

Secondly, I don’t think this post qualifies as a sticky post as this post doesn’t help with the mysql category. Please do not create stickies on non-helpful posts.

Cheers!

EDIT: JimL, I admire your strength in not going there with the mysql query code.

#5

Why not? I could agree that backticks could lead to bad naming conventions as you’re suddenly allowed to have whatever table/column names you want, but they’re perfectly legal to use and it’s really just a question of preference. If you want to use any of the reserved words you also have to put the names in backticks so you won’t confuse MySQL.
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

#6

JimL,

Going on past experience I’ve never had queries work with the quotes surrounding the column name; I’ve tried it before. Maybe it does, but never for me. But now come to think about it looking at his code again, he doesn’t even state the first query that he is getting the row count on, unless he has it in the connect_db.php file; which isn’t advisable.

Cheers!

#7

I use single quotes or backticks as necessary on my queries. But I think the problem lies in the single quotes around the variable $i. It’s going as a literal not a variable.

#8

Astonecipher,

It was late and I didn’t realize it was backticks not quotes so that’s fine. The whole query is surrounded in double-quotes so the variable $i will be taken as the value within. I think it’s the fact that there is no query being done on the num_row function so it’s passing in null. Have not gotten anything back from OP to see if this is the issue though.

Cheers!

#9

Looks to me like single quotes surround the variable, which is not a string. That would be the same as using a post array which is used, poorly, as ’ ". $var . " ’

#10

Astonecipher,

I did a test on my local server and the following code:

[php]<?php

$var = ‘pie’;

echo “SELECT * FROM table WHERE food=’$var’”;

?>[/php]

…displays this:

SELECT * FROM table WHERE food='pie'

Remember, even though the variable is surrounded by single-quotes, the fact that the entire string is surrounded by double-quotes allows the variable to give its value. I’m still going with the null value in the num_rows function but until the OP get back to us, we’ll never know.

Cheers!

#11

[php]
$query = mysql_query("SELECT * FROM debtor_payments") or die(“Error in query:
”.mysql_error());

$nRecords = mysql_num_rows($query);
[/php]

#12

My apologies, That day I wanted to finish the script the same day and was looking for a fast reply, which didn’t happen, I removed the sticky thread.

#13

backtick is used to avoid naming errors in your query for example if you name your table name SELECT you will have an error although I have not done it yet using SELECT should be all good.

#14

i tested to see if the query was being updated and here is the reult

UPDATE debtor_payments SET `confirmation`= '0' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '1' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '2' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '3' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '4' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '5' WHERE `confirmation`=0
-----------------------------------------------------------
------------------AND SO ON ON------------------------------
----------------------------------------------------------
UPDATE debtor_payments SET `confirmation`= '279' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '280' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '281' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '282' WHERE `confirmation`=0
UPDATE debtor_payments SET `confirmation`= '283' WHERE `confirmation`=0

so the loop ran 283 times however in the database, i see that all records in the confirmation field that had 0 now they all 1, any Idea?

#15

Wilson,

Your problem stems from how mysql runs the query. The first pass will change all the confirmations to 0, the second pass will work as well as the confirmations are still zero. Now when the counter gets to 2, all of the confirmations in all the rows have turned their values to 1 as the UPDATE query will turn ALL rows in the database that matches your condition. How you have it, once you turn the value of confirmation into something besides zero, the UPDATE query’s condition is no longer met anymore and the remaining queries aren’t performed. This is your problem.

Cheers!

#16

Wilson,

To fix your issue, add LIMIT 1 to the end of the query string and it should work how you want it to.

Cheers!

#17

OpzMaster,

you are totally right. you are the man!!!

so my approach would be:
[php]
include("…/include/connect_db.php");

$query = mysql_query(“SELECT payment_id FROM debtor_payments WHERE confirmation=0”) or die(“Error in query:
”.mysql_error());

$id_arr = array();//store unique ID in an array

while ($rows = mysql_fetch_assoc($query))
{
$id_arr[] = $rows[‘payment_id’];
}

for ($i = 0; $i <= sizeof($id_arr); $i++)
{
$SQL = “UPDATE debtor_payments SET confirmation= ‘$i’ WHERE payment_id=’{$id_arr[$i]}’”;
mysql_query($SQL) or die(mysql_error());
}
[/php]

Thanks Guys, I’m Pretty sure this approach will now work, since the other one was a a kinda ovios mistake yet really hard to spot.

#18

I came up with a better approach, thanks Man.

Sponsor our Newsletter | Privacy Policy | Terms of Service