Avatar of causewaybay
causewaybay
Flag for Canada 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
MySQL ServerPHP

Avatar of undefined
Last Comment
Derek Jensen

8/22/2022 - Mon
SOLUTION
Ray Paseur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck