How to dump PHP object into an SQL Table?

Question

I'm wondering if there is a quick way to do the following:

I have a PHP object with multiple attributes. For example

$person;
$person->height = 165;
$person->name = 'john';

I would like to dump this into an SQLite MySQL DB while creating each column automatically from the attribute names of the PHP object. So for example, if I were to dump the above object into mySQL, it would look something like this:

Table : people
Name(Column, VARCHAR)  -  "John"
Height(Column, INT)  -  165

The reason I am asking this is because the number of attributes is growing constantly, and having to create and manage the table columns manually is a lot of work. I am wondering if there is an automated way of doing this.

sqlite version? want to know if it supports json datatype. Because you can convert php object to json, and store json data to sqlite. Never tried, just a guess. [sqlite json1](https://sqlite.org/json1.html) — Jigar, Dec 12 '16 at 06:01
have a look at this http://php.net/manual/en/function.serialize.php — Sayantan Das, Dec 12 '16 at 06:02
Sound like MongoDB (or some other document store) would suit you better. Altering a RDBMS table scheme automatically, like you ask, seems like a bad idea, since you don't just need to know the column name, but also data type, size etc. — M. Eriksson, Dec 12 '16 at 06:06
Having the number of columns changing that often sounds nasty (and you will hit the max number of columns pretty soon). Probably better to have a table listing attributes, and another table which has columns for the attribute id, person id and the value. Then no need to change the tables as attributes are added, merely add rows to existing tables. — Kickstart, Dec 12 '16 at 11:52

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

First you need to convert object into array and then you can iterate through it and can create table and insert values in it.

Something like below:

Step 1: Convert object to array

Step 2: Get keys(fields) and values out of array

Step 3: Generate sql queries

    <?php
    //Step 1: convert object to array
    //$persion =  (array) $yourObject;
    
    //Step 2: get keys(fields) and values out of array
    $person = array(
        "height" => "165",
        "name" => "john",
        "age" => "23"
    );
    
    function data_type($val) {
        if(is_numeric($val)) { 
            return "int"; 
        } 
        else {
            return "varchar(15)";
        }   
    }
    
    //Step 3: sql query, only including sql query
    function create_table($person) {
        $create = "CREATE TABLE IF NOT EXISTS people";
        $ctr = 0;
        foreach($person as $key => $value) {
            if($ctr == 0) {
                $field_query = $key." ".data_type($value);
            } else {
                $field_query .= ", ".$key." ".data_type($value);
            }
            $ctr++;
        }
        echo $create .= " (".$field_query.")";
        echo "<br/>";
    }
    create_table($person);
    
    function insert_table($person) {
        $ctr = 0;
        foreach($person as $key => $value) {
            if($ctr == 0) {
                $field_query = $key;
                $value_query = $value;
            } else {
                $field_query .= ", ".$key;
                $value_query .= ", ".$value;
            }
            $ctr++;
        }
        echo $insert = "INSERT INTO people"." (".$field_query.") VALUES (".$value_query.")";
    }
    insert_table($person);
    
    ?>

Hope this will help you in some way(y).

How to dump PHP object into an SQL Table?

1 Answers1

Step 1: Convert object to array

Step 2: Get keys(fields) and values out of array

Step 3: Generate sql queries