Asperon Posted July 30, 2007 Share Posted July 30, 2007 so I want to generate a random number $text = mt_rand() and have that number checked with the database to makesure it already does not exist. I have $query = "SELECT repNumber FROM salesrep"; $result = mysql_query($query) or die('Query failed: '.mysql_error()); to get the data from the table, how do loop a variable like $text against that result to see if the contents of $text are not already in the table, and then if that number exists to do $text = mt_rand() again and then loop again..and do that until it finds a number that isn't already created, and then writes that number to the table. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted July 30, 2007 Share Posted July 30, 2007 Something like: <?php $num = 1; while($num != 0){ $rand = mt_rand(1,10000); $sql = mysql_query("SELECT count(*) FROM salesrep WHERE repNumber=$rand"); $num = mysql_result($sql,0); } echo 'Unique redNumber: '.$rand; ?> However, wouldn't you be better just using an auto-increment field to have a unique number for your reps? Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted July 30, 2007 Share Posted July 30, 2007 Why dont you use $text within a WHERE clause letting MySQL do the comparison instead: <?php $query = "SELECT repNumber FROM salesrep WHERE repNumber='$text'"; $result = mysql_query($query) or die('Query failed: '.mysql_error()); // check that mysql returned a row // if it did there was a match if(mysql_num_rows($result) == 1) { echo $text . ' - Match Found'; } else { echo $text . ' - Match Not Found'; } ?> Quote Link to comment Share on other sites More sharing options...
Asperon Posted July 30, 2007 Author Share Posted July 30, 2007 I have a repID that is auto_incement that is not viewable by the sales rep, that is for database purposes. the repNumber is going to be a generated number that will be unique to them, but don't want any kind of pattern to it, so that the first one isn't 10001 and the next isn't 10002 etc. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted July 30, 2007 Share Posted July 30, 2007 Ok, well what i suggested should work, although, on second thoughts, i would imagine it might be more efficient to generate an array of the rep numbers that already exist, and loop untill you find a number thats not in that array: <?php $query = "SELECT repNumber FROM salesrep"; $result = mysql_query($query) or die('Query failed: '.mysql_error()); $numbers = array(); while($row = mysql_fetch_assoc($result)){ $numbers[] = $row['repNumber']; } $rand = mt_rand(1,10000); while(in_array($rand,$numbers)){ $rand = mt_rand(1,10000); } //insert using $rand ?> Otherwise you could end up doing quite a few queries, at least with this way, it'll only be the one. Quote Link to comment Share on other sites More sharing options...
Asperon Posted July 30, 2007 Author Share Posted July 30, 2007 what exactly does the mysql_fetch_assoc() do? does it take the next row and insert it into the array? Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted July 30, 2007 Share Posted July 30, 2007 Well, as described on the manual, mysql_fetch_assoc returns an associative array of the current row and moves the internal pointer ahead. So, by using a loop like the one i posted, you loop through each row returned from your query. This allows you to create an array of all the existing repNumbers Quote Link to comment Share on other sites More sharing options...
Asperon Posted July 30, 2007 Author Share Posted July 30, 2007 understood thank you Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted July 30, 2007 Share Posted July 30, 2007 No problem. And if you did understand it, you did better than me. Took me an age to grasp the concept! Quote Link to comment Share on other sites More sharing options...
Asperon Posted July 30, 2007 Author Share Posted July 30, 2007 I've been in and out a couple programming languages, I just barely started looking at php a week or so ago, I'm an amatuer web programmer on mission from my boss to do this project he wants done. so I just started looking at php and mysql about 2 weeks ago lol, so I'm still trying to understand things and how it all works. thank you for your help. I got it working like a charm. thought I'm sure that I'm still doing things the long way..here is the code to the page..I haven't done he password shal1() function stuff yet, but its coming. I just figured out the basics of $_SESSION variables and how to do secure login etc.. <?php $firstName = $_POST['firstName']; $lastName = $_POST['lastName']; $gender = $_POST['gender']; $address = $_POST['address']; $state = $_POST['state']; $city = $_POST['city']; $zip = $_POST['zip']; $email = $_POST['email']; $password = $_POST['password']; $confirmPassword = $_POST['confirmPassword']; ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <?php if(!$firstName || !$lastName || !$gender || !$address || !$state || !$city || !$zip || !$email || !$password || !$confirmPassword) { echo 'Incomplete form. Please go '."<a href='javascript:history.back()'>back</a>".' complete all fields'; exit; } if($password != $confirmPassword) { echo 'Passwords do not match. Please go '."<a href='javascript:history.back()'>back</a>".' and re-enter your passwords.'; exit; } // Connecting, selecting database $link = mysql_connect('l****', '****', '****') or die('Could not connect: ' . mysql_error()); echo 'Connected successfully'; mysql_select_db('*****') or die('Could not select database'); //check for repNumber $query_repNumber = "SELECT repNumber FROM salesrep"; $result_repNumber = mysql_query($query_repNumber) or die('Query failed: '.mysql_error()); $numbers = array(); while($row = mysql_fetch_assoc($result_repNumber)){ $numbers[] = $row['repNumber']; } $rand = mt_rand(); while(in_array($rand,$numbers)){ $rand = mt_rand(); } // insert into database $query="INSERT INTO salesrep (repNumber,firstName,lastName,gender,address,city,state,zip,email,password) VALUES ('$rand','$firstName','$lastName','$gender','$address','$city','$state','$zip','$email','$password')"; $result = mysql_query($query); // Free resultset mysql_free_result($result); mysql_free_result($result_repNumber); // Closing connection mysql_close($link); ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.