php pass unknown number of values to mysql prepared statement

Question

I've been googling for the past few hours for a solution but nothing that fits my need. These insert array with keys works if the array has a key that matches the database columns, insert array this works if the columns matches the values (bind each column to a value) and others that are similar but can't find anything that works for my situation.

I've an array that's posted through a jquery multiple select option, it's then stored in a $eng and passed to a function.

Here's the result of a var_dump $eng (the array).

{ [0]=> array(3) { [0]=> string(5) "Games" 1=> string(5) "Music" 2=> string(4) "Walk" } }

The array can have from 1 value to 5. All depending on what the user selects. I will like to insert the values in a database.

Here's my code so far, it works if the array count matches my table columns, otherwise I get an error Insert value list does not match column list I need any recommendation to be in a prepared statement for obvious reason, but I just can't figure it out.

public function addActivity($eng, $reqid)
   {

    $act = implode("','",array_values($eng[0]));    

    $query  = $this->dbh->prepare("INSERT INTO reqactivity VALUES ('NULL','$reqid','$act')");
            $query->execute();

            var_dump($eng);
   }

Here's the table structure

CREATE TABLE IF NOT EXISTS reqactivity (
id int(12) NOT NULL AUTO_INCREMENT,
reqid int(12) NOT NULL,
act1 varchar(15) NOT NULL,
act2 varchar(15) NOT NULL,
act3 varchar(15) NOT NULL,
act4 varchar(15) NOT NULL,
act5 varchar(15) NOT NULL,
PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

you are selecting only the position 0 in the array. Did you try do it with a cycle like a `for`and use it to do the insert for each value in the array? — Fabio Cardoso, Oct 29 '14 at 22:53
Would that be in the same row or different row foreach value in the array? I want to create just one row and the array goes into a different column on the row. — Keez, Oct 29 '14 at 23:29
@keez How do you expect that to work? if there are ten values in the array and only 5 act columns, where do you plan to put the other 5 activities? — I wrestled a bear once., Oct 29 '14 at 23:31
@Adelphia I was going to add more columns. I know the activity list will grow and as new activities are added new columns are created. Are there any down side of storing all activities in one column, comma separated, knowing that it will grow? — Keez, Oct 29 '14 at 23:42
@keez No, there aren't any downsides to doing it that way. And with a variable number of indexes that's really the only way you *can* do it. — I wrestled a bear once., Oct 29 '14 at 23:44
But.. If you can make sure that the array always has the same number of indexes as there are columns, then that's ok too. — I wrestled a bear once., Oct 29 '14 at 23:45

aland · Answer 1 · 2014-10-29T23:07:16.703

2

You should be using parameters (named is possible but I just use ?) for the prepare statement. I'll assume PDO - http://php.net/manual/en/pdo.prepare.php

$values = array_merge(array('null', $reqid), $eng[0]);
$placers = array_fill(0, count($values), '?');
$query = $this->dbh->prepare("INSERT INTO reqactivity VALUES (".implode(',', $placers.")");
$query->execute($values);

edited Oct 29 '14 at 23:07

answered Oct 29 '14 at 22:54

aland

1,824
2
26
43

ah, I'm too used to my library and forgot the native methods. Will edit – aland Oct 29 '14 at 23:06
That's better syntax, but even still. He's talking about an unknown number of fields. You're basically just throwing data in cells without any regaurd to whet they are. His original statement implied 3 columns. – I wrestled a bear once. Oct 29 '14 at 23:09
This is why I asked for the table structure of reqactivity - he mentioned that up to 10 values can be selected by the user so the table may have structure of (pk, fk, field1,field2,field3..field10). If the 10 fields have different names, then for sure named parameters (your answer) would be better – aland Oct 29 '14 at 23:15
It looks like he just want a comma delimited list in one of the columns. – I wrestled a bear once. Oct 29 '14 at 23:17
the array should be inserted into different columns on one row – Keez Oct 29 '14 at 23:27
I didn't use this but I up-voted it because I think it's a good solution if I can predict the number of values. – Keez Oct 29 '14 at 23:54

score 2 · Accepted Answer · edited Sep 28 '19 at 07:27

2

Scrap that table.

If you have phpMyAdmin, open the table, go to operations, then drop table. Now do this instead:

CREATE TABLE IF NOT EXISTS reqactivity (
id int(12) NOT NULL AUTO_INCREMENT,
reqid int(12) NOT NULL,
activities varchar(250) NOT NULL
PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

Then use this to insert your activities. They will all be in one column separated by a comma. You can't just cram any old data into any old column and hope that there will be enough or not too many.

$query = $this->dbh->prepare("INSERT INTO reqactivity VALUES (NULL, :rid, :act)");
$query->execute(array(":rid"=>$reqid, ":act"=>$act));

Can't leave execute empty. See PDO on the manual.

Alternative approach

If you want to make this work without changing your table you need to somehow make sure that that array always has exactly 5 values. no more, no less. If you can do that then change your query the way @aland put it in his answer.

edited Sep 28 '19 at 07:27

halfer

19,824
17
99
186

answered Oct 29 '14 at 23:03

I wrestled a bear once.

22,983
19
69
116

1

Don't quote "NULL" unless you mean to actually insert the word "NULL". – I wrestled a bear once. Oct 29 '14 at 23:15
I am getting the same error with this. SQLSTATE[21S01]:`Insert value list does not match column list: 1136 Column count doesn't match value count at row 1` – Keez Oct 29 '14 at 23:16
@keez Without the structure of your table to look at I can't give you a query that will work. How many columns (fields) are in your table and what are they called? – I wrestled a bear once. Oct 29 '14 at 23:19
I just included the table structure – Keez Oct 29 '14 at 23:25
I am trying this now. Just thinking if there are any downside to saving everything in one column knowing that activities could grow. – Keez Oct 29 '14 at 23:43
Why would there be downsides? just make sure that there are enough charachters alloted to that column. You actually might want to chage the `varchar(15)` part to `varchar(150)` or something. – I wrestled a bear once. Oct 29 '14 at 23:46
1

Seems like a perfect case to make a new table `activities` and store each activity as a row in this table. See http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – aland Oct 30 '14 at 18:56

php pass unknown number of values to mysql prepared statement

2 Answers2

Alternative approach