CODEWITHSUNDEEP
phpmysqlarrayssession
jediah
jediah

Reputation: 102

How do i put an array in mysql?

How do i put an session array into mysql rows?

Session array output when using print_r($_SESSION['producten']):

Array
(
    [producten] => Array
        (

            [0] => Array
                (
                    [product_id] => 3
                    [aantal] => 2
                )

            [1] => Array
                (
                    [product_id] => 2
                    [aantal] => 1
                )

        )
)

Mysql:

$order_ins = "INSERT INTO orders_products (order_id,product_id,product_amount) VALUES
('".$_SESSION['order_id']."', '".$_SESSION['product_id']."','".$_SESSION['aantal']."' )";

mysql_query($order_ins) or die("Ehh..error!?" . mysql_error());

What I want is that the "product_id" and "aantal" (means amount in dutch) will be inserted in mysql rows. Afterwards the products and amounts can be filtered using the "order_id"

Upvotes: 1

Views: 191

Answers (4)

Geoff Montee
Geoff Montee

Reputation: 2597

It's possible to store session information in a database. I wrote a class that does this.

See here:

sessionwrapper.php

I use a small wrapper around PDO:

databasehelper.php

It's very easy to use:

Instead of:

session_start();

Do:

require_once("sessionwrapper.php");

SessionWrapper::startGlobalSession();

The DDL for the table:

CREATE TABLE user_session (
    session_id VARCHAR(64) PRIMARY KEY,
    last_request_time INTEGER,
    session_data TEXT
);

It automatically keeps track of your session information in the PDO database connection that you provide. You don't need to do anything different. Just set your $_SESSION variable as normal.

if (isset($_SESSION['user_id']))
{
    echo "You are already logged in. <br />\n";
}
else
{
    printLoginForm();
}

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562230

It's much easier with PDO and parameterized statements. No risk of SQL injection, no confusing string concatenation, and no worry about whether you escaped your quote characters correctly.

<?php

$sql = "INSERT INTO orders_products (order_id, product_id, product_amount)
        VALUES (:order_id, :product_id, :product_amount)";
$stmt = $pdo->prepare($sql);
$data = array("order_id" => $_SESSION["order_id"]);
foreach ((array) $_SESSION["producten"] as $order) {
    $data["product_id"]     = $order["product_id"];
    $data["product_amount"] = $order["aantal"];
    $stmt->execute($data);
}

Upvotes: 1

Kermit
Kermit

Reputation: 34054

This should work, however, be aware of the risk of SQL injection.

$sql = "INSERT INTO orders_products (order_id, product_id, product_amount) VALUES ";

$i = 1;

foreach($_SESSION[producten] as $innerArray) {
    $sql .= '(' . $_SESSION[order_id] . ', ' . $innerArray[product_id] . ', ' . $innerArray[aantal] . ')';

    if($i < count($_SESSION[producten])) $sql .= ', ';

    $i++;
}

mysql_query($sql) or die("Ehh..error!?" . mysql_error());

Result

The value of $sql would be:

INSERT INTO orders_products (order_id, product_id, product_amount) VALUES (#, 3, 2), (#, 2, 1)

Upvotes: 0

GDmac
GDmac

Reputation: 880

You can store the array if you serialize it first. That way it wil be a string you can save.

For searching and statistics it could make sense to store each product in the cart on its own row in the table. But be careful to choose the unique id column in the table (if you have one). For instance, Can the cart have the same product twice in the cart (the same t-shirt, one in red and one in green).

Upvotes: 0

Related Questions