asked on

php function page for mysql_query("INSERT......)

I have a mySql INSERT statement with 11 columns on a page call "add_product.php". The statement is inside an 'else' clause on line 132. I want to write it on a separate function page so I may catch my sql mistake faster (not too long ago I inadvertently leave a comma before "WHERE" and mess up the whole thing) . I am a newbie to php and have no experience creating a separate function page. Please help me by giving me details as to how to move the query(INSERT...) statement to create a function.php page; and how to call that function into action on the "add_product.php" page - which I attach herewith. Thank you
add-product.php

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

causewaybay

ASKER

Thank you for telling me about the code snippet. I do have a common.php page for starting the database as you will see in the snippet:

<?php
 $sDbHost = 'localhost';
 $sDbName = 'northwind'; 
 $sDbPwd  = '';

$dbConn = mysql_connect ($sDbHost, $sDbUser, $sDbPwd) or die ('MySQL connect failed. ' . mysql_error());
mysql_select_db($sDbName,$dbConn) or die('Cannot select database. ' . mysql_error());
?>

Open in new window

My aim is to learn how to make some codes portable and easier to debug. Since I easily mess up the sql code, and I agree in entirety to your earlier suggestion of keeping each name=value pair (and WHERE in the UPDATE page) on an individual line, that's why the question. I had attached the INSERT page, not the UPDATE page, as example because it was shorter. But to express myself better, let's post the UPDATE page now in snippet. the modular I want to move is the mysql_query("UPDATE products SET... on line 141:

<?php 
 function editValid($ProductId,$ProductName,$SupplierId,$CategoryId,$QuantityPerUnit,$UnitPrice,$UnitsInStock,$UnitsOnOrder,$ReorderLevel,$Discontinued,$LastModifiedDateTime, $error)
 {
 ?>
 
 <html>
 <head>
 <title>one_product</title>
 </head>
 <body>
 <?php

 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
 ?>

 <form action="" method="post">
 <input type="hidden" name="ProductId" value="<?php echo $ProductId; ?>"/>

 <table border="1">
    <tr>
      <td colspan="2"><b><font color='Red'>Edit Records </font></b></td>
      </tr>
 <tr>
     <td width="179"><b><font color='#663300'>ProductName<em>*</em></font></b></td>
     <td><label>
      <input type="text" name="ProductName" value="<?php echo $ProductName; ?>" />
     </label></td>
   </tr>

   <tr>
       <td width="179"><b><font color='#663300'>SupplierId<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="SupplierId" value="<?php echo $SupplierId; ?>" />
       </label></td>
   </tr>

   <tr>
       <td width="179"><b><font color='#663300'>CategoryId<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="CategoryId" value="<?php echo $CategoryId; ?>" />
       </label></td>
   </tr>
   
<tr>
       <td width="179"><b><font color='#663300'>QuantityPerUnit<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="QuantityPerUnit" value="<?php echo $QuantityPerUnit; ?>" />
       </label></td>
   </tr>
   
<tr>
       <td width="179"><b><font color='#663300'>UnitPrice<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="UnitPrice" value="<?php echo $UnitPrice; ?>" />
       </label></td>
   </tr>
   
<tr>
       <td width="179"><b><font color='#663300'>UnitsInStock<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="UnitsInStock" value="<?php echo $UnitsInStock; ?>" />
       </label></td>
   </tr>
   
<tr>
       <td width="179"><b><font color='#663300'>UnitsOnOrder<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="UnitsOnOrder" value="<?php echo $UnitsOnOrder; ?>" />
       </label></td>
   </tr>
   
<tr>
       <td width="179"><b><font color='#663300'>ReorderLevel<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="ReorderLevel" value="<?php echo $ReorderLevel; ?>" />
       </label></td>
   </tr>
   
<tr>
       <td width="179"><b><font color='#663300'>Discontinued<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="Discontinued" value="<?php echo $Discontinued; ?>" />
       </label></td>
   </tr>
   
<tr>
       <td width="179"><b><font color='#663300'>LastModifiedDateTime<em>*</em></font></b></td>
       <td><label>
        <input type="text" name="LastModifiedDateTime" value="<?php echo $LastModifiedDateTime; ?>" />
       </label></td>
   </tr>
   

   <tr align="Right">
       <td colspan="2"><label>
          <input type="submit" name="submit" value="Edit Records">
       </label></td>
       </tr>
</table>
 </form>
 </body>
 </html>
 <?php
 }

 include('common.php');

 if (isset($_POST['submit']))
 {

 if (is_numeric($_POST['ProductId']))
 {

 $ProductId = ($_POST['ProductId']);
 $ProductName = ($_POST['ProductName']);
 $SupplierId = ($_POST['SupplierId']);
 $CategoryId = ($_POST['CategoryId']);
 $QuantityPerUnit = ($_POST['QuantityPerUnit']);
 $UnitPrice = ($_POST['UnitPrice']);
 $UnitsInStock = ($_POST['UnitsInStock']);
 $UnitsOnOrder = ($_POST['UnitsOnOrder']);
 $ReorderLevel = ($_POST['ReorderLevel']);
 $Discontinued = ($_POST['Discontinued']);
 $LastModifiedDateTime = ($_POST['LastModifiedDateTime']);

                  
  if ($ProductName == '' || $SupplierId == '' ||  $CategoryId == '' ||  $QuantityPerUnit == '' ||  $UnitPrice == '' ||  $UnitsInStock == '' ||  $UnitsOnOrder == '' ||  $ReorderLevel == '' ||  $Discontinued == '' ||  $LastModifiedDateTime == '')
 {

 $error = 'ERROR: Please fill in all required fields!';


editValid($ProductId, $ProductName, $SupplierId,$CategoryId,$QuantityPerUnit,$UnitPrice,$UnitsInStock,$UnitsOnOrder,$ReorderLevel,$Discontinued,$LastModifiedDateTime, $error);
 }
 else
 {

 mysql_query("UPDATE products SET ProductName='$ProductName', SupplierId='$SupplierId' ,CategoryId='$CategoryId', QuantityPerUnit='$QuantityPerUnit',UnitPrice='$UnitPrice',UnitsInStock='$UnitsInStock',UnitsOnOrder='$UnitsOnOrder',ReorderLevel='$ReorderLevel',Discontinued='$Discontinued',LastModifiedDateTime='$LastModifiedDateTime' WHERE ProductId='$ProductId'")
 or die(mysql_error());

 header("Location: product.php");
 }
 }
 else
 {

 echo 'Error!';
 }
 }
 else

 {

 if (isset($_GET['ProductId']) && is_numeric($_GET['ProductId']) && $_GET['ProductId'] > 0)
 {

 $ProductId = $_GET['ProductId'];
 $result = mysql_query("SELECT * FROM products WHERE ProductId=$ProductId")
 or die(mysql_error());
 $row = mysql_fetch_array($result);

 if($row)
 {

 $ProductName = $row['ProductName'];
 $SupplierId = $row['SupplierId'];
 $CategoryId= $row['CategoryId'];
 $QuantityPerUnit = $row['QuantityPerUnit'];
 $UnitPrice = $row['UnitPrice'];
 $UnitsInStock = $row['UnitsInStock'];
 $UnitsOnOrder = $row['UnitsOnOrder'];
 $ReorderLevel = $row['ReorderLevel'];
 $Discontinued = $row['Discontinued'];
 $LastModifiedDateTime = $row['LastModifiedDateTime'];

 editValid($ProductId, $ProductName, $SupplierId,$CategoryId,$QuantityPerUnit,$UnitPrice,$UnitsInStock,$UnitsOnOrder,$ReorderLevel,$Discontinued,$LastModifiedDateTime,'');
 }
 else
 {
 echo "No results!";
 }
 }
 else

 {
 echo 'Error!';
 }
 }
?>

Open in new window

My apology for using a lot of layman php description. I'll keep learning to improve it.

ASKER CERTIFIED SOLUTION

Derek Jensen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

php function page for mysql_query(&quot;INSERT......)

php function page for mysql_query("INSERT......)