php/ mysql - Sending multiple data to MySQL

Question

I am trying to store information in MySQL and have run into an issue. Only the 'name' is stored in MySQL even though I am trying to store both name and total. I have tried to use email instead of total but still it is not stored, this tells me it is not a variable specific problem?

Currently I have this code:

<?php

require 'cart.php';

define('DB_NAME', 'orders');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if(!$db_selected) {
    die('Unable to use ' . DB_NAME . ': ' . mysql_error());
}

...

    $name = $_POST['name'];
    $total = $_POST['total'];

    $sql = "INSERT INTO orders (name, total) VALUES ('$name', '$total')";

    if (!mysql_query($sql)) {
        die ('Error: ' . mysql_error());
    }


    mysql_close();

    ?>

Here is my whole cart.php:

<?php

session_start();

$page = 'index.php';

mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('cart') or die(mysql_error());

if (isset($_GET['add'])) {
    $quantity = mysql_query('SELECT id, quantity FROM products WHERE id='.mysql_real_escape_string((int)$_GET['add']));
    while ($quantity_row = mysql_fetch_assoc($quantity)) {
        if ($quantity_row['quantity']!=$_SESSION['cart_'.(int)$_GET['add']]) {
            $_SESSION['cart_' . (int)$_GET['add']] +='1';
            header('Location: order.php');

        }
    }
    header('Location: order.php');

}

if (isset($_GET['remove'])) {
    $_SESSION['cart_'.(int)$_GET['remove']]--;
    header ('Location: order.php');

}

if (isset($_GET['delete'])) {
    $_SESSION['cart_' . (int)$_GET['delete']]='0';
    header ('Location: order.php');
}



function products() {
    $get = mysql_query('SELECT id, name, description, price FROM products WHERE quantity > 0 ORDER BY id ASC');
if (mysql_num_rows($get) == 0) {
    echo "There are no products to display.";
}
else {
    echo "<center>\n";
    echo "  <table class='menufinal' border=0 width=75%>\n";
    echo "      <tr>\n";
    echo "      <th>View</th>\n";
    echo "      <th>Dish</th>\n";
    echo "      <th>Description</th>\n";
    echo "      <th>Item Price</th>\n";
    echo "      </tr>\n";
    while ($get_row = mysql_fetch_assoc($get)) {

    ?>
    <tr>
        <td><img src="template.png" height="110" width="110"/> </td>
        <td> <?echo '<p><strong>'.$get_row['name'] . '</strong>'?> </td>
        <td> <?echo $get_row['description']?> </td>
        <td><strong> <?echo '<br>&pound'.number_format($get_row['price'],2).'<br><br> <a href="cart.php?add='.$get_row['id'].'"><button>Add</button></a></p>';?> </strong></td>
    </tr>
    <?
    } 
    echo "</table>\n";
    echo "</center>\n";
}
} 

function cart() {

$output = '';
$output .= '<center>';
$output .= '<table class="menufinal" border=0 width=75%>';
$output .= '<tr>';
$output .= '<th>Remove Item</th>';
$output .= '<th>Item Name</th>';
$output .= '<th>Item Price</th>';
$output .= '<th>Quantity</th>';
$output .= '<th>Line Total</th>';
$output .= '</tr>';

foreach($_SESSION as $name => $value) {
    if ($value>0){
        if (substr($name, 0, 5)=='cart_') {
            $id = substr($name, 5, (strlen($name)-5));
            $get = mysql_query('SELECT id, name, price FROM products WHERE id='.mysql_real_escape_string((int)$id));
           while ($get_row = mysql_fetch_assoc($get)) {
                $sub = $get_row['price']*$value;
                $output .= '<tr>';
                $output .= '<td><a href="cart.php?delete=' .$id.'"><img src="x.png"></a><br></td>';
                $output .= '<td>' . $get_row['name'] . '</td>';
                $output .= '<td>&pound ' . number_format($get_row['price'], 2) . '</td>';
                $output .= '<td><a href="cart.php?remove=' .$id. '"style="text-decoration:none"><strong>- </strong></a>' .$value. '<a href="cart.php?add=' .$id. '"style="text-decoration:none"> +</a></td>';
                $output .= '<td>&pound ' . number_format($sub, 2) . '</td>';
                $output .= '</tr>';
            }
        } 
        if (empty($total)) {

            if (empty($sub)) {
                //do nothing
            } else {
                $total = $sub;
            }
        } else {
            $total += $sub;
        }
    }
}

$output .= '</table>';

if (empty($total)){
    header ('Location: index.php');
    exit;    
}

$output .=  '<br><br><br><br><div id="finalPrice">Total: &pound ' . number_format($total, 2) . '<br></div>';
$output .=  '<br><br><br><p><a href="index.php"><img src="dishes.png" width="240" height="152"></a> <img src="spacer.png" width="110"> <a href="checkout.php"><img src="checkout.png" width="240" height="151"></a>';

echo $output;
}

?>

If I need to show more code please let me know.

Answer 1

There are a couple of linked issues here. Your cart function is effectively:

function cart() {

    //print some stuff

    foreach($_SESSION as $name => $value) {
        if (empty($total)) {

            if (empty($sub)) {
                //do nothing
            } else {
                $total = $sub;
            }
        } else {
            $total += $sub;
        }
    }
}

You're setting a variable called $total inside your function, but PHP treats that as what's called a local variable - that's a variable that exists within the function itself, but not outside of it. Likewise, variables that are set outside the function can't be used inside it unless you explicitly make them available - this is called variable scope.

There are a few ways to access variables set inside functions. Since you're using $_SESSION already, I'd suggest using this:

function cart() {
    $total = 0;

    //print some stuff

    foreach($_SESSION as $name => $value) {
        if (! empty($sub)) {
            $total += $sub;
        }
    }

    $_SESSION['total'] = $total;
}

I'm initialising $total at the start of the function, so you don't need to check if it exists before trying to add to it. I'm also setting a variable in the session, which you can use outside the function.

Secondly - you need to call your function before you can use it. None of your code above is calling your function - all you need to do is have a line with cart(); on it.

Then, when you're setting the variables for the database, you can use:

$name = $_POST['name'];
$total = $_SESSION['total'];

A couple of other things - mysql_ functions are deprecated, and will be removed from a future version of PHP. You shouldn't really be using them for new code now - mysqli_ functions are fairly easy to switch to, and let you use prepared statements, which help you make your code more secure; and there's also the PDO library, which isn't as direct to switch code over to, but which isn't tied in to a specific database.

Also - and this is a personal preference - your cart() function is printing out the contents of the cart, as well as doing some calculations on it. I try to have functions that do one single thing, rather than lump them together, even if they do fit. In this case, you could have a print_cart() function and a get_cart_total() function - I find that it's a lot easier to maintain code that's written that way.

Answer 2

If NULL or blank is being inserted for a value in the row it means that the value has not been set correctly as @andrewsi has said in the comments.

You need to add a check for the $_POST variables your attempting to use, to verify that they are being sent correctly.

You also need to check the form (or other places) that this specific PHP is requested from.

In the code above you could use the answer from: https://stackoverflow.com/a/9154726/1688441

Example:

if(isset($_POST['total']) && "" != trim($_POST['total'])){
    echo "Do what you need here";
}      
else
{
  die("Incorrect input sent to POST variable total.");
}

Update

If your setting the value of total in cart.php you should not try to assign from $_POST unless your sending through post. Just delete the line $total = $_POST['total'];.