telsiin Posted February 4, 2008 Share Posted February 4, 2008 Hello Everyone I am try to do an insert if a customer is NOT in the SQL table or an Update if the customer IS in the my sql table but I don't think my if statment are correct Thank you for all your help in advance first this is my table in sql DROP TABLE IF EXISTS `testfeat`; CREATE TABLE IF NOT EXISTS `testfeat` ( `Product1` tinyint(1) NOT NULL, `Product2` tinyint(1) NOT NULL, `Product3` tinyint(1) NOT NULL, `Product4` tinyint(1) NOT NULL, `Product5` tinyint(1) NOT NULL, `Product6` tinyint(1) NOT NULL, `Product7` tinyint(1) NOT NULL, `CustomerID` int(4) NOT NULL auto_increment, PRIMARY KEY (`CustomerID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ; INSERT INTO `testfeat` VALUES (0, 0, 1, 1, 0, 0, 0, 1); INSERT INTO `testfeat` VALUES (1, 1, 0, 0, 1, 1, 0, 2); INSERT INTO `testfeat` VALUES (1, 1, 1, 0, 0, 1, 1, 3); INSERT INTO `testfeat` VALUES (0, 0, 1, 1, 0, 0, 0, 4); Now this is the code i am working with <?php require_once('Connections/test.php'); ?> <?php function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue; switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } $editFormAction = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if($row_Recordset1['CustomerID'] !=$_POST['Customer_Search']) { if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) { $insertSQL = sprintf("INSERT INTO testfeat (Product1, Product2, Product3, Product4, Product5, Product6, Product7, CustomerID) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", GetSQLValueString($_POST['Product1'], "int"), GetSQLValueString($_POST['Product2'], "int"), GetSQLValueString($_POST['Product3'], "int"), GetSQLValueString($_POST['Product4'], "int"), GetSQLValueString($_POST['Product5'], "int"), GetSQLValueString($_POST['Product6'], "int"), GetSQLValueString($_POST['Product7'], "int"), GetSQLValueString($_POST['CustomerID'], "int")); mysql_select_db($database_test, $test); $Result1 = mysql_query($insertSQL, $test) or die(mysql_error()); } } else if($row_Recordset1['CustomerID'] == $_POST['Customer_Search']) { if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) { $updateSQL = sprintf("UPDATE testfeat SET Product1=%s, Product2=%s, Product3=%s, Product4=%s, Product5=%s, Product6=%s, Product7=%s WHERE CustomerID=%s", GetSQLValueString($_POST['Product1'], "int"), GetSQLValueString($_POST['Product2'], "int"), GetSQLValueString($_POST['Product3'], "int"), GetSQLValueString($_POST['Product4'], "int"), GetSQLValueString($_POST['Product5'], "int"), GetSQLValueString($_POST['Product6'], "int"), GetSQLValueString($_POST['Product7'], "int"), GetSQLValueString($_POST['CustomerID'], "int")); mysql_select_db($database_test, $test); $Result1 = mysql_query($updateSQL, $test) or die(mysql_error()); } } mysql_select_db($database_test, $test); $query_Recordset1 = "SELECT * FROM testfeat WHERE testfeat.CustomerID='$_POST[Customer_Search]'"; $Recordset1 = mysql_query($query_Recordset1, $test) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); $maxRows_Recordset2 = 10; $pageNum_Recordset2 = 0; if (isset($_GET['pageNum_Recordset2'])) { $pageNum_Recordset2 = $_GET['pageNum_Recordset2']; } $startRow_Recordset2 = $pageNum_Recordset2 * $maxRows_Recordset2; mysql_select_db($database_test, $test); $query_Recordset2 = "SELECT * FROM testfeat"; $query_limit_Recordset2 = sprintf("%s LIMIT %d, %d", $query_Recordset2, $startRow_Recordset2, $maxRows_Recordset2); $Recordset2 = mysql_query($query_limit_Recordset2, $test) or die(mysql_error()); $row_Recordset2 = mysql_fetch_assoc($Recordset2); if (isset($_GET['totalRows_Recordset2'])) { $totalRows_Recordset2 = $_GET['totalRows_Recordset2']; } else { $all_Recordset2 = mysql_query($query_Recordset2); $totalRows_Recordset2 = mysql_num_rows($all_Recordset2); } $totalPages_Recordset2 = ceil($totalRows_Recordset2/$maxRows_Recordset2)-1; ?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Test</title> </head> <body> <form action="" method="post" target="_self"> <table width="200"> <tr> <td><input name="Customer_Search" type="text"></td> <td><input name="Search" type="button" value="Search for Customer"></td> </tr> </table> </form> <form method="POST" name="form1" action="<?php echo $editFormAction; ?>"> <table align="center"> <tr valign="baseline"> <td nowrap align="right">Product1:</td> <td><input type="text" name="Product1" value="<?php echo $row_Recordset1['Product1']; ?>" size="32"></td> </tr> <tr valign="baseline"> <td nowrap align="right">Product2:</td> <td><input type="text" name="Product2" value="<?php echo $row_Recordset1['Product2']; ?>" size="32"></td> </tr> <tr valign="baseline"> <td nowrap align="right">Product3:</td> <td><input type="text" name="Product3" value="<?php echo $row_Recordset1['Product3']; ?>" size="32"></td> </tr> <tr valign="baseline"> <td nowrap align="right">Product4:</td> <td><input type="text" name="Product4" value="<?php echo $row_Recordset1['Product4']; ?>" size="32"></td> </tr> <tr valign="baseline"> <td nowrap align="right">Product5:</td> <td><input type="text" name="Product5" value="<?php echo $row_Recordset1['Product5']; ?>" size="32"></td> </tr> <tr valign="baseline"> <td nowrap align="right">Product6:</td> <td><input type="text" name="Product6" value="<?php echo $row_Recordset1['Product6']; ?>" size="32"></td> </tr> <tr valign="baseline"> <td nowrap align="right">Product7:</td> <td><input type="text" name="Product7" value="<?php echo $row_Recordset1['Product7']; ?>" size="32"></td> </tr> <tr valign="baseline"> <td nowrap align="right">CustomerID:</td> <td><input type="text" name="CustomerID" value="<?php echo $row_Recordset1['CustomerID']; ?>" size="32"></td> </tr> <tr valign="baseline"> <?php if($row_Recordset1['CustomerID'] !=$_POST['Customer_Search']) { echo "<td nowrap align='right'> </td>"; echo "<td><input type='submit' value='Insert record'></td>"; } else if($row_Recordset1['CustomerID'] == $_POST['Customer_Search']) { echo "<td nowrap align='right'> </td>"; echo "<td><input type='submit' value='Update record'></td>"; } ?> </tr> </table> <p> </p> <p> <input type="hidden" name="MM_insert" value="form1"> <input type="hidden" name="MM_update" value="form1"> </p> <table border="1" cellpadding="1" cellspacing="2"> <tr> <td>Product1</td> <td>Product2</td> <td>Product3</td> <td>Product4</td> <td>Product5</td> <td>Product6</td> <td>Product7</td> <td>CustomerID</td> </tr> <?php do { ?> <tr> <td><?php echo $row_Recordset2['Product1']; ?></td> <td><?php echo $row_Recordset2['Product2']; ?></td> <td><?php echo $row_Recordset2['Product3']; ?></td> <td><?php echo $row_Recordset2['Product4']; ?></td> <td><?php echo $row_Recordset2['Product5']; ?></td> <td><?php echo $row_Recordset2['Product6']; ?></td> <td><?php echo $row_Recordset2['Product7']; ?></td> <td><?php echo $row_Recordset2['CustomerID']; ?></td> </tr> <?php } while ($row_Recordset2 = mysql_fetch_assoc($Recordset2)); ?> </table> </form> <p> </p> </body> </html> <?php @mysql_free_result($Recordset1); @mysql_free_result($Recordset2); ?> Link to comment https://forums.phpfreaks.com/topic/89420-insert-update-with-if-statment/ Share on other sites More sharing options...
revraz Posted February 4, 2008 Share Posted February 4, 2008 http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Link to comment https://forums.phpfreaks.com/topic/89420-insert-update-with-if-statment/#findComment-457926 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.