[SOLVED] Random Unique Number - joint MySQL

[samus]

It has been asked of me to create a randomised unique number.  I have generated the random number, however I want to make it unique by cross referrencing the database. IF the number has already been stored it shall REPEAT the function until the new random number is not in the database.  When it is unique it shall then be stored in the database.

 

<?
//random number
function generate_cust_ID()
{
srand ((double)microtime()*1000000);
$number=rand(1,9999);
return $number;
}

if (!isset($cust_ID))
{
$cust_ID=generate_cust_ID($number);
}

echo "you are visitor number $cust_ID <br><br>";


require_once ('connect_inc.php');

    			$query = "SELECT unique FROM unique_random WHERE unique = '$cust_ID'";
        		$result = mysql_query($query);
        		$num = mysql_num_rows($result);

        		if ($num> 0) {

									while ($num> 0) 
										{
											mt_srand ((double)microtime() * 100000);
											$unique=mt_srand(1,9999);
											return $unique;
											$cust_ID=generateagain($unique);

											echo "hello";

											$query = "SELECT unique FROM unique_random WHERE unique = '$cust_ID'";
        										$result = mysql_query($query);
        										$num = mysql_num_rows($result);

										}
        								} 
					else {
										$query2="INSERT INTO unique ('unique_random') VALUES ('$cust_ID')";
										$results=mysql_query($query2) OR DIE("Your SQL: " . $query2 . "<br/> Produced this Error: " . mysql_error());
								if (mysql_affected_rows() == 1) {
											echo "<center><font='black'>you have a unique number!</font>";
       					 				}
					 	}
?>

 

This is MY interpretation of how it may of worked (obviously it didn't) and this is the result I get.

 

you are visitor number 6370

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/asmith/public_html/website/datetime.php on line 38

Your SQL: INSERT INTO unique ('unique_random') VALUES ('6370')

Produced this Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'unique ('unique_random') VALUES ('6370')' at line 1

 

I have doubled checked the database and all the names look to be correct.  If you have ANY suggestions of any other ways I could have this.

 

The end result is to have this unique random number set as a cookie.

 

Any help will be brilliant.

[AndyB]

unique is a MySQL reserved word - http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

 

Avoid using reserved words as field or table names.

[samus]

Ok

 

So I changed the names.

However I still don't understand, btu I have a theory that it is mySQL matters, however, even if it is, does anyone else think how I could improve the actual PHP function?

 

<?php
//random number
function generate_cust_ID()
{
srand ((double)microtime()*1000000);
$number=rand(1,9999);
return $number;
}

if (!isset($cust_ID))
{
$cust_ID=generate_cust_ID($number);
}

echo "you are visitor number $cust_ID <br><br>";


require_once ('connect_inc.php');

    			$query = "SELECT di FROM random WHERE di = '$cust_ID'";
        		$result = mysql_query($query);
        		$num = mysql_num_rows($result);

        		if ($num> 0) {

									while ($num> 0) 
										{
											mt_srand ((double)microtime() * 100000);
											$unique=mt_srand(1,9999);
											return $unique;
											$cust_ID=generateagain($unique);

											echo "hello";

											$query = "SELECT di FROM random WHERE di = '$cust_ID'";
        										$result = mysql_query($query);
        										$num = mysql_num_rows($result);

										}
        								} 
					else {
										$query2="INSERT INTO random ('di') VALUES ('$cust_ID')";
										$results=mysql_query($query2) OR DIE("Your SQL: " . $query2 . "<br/> Produced this Error: " . mysql_error());
								if (mysql_affected_rows() == 1) {
											echo "<center><font='black'>you have a unique number!</font>";
       					 				}
					 	}
?>

 

Here is the database.

 

CREATE TABLE `random` (
`di` INT NOT NULL ,
PRIMARY KEY ( `di` )
) TYPE = MYISAM ;

 

And here now the error.

 

you are visitor number 1474

 

13/6/2007

18 : 11 : 14

Your SQL: INSERT INTO random ('di') VALUES ('1474')

Produced this Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''di') VALUES ('1474')' at line 1

 

 

DO'H  I know what was wrong XD  ('di')  Should've been (di)

 

SOLVED :B

[kenrbnsn]

Names of fields in a query should not be surrounded by single quotes. Either use back-ticks if the name conflicts with a MySQL reserved name or nothing.

 

<?php
$query2="INSERT INTO random (`di`) VALUES ('$cust_ID')";
><

or

<?php
$query2="INSERT INTO random (di) VALUES ('$cust_ID')";
?>

 

Ken