CODEWITHSUNDEEP
phpmysqlarrays
Keez
Keez

Reputation: 65

php pass unknown number of values to mysql prepared statement

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 ;

Upvotes: 2

Views: 1021

Answers (2)

I wrestled a bear once.
I wrestled a bear once.

Reputation: 23399

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.

Upvotes: 2

aland
aland

Reputation: 2004

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

Upvotes: 2

Related Questions