php

mysql

loops

pdo

How to insert an array into a single MySQL Prepared statement w/ PHP and PDO

Viewed 68 times

During an online enrollment, a customer may select a number of programs which they choose to enroll for. These programs are three digit integers and are stored in an array.

For example:

I want to enroll in programid 155, 165, 175, and 185.

My array is set up as simple as:

$data = array();

$data[] = 155;

$data[] = 165;

$data[] = 175;

$data[] = 185;

When it comes time to insert this information into the associated table, I also include additional elements from the other part of the enrollment:

For example, if I were doing a SINGLE program insert statement, it would look as follows:

$stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()");
$stmt->execute(array($memberid, 155));

I would normally create a simple loop for the array above which would call multiple instances of the sql statement and execute such as:

for($j = 0; $j < (count($data)-1); $j++) {
   $stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()");
   $stmt->execute(array($memberid, $data[$j]));
}

I do realize the code above is invalid ( $data[$j] ) but looking for the right way to do the call.

I have also been told before that building a single dynamic sql statement is overall better than multiple calls like above. My first pass would be something like:

$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.$memberid.'", "'.$row[$j].'", NOW()")';
}
mysql_real_query('INSERT INTO table (memberid, programid) VALUES '.implode(',', $sql));

but with PDO I am not quite sure how this works, especially with placeholders (?).

Any suggestions?

Answers

You could build the query programatically...:

$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
foreach ($data as $row) {
    $insertQuery[] = '(?, ?)';
    $insertData[] = $memberid;
    $insertData[] = $row;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
}

Wednesday, August 24, 2022

clocnge-acb5f970278c

There's a conflict between the PHP that is used by Apache and the PHP that is linked to the command line. (It happens more often that it should to be honest).

What is typically done is:

which php

This tells you which php will be expecuted when running in the command line. e.g. /usr/bin/php

mv /usr/bin/php /usr/bin/php.old

Then link or copy the correct PHP version to an executable path:

ln -s /path/to/php/bin/php /usr/bin/php

or the following should also work.

cp /path/to/php/bin/php /usr/bin/php

Also suggested if you want to be able to manually install mods:

ln -s /path/to/php/bin/phpize /usr/bin/phpize
ln -s /path/to/php/bin/php-config /usr/bin/php-config

This way your CLI will match your webserver.

Update:

If as noted in this answer if you are using Ubuntu with multiple alternative installations of PHP you can do:

sudo update-alternatives --set php /usr/bin/php<version>
sudo update-alternatives --set phar /usr/bin/phar<version>
sudo update-alternatives --set phar.phar /usr/bin/phar.phar<version> 
sudo update-alternatives --set phpize /usr/bin/phpize<version> 
sudo update-alternatives --set php-config /usr/bin/php-config<version>

Thursday, December 22, 2022

vittorio.milazzo-a15cb9a6991c

You seem to be looking for pass-by-reference, to do that make your function look this way (note the ampersand):

function foo(&$array)
{
    $array[3]=$array[0]+$array[1]+$array[2];
}

Alternately, you can assign the return value of the function to a variable:

function foo($array)
{
    $array[3]=$array[0]+$array[1]+$array[2];
    return $array;
}

$waffles = foo($waffles)

Wednesday, August 10, 2022

mr-sk

Assign

$_SESSION['question'] = $que;

print_r($_SESSION['question'][0]); will give you first question.

Friday, November 18, 2022

florian_ledermann

This got it done. Thanks to Marc B for his thoughts:

$e = 0;

while($e<$num1){
$units = 0;
$r = 0;
$SO_Line_Item=mysql_result($result1,$e,"SO_Line_Item");

    foreach ($Boxes[$e] as $a => $b)  
    {

        $zzz[] = $Boxes[$e][$r];

$ce = count($Boxes[$e]);        

    $r++; 
    }
//end inner foreach

$products = implode(',', array_fill(0,$ce, '?'));

$db = new PDO('mysql:host=192.168.1.197 ;dbname=Tracking', $dbuser,$dbpass);
$stmt = $db->prepare("SELECT Box_Num,Timestamp,E3_SN,Assy_Status FROM Current_Box WHERE Box_Num IN( $products )");
$stmt->execute($zzz);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
unset($zzz);
$e++;
}

Monday, December 12, 2022

zamnuts

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

loops

pdo

How to insert an array into a single MySQL Prepared statement w/ PHP and PDO

Answers

php,mysql,loops,pdo