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
add-product.php
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
http://www.landonbaseball.com/basic.php
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.
<?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('','','','','','','','','','','');
}
?>
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');
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.
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:
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:
My apology for using a lot of layman php description. I'll keep learning to improve it.
<?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());
?>
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!';
}
}
?>
My apology for using a lot of layman php description. I'll keep learning to improve it.
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:
editValid.php:
index.php (I assume?):
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. ;-)
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);
}
?>
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
}
?>
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!';
?>
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. ;-)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial