1

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.

Improve this question
7
  • 2
    time to invest in an ORM Commented Dec 12, 2016 at 6:01
  • 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 Commented Dec 12, 2016 at 6:01
  • 2
    have a look at this php.net/manual/en/function.serialize.php Commented Dec 12, 2016 at 6: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. Commented Dec 12, 2016 at 6:06
  • fuggly, dont... Commented Dec 12, 2016 at 6:14

1 Answer 1

Reset to default
2

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.