While loop driving me loopy!

lynmangoro

Hi, I am trying to implement a function that generates a random 5 digit id number when a new product is added to the database. I think that part works, the problem is the next bit when I want it to check to see whether the id number is unique and if not generates another one.
When I try out the code it just gets stuck and timeout, please help! Heres what I got...

<?
// function to generate random ID number
function createID() {
for ($i = 1; $i <= 5; $i++) {
$id .= rand(0,9);
}
return $id;
}

// generates random ID number
$id = createID();

mysql_select_db($db);
$query = "SELECT id FROM templates WHERE id = $id";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
// loop until no match is found

while ($result > 0) {
// generates random ID number
$id = createID();
$query = "SELECT id FROM templates WHERE id = $id";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
}

// output ID number
echo $id;

laserlight

Well, you could try using an infinite loop:

<?php
mysql_select_db($db);
for (;;) {
	$id = createID();
	$query = "SELECT id FROM templates WHERE id='$id'";
	$result = mysql_query($query)
		OR die("Error in query: " . $query . "." . mysql_error());
	if (mysql_num_rows($result) == 0)
		break;
}
echo $id;
?>

conversely you could try a do while loop:

<?php
mysql_select_db($db);
do {
	$id = createID();
	$query = "SELECT id FROM templates WHERE id='$id'";
	$result = mysql_query($query)
		OR die("Error in query: " . $query . "." . mysql_error());
} while (mysql_num_rows($result) > 0);
echo $id;
?>

You may have to check the variable scope though, but PHP generally is not so picky on that.

tekky

someone forgot to turn smilies off 😉

also you may want to consider checking mysql_num_rows() instead of $result, if I recall, mysql_query() only returns FALSE if the query fails, a query resulting in 0 rows is not considered a FAILURE so it still returns a TRUE value

from mysql_query() documents
Only for SELECT,SHOW,EXPLAIN or DESCRIBE statements mysql_query() returns a resource identifier or FALSE if the query was not executed correctly. For other type of SQL statements, mysql_query() returns TRUE on success and FALSE on error. A non-FALSE return value means that the query was legal and could be executed by the server. It does not indicate anything about the number of rows affected or returned. It is perfectly possible for a query to succeed but affect no rows or return no rows.

lynmangoro

Thanks, I was originally using mysql_num_rows instead of mysql_query but I get this error -

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sites/site476/web/mangoadmin/addaction.php on line 33
Error in query: SELECT id FROM templates WHERE id = 60964.

drawmack

I don't like that for loop in your code so I would do this:

<?
// function to generate random ID number
function createID($length) {

if(!ereg("^[1-9][0-9]*$",$length) {
  die("You must supply an integer for length.");
}

//get the least significant digits from the timestamp
$id = time()%(10^$length);

//zero out the last two numbers
$id = $id - ($base%100);

//tack a random number onto the end
$base = $id + rand(1,99);

return $id;
}

// generates random ID number
$id = createID();

mysql_select_db($db);
$query = "SELECT count(id) FROM templates WHERE id = $id";
$result = mysql_query($query) or die("Error in query: $query");
$exists = mysql_result($result,0,0)
// loop until no match is found

while ($exists) {
// generates random ID number
$id = createID();
$result = mysql_query($query) or die("Error in query: $query");
$exists = mysql_result($result,0,0)
}

// output ID number
echo $id;

?>

laserlight

hmm... yeah, should have checked about the smilie thing.

anyway, the invalid MySQL result resource means your query is incorrect.
That is confirmed by your "Error in query" check.
Does the templates table exist, and does it have an id column?
Did you connect to the database correctly?

laserlight

does your createID() function actually work, drawmack?

I tried it out, and after fixing a slight typo it gave me a single digit number for createID(5)

drawmack

the only way that should happen is if the first x digits of the timestamp are zero.

lynmangoro

laserlight,

I have used your first suggestion of a do while loop and it is now working fine - so thanks, and thanks for all the other replies too.

Just for the record, the template table definitely exists, there is definitely an id column and the database connection was ok - I checked all of this and have no prblems just simply pulling data from this table and column.

Is there any other reason why I would get the error I pasted?

tekky

would depend on where you put the mysql_num_rows() at too... after the query obviously is the right place 🙂