Updating a DB with a loop using rand() and time()

Each row needs a different value

[henry0]

I am trying to pre-populate a table made out of a long list of emails from XL
With a rand password and time().
Done a CSV and created a MySQL table
Next I need to update that table by updating each row with password out of a rand() and a time out of time()
But while looping I verified (echoed) that I create for example in my case 9 different values of PW, and time,
I cannot update each row with a different value
As is it updates 9 times with the same rand value
How may I fix it
I also tried do while but got nowhere

for ($r = 1; $r <= 9; $r++)
{

$rand=mKey();
//echo"$rand";

$new_time=time();
sleep(1);
//echo"$new_time<br>";

$sql = "update load_email
set password= '$rand',
regTime= '$new_time' ";
$result = mysql_query($sql, $conn);
}

[Demaestro]

You have no "where" clause in your update statement.

So every time it does the loop it updates every record in your table.... what you will be left with is all rows showing the last iteration of the loop.

Here is an example of what I mean.

Here is your table before you execute that code

id ¦ pass ¦time
1 ¦ null ¦ null
2 ¦ null ¦ null
3 ¦ null ¦ null
4 ¦ null ¦ null
5 ¦ null ¦ null
6 ¦ null ¦ null

The first time through the loop it will get a random password and time then update the whole table so it would look like this

id ¦ pass ¦time
1 ¦ loop1_val ¦ loop1_val
2 ¦ loop1_val ¦ loop1_val
3 ¦ loop1_val ¦ loop1_val
4 ¦ loop1_val ¦ loop1_val
5 ¦ loop1_val ¦ loop1_val
6 ¦ loop1_val ¦ loop1_val

That is after only 1 loop... the second time through it will look like this

id ¦ pass ¦time
1 ¦ loop2_val ¦ loop2_val
2 ¦ loop2_val ¦ loop2_val
3 ¦ loop2_val ¦ loop2_val
4 ¦ loop2_val ¦ loop2_val
5 ¦ loop2_val ¦ loop2_val
6 ¦ loop2_val ¦ loop2_val

Since you have no "where" clause it updates every row every time until the last loop you are left with

id ¦ pass ¦time
1 ¦ loop_final_val ¦ loop_final_val
2 ¦ loop_final_val ¦ loop_final_val
3 ¦ loop_final_val ¦ loop_final_val
4 ¦ loop_final_val ¦ loop_final_val
5 ¦ loop_final_val ¦ loop_final_val
6 ¦ loop_final_val ¦ loop_final_val

So all you ever see are the final loop values.

You need to say
for ($r = 1; $r <= 9; $r++)
{

$rand=mKey();
//echo"$rand";

$new_time=time();
sleep(1);
//echo"$new_time<br>";

$sql = "update load_email
set password= '$rand',
regTime= '$new_time' where id=$r";

$result = mysql_query($sql, $conn);

This would work if your table had an id column and values of 1,2,3,4,5,6,7,8,9

Then you would get this

id ¦ pass ¦time
1 ¦ loop_1_val ¦ loop_1_val
2 ¦ loop_2_val ¦ loop_2_val
3 ¦ loop_3_val ¦ loop_3_val
4 ¦ loop_4_val ¦ loop_4_val
5 ¦ loop_5_val ¦ loop_5_val
6 ¦ loop_6_val ¦ loop_6_val

Or another way.....rather then trying pass an ID to the where clause....... if you have null values in those fields now you could say

for ($r = 1; $r <= 9; $r++)
{

$rand=mKey();
//echo"$rand";

$new_time=time();
sleep(1);
//echo"$new_time<br>";

$sql = "update load_email
set password= '$rand',
regTime= '$new_time' where password is null limit 1";

$result = mysql_query($sql, $conn);

}

This way would find only 1 row with no password and then update it... the next time through it would find a different row because the one it just wrote to would now have a value... it would update blank rows one at a time.

I hope this makes sense and is what you need to move past this issue... if not post back and I will help you through it.

[edited by: Demaestro at 8:02 pm (utc) on July 12, 2007]

[henry0]

Hi Demaestro,
Thanks for the demo!
I was playing with it and tried both ways
Sorry to report that it still only enter a similar input per row.

I even set a new field named aaa, entered in each row a similar value of bbb
and did a .... where aaa='bbb'
Again got the same result
I have the feeling that we are not far from making it
but just missing a little something like if the sql was not iterating properly
<edit>
of course # 9 is just a test :)
the real thing is quite another number
</edit>

[Demaestro]

hmmm....

Can you show me what it gives you when you echo the results.

[mattclayb]

Try this -

(this is using the example where you have an id column of integers from 1-9)

$r = 1;

while($r <= 9){
$rand = mKey();
$unique = uniqid($rand);

$new_time=time();

mysql_query("UPDATE load_email SET password = '$unique', regTime = '$new_time' WHERE id = $r");

$r++;
}

[henry0]

Hi mattclayb
thanks but I can not use it since ID needs to be empty so it will be incremented by the final table where I need adding the email list by only inserting the new rows we are trying to get.

Demaestro
Here is what I got
Done with WHERE state=CT
But no updates performed!
Result is:
jQOzUz
1184326816
KukPXa
1184326817
VMgbvO
1184326818
oJBTcK
1184326819
FzApyG
1184326820
njzKuE
1184326821
ALzlSi
1184326822
IqtXAD
1184326823
vFelRc
1184326824

If I used the WHERE password is null limit 1
I get the same kind of “echo”
But no updates are performed
Despite password empty and NULL

Sorry if I Do not respond in a heart bit but today due to a client I will be in and out :)

Table structure
CREATE TABLE `load_email` (
`email` varchar(150) NOT NULL default '',
`password` varchar(100) default NULL,
`title` varchar(10) default NULL,
`firstName` varchar(255) NOT NULL default '',
`lastName` varchar(255) NOT NULL default '',
`add_1` varchar(100) NOT NULL default '',
`add_2` varchar(100) NOT NULL default '',
`city` varchar(100) NOT NULL default '',
`state` varchar(100) NOT NULL default '',
`zoneId` int(3) NOT NULL default '0',
`zip` varchar(15) NOT NULL default '',
`country` char(3) NOT NULL default '',
`phone` varchar(20) NOT NULL default '',
`mobile` varchar(50) default NULL,

`customer_id` varchar(1) default NULL,
(It does not show increment because I do not need that to create only the new ready to insert rows, as mentioned before when finally inserted id will be incremented by the "real" table)

`regTime` int(10) NOT NULL,
`ipAddress` varchar(30) NOT NULL default '',
`noOrders` int(11) default '0',
`optIn1st` int(11) NOT NULL default '0',
`htmlEmail` int(11) NOT NULL default '1',
`type` int(11) default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

<EDIT>
Last: if I keep customer ID empty
and do WHERE customer id=$r
it updates
but no sequence only still the same data for each rows
</EDIT>

[WesleyC]

Might I suggest using an autoincrement ID column, even if you don't need the ID from it in the final table? That way you could run scripts by the ID of the row, as other members have already described. Then, when you move the data to the final table, simply don't move the ID column.

[henry0]

WesleyC,
Welcome to WebmasterWorld!
OK that did it
reestablishing an id and using id=$r worked
case closed
thanks all.