Is this simple query even possible?

[cunoodle2]

I'm looking to do this..

 

INSERT INTO `User` ('Name', 'Key1', 'Key2') VALUES ('Curly', RAND(), RAND());

 

Looks simple enough right?  Well here is how I want to modify it and I'm not sure how to do it.  Key1 and Key2 are integer fields.  IF the group of random numbers chosen by the query are already in the table then I would like them to choose a different numbers. 

 

Say my table was...

ID     Key1    Key2
1      345      574
2      942      455

 

I wouldn't care if it inserted "345" in another column in the Key1 column.  I just would NOT want another record to have both "345" AND "574." 

 

I know that I can do this with a few loops in php but I just wanted to do all of this in SQL (if possible) in a single statement. 

 

Thanks again.

[kickstart]

Hi

 

Interesting one.

 

I have found a way to do it (I think) but pretty horrible so would be interested to see anyone elses suggestions:-

 

INSERT INTO `User` (`Name`, `Key1`, `Key2`) 
SELECT 'Curly',RandA, RandB
FROM (SELECT FLOOR(100 + RAND() * (899)) RandA, FLOOR(100 + RAND() * (899)) RandB
UNION
SELECT FLOOR(100 + RAND() * (899)) RandA, FLOOR(100 + RAND() * (899)) RandB
UNION
SELECT FLOOR(100 + RAND() * (899)) RandA, FLOOR(100 + RAND() * (899)) RandB
UNION
SELECT FLOOR(100 + RAND() * (899)) RandA, FLOOR(100 + RAND() * (899)) RandB
UNION
SELECT FLOOR(100 + RAND() * (899)) RandA, FLOOR(100 + RAND() * (899)) RandB
UNION
SELECT FLOOR(100 + RAND() * (899)) RandA, FLOOR(100 + RAND() * (899)) RandB
UNION
SELECT FLOOR(100 + RAND() * (899)) RandA, FLOOR(100 + RAND() * (899)) RandB
UNION
SELECT FLOOR(100 + RAND() * (899)) RandA, FLOOR(100 + RAND() * (899)) RandB) as Fred
WHERE RandA NOT IN (SELECT Key1 from `User` z WHERE z.Key2 = Fred.RandB)
LIMIT 1

 

Basically generates 8 pairs of random numbers (numbers between 100 and 999) and selects those which are not in use from those 8 and limits it to 1 for the insert. Ultimatly not perfect as you could potentially have all 8 pairs already in use.

 

All the best

 

Keith

[cunoodle2]

Keith

 

I VERY much appreciate that and it gives me a little more insight into what I'm doing.  I had something along those lines but was only using 6 pairs of numbers.  I'm guessing that I need to use some kind of loop in the statement (is that even possible??)  because yes any of the pairs of numbers could be in use and I could simply increase it to 12, 16 (or more) sets of numbers.  I can do it in php/mysql with two different queries and a loop but was wondering if I could just do it in one shot.  I don't think it is possible though.

 

Is there a way to do a "loop" in a SQL statement?

[corbin]

You could always just set a UNIQUE constraint on both columns (as in both columns as a group, not both individually).

 

Then, if the query fails, you could just issue another one, assuming you're willing to always assume the query is failing because of the UNIQUE constraint.

 

 

(A unique key though will stop aa and bb, but it will not stop ab and ba.)

[kickstart]

Hi

 

Another idea. This is possibly more elegant in theory, but is VERY resource hungry (if you add clauses for the thousands to the 2 inner selects then my PC runs out of memory trying to run it).

 

INSERT INTO `User` (`Name`, `Key1`, `Key2`) 
SELECT 'Curly', ourtable1.ournumbers AS RandA, ourtable2.ournumbers AS RandB
FROM (

SELECT hundreds.i *100 + tens.i *10 + units.i AS ournumbers
FROM integers AS hundreds
CROSS JOIN integers AS tens
CROSS JOIN integers AS units
) AS ourtable1
JOIN (

SELECT hundreds.i *100 + tens.i *10 + units.i AS ournumbers
FROM integers AS hundreds
CROSS JOIN integers AS tens
CROSS JOIN integers AS units
) AS ourtable2
WHERE ourtable1.ournumbers NOT
IN (SELECT Key1
FROM `user` z
WHERE z.Key2 = ourtable2.ournumbers) 
ORDER BY Rand()
LIMIT 1

 

All the best

 

Keith

[cunoodle2]

You could always just set a UNIQUE constraint on both columns (as in both columns as a group, not both individually).

 

Then, if the query fails, you could just issue another one, assuming you're willing to always assume the query is failing because of the UNIQUE constraint.

 

 

(A unique key though will stop aa and bb, but it will not stop ab and ba.)

I considered that but I'm more so concerned with "unique groups" as apposed to unique columns.  I may just be better off doing this with two different queries, php and a loop.  Unless anyone else has any other ideas?  This is so simple yet so complex at the same time.

[cunoodle2]

Hi

 

Another idea. This is possibly more elegant in theory, but is VERY resource hungry (if you add clauses for the thousands to the 2 inner selects then my PC runs out of memory trying to run it).

Keith

Keith you clearly have some raw sql talent and would school me in this stuff.  I appreciate your response but was hoping for something less resource hungry.  I may just be better off doing this with two different queries, php and a loop.  Unless anyone else has any other ideas...

 

Again Keith appreciate your input VERY much.

[cunoodle2]

For now I'm going to do the following unless someone else comes up with a single statement.  It uses two queries (or more depending on the number of times through the loop) but it does the trick.

 

<?php

do{
//get three random numbers to be used for security tracking in database
$k1 = rand();
$k2 = rand();
$k3 = rand();

$stmt = $db->prepare("SELECT `K1` FROM `Member` where K1 = ?, K2 = ? AND K3 = ?;");
$stmt->execute(array($k1, $k2, $k3));
$result = $stmt->fetch(PDO::FETCH_ASSOC);
} while ($k1 == $result["K1"]);

//then do big insert here with security numbers and everything else
?>

 

This will be used again unless someone comes up with anything better.  Any help would be greatly appreciated =)

[Daniel0]

Well, you definitely do not want to prepare the statement within the loop. Doing that you throw away the performance gains you would otherwise get. Also, mt_rand is faster than rand(). Also, it should be WHERE conditions should be separated by AND and not a comma.

[fenway]

You could always just set a UNIQUE constraint on both columns (as in both columns as a group, not both individually).

 

Then, if the query fails, you could just issue another one, assuming you're willing to always assume the query is failing because of the UNIQUE constraint.

 

 

(A unique key though will stop aa and bb, but it will not stop ab and ba.)

I considered that but I'm more so concerned with "unique groups" as apposed to unique columns.  I may just be better off doing this with two different queries, php and a loop.  Unless anyone else has any other ideas?  This is so simple yet so complex at the same time.

Wait, why won't this work? Make the UNIQUE index span both columns... then use INSERT IGNORE.