2

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 ;

Improve this question
7
  • What's the structure of reqactivity? Commented Oct 29, 2014 at 22:46
  • you are selecting only the position 0 in the array. Did you try do it with a cycle like a forand use it to do the insert for each value in the array? Commented Oct 29, 2014 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. Commented Oct 29, 2014 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? Commented Oct 29, 2014 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? Commented Oct 29, 2014 at 23:42

2 Answers 2

Reset to default
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.

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

7 Comments

Don't quote "NULL" unless you mean to actually insert the word "NULL".
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 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 just included the table structure
Seems like a perfect case to make a new table activities and store each activity as a row in this table. See stackoverflow.com/questions/3653462/…
|
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);
Improve this answer

6 Comments

ah, I'm too used to my library and forgot the native methods. Will edit
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.
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
It looks like he just want a comma delimited list in one of the columns.
the array should be inserted into different columns on one row
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.