Inserting values via php to sql database

Veraedon · November 2, 2011

Hi guys,

I am building a website with basic e-commerce functionality, using php and using xampp to test it.

I am having issues when attempting to submit a quantity (into table orders) using a form and validating it against an existing value (from table products), giving a response on whether there is sufficient quantity in the second table. I am then, in another page (same one performing the validations), attempting to then show a result based on the initial quantity entered, with a summary of the order details and calculation of the quantity * price to display a total as well. This has all been built from scratch, however I may have taken the wrong approach for these two pages... any assistance or insight as to where I am going wrong would be greatly appreciated.

Here is the page I have placed the products, existing quantity and a text field they are able to enter their desired quantity:

<?php
session_start();
require_once "../database/db.php";
require_once "../includes/functions.php";

$page_title = 'Product Catalogue';
include_once "header.php";

$conn = mysqli_connect ($dbhost, $dbuser, $dbpassword, $dbname);

$query = "SELECT * from products";

$result = mysqli_query($conn, $query);

if (!$result)
{
include_once "header.php";
die ("Error, could not query the database");
}

else 
{ $rows = mysqli_num_rows($result);

if ($rows>0)
{

while ($row = mysqli_fetch_array($result))
{
	?>
			<form>
                <br />
                <br />
                <br />
                <table>
                <tr>
                <td style="width: 200px">Product Code:</td>
                <td><?php echo $row['ProductCode']; ?></td>
                </tr>
                <tr>
                <td>Product Name:</td>
                <td><?php echo $row['ProductName']; ?></td>
                </tr>
                <tr>
                <td>Product Description:</td>
                <td><?php echo $row['ProductDescription']; ?></td>
                </tr>
                <tr>
                <td>Product Colour:</td>
                <td><?php echo $row['ProductColour']; ?></td>
                </tr>
                <tr>
                <td>Product Price:</td>
                <td>$<?php echo number_format($row['ProductPrice'],2); ?></td>
                </tr>
                <tr>
                <td>Product Image:</td>
                <td><img src="<?php echo $row['ProductImagePath']?>"/></td>
                </tr>
                <tr>
                <td>Quantity in Stock:</td>
                <td><?php echo $row['ProductQuantity']; ?></td>
                </tr>
                </table>
                </form>
                <form method="post"action="processQuantity.php">
                <table>
                <tr>
                <td style="width: 200px">Quantity:</td>
                <td><input type="number" name="Quantity" id="Quantity" value="<?php if (isset ($quantity)) echo $quantity; ?>"size = "20" /></td>
                <td><input type="submit" name="Purchase" value= "Purchase" /></td>
                </tr>
                </table>
                </form>
                <hr />
                <?php
	}
		include "footer.html";
}
}
?>

Here is the page that I am using to validate the data as well as show a result based on the entered amount:

<?php

session_start();

require_once "../includes/functions.php";
require_once "../database/db.php";

$quantity = $_POST['Quantity'];
$productquantity = $_POST['ProductQuantity'];
$orderid = $_POST['orderid'];
$productcode = $_POST['productcode'];
$productprice = $_POST['productprice'];
$total = $quantity * $productprice;

$error_message = '';

if ($error_message != '')
{
include_once "displayCatalogue-PlaceOrder.php";
exit();

$conn = mysqli_connect ($dbhost, $dbuser, $dbpassword, $dbname);

if (!$conn)
{
echo "Error";
}
else
{
//sanitise date
$scustomerid = sanitiseMySQL($customerid);
$sproductcode = sanitiseMySQL($productcode);
$squantity = sanitiseMySQL($quantity);
$sproductprice = sanitiseMySQL($productprice);
$sorderdate = sanitiseMySQL($orderdate);

$query = "select productquantity from products where productcode = '$sproductcode'";

$result = msqli_query ($conn, $query);

$productquantity = mysqli_num_rows($result);

if ($quantity < $productquantity)
{
	$error_message = "You cannot order more than what is currently instock";
		include_once "displayCatalogue-PlaceOrder.php";
		exit ();
}
else
{
	$row = mysqli_fetch_row($result);

	$query = "INSERT into orders (customerid, productcode, quantity, productprice, orderdate) values ('$scustomerid', $sproductcode', '$squantity', '$sproductprice', '$sorderdate')";

	$result = mysqli_query($conn, $query);

	$row = mysqli_affected_rows($conn);

	if ($row > 0)
	{
		include "header.php";?>
            <h3>Order Confirmation</h3>
            <p>Thank you, your order is now being processed.</p>
                <table>
			<tr>
			<td style="width: 200px">Order Number:</td>
                <td><?php echo $orderid; ?></td>
               	</tr>
                <tr>
                <td>Product Code:</td>
                <td><?php echo $productcode; ?></td>
                </tr><tr>
                <td>Quantity:</td>
                <td><?php echo $quantity; ?></td>
                </tr>
                <tr>
                <td>Price:</td>
                <td><?php echo $productPrice; ?></td>
                </tr>
                <tr>
                <td>Total Cost of Order:</td>
                <td><?php echo $total; ?></td>
                </tr>
                </table>

                <?php
			include "footer.html";
	}
	else
	{
		$error_message ="Error placing your order, please try again";
		include "displayCatalogue-PlaceOrder.php";
		exit();
	}
}
}
}
//this is used to validate the quantity entered against what is available in the database
?>

