How to write query for a php array?

Question

I have the following php array that gets all values from a form full of radios and check-boxes.

foreach(array('buss_type','anotherfield','anotherfield','...etc') as $index)
{
    if (isset($this->request->post[$index])) {
        $this->data[$index] = $this->request->post[$index];
    } else { 
        $this->data[$index] = NULL; 
    }
}

Now, I am wondering how to write the query to send those values to my database, to a new table I just created (retailer). Every radio/checkform value has its column in my retailer table, how do I write the query so that all the values contained in $index go to their specific column.

The following is an example of how my other queries look like...

public function addCustomer($data) {
    //this is the one I am trying to write, and this one works, 
    //but I'd have to add every single checkbox/radio name to the 
    //query, and I have 30!
    $this->db->query("INSERT INTO " . DB_PREFIX . "retailer SET buss_t = '" . 
            (isset($data['buss_t']) ? (int)$data['buss_t'] : 0) . 
            "', store_sft = '" . 
            (isset($data['store_sft']) ? (int)$data['store_sft'] : 0) . 
        "'");
    //Ends Here
    $this->db->query("INSERT INTO " . DB_PREFIX . "customer SET store_id = '" . 
            (int)$this->config->get('config_store_id') . "', firstname = '" . 
            $this->db->escape($data['firstname']) . "', lastname = '" . 
            $this->db->escape($data['lastname']) . "', email = '" . 
            $this->db->escape($data['email']) . "', telephone = '" . 
            $this->db->escape($data['telephone']) . "', fax = '" . 
            $this->db->escape($data['fax']) . "', password = '" . 
            $this->db->escape(md5($data['password'])) . "', newsletter = '" . 
            (isset($data['newsletter']) ? (int)$data['newsletter'] : 0) . 
            "', customer_group_id = '" . 
            (int)$this->config->get('config_customer_group_id') . 
            "', status = '1', date_added = NOW()");

Thanks a lot for any insight you can provide.

In the future, please look at the output of your code and think, "Does this look nicely formatted?" — jprofitt
– jprofitt, Commented Mar 17, 2012 at 0:54
@jprofitt Not sure what you mean? If you have a recommendation, I'd be glad to take it into account but please be specific. Thanks. — codekmarv
– codekmarv, Commented Mar 17, 2012 at 1:25
You had most of the code on one line that you had to scroll way over to read, which made it hard to understand. I edited it to be more readable on the site for you. — jprofitt
– jprofitt, Commented Mar 17, 2012 at 1:31
Ah ok, Thanks, will take it into account for next time. Thanks. — codekmarv
– codekmarv, Commented Mar 17, 2012 at 2:57

blejzz · Accepted Answer · 2012-03-17 00:56:09Z

1

the best way would be to create a function that accepts an array and table name as an argument and executes a insert query.

function insertArray($table, $array)
{
  $keys =""; $values = "";
  foreach($table as $k=>$v)
  { 
      $keys.=($keys != "" ? ",":"").$k:
      $values .=($values != "" ? "," :"")."'".$v."'";
  }
  $this->db->query("INSERT INTO ".$table." (".$keys.") VALUES (".$values.");
}

The array has to be structured like this:

 array("db_attribute1"=>"value1","db_attribute2"=>"value2");

answered Mar 17, 2012 at 0:56

blejzz

3,3575 gold badges43 silver badges61 bronze badges

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

Comments

QuantumBlack · Accepted Answer · 2012-03-17 01:00:13Z

1

Store the column names and column values in separate arrays and use implode() to generate a comma-separated list of columns and values

$values = array();
$columns = array('buss_type','anotherfield','anotherfield','...etc');
foreach($columns as $index)
{
    if (isset($this->request->post[$index]))
    {
        $this->data[$index] = $this->request->post[$index];
        $values[] = $this->db->escape($this->request->post[$index]);
    }
    else
    { 
        $this->data[$index] = NULL;
        $values[] = "''";
    }
}



$this->db->query("INSERT INTO table_name (" . implode(",", $columns) . ") VALUES (" . implode(",", $values) . ");

answered Mar 17, 2012 at 1:00

QuantumBlack

1,5561 gold badge13 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to write query for a php array?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related