How do i put an array in mysql?

Question

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"

Give each field its own column. That what databases are designed to work. — John Conde
– John Conde, Commented Oct 29, 2012 at 19:04
Session data (can be edited outside of your script) + string concatenation SQL query building = risk of SQL injection. — hakre
– hakre, Commented Oct 29, 2012 at 19:09
That's a very simple problem to solve. Use foreach to create the sql — MikeSW
– MikeSW, Commented Oct 29, 2012 at 19:10
foreach ($_SESSION['producten'] as $product) {. Inside foreach use $product['order_id'], $product['product_id'] and $product['aantal'] in you query. — air4x
– air4x, Commented Oct 29, 2012 at 19:12

Bill Karwin · Accepted Answer · 2012-10-29 19:30:26Z

1

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);
}

answered Oct 29, 2012 at 19:30

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jediah Over a year ago

Thanks, very useful to learn from. It's always good to learn new methods

Geoff Montee · Accepted Answer · 2012-10-29 19:34:01Z

1

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();
}

edited Oct 29, 2012 at 19:34

answered Oct 29, 2012 at 19:20

Geoff Montee

2,59715 silver badges14 bronze badges

2 Comments

jediah Over a year ago

Thanks, very useful for future projects! Will give credits in the script for using it. Do you have a site or email i can put in the credits?

Geoff Montee Over a year ago

@jediah I'd rather not put my email on the public web. You can just use my name if you'd like. It isn't a very common name.

Kermit · Accepted Answer · 2012-10-29 19:20:43Z

0

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)

edited Oct 29, 2012 at 19:20

answered Oct 29, 2012 at 19:15

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

3 Comments

jediah Over a year ago

Got any tips to remove the SQL injection vulnerability?

Geoff Montee Over a year ago

@jediah use prepared statements like the ones in my answer and Bill's.

Kermit Over a year ago

@jediah You can use mysqli_prepare. Read up on mysqli_ functions here.

GDmac · Accepted Answer · 2012-10-29 19:15:34Z

0

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).

answered Oct 29, 2012 at 19:15

GDmac

8801 gold badge9 silver badges28 bronze badges

Collectives™ on Stack Overflow

How do i put an array in mysql?

4 Answers 4

1 Comment

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related