dropbop · November 2, 2011

Hi,

Im not sure if it related to your issue, but you are missing a ' in this line to the left of $sproductcode

$query = "INSERT into orders (customerid, productcode, quantity, productprice, orderdate) values ('$scustomerid', $sproductcode', '$squantity', '$sproductprice', '$sorderdate')";

Hop you get the problem fixed..

Veraedon · November 2, 2011

Hi, thanks for your reply. That still gave me the same error as before, which is:

Notice: Undefined index: ProductQuantity in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 9

Notice: Undefined index: orderid in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 10

Notice: Undefined index: productcode in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 11

Notice: Undefined index: productprice in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 12

This is my first attempt at creating an ecommerce site, I just want to get the basic structure down before looking at anything else. I thought my code made sense as I have a query to select the available quantity from the products table, compare it to the value entered, and then if it is less than or equal (i added the <= between the variables instead of just <) to the amount entered, the order confirmation is displayed. I'm not sure where I am going wrong here =\

brady123 · November 3, 2011

In your second set of code, you don't have "mysql" written out correctly, you're missing the "y". Is that it?

Veraedon · November 3, 2011

If you are referring to the mysqli_fetch commands etc, that is the syntax for sending/gathering data from the sql database (located in phpMyAdmin).

seany123 · November 3, 2011

Your not posting the values for:

$_POST['ProductQuantity'];
$_POST['orderid'];
$_POST['productcode'];
$_POST['productprice'];

the only value your posting is the quantity value shown here:

<input type="number" name="Quantity" id="Quantity" value="<?php if (isset ($quantity)) echo $quantity; ?>"size = "20" />

Veraedon · November 3, 2011

I thought I was posting the values in the processquantity.php using:

