Checking a Random Query against another table

[Mars1936]

Is it possible to check a random query against another table through php? Say I pull a random query from table A and then transfer it to table B. Then I pull another random query from table A. Is there a way to check the 2nd random query against table B to see if the query is the same? And if it is the same start over with the 2nd query again? Also can I skip a line is my database table?

example
id name
1 Bill
2 Joe
3
4 Rob
5 Jim
and so on.
If so can anyone help give me a push in the right direction?

[Wraithmanilian]

Instead of checking, couldn't you just use REPLACE instead of INSERT in the MySQL command? If the insert was a duplicate, then it would delete and replace the old, basically re-copying itself. If it was unique, then it would just insert the data, at least that is how I understand it.

[Mars1936]

Thank you for the reply but not what I'm really trying to do. What I'm trying to do is use php and mysql to pull random names from a table skipping a line every 2 names pulled to make a doubles team which I have scripted out and works great except sometimes people are getting the same partner multiply times. I have no idea how to go about this except to make a few tables to save my old queries on and see if anyone would have the same partner 2 weeks in a row. I only use this once a week. If anyone has better idea please pass it by me.

[vibhaJ]

Check this, if it helps you.
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

[diafol]

Your problem is that you don't want a truly random result as those will throw up repeat pairing from time to time due to the laws of probability. Are you trying to ensure that each member pairs up with every other other member (or at least the minimum number of duplicate pairings or no duplicates if there are more possible members pairings than events).

Could you be more specific?

[Wraithmanilian]

I think that I finally understand the problem. An example is always great reference.

Let's say I have 30 students in my class. At the end of each week, I pair them off in pairs to have them complete an assignment. Now, I don't want to play favorites, or pair off friends constantly. I want the pairings to be random, and I want them to be different each week.

Therefore, I want to keep record of who each student has paired with so that I do not have the same two students paired twice for the duration of the class. With 30 students, this would theoretically work 29 times.

Basically, mars1936 wants to pair off people from the database and track who has been paired with who in the past to avoid pairing them again in the future. This is what I gleaned from the information provided.

mars1936, have I missed anything?

[Mars1936]

Yep that's it. Sorry I tried to explain it the best I could thank you Wraithmanilian for helping explain it better. I know I can do this manually. Just was wondering if this couldbe done via php mysql

[Wraithmanilian]

OK, here's some quick and dirty (UNTESTED) example code to get you going in the (hopefully) right direction.

pairpage.php

<?php

// First, let's get the student names from the database and place them in two arrays, group1 and group2

$sql = "SELECT * FROM students";
$result = mysql_query($sql) or die('\"'.$sql.'\" Query failed: ' . mysql_error());
while($row = mysql_fetch_array($result, MYSQL_BOTH)) {
	$group1[] = $row['name'];
	$group2[] = $row['name'];
}

// Now, let's see how many students we are looking at

$studentcount = count($group1)-1;
$totalcount = count($group1);

// That number represents the total combinations of unique pairings per student.
// Now, let's create a piece of html code that we can use to determine which mixing we use today

$myform = "<form action=\"pairpage.php\" method=\"post\">\n";
$myform .= "Week: <select name=\"week\">\n";
$counter = 1;
while($counter<=$studentcount) {
	$myform .= "<option value=\"".$counter."\">".$counter."</option>\n";
	$counter++;
}
$myform .= "</select><input type=\"submit\" name=\"submit\" value=\"Go\" /></form>";

// Now, we need to check to see if this page is coming from a form submission

if($_POST['submit']) {
	// Let's get the week we want and fix the second array accordingly
	$postweek = $_POST['week'];
	$counter = 0;
	while($counter<$postweek) {
		$popname = array_shift($group2);  // Takes the first element from the list...
		$group2[] = $popname;   // ...and places it at the end of the list...
		$counter++;  // ...shifting the names by one place per week.
	}
	
	// Now we have two arrays. $group1 has the original name list in order.
	// $group2 has the names shifted the number of times specified.
	// Now all we have to do is print them.
	$output = "<h3>Pairings For Week ".$postweek."</h3>";
	$counter = 0;   // Yes, I use the $counter var a LOT. Personal preference. :)
	$listcount = $totalcount/2;
	while($counter<$listcount) {
		$output .= "<p>".$group1[$counter]."<br />".$group2[$counter]."</p>";
		$counter++;
	}
	echo $output;
}
?>

Hope this helps.

[Mars1936]

Thank you for the push in the right direction. I will work on it tonight when I get home from work. Will let you know what happens.