469,304 Members | 2,192 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,304 developers. It's quick & easy.

updating mysql db with PDO php

anfetienne
424 256MB
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

Expand|Select|Wrap|Line Numbers
  1. // configuration
  2. $dbtype     = "sql"; 
  3. $dbhost     = "$hostname"; 
  4. $dbname     = "$database"; 
  5. $dbuser     = "$username"; 
  6. $dbpass     = "$password"; 
  7.  
  8. // database connection 
  9. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass); 
  10.  
  11. // query 
  12. $sql = "INSERT flashGallery (imageNo,tempID,imageLoc,thumbLoc,imageCap) VALUES (:imageNo,:tempID,:imageLoc,:thumbLoc,:imageCap)"; 
  13. $q = $conn->prepare($sql); 
  14.  
  15. foreach ($_POST['picT'] as $key => $value)
  16. {
  17.   $q->execute(array(':tempID'=>$random_digit,
  18.                     ':imageNo'=>$value,
  19.                       ':imageLoc'=> $_POST['imageT'][$key],
  20.                     ':thumbLoc'=> $_POST['thumbnailT'][$key], 
  21.                     ':imageCap'=> $_POST['captionT'][$key])); 
  22. }
  23.  
Aug 26 '09 #1
22 5121
Markus
6,050 Expert 4TB
Yes.

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

Expand|Select|Wrap|Line Numbers
  1. $sql = "UPDATE `tbl` SET `col1` = 'column 1' WHERE `user_id` = 3;";
  2.  
Mark.
Aug 26 '09 #2
anfetienne
424 256MB
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?
Aug 26 '09 #3
Markus
6,050 Expert 4TB
@anfetienne
Can you show me how you're doing it?
Aug 26 '09 #4
Dormilich
8,651 Expert Mod 8TB
@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.
Aug 26 '09 #5
anfetienne
424 256MB
using this code just inputs the last set of values into the db

Expand|Select|Wrap|Line Numbers
  1. // query 
  2. $sql = "UPDATE flashGallery SET imageNo = :imageNo, imageLoc = :imageLoc, thumbLoc = :thumbLoc, imageCap = :imageCap WHERE tempID='{$random_digit}'"; 
  3. $q = $conn->prepare($sql); 
  4.  
  5. foreach ($_POST['picT'] as $key => $value)
  6. {
  7.   $q->execute(array(':imageNo'=>$value,
  8.                       ':imageLoc'=> $_POST['imageT'][$key],
  9.                     ':thumbLoc'=> $_POST['thumbnailT'][$key], 
  10.                     ':imageCap'=> $_POST['captionT'][$key])); 
  11. }
  12.  
Aug 26 '09 #6
Dormilich
8,651 Expert Mod 8TB
@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!
Aug 26 '09 #7
anfetienne
424 256MB
i've changed it for WHERE tempID = :tempID and it still does the same thing
Aug 26 '09 #8
Dormilich
8,651 Expert Mod 8TB
do you pass a new tempID every time?
Aug 26 '09 #9
anfetienne
424 256MB
no, the tempID gets created at the start of the template process and its posted to all pages that filter off the 1st page
Aug 26 '09 #10
Dormilich
8,651 Expert Mod 8TB
then there is no need for the loop at all. no matter what you do, your query will affect only this one row.
Aug 26 '09 #11
anfetienne
424 256MB
the reason for the loop is because of the multiple rows that im inserting into the db....is there another way around this?
Aug 26 '09 #12
Dormilich
8,651 Expert Mod 8TB
you do not insert, you update.

if you indeed want to insert use
Expand|Select|Wrap|Line Numbers
  1. $sql = "INSERT INTO flashGallery …
Aug 26 '09 #13
anfetienne
424 256MB
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
Aug 26 '09 #14
Dormilich
8,651 Expert Mod 8TB
@anfetienne
nope, you exactly specify which row you want to update.

let me explain a bit
Expand|Select|Wrap|Line Numbers
  1. UPDATE table_name SET `value` = 1 WHERE `id` = 1
  2. UPDATE table_name SET `value` = 2 WHERE `id` = 1
  3. 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?
Aug 26 '09 #15
anfetienne
424 256MB
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
Aug 26 '09 #16
anfetienne
424 256MB
it's great! works like a charm
Aug 26 '09 #17
Dormilich
8,651 Expert Mod 8TB
@anfetienne
this sounds as if I’ve missed the point… what works like a charm?
Aug 26 '09 #18
anfetienne
424 256MB
@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.
Aug 26 '09 #19
Dormilich
8,651 Expert Mod 8TB
hehe, nothing beats a good example ;)





(despite the logic behind it)
Aug 26 '09 #20
anfetienne
424 256MB
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?
Aug 26 '09 #21
Dormilich
8,651 Expert Mod 8TB
gets problematic if you delete a row later. if your id row builds on AUTOINCREMENT you can use SQL’s LAST_INSERT_ID() function
Aug 26 '09 #22
anfetienne
424 256MB
thanks i will read up on it, test then let you know my results
Aug 26 '09 #23

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by kaptain kernel | last post: by
11 posts views Thread by Jason | last post: by
reply views Thread by Jeremiah Jacks | last post: by
reply views Thread by fldmark | last post: by
33 posts views Thread by bill | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.