If I order two products, it will ony save one product in my database

Question

When I click on checkout, it will send me an email with my order and send the products I ordered to mysql database. But only one(the last product) product is send and saved in the database. How can I fix this?

//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");

//get order id ONLY to get order id <<<<<<<
$vol = mysql_query("SELECT orderid FROM ordertracking WHERE email='$email'");
while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
} // end of getting order id from table ordertracking
    // add new order
    $order = "INSERT INTO `order` (orderid, customerid, productid, brand, model, price, amount, totalcost, image) VALUES ('$orderid', '$customerid', '$productid', '$brand' , '$model', '$price', '$amount', '$totalcost', '$image')";
    if (!mysql_query($order,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
        echo "New order added" . "<br />";

        mysql_close($connection);

Entire script:

<?php
error_reporting(-1);ini_set('display_errors', 'stdout');
var_dump($_GET);

//collect all information
$name = $_GET["name"];
$surname = $_GET["surname"];
$city = $_GET["city"];
$postalcode = $_GET["postalcode"];
$phonenumber = $_GET["phonenumber"];
$email = $_GET["email"];

$i = 1;
while (isset($_GET["Product_ID_".$i])) {
    $productid = $_GET["Product_ID_".$i];
    $brand = $_GET["Brand_".$i];
    $model = $_GET["Model_".$i];
    $price = $_GET["Price_".$i];
    $amount = $_GET["Amount_products_".$i];
    $totalcost = $_GET["Total_cost_".$i];
    $i++;
}

$image = "includes/images/mouse_4.jpg";


$date = date("F j, Y, g:i a");



//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");  

//check if already customer
$result = mysql_query("SELECT * FROM customer WHERE email='$email'");
$rows = mysql_num_rows($result);


    if ($rows) 
    {
      echo '<br>Welcome back ' . $name .' '. $surname. '<br>';
    }
    else
    {
        //if new customer, add to database
        $customer = "INSERT INTO customer (customerid, name, surname, email, city, postalcode, phonenumber) VALUES ('', '$name', '$surname', '$email', '$city', '$postalcode', '$phonenumber')";
        if (!mysql_query($customer,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
        echo "New customer added" . "<br />";
        echo '<br>Welcome as our new customer ' . $name . ' '. $surname;

        mysql_close($connection);   
    }

//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");


//get customer id
$res = mysql_query("SELECT customerid FROM customer WHERE email='$email'");
while($row=mysql_fetch_array($res))
{
 $customerid=$row['customerid'];
}
    //add new ordertracking
    $ordertracking = "INSERT INTO `ordertracking` (orderid, customerid, email, progress, date) VALUES ('', '$customerid', '$email', 'Pending', '$date')";
    if (!mysql_query($ordertracking,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
        echo "New order added" . "<br />";

        mysql_close($connection);

//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");

//get order id
$vol = mysql_query("SELECT orderid FROM ordertracking WHERE email='$email'");
while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
}
    // add new order
    $order = "INSERT INTO `order` (orderid, customerid, productid, brand, model, price, amount, totalcost, image) VALUES ('$orderid', '$customerid', '$productid', '$brand' , '$model', '$price', '$amount', '$totalcost', '$image')";
    if (!mysql_query($order,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
        echo "New order added" . "<br />";

        mysql_close($connection);


$to = $email;
$subject = "Order information of: ";

$headers = "From: " . "postmaster@localhost" . "\r\n";
$headers .= "Reply-To: ". "postmaster@localhost" . "\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";

$message = '<html><body>';
$message .= '<h1>Dear ' . $name . ' ' . $surname .  ',</h1>' . '<br />';
$message .= 'Order date and time: ' . $date . '<br />';
$message .= 'Thank you for your order at our online shop!' . '<br />';
$message .= 'Your order information: ' . '<br /><br /><br />';
$i = 1;
while (isset($_GET["Product_ID_".$i])) {
    $productid = $_GET["Product_ID_".$i];
    $brand = $_GET["Brand_".$i];
    $model = $_GET["Model_".$i];
    $price = $_GET["Price_".$i];
    $amount = $_GET["Amount_products_".$i];
    $totalcost = $_GET["Total_cost_".$i];

    $message .= ' Product ID: ' . $productid . "<br />" .
                'Brand: '. $brand . "<br />" .
                'Model: ' . $model . "<br />" .
                'Price per item: ' . $price . "<br />" .
                'Amount of item: ' . $amount . "<br />" .
                'Total cost: ' . $totalcost . "<br />" .
                '_________________________________________________| ' . "<br />" .
    $i++;
}
$message .= 'To follow your odertracking please remember your order ID and customer ID' . '<br />';
$message .= 'Order ID: ' . $orderid . '<br />';
$message .= 'Customer ID: ' . $customerid . '<br />';
$message .= 'Link to track your order: ' . '<a href="http://localhost/school/shoppingcart/ordertracking.php">Ordertracking system</a>' . '<br />';

$message .= '</body></html>';

mail($to, $subject, $message, $headers);
?>

PHP's `mysql_*` functions are [deprecated](http://www.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated). There are [alternatives](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) that are both supported and [much safer](http://stackoverflow.com/a/60496/132382). — pilcrow, Jul 03 '12 at 15:27

score 1 · Answer 1 · answered Jul 01 '12 at 09:36

1

You are only running the query once since you are closing your while loop too soon:

while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
}

you should close it after the line:

 echo "New order added" . "<br />";

answered Jul 01 '12 at 09:36

Tomer

17,787
15
78
137

Did not work, It still is sending the last product, but more times – MOTIVECODEX Jul 01 '12 at 09:49
1

Do a print_r($volume) so you can see what you are getting. – Tomer Jul 01 '12 at 09:52
Not much, but actually `while($volume=mysql_fetch_array($vol)) { $orderid = $volume['orderid']; }` is only to fetch the orderid from the previous added orderid in ordertracking. It has nothing to do with the adding of the order in order. So I'm fetching the orderid from table ordertracking and adding that orderid to `orderid` in table order. I think the problem is somewhere else, I am going to edit my question a little bit – MOTIVECODEX Jul 01 '12 at 10:01
2

What I'm getting at is that you might have the same data twice in the volumes array, and the problem lies in fetching the data from ordertracking and not in the insert. – Tomer Jul 01 '12 at 10:06
I'm giving up, this script has two problems, one not sending all the products to my database, only the last product and one is that the email is not showing all the data. only the first 4 products. It's like the email has line limit or something. Well have to show this tomorrow at school and I am just giving up on the two problems. The teacher has to deal with it. Thank you I will upvote your posts but can't flag it as finished – MOTIVECODEX Jul 01 '12 at 10:15

score 1 · Accepted Answer · answered Jul 01 '12 at 09:38

what you are doing here is this:

get all the order ID from the orderTracking table where email is the given email. I assume that more than one row is fetched.

the code

while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
}

works great but when you try to insert the value of orderid to [order] table only one row is updated because the variable $orderid now contains only one string (The last row's orderid of the query).

so the solution is to run insert inside the while loop

something like this

$vol = mysql_query("SELECT orderid FROM ordertracking WHERE email='$email'");
while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
// add new order
    $order = "INSERT INTO `order` (orderid, customerid, productid, brand, model, price, amount, totalcost, image) VALUES ('$orderid', '$customerid', '$productid', '$brand' , '$model', '$price', '$amount', '$totalcost', '$image')";
    if (!mysql_query($order,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
}

hope you will get an idea.

the second solution can be that instead of using simple variable $orderid you can use an array variable. then loop through the variable to get the orderid's.

in short according to your code you need a loop to insert more than one record in the database.

Yes it send two orders now, but both are the same product. Example, I order product1 and product2. It will send product2 twice to my database. — MOTIVECODEX, Jul 01 '12 at 09:43
@F4LLCON this should work until and unless the data is not correct. still i will try the script with a dummy database and will get back. — Ankit Suhail, Jul 01 '12 at 10:23
the code should work as its working fine with some dummy data. Check your database values. The Php code is correct according to me. — Ankit Suhail, Jul 02 '12 at 05:39

If I order two products, it will ony save one product in my database

2 Answers2