Random number in mysql
Does anyone know how I can get a random number to be sent to a mysql table? I am trying to make a random ticket identification number for my support ticket system. Right now it just increments, but I want the number to be random.
Suzanne posted this at 00:00 — 24th March 2004.
She has: 5,507 posts
Joined: Feb 2000
I think maybe this might be a server-side question, mmm? Mayhaps?
What language are you using? Or are you talking right from the MySQL prompt?
Greg K posted this at 00:16 — 24th March 2004.
He has: 2,145 posts
Joined: Nov 2003
Are you wanting it to be random AND unique?
-Greg
airoid3000 posted this at 00:24 — 24th March 2004.
They have: 71 posts
Joined: Mar 2004
Yes.....random and unique. Otherwise the tickets will be confused.
I am using mysql in cPanel.
Mark Hensler posted this at 01:17 — 24th March 2004.
He has: 4,048 posts
Joined: Aug 2000
rand()
airoid3000 posted this at 02:54 — 24th March 2004.
They have: 71 posts
Joined: Mar 2004
I'm looking for something maybe in a php code.....
Mark Hensler posted this at 05:17 — 24th March 2004.
He has: 4,048 posts
Joined: Aug 2000
You'll need to use a loop. First, create a random string. Second, check DB to see if it exists. If so, loop. Else, exit loop with your new ticket id.
Making a random string...
Do you want your tickets to follow a pattern (# letters, # numbers, back to # letters, any dashes, etc)?
<?php// makes a California license plate (#AAA###)
$id = rand(0,9);
for ($i=0; $i<3; $i++) {
$id .= chr(rand(65,90));
}
for ($i=0; $i<3; $i++) {
$id .= rand(0,9));
}
?>
Mark Hensler
If there is no answer on Google, then there is no question.
airoid3000 posted this at 01:14 — 26th March 2004.
They have: 71 posts
Joined: Mar 2004
And how do I check the database to check if it exists, and if it does, start over?
Chroder posted this at 04:36 — 26th March 2004.
He has: 91 posts
Joined: Mar 2004
<?php// could use COUNT(*) then fetch it, not sure which is fastest
$q = mysql_query(\"SELECT id FROM table WHERE ticket_id = 4556\");
if(mysql_num_rows($q) > 0)
{
// its already there!
// could change > 0 to == 0 and use break; to break out of the loop
}
?>
I hope I got my syntax right, I've been using a DB class for a while...
The New Tech - New
Webmaster-Talk.com
Chroder.com
airoid3000 posted this at 03:15 — 28th March 2004.
They have: 71 posts
Joined: Mar 2004
I'm a little confused, can someone put those two codes together? I am trying to make a 5 digit random code.
airoid3000 posted this at 01:40 — 30th March 2004.
They have: 71 posts
Joined: Mar 2004
Anyone? I still need this code!
Chroder posted this at 03:22 — 30th March 2004.
He has: 91 posts
Joined: Mar 2004
Using Mark's code...
<?php
// keep trying until you find a unique one
while(1)
{
$id = rand(0,9);
for ($i=0; $i<3; $i++) {
$id .= chr(rand(65,90));
}
for ($i=0; $i<3; $i++) {
$id .= rand(0,9));
}
$q = mysql_query(\"SELECT id FROM table WHERE ticket_id = $id\");
if(mysql_num_rows($q) < 1)
{
break;
}
}
// we're out, so you've found a unique one
echo \"Unique TicketID: $id\";
?>
The New Tech - New
Webmaster-Talk.com
Chroder.com
Mark Hensler posted this at 00:42 — 1st April 2004.
He has: 4,048 posts
Joined: Aug 2000
if all you need is five numerics, then...
<?php
// keep trying until you find a unique one
while(1)
{
$id = rand(10000,99999));
$q = mysql_query(\"SELECT id FROM table WHERE ticket_id = $id\");
if(mysql_num_rows($q) < 1)
{
break;
}
}
// we're out, so you've found a unique one
echo \"Unique TicketID: $id\";
?>
Mark Hensler
If there is no answer on Google, then there is no question.
airoid3000 posted this at 22:45 — 8th April 2004.
They have: 71 posts
Joined: Mar 2004
I'm having trouble finding where to put that code. Right now I believe the php sends a null command to mysql and mysql automatically increments the number.
Anyone have an idea or possible look of where it would go?
airoid3000 posted this at 23:20 — 8th April 2004.
They have: 71 posts
Joined: Mar 2004
I believe this is where the ID is created:
$sql = "INSERT into $mysql_tickets_table values(
NULL, '$time', $sg, '$division', 1, '$priority', '$status','$username', '$email', '$office', '$phone', '$category', '$platform', '$short', '$description', NULL, 0, '$time', ''Where the NULL is above (the first one) is where I think it is. Like I said the database automatically increments the ID now.
How can i place the random ID Code there?
Mark Hensler posted this at 23:30 — 8th April 2004.
He has: 4,048 posts
Joined: Aug 2000
swap out NULL for '$id'
airoid3000 posted this at 00:10 — 9th April 2004.
They have: 71 posts
Joined: Mar 2004
I get a parse error. Anything wrong with that code you gave me?
airoid3000 posted this at 05:37 — 9th April 2004.
They have: 71 posts
Joined: Mar 2004
Below is an entire section from the script that creates the ticket and its ID. I received an error that says: Parse error: parse error in /hidden/support/tcreate.php on line 68. Line 68 is where the while(1) statement is. (Notice I changed $id to $makerandomid to stop confusing with other $id's in the file.
REMOVED
airoid3000 posted this at 16:43 — 9th April 2004.
They have: 71 posts
Joined: Mar 2004
Those changes are correct, but I still get a parse error. I think that maybe I'm putting the code in the wrong place or something. Here is what it looks like now:
<?php
if(isset($create)){
//if the system is public, set some cookies so user information will be recorded for next time
setcookie(\"cookie_user_name\", $username, time()+31536000);
setcookie(\"cookie_email\", $email, time()+31536000);
setcookie(\"cookie_office\", $office, time()+31536000);
setcookie(\"cookie_phone\", $phone, time()+31536000);
$time = time() + ($time_offset*3600);
while(1)
{
$makerandomid = rand(10000,99999));
$q = mysql_query(\"SELECT id FROM $mysql_tickets_table WHERE id = $makerandomid\");
if(mysql_num_rows($q) < 1)
{
break;
}
}
if($group == '' || $priority == '' || $username == '' || $description == ''){
header(\"Location: index.php?t=terr\");
}
else{
if($short == ''){
$short = \"$lang_nodesc\";
}
if($sg == ''){
$sg = 1;
}
$status = getStatus(getLowestRank($mysql_tstatus_table));
$short = addslashes(stripScripts($short));
$description = addslashes(stripScripts($description));
$sql = \"INSERT into $mysql_tickets_table values('$makerandomid', '$time', $sg, 'Division', 1, '$priority', '$status',
'$username', '$email', '$office', '$phone', '$category', '$platform', '$short', '$description', NULL, 0, '$time', ''
)\";
?>
airoid3000 posted this at 15:54 — 10th April 2004.
They have: 71 posts
Joined: Mar 2004
Fixed! There was a small typo in the code you gave me.
This:
$makerandomid = rand(10000,99999));
Should have been this:
$makerandomid = rand(10000,99999);
Thanks for your help!!!
Mark Hensler posted this at 03:00 — 9th April 2004.
He has: 4,048 posts
Joined: Aug 2000
values('$id', '$time'
If you still get an error, copy & paste your entire code segment here (with info about the error your getting).
Mark Hensler posted this at 16:20 — 9th April 2004.
He has: 4,048 posts
Joined: Aug 2000
I'm assuming the primary key of the table is "id"... so replace with:
$q = mysql_query("SELECT id FROM $mysql_tickets_table WHERE id = $makerandomid");
Mark Hensler
If there is no answer on Google, then there is no question.
Mark Hensler posted this at 14:19 — 10th April 2004.
He has: 4,048 posts
Joined: Aug 2000
try:
while (true)
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.