Multiple Queries in one Query & PHP

[ciber]

Hi,

 

I am currently working on a very detailed personal project during my vacation to keep me busy, but I have a problem, I am trying to create a unique id, without having the chance of a repetition of the id, even if its infinitely small. So I found the flickr ticket id system to be a good choice, since it was sequential and effective.

 

My problem now is, how can I execute 2 queries simultaneously, given the hypothetical change that if I execute them with two separate queries (mysql_query), that there is the chance that another query could be executed in between the break, causing two id's to be the same. Since mysql_query can only execute a single query I am a bit stuck.

 

I thought about possibly using CGI, but then again I have never used it, so I don't know its limitations. Can anyone suggestion anything to me for this problem, as to how I can execute two query strings in the same query.

[AbraCadaver]

I'll give it some thought while others answer, but that's what auto increment fields are for, why use flickr ticket id system (I've never heard of this) to generate a key?

[ciber]

basically I got 10 different tables, which I need each to have a unique key throughout the database.

see this link: http://code.flickr.com/blog/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/

 

also I want to transform this into my project next year for university, so im building it large scale, even though it will never run live

[ManiacDan]

Use the UUID feature of the database or the operating system.

 

-Dan

[AbraCadaver]

OK, so they show you exactly how to do it and it does use auto increment.  But instead of using auto increment to add a new key to the table where you actually want to store data, you have a table that creates the key for you and then you use it on other tables:

 

When you need a new key to use somewhere (using their example):

 

mysql_query("REPLACE INTO Tickets (stub) VALUES ('a')");
$unique_key = mysql_insert_id();
// now actually insert your data somewhere
mysql_query("INSERT INTO some_table (id, field1, field2) VALUES ($unique_key, 'some data', 'more data'");

 

 

[ciber]

OK so by using mysql_insert_id(), is there a chance that another request for a key could be executed inbetween the two queries, if your server is very busy and processing thousands of queries?

 

say for example:

R1: mysql_query("REPLACE INTO Tickets64 (stub) VALUES ('a')");

R2: mysql_query("REPLACE INTO Tickets64 (stub) VALUES ('a')");

R1: $key = mysql_insert_id();

R2: $key = mysql_insert_id();

 

where R1 is request 1 by some user, and R2 is another request by another user

[ciber]

Use the UUID feature of the database or the operating system.

 

-Dan

so if I use PHP's uniqid, will the ID's generated ALWAYS be unique? even if its run on multiple systems? That is why I would prefer using a ticketing system, since regardless it will always be unique.

[AbraCadaver]

OK so by using mysql_insert_id(), is there a chance that another request for a key could be executed inbetween the two queries, if your server is very busy and processing thousands of queries?

 

say for example:

R1: mysql_query("REPLACE INTO Tickets64 (stub) VALUES ('a')");

R2: mysql_query("REPLACE INTO Tickets64 (stub) VALUES ('a')");

R1: $key = mysql_insert_id();

R2: $key = mysql_insert_id();

 

where R1 is request 1 by some user, and R2 is another request by another user

 

I guess it's possible since the id is for the last query, though I think it's highly unlikely.  To mitigate this you could do something like this (the stub field could be a decimal type called mtime instead):

 

function get_ticket() {
   $mtime = microtime(true);
   mysql_query("REPLACE INTO Tickets (mtime) VALUES ($mtime)");
   $row = mysql_fetch_assoc(mysql_query("SELECT id FROM Tickets WHERE mtime=$mtime LIMIT 1"));

   if($row) {
      return $row['id'];
   } else {
      return get_ticket();
   }
}

 

A uniqueid either from the DB or PHP has a very slim chance of being duplicated, but what ever method you use, the best is to create the ID and then check to make sure it isn't in the DB before you use it (just an example):

 

function get_ticket() {
   $uid = uniqid();
   $exists = mysql_num_rows(mysql_query("SELECT id FROM some_table WHERE id=$uid LIMIT 1"));
   
   if(!$exits) {
      return $uid;
   } else {
      return get_ticket();
   }
}

 

Just brainstorming, not sure what is optimal.

 

[ciber]

Yea, I think I must generate it, and check rather its better that way!. Thanks for your help.