updating mysql db with PDO php

[anfetienne]

is it possible to run an UPDATE sql to update multiple rows in the same way the i insert data into multiple rows? this is my code to insert

// configuration
$dbtype     = "sql"; 
$dbhost     = "$hostname"; 
$dbname     = "$database"; 
$dbuser     = "$username"; 
$dbpass     = "$password"; 
 
// database connection 
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass); 
 
// query 
$sql = "INSERT flashGallery (imageNo,tempID,imageLoc,thumbLoc,imageCap) VALUES (:imageNo,:tempID,:imageLoc,:thumbLoc,:imageCap)"; 
$q = $conn->prepare($sql); 
 
foreach ($_POST['picT'] as $key => $value)
{
  $q->execute(array(':tempID'=>$random_digit,
                    ':imageNo'=>$value,
                      ':imageLoc'=> $_POST['imageT'][$key],
                    ':thumbLoc'=> $_POST['thumbnailT'][$key], 
                    ':imageCap'=> $_POST['captionT'][$key])); 
}
 

[Markus]

Yes.

For instance, the following will update all rows that have the user_id 3.

$sql = "UPDATE `tbl` SET `col1` = 'column 1' WHERE `user_id` = 3;";
 

Mark.

[anfetienne]

i've tried as much and all i got was an error saying PDO finds it invalid and when i use single quotes for the values to change it just enters :value into the db.

do i have to do it exactly as you wrote it with the backquotes as well?

[Markus]

@anfetienne
Can you show me how you're doing it?

[Dormilich]

@anfetienne
that’s reasonable, because it is treated as string. at least there seems nothing wrong with the SQL code (I use it the same way).

what says the PDO exception exactly?

although it is not necessary, there are the PDOStatement::bindValue() and PDOStatement::bindParam() methods, which give you control over the data type to pass.

@anfetienne
those backticks are the standard SQL quotation chars.

[anfetienne]

using this code just inputs the last set of values into the db

// query 
$sql = "UPDATE flashGallery SET imageNo = :imageNo, imageLoc = :imageLoc, thumbLoc = :thumbLoc, imageCap = :imageCap WHERE tempID='{$random_digit}'"; 
$q = $conn->prepare($sql); 
 
foreach ($_POST['picT'] as $key => $value)
{
  $q->execute(array(':imageNo'=>$value,
                      ':imageLoc'=> $_POST['imageT'][$key],
                    ':thumbLoc'=> $_POST['thumbnailT'][$key], 
                    ':imageCap'=> $_POST['captionT'][$key])); 
}
 

[Dormilich]

@anfetienne
because the WHERE condition doesn’t change. you repeatedly overwrite one row.

@anfetienne
that doesn’t make sense. mind that you use a Prepared Statement!

[anfetienne]

i've changed it for WHERE tempID = :tempID and it still does the same thing

[Dormilich]

do you pass a new tempID every time?

[anfetienne]

no, the tempID gets created at the start of the template process and its posted to all pages that filter off the 1st page

[Dormilich]

then there is no need for the loop at all. no matter what you do, your query will affect only this one row.

[anfetienne]

the reason for the loop is because of the multiple rows that im inserting into the db....is there another way around this?

[Dormilich]

you do not insert, you update.

if you indeed want to insert use

$sql = "INSERT INTO flashGallery …

[anfetienne]

update is what im trying to do but it is only using the last set of values in my arrays the loop works fine with INSERT and INSERT INTO but as soon as i use UPDATE it changes all rows to the very last value.....my guess is because i'm not specifying the rows i want to update just the columns so its not cycling through...is this possible? i reckon to delete and re-insert is my best option

@anfetienne

[Dormilich]

@anfetienne
nope, you exactly specify which row you want to update.

let me explain a bit

UPDATE table_name SET `value` = 1 WHERE `id` = 1
UPDATE table_name SET `value` = 2 WHERE `id` = 1
UPDATE table_name SET `value` = 3 WHERE `id` = 1

now the big question, what’s the value for "value" with "id = 1", how many queries did it do and how many rows were affected?

[anfetienne]

as you can see on my code i have an array called imageNo. id would be imageNo.

thanks for the tip i will test this now

[anfetienne]

it's great! works like a charm

[Dormilich]

@anfetienne
this sounds as if I’ve missed the point… what works like a charm?

[anfetienne]

@Dormilich
you gave the the clue i needed to get my loop and arrays to work with updating each different row. I was going about it wrong trying to update each row using the tempID which is just a static number (eg 123456). I should of tried to update using the imageNO (eg which goes 1,2,3,4,5,6).

all i did was swap the WHERE tempID = :tempID to WHERE id = :imageNo and each and every single row to do with the current tempID updates as it should.

[Dormilich]

hehe, nothing beats a good example ;)





(despite the logic behind it)

[anfetienne]

lol one question though.....how do i select the last row in a table? with this i wont generate a new imageNo starting from 1 i will just use the last number and carry on from there....keeps it nice and simple

would i use count?

[Dormilich]

gets problematic if you delete a row later. if your id row builds on AUTOINCREMENT you can use SQL’s LAST_INSERT_ID() function

[anfetienne]

thanks i will read up on it, test then let you know my results