Solved

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

Posted on 2012-12-31
3
320 Views
Last Modified: 2013-06-17
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
0
Comment
Question by:causewaybay
3 Comments
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
Comment Utility
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
 

Author Comment

by:causewaybay
Comment Utility
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
 
LVL 9

Accepted Solution

by:
Derek Jensen earned 250 total points
Comment Utility
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now