$row = mysqli_affected_rows($conn);

	if ($row > 0)
	{
		include "header.php";?>
            <h3>Order Confirmation</h3>
            <p>Thank you, your order is now being processed.</p>
                <table>
			<tr>
			<td style="width: 200px">Order Number:</td>
                <td><?php echo $orderid; ?></td>
               	</tr>
                <tr>
                <td>Product Code:</td>
                <td><?php echo $productcode; ?></td>
                </tr><tr>
                <td>Quantity:</td>
                <td><?php echo $quantity; ?></td>
                </tr>
                <tr>
                <td>Price:</td>
                <td><?php echo $productPrice; ?></td>
                </tr>
                <tr>
                <td>Total Cost of Order:</td>
                <td><?php echo $total; ?></td>
                </tr>
                </table>

That second snippet of code that you posted is the piece I am using to submit a value to the database, to insert it into the orders table, have I done that wrong?

seany123 · November 3, 2011

No all thats doing is trying to echo out the values of what SHOULD of been posted.

Firstly, the 2 forms you have you should turn into 1 form.

second set all the $row['exmaple']; values so they have constant variable (think thats what its called.) EG.

$ProductCode = $row['ProductCode'];

Okay now you need to POST each value i have given an example below of how to add a hidden input which POSTS the value.

<td><?php echo $row['ProductCode']; ?><input type='hidden' name="productCode" value="<?=$productCode;?>"/></td>

Veraedon · November 3, 2011

Thank you very much for your input.

I applied the editing you advised as shown below (I hope it's right)

I was previously only using one form, but I had 2 tables, I still have 2 tables because I need the output from the quantity entered with the order summary details on a separate page to show an order confirmation.

Catalogue/Place order page:

<?php
session_start();
require_once "../database/dbcustomer.php";
require_once "../includes/functions.php";

$page_title = 'Product Catalogue';
include_once "header.php";

$error_message = '';

$productcode = $row['ProductCode'];
$productname = $row['ProductName'];
$productdescription = $row['ProductDescription'];
$productcolour = $row['ProductColour'];
$productprice = $row['ProductPrice'];
$productimage = $row['ProductImagePath'];
$productquantity = $row['ProductQuantity'];
$quantity = $row['Quantity'];

$conn = mysqli_connect ($dbhost, $dbuser, $dbpassword, $dbname);

$query = "SELECT * from products";

$result = mysqli_query($conn, $query);

if (!$result)
{
include_once "header.php";
die ("Error, could not query the database");
}

else 
{ $rows = mysqli_num_rows($result);

if ($rows>0)
{

while ($row = mysqli_fetch_array($result))
{
	?>
			<form>
                <br />
                <br />
                <br />
                <table>
                <tr>
                <td style="width: 200px">Product Code:</td>
                <td><?php echo $row['ProductCode']; ?><input type='hidden' name="ProductCode" value="<?php $productcode;?>" /></td>
                </tr>
                <tr>
                <td>Product Name:</td>
                <td><?php echo $row['ProductName']; ?><input type='hidden' name="ProductName" value="<?php $productname;?>" /></td>
                </tr>
                <tr>
                <td>Product Description:</td>
                <td><?php echo $row['ProductDescription']; ?><input type='hidden' name="ProductDescription" value="<?php $productdescription;?>" /></td>
                </tr>
                <tr>
                <td>Product Colour:</td>
                <td><?php echo $row['ProductColour']; ?><input type='hidden' name="ProductColour" value="<?php $productcolour;?>" /></td>
                </tr>
                <tr>
                <td>Product Price:</td>
                <td><?php echo $row['ProductPrice']; ?><input type='hidden' name="ProductPrice" value="<?php $productprice;?>" /></td>
                </tr>
                <tr>
                <td>Product Image:</td>
                <td><?php echo $row['ProductImagePath']; ?><input type='hidden' name="ProductImage" value="<?php $productimage;?>" /></td>
                </tr>
                <tr>
                <td>Quantity in Stock:</td>
                <td><?php echo $row['ProductQuantity']; ?><input type='hidden' name="ProductQuantity" value="<?php $productquantity;?>" /></td>
                </tr>
                </table>
                </form>
                <form method="post"action="processQuantity.php">
                <table>
                <tr>
                <td style="width: 200px">Quantity:</td>
                <td><input type="number" name="Quantity" id="Quantity" value="<?php if (isset ($quantity)) echo $quantity; ?>"size = "20" /></td>
                <td><input type="submit" name="Purchase" value= "Purchase" /></td>
                </tr>
                </table>
                </form>
                <hr />
                <?php
	}
		include "footer.html";
}
}

?>

Process Order / Confirmation page:

<?php

session_start();

require_once "../includes/functions.php";
require_once "../database/db.php";

$quantity = $_POST['Quantity'];
$productquantity = $_POST['ProductQuantity'];
$orderid = $_POST['OrderId'];
$productcode = $_POST['ProductCode'];
$productprice = $_POST['ProductPrice'];
$productimagepath = $_POST['ProductImagePath'];
$total = $quantity * $productprice;

$error_message = '';

if ($error_message != '')
{
include_once "displayCatalogue-PlaceOrder.php";
exit();

$conn = mysqli_connect ($dbhost, $dbuser, $dbpassword, $dbname);

if (!$conn)
{
echo "Error";
}
else
{
//sanitise date
$scustomerid = sanitiseMySQL($customerid);
$sproductcode = sanitiseMySQL($productcode);
$squantity = sanitiseMySQL($quantity);
$sproductprice = sanitiseMySQL($productprice);
$sorderdate = sanitiseMySQL($orderdate);

$query = "select productquantity from products where productcode = '$sproductcode'";

$result = msqli_query ($conn, $query);

$productquantity = mysqli_num_rows($result);

if ($quantity <= $productquantity)
{
	$error_message = "You cannot order more than what is currently instock";
		include_once "displayCatalogue-PlaceOrder.php";
		exit ();
}
else
{
	$row = mysqli_fetch_row($result);

	$query = "INSERT into orders (customerid, productcode, quantity, productprice, orderdate) values ('$scustomerid', '$sproductcode', '$squantity', '$sproductprice', '$sorderdate')";

	$result = mysqli_query($conn, $query);

	$row = mysqli_affected_rows($conn);

	if ($row > 0)
	{
		include "header.php";?>
            <h3>Order Confirmation</h3>
            <p>Thank you, your order is now being processed.</p>
                <table>
			<tr>
			<td style="width: 200px">Order Number:</td>
                <td><?php echo $orderid; ?></td>
               	</tr>
                <tr>
                <td>Product Code:</td>
                <td><?php echo $productcode; ?></td>
                </tr><tr>
                <td>Quantity:</td>
                <td><?php echo $quantity; ?></td>
                </tr>
                <tr>
                <td>Price:</td>
                <td><?php echo $productPrice; ?></td>
                </tr>
                <tr>
                <td>Total Cost of Order:</td>
                <td><?php echo $total; ?></td>
                </tr>
                </table>

                <?php
			include "footer.html";
	}
	else
	{
		$error_message ="Error placing your order, please try again";
		include "displayCatalogue-PlaceOrder.php";
		exit();
	}
}
}
}
//this is used to validate the quantity entered against what is available in the database
?>

Veraedon · November 3, 2011

I forgot to say, I am getting this error on the catalogue page:

Notice: Undefined variable: row in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 11

Notice: Undefined variable: row in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 12

Notice: Undefined variable: row in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 13

Notice: Undefined variable: row in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 14

Notice: Undefined variable: row in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 15

Notice: Undefined variable: row in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 16

Notice: Undefined variable: row in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 17

Notice: Undefined variable: row in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 18

Am I not supposed to use a mysqli_fetch command in order to define the variables?

Am I also getting the following error as part of the process page:

Notice: Undefined index: ProductQuantity in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 9

Notice: Undefined index: OrderId in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 10

Notice: Undefined index: ProductCode in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 11

Notice: Undefined index: ProductPrice in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 12

Notice: Undefined index: ProductImagePath in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 13

seany123 · November 3, 2011

Okay well firstly im really not aware of what the purpose of mysqli is/does... i have always found that mysql has done the job perfectly so im really not sure why you are using mysqli (however as i said im not experienced with it so it could be the next big thing.)

When i said put the 2 forms into 1, thats exactly what i meant, all the html tables are just for usability.

see if this is any better!

Catalogue/Place order page:

<?php
session_start();
require_once "../database/dbcustomer.php";
require_once "../includes/functions.php";

$page_title = 'Product Catalogue';
include_once "header.php";

$error_message = '';

$conn = mysqli_connect ($dbhost, $dbuser, $dbpassword, $dbname);

$query = "SELECT * from products";

$result = mysqli_query($conn, $query);

if (!$result)
{
include_once "header.php";
die ("Error, could not query the database");
}

else 
{ $rows = mysqli_num_rows($result);

if ($rows>0)
{

while ($row = mysqli_fetch_array($result))
{
            $productcode = $row['ProductCode'];
            $productname = $row['ProductName'];
            $productdescription = $row['ProductDescription'];
            $productcolour = $row['ProductColour'];
            $productprice = $row['ProductPrice'];
            $productimage = $row['ProductImagePath'];
            $productquantity = $row['ProductQuantity'];
            $quantity = $row['Quantity'];

	?>
			<form method="post"action="processQuantity.php">
                <br />
                <br />
                <br />
                <table>
                <tr>
                <td style="width: 200px">Product Code:</td>
                <td><?php echo $productcode; ?><input type='hidden' name="ProductCode" value="<?php $productcode;?>" /></td>
                </tr>
                <tr>
                <td>Product Name:</td>
                <td><?php echo $productname; ?><input type='hidden' name="ProductName" value="<?php $productname;?>" /></td>
                </tr>
                <tr>
                <td>Product Description:</td>
                <td><?php echo $productdescription;; ?><input type='hidden' name="ProductDescription" value="<?php $productdescription;?>" /></td>
                </tr>
                <tr>
                <td>Product Colour:</td>
                <td><?php echo $productcolour; ?><input type='hidden' name="ProductColour" value="<?php $productcolour;?>" /></td>
                </tr>
                <tr>
                <td>Product Price:</td>
                <td><?php echo $productprice; ?><input type='hidden' name="ProductPrice" value="<?php $productprice;?>" /></td>
                </tr>
                <tr>
                <td>Product Image:</td>
                <td><?php echo $productimage; ?><input type='hidden' name="ProductImage" value="<?php $productimage;?>" /></td>
                </tr>
                <tr>
                <td>Quantity in Stock:</td>
                <td><?php echo $productquantity; ?><input type='hidden' name="ProductQuantity" value="<?php $productquantity;?>" /></td>
                </tr>
                </table>
                <table>
                <tr>
                <td style="width: 200px">Quantity:</td>
                <td><input type="number" name="Quantity" id="Quantity" value="<?php if (isset ($quantity)) echo $quantity; ?>"size = "20" /></td>
                <td><input type="submit" name="Purchase" value= "Purchase" /></td>
                </tr>
                </table>
                </form>
                <hr />
                <?php
	}
		include "footer.html";
}
}

?>

cedricganon · November 3, 2011

heres what i noticed:

$query = "select productquantity from products where productcode = '$sproductcode'";

Try this:

$query = "SELECT productquantity FROM products WHERE productcode='" . $productcode . "';";

see if that helps!

Veraedon · November 3, 2011

Okay well firstly im really not aware of what the purpose of mysqli is/does... i have always found that mysql has done the job perfectly so im really not sure why you are using mysqli (however as i said im not experienced with it so it could be the next big thing.)

When i said put the 2 forms into 1, thats exactly what i meant, all the html tables are just for usability.

see if this is any better!

Catalogue/Place order page:

<?php
session_start();
require_once "../database/dbcustomer.php";
require_once "../includes/functions.php";

$page_title = 'Product Catalogue';
include_once "header.php";

$error_message = '';

$conn = mysqli_connect ($dbhost, $dbuser, $dbpassword, $dbname);

$query = "SELECT * from products";

$result = mysqli_query($conn, $query);

if (!$result)
{
include_once "header.php";
die ("Error, could not query the database");
}

else 
{ $rows = mysqli_num_rows($result);

if ($rows>0)
{

while ($row = mysqli_fetch_array($result))
{
            $productcode = $row['ProductCode'];
            $productname = $row['ProductName'];
            $productdescription = $row['ProductDescription'];
            $productcolour = $row['ProductColour'];
            $productprice = $row['ProductPrice'];
            $productimage = $row['ProductImagePath'];
            $productquantity = $row['ProductQuantity'];
            $quantity = $row['Quantity'];

	?>
			<form method="post"action="processQuantity.php">
                <br />
                <br />
                <br />
                <table>
                <tr>
                <td style="width: 200px">Product Code:</td>
                <td><?php echo $productcode; ?><input type='hidden' name="ProductCode" value="<?php $productcode;?>" /></td>
                </tr>
                <tr>
                <td>Product Name:</td>
                <td><?php echo $productname; ?><input type='hidden' name="ProductName" value="<?php $productname;?>" /></td>
                </tr>
                <tr>
                <td>Product Description:</td>
                <td><?php echo $productdescription;; ?><input type='hidden' name="ProductDescription" value="<?php $productdescription;?>" /></td>
                </tr>
                <tr>
                <td>Product Colour:</td>
                <td><?php echo $productcolour; ?><input type='hidden' name="ProductColour" value="<?php $productcolour;?>" /></td>
                </tr>
                <tr>
                <td>Product Price:</td>
                <td><?php echo $productprice; ?><input type='hidden' name="ProductPrice" value="<?php $productprice;?>" /></td>
                </tr>
                <tr>
                <td>Product Image:</td>
                <td><?php echo $productimage; ?><input type='hidden' name="ProductImage" value="<?php $productimage;?>" /></td>
                </tr>
                <tr>
                <td>Quantity in Stock:</td>
                <td><?php echo $productquantity; ?><input type='hidden' name="ProductQuantity" value="<?php $productquantity;?>" /></td>
                </tr>
                </table>
                <table>
                <tr>
                <td style="width: 200px">Quantity:</td>
                <td><input type="number" name="Quantity" id="Quantity" value="<?php if (isset ($quantity)) echo $quantity; ?>"size = "20" /></td>
                <td><input type="submit" name="Purchase" value= "Purchase" /></td>
                </tr>
                </table>
                </form>
                <hr />
                <?php
	}
		include "footer.html";
}
}

?>

gives me a Notice: Undefined index: Quantity in C:\xampp\htdocs\Bazaar\shop\displayCatalogue-PlaceOrder.php on line 38 error on the product/catalogue page (bear in mind that quantity is in the orders table, separate from all the other fields which are in the products table) and when I attempt to process an order I get the following errors -

Notice: Undefined index: OrderId in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line 10

Notice: Undefined index: ProductImagePath in C:\xampp\htdocs\Bazaar\shop\processQuantity.php on line

seany123 · November 3, 2011

Okay so you can removed the line:

now im abit confused, let me have another look through to get my head round what the script is doing.

$quantity = $row['Quantity'];

because the from is obviously going to deal with the quantity.

For the OrderID/ProductImagePath your getting the error because the variable is not being assigned again... they need to either be posted from the form, retrived from the database or manually assigned in the script?

Pikachu2000 · November 3, 2011

There is no such thing as <input type="number". That isn't a valid attribute.

seany123 · November 3, 2011

There is no such thing as <input type="number". That isn't a valid attribute.

missed that one!

@Veraedon

Your script seems to have quite a few different problems, i would really suggest just completely rewriting the code... i would really advise looking into COOKIES/SESSIONS to populate your sales cart/bucket... also have a good look into your tables structure and try any work out exactly what everything is doing.. finally i still havnt looked into mysqli but for the life of me i cant figure out why your not using 'normal' mysql (i dont think its directly contributing towards your errors, but its certainly making things more confusing for me! lol)

Veraedon · November 3, 2011

I don't believe that is the case? - http://www.html5tutorial.info/html5-number.php

However, I am happy to change it if you think that would solve the issue. However I don't believe that would resolve the current problem I am having.

Veraedon · November 3, 2011

mysql and mysqli do the same thing, it's just how I learned to code it I may well have to recode all of it from scratch. Still trying to figure out how I would achieve my desired result though. I keep hitting a wall with the queries into php, so messy =\

seany123 · November 3, 2011

You need to make sure that all of your variables are being set from sources that actually exist. for example to $_POST a variable it needs to be inside a form with

<form method="POST">

</form>

it also needs to be assigned a "name" which would in turn work out as $_POST['name'];

and also it needs to be given a value.

EG.

<input type='text' name='productname' value='boots'/>

When dealing with the variables that are being assigned from a $row...etc

the name+value comes from the database table that you have selected with the mysql select query

in your case:

$query = "SELECT * from products";

$result = mysqli_query($conn, $query);
$row = mysqli_fetch_array($result);

im guessing that the quantity inside products would be the quantity that you have in stock...

so you will be wanting to pass the quantity value over using $_POST from the form.

Pikachu2000 · November 3, 2011

Alright, the <input type="number" is a new one for HTML5, and I"d never seen it before. But I agree that the rest of the code has a lot of problems, and should probably be rewritten.

Veraedon · November 3, 2011

Agreed, I am going to have to rewrite the whole thing. One question, unrelated to this, has me completely stumped.

I have written this code for logon for customers so that they can access other pages. Now it was working perfectly before... now it's not working at all..I simply get a blank page.

Am I missing something here? I haven't made any changes since and the queries match the tables otherwise I would be getting an error message:

Logon Page:

<?php
if (isset($_SESSION['logged']))

$page_title = 'Log on';
require_once "header.php";
?>

<h3>Previously registered? Enter your details here.</h3>

<?php

if (!empty($error_message))
{
echo '<p style="color:red">'. $error_message . '</p>';
}
?>

<form method="post"action="processLogon.php">
<table>
<tr>
    	<td>Username:</td>
        <td><input type="text" name="username" id="username" value="<?php if (isset($username)) echo $username; ?>" size = "40" /></td>
    </tr>
    <tr>
    	<td>Password:</td>
        <td><input type="password" name="password" id="password" value="<?php if (isset($password)) echo $password; ?>" size = "40" /></td>
    </tr>
    <tr>
    <td></td>
        <td><input type="submit" name="submit" id="submit" value="Submit" /><input type="reset" name="reset" value="Reset" /></td>
    </tr>
</table>
</form>

<?php include_once "footer.html";?>

Process Logon Page:

<?php
//the logged session must be set before the header file is included (due to navigation bars)
if (!isset($_SESSION))
{
session_start();
}

require_once "../includes/functions.php";
require_once "../database/dbcustomer.php";

//get inputs from form
$username = $_POST['username'];
$password = $_POST['password'];

$error_message = '';

//validate data
$error_message .= validate_username($username);
$error_message .= validate_password($password);

//sanitise data
$susername = sanitiseString($username);
$spassword = sanitiseString($password);

if ($error_message != '')
{
include_once "index.php";
exit();
}
else
{
$conn = mysqli_connect($dbhost, $dbuser, $dbpassword, $dbname);

if (!$conn)
{
$error_message = "Could not connect to server";
include_once "logon.php";
exit();
}
else
{
$hspassword = hash('sha256', $spassword);

$query = "select * from customers where username = '$susername' and password = '$hspassword'";

$result = mysqli_query($conn, $query);

if ($result)
{
	$norow = mysqli_num_rows($result);

	if ($norow == 1)
	{
		$row = mysqli_fetch_row($result);
		$_SESSION['name'] = $row[1];
		$_SESSION['logged'] = true;
		include_once "header.php";
		echo "<br /><br /><br />Welcome back " . $_SESSION['name'];
		echo "<p>You are now logged on.</p>";
		include_once "footer.html";
	}

	else
	{
		$error_message = "Username and password are invalid <br />";
		include_once "logon.php";
		exit ();
	}
}
}
}
?>


<?php
//To check that a user is registered, you would write an sql query to select a record from the table where theusername  the username entered in the form and the password = the password entered in the form.
?>

Pikachu2000 · November 3, 2011

Nothing jumps out as being "wrong" with it; there aren't any parse errors or anything like that. You should start by adding some echos in the conditionals to debug where the script starts to deviate from what you think it should be doing.

Veraedon · November 3, 2011

After some fiddling, for some reason it was the connection script to the database.. which makes no sense as there are sufficient privileges to access that kind of data. Oh well...

Back on topic though:

Code rewritten, only getting one error which is: Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, null given in C:\xampp\htdocs\Bazaar\includes\functions.php on line 201

From my understanding of the error, it is to do with the database needing to be open... which it should be from the $conn already..

function sanitiseMySQL($var)
{
global $conn;

[b]$var = mysqli_real_escape_string ($conn, $var);[/b] //Line 201
$var = sanitiseString($var);
return $var;
}

Place Order Page

<?php

if (!isset($_SESSION))
{
session_start();
}

require_once "../database/dbcustomer.php";
require_once "../includes/functions.php";

$page_title = 'Product Catalogue';
include_once "header.php";

$conn = mysqli_connect ($dbhost, $dbuser, $dbpassword, $dbname);

$query = "SELECT * from products";

$result = mysqli_query($conn, $query);

if (!$result)
{
include_once "header.php";
die ("Error, could not query the database");
}

else 
{ $rows = mysqli_num_rows($result);

if ($rows>0)
{

while ($row = mysqli_fetch_array($result))
{
	?>
			<form method="post"action="processQuantity.php">
                <br />
                <br />
                <br />
                <table>
                <tr>
                <td style="width: 200px">Product Code:</td>
                <td><?php echo $row['ProductCode']; ?><input type="hidden" name="ProductCode" id="ProductCode" value="<?php if (isset ($productcode)) echo $productcode; ?>"size = "20" /></td>
                </tr>
                <tr>
                <td>Product Name:</td>
                <td><?php echo $row['ProductName']; ?><input type="hidden" name="ProductName" id="ProductName" value="<?php if (isset ($productname)) echo $productname; ?>"size = "20" /></td>
                </tr>
                <tr>
                <td>Product Description:</td>
                <td><?php echo $row['ProductDescription']; ?><input type="hidden" name="ProductQuantity" id="ProductQuantity" value="<?php if (isset ($productquantity)) echo $productquantity; ?>"size = "20" /></td>
                </tr>
                <tr>
                <td>Product Colour:</td>
                <td><?php echo $row['ProductColour']; ?><input type="hidden" name="ProductColour" id="ProductColour" value="<?php if (isset ($productcolour)) echo $productcolour; ?>"size = "20" /></td>
                </tr>
                <tr>
                <td>Product Price $:</td>
                <td><?php echo $row['ProductPrice']; ?><input type="hidden" name="ProductPrice" id="ProductPrice" value="<?php if (isset ($productprice)) echo $productprice; ?>"size = "20" /></td>
                </tr>
                <tr>
                <td>Product Image:</td>
                <td><img src="<?php echo $row['ProductImagePath']; ?>" /><input type="hidden" name="ProductImagePath" id="ProductImagePath" value="<?php if (isset ($productimagepath)) echo $productimagepath; ?>"size = "20" /></td>
                </tr>
                <tr>
                <td>Quantity in Stock:</td>
                <td><?php echo $row['ProductQuantity']; ?><input type="hidden" name="ProductQuantity" id="ProductQuantity" value="<?php if (isset ($productquantity)) echo $productquantity; ?>"size = "20" /></td>
                </tr>
                <tr>
                <td>Quantity:</td>
                <td><input type="number" name="Quantity" id="Quantity" value="<?php if (isset ($quantity)) echo $quantity; ?>"size = "20" /></td>
                <td><input type="submit" name="Purchase" value= "Purchase" /></td>
                </tr>
                </table>
                </form>
                <hr />
                <?php
	}
		include "footer.html";
}
}


Process Order Page

?>

<?php
if (!isset($_SESSION))
{
session_start();
}

require_once "../includes/functions.php";
require_once "../database/db.php";

//get variables from form

$quantity = $_POST['Quantity'];
$productquantity = $_POST['ProductQuantity'];
$productcode = $_POST['ProductCode'];
$productprice = $_POST['ProductPrice'];
$productimagepath = $_POST['ProductImagePath'];
$total = $quantity * $productprice;

$error_message = '';

//sanitise date
$sproductcode = sanitiseMySQL($productcode);
$squantity = sanitiseMySQL($quantity);
$sproductprice = sanitiseMySQL($productprice);


$error_message = '';

if ($error_message != '')
{
include_once "index.php";
exit();
}
else
{
$conn = mysqli_connect($dbhost, $dbuser, $dbpassword, $dbname);

if (!$conn)
{
	$error_message = "Could not connect to server";
	include_once "displayCatalogue-PlaceOrder.php";
	exit ();
}
else
{
	$query = "SELECT productquantity FROM products WHERE productcode = '$sproductcode'";

	$result = mysqli_query ($conn, $query);

	$productquantity = mysqli_num_rows($result);

if ($quantity <= $productquantity)
{
	$error_message = "You cannot order more than what is currently instock";
		include_once "displayCatalogue-PlaceOrder.php";
		exit ();
}
else
{
	$row = mysqli_fetch_row($result);

	$query = "INSERT INTO orders (productcode, quantity, productprice, orderdate) VALUES ('$sproductcode', '$squantity', '$sproductprice', '(CURDATE())')";

	$result = mysqli_query($conn, $query);

	$row = mysqli_affected_rows($conn);

	if ($row > 0)
	{
		include "header.php";?>
            <h3>Order Confirmation</h3>
            <p>Thank you, your order is now being processed.</p>
                <table>
			<tr>
			<td style="width: 200px">Order Number:</td>
                <td><?php echo $orderid; ?></td>
               	</tr>
                <tr>
                <td>Product Code:</td>
                <td><?php echo $row('productcode'); ?></td>
                </tr><tr>
                <td>Quantity:</td>
                <td><?php echo $row('quantity'); ?></td>
                </tr>
                <tr>
                <td>Price:</td>
                <td><?php echo $row('productPrice'); ?></td>
                </tr>
                <tr>
                <td>Total Cost of Order:</td>
                <td><?php echo $total; ?></td>
                </tr>
                </table>

                <?php
			include "footer.html";
	}
	else
	{
		$error_message ="Error placing your order, please try again";
		include "displayCatalogue-PlaceOrder.php";
		exit();
	}
}
}
}
//this is used to validate the quantity entered against what is available in the database
?>

PFMaBiSmAd · November 3, 2011

The last code file you posted is calling the function that uses $conn before your code has created a database connection in $conn. You are also not testing if a form was submitted before you attempt to use any of the $_POST data from the form. That will just cause errors any time the page gets requested when there is not a form submission.

Veraedon · November 4, 2011

Well that is the idea, the process order page is actually a result page of the place order page, so I don't mind that at all. I'm not quite sure what you mean about the $conn being called for prior to a connection being made... which extract of code are you referring to?

Sign In

Inserting values via php to sql database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information