[RESOLVED] interesting problem

[slimjim]

I have a issue I never had to do before.
I have 2 fields, field_1 and field_2
How can I first see if either of the both numbers are found in any row together.
If not then do the insert
If both numbers are found in same row and any row, then do the random numbers again until not found.

Thing is I dont want to keep submitting a form until the right random numbers comes up that are not in the database. Need to do the insert first time around.

Any idea...

//this would go in field_1
$rand1="127";		        

$rand1b = ($rand1)*rand(1,6);

//this would go in field_2
$rand2="127";		        

$rand2b = ($rand2)*rand(1,6);
//now if both numbers are not found in same any row, then do insert
//do insert

[etully]

In general, you need to set up a while loop that keeps picking random numbers and seeing if they are in the same row together. The loop keeps going until you find a pair of numbers that are not. That way, as soon as the loop ends, you know it's safe to do the insert because it found a valid pair.

There are two ways you can check to see if the random numbers are valid for insert. One is to put a select statement inside the while loop as in: select count(*) from my_table where field_1 = $rand1b and field_2 = $rand2b

The problem with that it's inefficient and it's a bad programming habit to get into. Theoretically, you might have to do 30 or 40 thousand select statements before you find one that is valid for inserting and that's putting unnecessary load on your CPU and hard drive which will make your server feel sluggish. Realistically, that's probably not going to happen on this project but if you form this bad habit now, then it could happen on your next project.

A more correct way to do it is to do one select statement where you read all the rows into an array. Then you set up a loop that keeps picking random numbers and checking them against the array until you find two that are valid for insert.