php将未知数量的值传递给mysql准备好的语句

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 ;

Answer 1

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 activites. 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));

Cant leave execute empty. See PDO on the manual.

ALTERNATIVELY:

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.