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
causewaybayAsked:
Who is Participating?
 
Derek JensenConnect With a Mentor Commented:
Okay okay okay...I think what you're looking for is much simpler than what everyone's making it out to be (no offense...everyone ;-)

If I understand correctly, all you're trying to do is separate out into another file the MySQL query, in hopes of more efficient debugging, right?

To do that, all you'd need to do is something like this:
(Note: I've taken the liberty of naming these files for simplicity's sake, but you can name them whatever you want, or even simply combining them all into common.php if you want. Just remember to adjust the include lines if you do that. :-)  I've also taken the liberty of cleaning up your code a bit; indents and whatnot...and yes, I'm extremely bored)

all_queries.php:
<?php
include_once "common.php";
function insertProduct($ProductName, $SupplierId, $CategoryId, $QuantityPerUnit, $UnitPrice, $UnitsInStock, $UnitsOnOrder, $ReorderLevel, $Discontinued, $LastModifiedDateTime, $ProductId) {
     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());
}
function getProduct($ProductId) {
    $result = mysql_query("SELECT * FROM products WHERE ProductId=$ProductId") or die(mysql_error());
    return mysql_fetch_array($result);
}
?>

Open in new window


editValid.php:
<?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='#630'>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='#630'>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='#630'>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='#630'>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='#630'>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='#630'>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='#630'>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='#630'>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='#630'>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='#630'>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
}
?>

Open in new window


index.php (I assume?):
<?php
include_once "all_queries.php";
include_once "editValid.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 {
            insertProduct($ProductName, $SupplierId, $CategoryId, $QuantityPerUnit, $UnitPrice, $UnitsInStock, $UnitsOnOrder, $ReorderLevel, $Discontinued, $LastModifiedDateTime, $ProductId);
            header("Location: product.php");
        }
    } else echo 'Error!';
} elseif (isset($_GET['ProductId']) && is_numeric($_GET['ProductId']) && $_GET['ProductId'] > 0) {
    $ProductId = $_GET['ProductId'];
    $row = getProduct($_GET['ProductId']);

    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



Aaand after all that, the only lines you really need to pay attention to are the include lines. As long as the new files are in the same folder as the calling file(usually index.php), then all you need to do is add an include line at the top w/that filename, and you now have access to all the functions contained within that "function page." ;-)

This, however, is an incredibly inefficient way to validate a form; myself personally, I'd probly just use a jQuery validator function & masks. It does the same amount of work as what you're doing here, but in about a tenth of the lines of code. :-)

...And no, I won't go into more detail on jQuery validators; I could go on all day about forms...suffice it to say, it's all out there for your own researching. ;-)
Clicking here would be a good first step. ;-)
0
 
Ray PaseurConnect With a Mentor Commented:
Please use the code snippet when you're posting code here at EE.  No reason to put it in an attachment file.  The snippet makes it easy to read and gives us line numbers to facilitate discussion.

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

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

 <form action="" method="post">
 <table border="1">
     <tr>
       <td colspan="2"><b><font color='Red'>Add Products </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>                            
    <tr align="Right">
        <td colspan="2"><label>
           <input type="submit" name="submit" value="Add Products">
        </label></td>
        </tr>
</table>
 </form>
 </body>
 </html>
 <?php
 }


 include('common.php');

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

 $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 = 'Please enter the details!';

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

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

 header("Location: product.php");
 }
 }
 else
 {
 addValid('','','','','','','','','','','');
 }
?>

Open in new window

The term "function page" is not a term of art in PHP programming, and what I think you might want is a common PHP script that has all of your necessary housekeeping stuff.  This would include starting the session, connecting and selecting the data base, as well as all of the code for your classes and functions.  If you organize things correctly, you can have an entire web page created with as few as three lines of code.  Example:
http://www.landonbaseball.com/basic.php

<?php // landonbaseball.com/basic.php
require_once('common.php');
require_once('header.php');

echo '<p>Hello World</p>' . PHP_EOL;

require_once('footer.php');

Open in new window

Organized in this way, your work will be more modular, portable and easier to debug.

A couple of other things will be helpful to you, too.  Adopt a coding standard, especially one that calls for indentation of the control structures.  I prefer a modified Zend standard (I differ from Zend in that I put the opening curly brace under the conditional or iterator statement), but any standard, as long as it allows you to line up your code, will be a great help.
0
 
causewaybayAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here