IT Marketing IT Marketing - 24 days ago 6
HTML Question

checkout products, storing duplicate records in database in php/mysql

I m working on ecommerce site. When u click on checkout in shopping cart, it ask for card information and stores the inputs in database. But, how to stores the product Id, name and price of every individual product in database? I try this on while loop, but it stores the last product ID, name and price in every record.

SQL

CREATE TABLE IF NOT EXISTS `orders` (
`id` int(100) NOT NULL AUTO_INCREMENT,
`txnid` varchar(20) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`payer_email` varchar(100) NOT NULL,
`itemid` int(100) NOT NULL,
`item_name` varchar(100) NOT NULL,
`payment_amount` varchar(100) NOT NULL,
`payment_status` varchar(25) NOT NULL,
`payment_type` varchar(100) NOT NULL,
`createdtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `txnid`, `first_name`, `last_name`, `payer_email`, `itemid`, `item_name`, `payment_amount`, `payment_status`, `payment_type`, `createdtime`) VALUES
(17, '', 'Nooh', 'Naeem', 'noohnaeem@yahoo.com', 3, 'Books - 1 - Title', '100', 'Complete', 'Credit Card', '2017-09-22 17:12:15'),
(16, '', 'Nooh', 'Naeem', 'noohnaeem@yahoo.com', 3, 'Books - 1 - Title', '100', 'Complete', 'Credit Card', '2017-09-22 17:12:15'),
(15, '', 'Nooh', 'Naeem', 'noohnaeem@yahoo.com', 3, 'Books - 1 - Title', '100', 'Complete', 'Credit Card', '2017-09-22 17:12:15');


PHP

<?php

$total= 0;

global $con;

$ipaddress = $_SERVER['REMOTE_ADDR'];

$sel_price= "select * from cart where ip_add='$ipaddress'";

$run_price= mysqli_query($con,$sel_price);

while($p_price=mysqli_fetch_array($run_price)){

$cart_id= $p_price['id'];
$ip_add= $p_price['ip_add'];
$pro_id= $p_price['p_id'];

$pro_price= "select * from academic_work where id='$pro_id'";

$run_pro_price= mysqli_query($con,$pro_price);

while($pp_price= mysqli_fetch_array($run_pro_price)){

$product_price=array( $pp_price['price']);

$product_name= $pp_price['title'];

$single_product_price=$pp_price['price'];

$values= array_sum($product_price);

$total +=$values;

echo "

<input name='cc_item_number' value='$pro_id'>
<input name='cc_item_name' value='$product_name'>
<input name='cc_amount' value='$single_product_price'>
";

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

$cc_first_name=$_POST['cc_first_name'];
$cc_last_name=$_POST['cc_last_name'];
$cc_payer_email=$_POST['cc_payer_email'];
$cc_item_number=$_POST['cc_item_number'];
$cc_item_name=$_POST['cc_item_name'];
$cc_amount=$_POST['cc_amount'];
$cc_payment_status=$_POST['cc_payment_status'];
$cc_payment_type=$_POST['cc_payment_type'];

$date = date("Y:m:d H:i:s");

$query = "insert into orders (first_name,last_name,payer_email,itemid,item_name,payment_amount,payment_status,payment_type,createdtime) values ('$cc_first_name','$cc_last_name','$cc_payer_email','$cc_item_number','$cc_item_name','$cc_amount','$cc_payment_status','$cc_payment_type','$date')";

$run_query=mysqli_query($con,$query);

if($run_query)
{
echo "<div class='alert alert-success'>
Successfly Entered Information !!!
</div>";
}
else{
echo "<div class='alert alert-danger'>
Please Try Again.
</div>";
}
}
}
}

?>

Answer Source

What you should do is create an OrderProduct table, which contains a productid and an orderid. To add 4 products, with id's 17 to 20 to the order with id 5 you would do:

INSERT INTO orderproduct (orderid, productid) VALUES (5,17);
INSERT INTO orderproduct (orderid, productid) VALUES (5,18);
INSERT INTO orderproduct (orderid, productid) VALUES (5,19);
INSERT INTO orderproduct (orderid, productid) VALUES (5,20);

To select all products that are in an order you can use:

SELECT * FROM product 
INNER JOIN orderproduct ON orderproduct.productid = orderproduct.itemid 
WHERE orderproduct.orderid = 5;

You might be able to find tutorials if you google 'many to many relation mysql`.