Comparing 2 sets of information

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
trent2800
Forum Commoner
Posts: 48
Joined: Mon Oct 02, 2006 7:02 am

Comparing 2 sets of information

Post 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>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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]
trent2800
Forum Commoner
Posts: 48
Joined: Mon Oct 02, 2006 7:02 am

Post by trent2800 »

Sweeet, thanks!

That one had been plaguing me for a while!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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)
Post Reply