SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Feb 2014
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Passing array into sql

    Hello, I wanted to ask if there is a way to pass arrays into SQL queries for instance having something under this shape:
    PHP Code:
    $form = array(
    first_name=>$first_name,
    last_name=>$last_name,
    gender=>$gender); 
    and so on passed to an insert statement.What I want exactly to do is somehow to be able to use the column,value and table name of the insert into sql statement as parameters of a php function so i can use it in my php code and thought to do so with arrays although I do not know if this is the way to solve my issue I'd like to be helped figuring this out. Thank you for your attention to this matter!

  2. #2
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    52 Post(s)
    Tagged
    1 Thread(s)
    Which extension are you using to execute your queries, mysql, mysqli, or PDO?
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  3. #3
    SitePoint Enthusiast doddsey_65's Avatar
    Join Date
    Sep 2010
    Location
    North East UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are using something like Doctrine it would be as simple as:

    Code:
    $db->insert('table', $array)
    However if you use something like PDO or mysql(i) you would need to loop through the array to build the sql.

    Code:
    foreach ($array as $column => $value)
    {
        $columns[] = $colums;
        $values[] = $value;
    }
    
    'INSERT INTO table (' . implode(', ', $columns) . ')  VALUES (' . implode(', ', $values) . ')'
    If you go for the latter you will need to wrap non integer values in quotes.
    Test and help out with a new
    100% open source forum package: A Simple Forum
    Check the A Simple forum Github Page
    Visit my Github profile to see what I work on.

  4. #4
    SitePoint Member
    Join Date
    Feb 2014
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by doddsey_65 View Post
    If you are using something like Doctrine it would be as simple as:

    Code:
    $db->insert('table', $array)
    However if you use something like PDO or mysql(i) you would need to loop through the array to build the sql.

    Code:
    foreach ($array as $column => $value)
    {
        $columns[] = $colums;
        $values[] = $value;
    }
    
    'INSERT INTO table (' . implode(', ', $columns) . ')  VALUES (' . implode(', ', $values) . ')'
    If you go for the latter you will need to wrap non integer values in quotes.
    Why do I need to loop through it, and yes I am using mysqli to answer the previous questions

  5. #5
    SitePoint Enthusiast doddsey_65's Avatar
    Join Date
    Sep 2010
    Location
    North East UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You wouldn't :P That is just me using a convoluted method. You could just use array_keys and array_values

    Code:
    'INSERT INTO table (' . implode(', ', array_keys($form)) . ') VALUES (' . implode(', ', array_values($form)) . ')'
    Test and help out with a new
    100% open source forum package: A Simple Forum
    Check the A Simple forum Github Page
    Visit my Github profile to see what I work on.

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,904
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Using prepare:

    PHP Code:
    // assume: $array assoc array of data to be inserter
    // assume: $db instance of PDO
    $placeholders substr(str_repeat('?,'count($array)), 0, -1);
    $stmt $db->prepare('INSERT INTO ('.implode(','array_keys($array)).') VALUES ('.$placeholders.')');
    $stmt->execute(array_values($array)); 
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •