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

    Join Date
    Nov 2010
    Posts
    19
    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. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,820
    Rep Power
    9646
    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
    19
    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. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,820
    Rep Power
    9646
    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
    19
    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,270
    Rep Power
    4193
    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.

    Comments on this post

    • mbamber1986 agrees : Amazing
    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
    19
    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
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    19
    Rep Power
    0
    Hi thanks for the response in this I managed to get the user input one to work how would I get the update one to work in the same. Format then I'd will be dragged over by the get superglobal same with the delete that will just be a simple get super global.
    Tia


    PS I'm always learning
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    19
    Rep Power
    0

    Wink Thanks to you Both


    Hi All you devs who have helped me after a bit of messing around i finally worked it out but
    i would like to thank you all for your support in this matter especially kicken as your solution did help me will display my fix to the problem i had so others who need it can also use it

    PHP Code:



    Class Test
    {

    public function 
    Create()
    {

      
    $host HOST;
            
    $db_name DBNAME;
            
    $db_user DBUSER;
            
    $db_pass DBPASS;

            
    $opt = [
                
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
                
    PDO::ATTR_EMULATE_PREPARES => false,
            ];


            
    $dsn "mysql:host=" $host ";dbname=" $db_name;
            
    $cnx = new PDO($dsn$db_user$db_pass$opt);

    // this code thanks to kicken has saved me alot of time

            
    $data get_object_vars($this);
            
    $columnList array_keys($data);
            
    $valueList array_values($data);
            
    $table "users";

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

    }

    function 
    update($id)
    {

      
    $host HOST;
            
    $db_name DBNAME;
            
    $db_user DBUSER;
            
    $db_pass DBPASS;

            
    $opt = [
                
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
                
    PDO::ATTR_EMULATE_PREPARES => false,
            ];


            
    $dsn "mysql:host=" $host ";dbname=" $db_name;
            
    $cnx = new PDO($dsn$db_user$db_pass$opt);


            
    $data get_object_vars($this);
            
    $table "users";

            foreach (
    $data as $key => $value) {
                
    $values[] = "{$key}""=""'{$value}'";
    }
                
    $sql "UPDATE users SET ";
              
    $sql .= join(',',$values);
                
    $sql .= " WHERE id='$id";

                
    $stmt $cnx->prepare($sql);
                
    $stmt->execute();

    // display the sql for error checking
            
    echo $sql;

            }
    }

    $user = new Test();
    $user->username "Martin";
    $user->password "james";
    // to create
    $user->create();
    //to Update

    $user->update($_GET['id'])





    i woiuld like to add i know that i have duplicated my code for the connection but this was for testing and display purposes and will eventually be linked to my original PDO settings


    once again thanks to you Both;

IMN logo majestic logo threadwatch logo seochat tools logo