#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    17
    Rep Power
    0

    trying to create a multi purpose crud


    Hi Fellow dev i am currently in the process of trying to make a oop based crud and im trying to not repeat my code and make it one code does all

    i need some assistance of guidance on how i can go around making the create and update process without having to keep providing an sql
    command i was something like laravel as follows

    $u = new user();
    $u->username = "martin";
    $u->fullname = "martin bloggs";
    $u->create();

    the current method i have is like so

    $myarray =
    [
    "username" => $u->username,
    "fullname" => "martin Bamber"
    ];


    // the sql

    public function Create($values)
    {
    global $pdo;
    $sql = 'INSERT INTO '.$this->table.'('.implode(",",array_keys($myarray)).') VALUES('.implode(",",array_values($myarray)).')
    $stmt = $this->find_by_sql($sql);
    $stmt->execute();
    }


    if you can assist on this method could you help wioth the update process as well

    thanks
  2. #2
  3. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,492
    Rep Power
    9645
    There has to be SQL involved at some point so you can't really get away from providing a query to run in some form or another.

    First you need to learn about prepared statements. Then you need to find a way to create a prepared statement that looks like
    Code:
    INSERT INTO table (`username`, `fullname`) VALUES (:username, :fullname)
    Those column and placeholder names come from the $myarray so that will work automatically. Prepare that statement, then bind the $myarray parameters like
    PHP Code:
    $stmt->bindParam(":username"$myarray["username"]); 
    Obviously you won't be writing "username" into the code there but instead getting it from $myarray. With a loop.

    When the statement is prepared and the parameters are bound you can execute it and it will perform the INSERT. You can adapt the technique for every other type of query too.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    17
    Rep Power
    0
    thannks for your advice is there anyway i would be able to do in the format of

    $user->username = "Martin";
    $user->fullname = "martin Blogs"
    $user->save();

    then push then to the insert command without having to either bind or or push it in an array im trying to make it so one sql insert works on all classes

    thanks for your help
  6. #4
  7. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,492
    Rep Power
    9645
    Only if you prepare the INSERT statement ahead of time. Which is wasteful. So no.

    Whether you use an array of values or object properties, it's all the same in the end: prepare the statement, bind the values, and execute.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    17
    Rep Power
    0
    Thanks for the support i have eventually done the following method

    i have a method in a class for inserting which has the sql command

    $sql = "insert into (username,fullname) VALUES(':username',':fullname');
    $stmt = $this->find_by_sql($sql);
    $stmt->bindparams(":username",$this->username,PDO::PARAM_STR);
    $stmt->bindparams(":fullname",$this->fullname,PDO::PARAM_STR);
    $strmt->execute();


    i haven then set the index page to be as follows

    $u = new user();
    $u->username = "martin";
    $u->fullname = "my fullname";
    $u->create();

    this has worked althought not sure if it is logical
  10. #6
  11. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,136
    Rep Power
    4103
    If you name your properties after your columns, then you could make a trait that does your database operations and just generates the SQL based on the current object.

    Something like this:
    PHP Code:
    <?php

    trait DatabaseOperations 
    {
        public function 
    create(\PDO $db){
            
    $data get_object_vars($this);
            
    $columnList array_keys($data);
            
    $valueList array_values($data);
            
    $table get_class($this);

            
    $sql '
            INSERT INTO '
    .$table.' ('.implode(','$columnList).')
            VALUES ('
    .implode(','array_fill(0count($valueList), '?')).')
            '
    ;

            
    $stmt $db->prepare($sql);
            
    $stmt->execute($valueList);
        }
    }


    class 
    User {
        use 
    DatabaseOperations;
        public 
    $username;
        public 
    $fullname;
    }

    $db = new \PDO('...');
    $u = new User();
    $u->username 'Kicken';
    $u->fullname 'Keith';
    $u->create($db);
    You'd likely want to have some way of making your column / table definitions not dependent on the class properties / name though so you can override them if needed, such as to exclude certain properties or use table prefixes for example. For updating/deleting you'd need a way to know which property is your primary key so you can use it in a where clause.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    17
    Rep Power
    0
    thanks Kicken your idea is what im looking for however when i displeyed the values came up with the id and the table name under the array key and values is there any way not to display these

    thanks i can probably do it with str_replace but then again that makes it limited to one table name

    thanks in advance

IMN logo majestic logo threadwatch logo seochat tools logo