Page 1 of 1

Comparing 2 sets of information

Posted: Tue Dec 05, 2006 10:20 pm
by trent2800
I'm currently trying to figure out how to compare a set of data submitted from a form and a set from a database. The multiple select will have options selected/highlighted based on what rows are in the database. What I have created is a script that deletes array rows when it finds matching sets in the database. What I also need to do is determine what options were deselected and therefore should be deleted from the database.

Example:

DB Result: 1,2,3,4

Form Result: 1,3,4,5


If you were to compare the two you would find that you had to add ID 5 and delete ID 2. I am trying to figure out just how to code that.

Code: Select all

<?PHP

$Submit = $_GET['Submit'];
$Sizes = $_POST['Sizes'];

$dbh=mysql_connect ("localhost", "*****", "*******") or die ('I cannot connect to the database because: ' . mysql_error());
		
mysql_select_db ('guttersg_ggoods');

$query2 = "SELECT `SizeID` FROM `INCENSE_SIZE` WHERE `ProductID` = '9'";
	
$result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
				
if ($Submit) {
	
	echo "Sizes before: <br />";
	var_dump($Sizes);
	echo "<br /><br />";
	
	while ($row2 = mysql_fetch_assoc($result2)) {	
	
	echo "Looking for SizeID: ".$row2['SizeID']."...<br />\n";
	
		foreach ($Sizes as $i => $s) {
			
			if($s == $row2['SizeID']) {
			
				echo "   Found size: $i[$s] in result.  ID: $i Value: $s<br /><br />\n";
				unset($Sizes[$i]);
				
			}/*else{
			
				echo "Adding value to array for deletion.  Value: ".$row2['SizeID']."<br /><br />\n";
				$delkey[] = $row2['SizeID'];
				
			}*/
			
		}
		
	}
	
	echo "Sizes after: <br />";
	var_dump($Sizes);
	echo "<br /><br />";

}else{
?>

Code: Select all

<form action="<?PHP $_SERVER['PHP_SELF'] ?>?Submit=1" method="post">
	<select multiple name="Sizes[]">
		<option value="1">Size 1</option>
		<option value="2">Size 2</option>
		<option value="3">Size 3</option>
		<option value="4">Size 4</option>
		<option value="5">Size 5</option>
	</select>
	
	<input type="submit">
</form>

Posted: Tue Dec 05, 2006 10:54 pm
by volka
If you want php to do the math you can use array_diff

Code: Select all

<?php
$db = array(1,2,3,4);
$form = array(1,3,4,5);

$delete = array_diff($db, $form);
$add = array_diff($form, $db);

echo 'delete', join(', ', $delete), "<br />\n";
echo 'add', join(', ', $add), "<br />\n";
?>
Otherwise you can use sql querries like

Code: Select all

DELETE FROM xyz WHERE id NOT IN (1,3,4,5)
INSERT IGNORE INTO xyz (id) values (1),(3),(4),(5) ON DUPLICATE 
The update should be pretty obvious.
INSERT IGNORE only inserts new rcords while doublets are ignored*. For this to work you need a UNIQUE index on the id field.

---
* in fact it does more, see http://dev.mysql.com/doc/refman/5.1/en/ ... l[quote]If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead.[/quote]

Posted: Tue Dec 05, 2006 11:03 pm
by trent2800
Sweeet, thanks!

That one had been plaguing me for a while!

Posted: Tue Dec 05, 2006 11:06 pm
by volka
oops, although there is a INSERT INTO ... ON DUPLICATE syntax it's wrong here (at least something's missing).
just INSERT IGNORE INTO xyz (id) VALUES (1),(3),(4),(5)