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. Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/ 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? Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311236 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'; } ?> Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311240 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. Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311242 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. Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311245 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? Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311252 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 Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311258 Share on other sites More sharing options...
Asperon Posted July 30, 2007 Author Share Posted July 30, 2007 understood thank you Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311260 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! Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311264 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> Link to comment https://forums.phpfreaks.com/topic/62530-solved-php-mysql-loop/#findComment-311275 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.