Inserting an array of values into database

Csabbencs · November 2011

I have an array of values (not associative) that I'd like to insert to the database. First, I thought that this simple line of code will do this for me:
$this->SQL->Insert('MyTable', $Data);
But it didn't work.

I checked class.mysqldriver.php, function GetInsert and found what I know: when you have an associative array of FieldName => Value pairs, it works like a charm.
Quite a common case when you just have your values to be inserted and you don't want to specify column names like:
INSERT INTO tbl_name VALUES(myvalue1, myvalue2, ... , myvalueX);
I think the function should handle that.

How can I avoid to build an associative array of FieldName => Value pairs?
Please explain.

Tim · November 2011

@Csabbencs This is not how the database layer works, and we don't plan to add additional magic functionality like the type you're asking. That is for the Model or Controller to handle.

@x00 is correct: you should always build a proper columnname -> value list, at the very least for the sake of clarity.

That said, you can do that step programmatically, like so:

$DataFormatted = array();
$DataLength = sizeof($Data);
for ($i = 1; $i <= $DataLength; $i++) {<br />
   $DataFormatted["Mycolumn{$i}"] = $Data[$i];
}

$this->SQL->Insert("MyTable", $DataFormatted);

x00 · November 2011

why would you want to insert like this? it is a recipe for disaster, like the bad old days of SQL spaghetti.

the models are not quite ORM, but you want to specify what you want inserted in which column. Good practice. Also your code will make more sense.

Btw you can get as an associative array.

Csabbencs · November 2011

Imagine that you get a text file every day with 1000 decimal numbers. You need to save them to DB so that later on you can do some calculations based on them. Your DB table has 1000 colums. Why would you build an associative array with fieldnames Mycolumn1, Mycolumn2, ... Mycolumn1000 ?
Needless.

Csabbencs · November 2011

Sure, I can do that.

Howdy, Stranger!

Categories

In this Discussion

Inserting an array of values into database

Best Answer

Answers