472,333 Members | 1,132 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,333 software developers and data experts.

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 5277
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,658 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,658 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,658 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,658 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,658 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,658 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,658 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,658 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,658 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

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

Similar topics

2
by: kaptain kernel | last post by:
how does one go about updating a substring in mysql e.g. I want to change all occurences of Gmbh to GMBH within a field string. BMW gmbh BMW...
3
by: | last post by:
Hello, Sorry to ask what is probably a simple answer, but I am having problems updating a table/database from a PHP/ PHTML file. I can Read From...
11
by: Jason | last post by:
Let's say I have an html form with 20 or 30 fields in it. The form submits the fields via POST to a php page which updates a table in a database...
0
by: Jeremiah Jacks | last post by:
I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using = the pre-compiled binaries. I have a database with INNODB tables. When...
0
by: Dan Jones | last post by:
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying...
0
by: fldmark | last post by:
Hi, I have an SQL Server database sitting on IIS with a booking system that holds info on customers, training courses etc and a callback log .. My...
33
by: bill | last post by:
In an application I am writing the user can define a series of steps to be followed. I save them in a sql database using the field "order" (a...
2
by: julie18881 | last post by:
I may be being really stupid here, i have spent the last 3 hours looking round your site and some other for answers to my problem, but have not had...
6
by: yenfa | last post by:
I've done a simple PHP-thing which adds data into my mySQL database, using that "PHP $_POST" thing, now I want to do editing that data through PHP...
9
by: Steve67 | last post by:
Need a little assistance with a VBA code for updating tables through a form. I have a form with two text boxes and a command button. One text box is...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.