[Resolved] Update database field with loops

[cbrknight]

What I want to do is update empty fields in my database with random ID#. I have a script I am using to generate the $random variable.

I have it in a Do While Loop.

The flaw in my logic seems to be that the code is updating all empty fields at the same time with the same $random variable. How do I get it to update the fields one at a time with different random codes?

here is the code I am working with....

<Start code>

<?php require_once('../Connections/testdb.php'); ?><?do {$random= ""; srand((double)microtime()*1000000);

$block = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; $block .= "0123456789";

for($i = 0; $i < 10; $i++) { $random .= substr($block,(rand()%(strlen($block))), 1); }

mysql_select_db($database_testdb, $testdb);$query_username = "SELECT username FROM users WHERE users.userid = ''";$username = mysql_query($query_username, $testdb) or die(mysql_error());$row_username = mysql_fetch_assoc($username);$totalRows_username = mysql_num_rows($username);

mysql_select_db($database_testdb, $testdb);$query_IDupdate = "UPDATE users SET users.userid = '$random' WHERE users.userid = ''";$IDupdate = mysql_query($query_IDupdate, $testdb) or die(mysql_error());

echo $totalRows_username;echo $IDupdate;echo ("<br>$random");

} while ($totalRows_username != 0);

mysql_free_result($username);?>

</End Code>

[paulnaj]

Hi,

Is username a 'unique' key? If not, is there a primary key for this table?
The userid would be the standard primary key but it can't be in your case, because of the empty values.

If you haven't got one already, have a think about sticking one in ... life's so much easier.

At the moment, on the first iteration of your loop, you update ALL the fields in one go (UPDATE ... WHERE userid='').

I suppose that you actually only want to update the row you already that you plucked off the top of the list, but this will only work if you can uniquely identify that row, usually done with the primary key.

Paul.

[cbrknight]

I can add a unique Identifier easily enough.

So let me guess.. then I would use a counter to increment the rows instead of the userid field?

[paulnaj]

Well, suppose your unique id was called 'uniqueid' (!), then ...

<?php 
// Seed the random number once per page
srand((double)microtime()*1000000);
// Next two are constants ... keep outside of loops
$block = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
$block_length = strlen($block);

// Call db functions
require_once('../Connections/testdb.php');

// Only need to select db once
mysql_select_db($database_testdb, $testdb);

// Get all the uniqueid's in one go
$user_sql = "SELECT uniqueid FROM users WHERE userid = ''";
$user_result = mysql_query($user_sql) or die(mysql_error());

// Cycle through the uniqueid's
while($row = mysql_fetch_array($user_result)){
	// Generate new random text
	$random = ""; 
	for($i = 0; $i < 10; $i++) {
		$randchar = rand() % $block_length;
		$random .= substr($block, $randchar, 1); 
	}
	// Update the current user
	$uniqueid = $user['uniqueid'];
	$update_sql = "UPDATE users SET userid = '".$random."' WHERE uniqueid = '".$uniqueid."'";

// This will update exactly one field.
mysql_query($update_sql) or die(mysql_error());
}

mysql_free_result($username);
?> 

The only problem I can foresee (except for bugs ... I can't debug this w/o building a copy of your db) is that it's still possible to generate an existing 'random' userid, just by chance.

Paul 🙂

PS. Hope you're not offended by my tweaking your code substantially ... it's easier than trying to explain. If you don't get the general method, come back and I'll try to explain.

[cbrknight]

Thanks for your help... it works now.

And thank you for cleaning up my code. I always like someone showing me an easier way to do something.