PHP: Unserialize Data That Has Been Serialized & Inserted Into A Mysql Database & Echo To A Table In PHP

Question

I have managed to serialize data (an Array) and insert it into a column in a database. I'd like to now SELECT that column and turn it back into and array and list each element of the array on a separate row.

what i am struggling with:

struggling with the array that i have unserialized. The reason i am Serializing in the first place is i am working with a multi-dimensional array that can have 1 or many items in an order.

once i have INSERTED the serialized data into the Mysql Database, the information looks like the following:

Example 1 - [One item in the order with a Quantity of 1].

 a:1:{i:7354028228659;a:1:{s:8:"quantity";i:1;}}

I'd like to unserialize it and populate it in a PHP table with the structure below.

|   Varaint_id  | Quantity | 
|---------------|----------|
| 7354028228659 |    1     |         
|---------------|----------|

If there is more than one item in the order, i'd like to achieve the same layout.

Example 2 - [3 items in the order each with a Quantity of 1].

a:3:{i:6836781613107;a:1:{s:8:"quantity";i:2;}i:6737290241;a:1:{s:8:"quantity";i:1;}i:49366524679;a:1:{s:8:"quantity";i:1;}}

The following below is a representation on the above, three items in the order each having 1 unit on order.

----------------------------
|   Varaint_id  | Quantity | 
----------------|-----------
| 6836781613107 |    1     |         
|---------------|----------|
| 6737290241    |    1     |         
|---------------|----------|
| 49366524679   |    1     |         
|---------------|----------|

What i'm currently struggling with is turning the string back into an array and then displaying it in a table using php.

Current Methodology:

Step 1 - Run SELECT query to the Database:
Step 2 - Fetch $stmt & Unserialize column 'items' from database. NOTE: I am struggling here!
Step 3 - Echo results into a table.

//1. Running a SELECT query to the Database.
$stmt = $db->query("SELECT items, order_number, cust_fname
                    From orders
                    WHERE '".$order_number."' = order_number");

//2. Fetch $stmt & Unserialize column 'items' from database.
while($row = $stmt->fetch()){

    $results = unserialize($row['items']);

//3. Echo results into a table. 
    echo "<tr>
        <td>" . $results . "</td>
        <td>" . $results . "</td> 
        </tr>";
 };

I'm not sure how to loop through the array and Unserialize the data and then echo just the variant_id in the one column and the quantity in the other column. Any help would be greatly appreciated.

Answer 1

as mensionned by @RiggsFolly , you can consume your array like any other array.

//1. Running a SELECT query to the Database.
// This other approch to query is not sensitive to SQL injection.
 $stmt = $db->prepare("SELECT items, order_number, cust_fname
    From orders
    WHERE order_number = :order_number
 ");

 $stmt->execute([
    ':order_number' => $order_number
 ]);

//2. Fetch $stmt & Unserialize column 'items' from database.
 while($row = $stmt->fetch()){
    $results = unserialize($row['items']);
    //3. Echo results into a table. 
    echo '<tr>';
    foreach ($results as $varaint_id => $result) {
        echo '<td>'.$result['quantity'].</td>
    }
    echo '</tr>';
} 

I know is not the actual topic, but let me point dangerous SQL injection issue on your code.

your code :

$db->query("SELECT items, order_number, cust_fname
    From orders
    WHERE '".$order_number."' = order_number
     ");

is sensitive to attack who can looks like :

$order_number = "1' --";
$myQuery = "SELECT items, order_number, cust_fname
        From orders
        WHERE '".$order_number."' = order_number
         "

// Will result 
SELECT items, order_number, cust_fname
        From orders
        WHERE '1'--' = order_number'

notice -- is one way to comment rest of the line in MySQL, this query will output all row inside orders.

By PDO::prepare and PDO::execute you avoid this kind of attack.

More information