Updating multiple records

[Al Moodie]

Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

@products_param = (

['11.90', '1034K'],
['22.90', '1034R'],
['43.90', '1034U']

); # no comma after last entry

I run the following from a file on the server:

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

####### Start products_param FOR loop

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

###### Start products_param FOR loop ######

$compiled = $dbh->prepare("

UPDATE products
SET products_price = ?
WHERE products_model = ?
VALUES (?,?)

");

foreach ($products_param as $products) {

$dbh->execute($compiled, $products);

};

This gives me an "Internal Server" error. I have several PHP and MySQL
books but cannot find the correct syntax.

Any help appreciated.

Al Moodie.

[webxan.com]

On Sep 2, 11:37*am, Al Moodie <[e-mail]:

Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

@products_param = (

* * * * * * * * ['11.90', '1034K'],
* * * * * * * * ['22.90', '1034R'],
* * * * * * * * ['43.90', '1034U']

* * * * * * * * ); # no comma after last entry

I run the following from a file on the server: * * * * *

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

####### Start products_param FOR loop

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

###### Start products_param FOR loop ######

$compiled = $dbh->prepare("

* * * * UPDATE products
* * * * SET products_price = ?
* * * * WHERE products_model = ?
* * * * VALUES (?,?)

* * * * ");

foreach ($products_param as $products) {

$dbh->execute($compiled, $products);

};

This gives me an "Internal Server" error. I have several PHP and MySQL
books but cannot find the correct syntax.

Any help appreciated.

Al Moodie.

seems to be error in this
where have you declared this?

@products_param = (

['11.90', '1034K'],
['22.90', '1034R'],
['43.90', '1034U']

);

---------------------
http://www.webxan.com
---------------------

[Captain Paralytic]

On 2 Sep, 16:37, Al Moodie <[e-mail]:

Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

@products_param = (

* * * * * * * * ['11.90', '1034K'],
* * * * * * * * ['22.90', '1034R'],
* * * * * * * * ['43.90', '1034U']

* * * * * * * * ); # no comma after last entry

I run the following from a file on the server: * * * * *

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

####### Start products_param FOR loop

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

###### Start products_param FOR loop ######

$compiled = $dbh->prepare("

* * * * UPDATE products
* * * * SET products_price = ?
* * * * WHERE products_model = ?
* * * * VALUES (?,?)

* * * * ");

foreach ($products_param as $products) {

$dbh->execute($compiled, $products);

};

This gives me an "Internal Server" error. I have several PHP and MySQL
books but cannot find the correct syntax.

Any help appreciated.

Al Moodie.

I have never seen that syntax for the execute method. Where are you
getting it from?

[Al Moodie]

On Tue, 2 Sep 2008 09:00:05 -0700 (PDT), Captain Paralytic
<pa**********@yahoo.comwrote:

>
I have never seen that syntax for the execute method. Where are you
getting it from?

I "adapted" it from an INSERT placeholder query in a PHP book. If you
know the correct sytax please let me know.

Al Moodie.

[Willem Bogaerts]

$compiled = $dbh->prepare("

>
UPDATE products
SET products_price = ?
WHERE products_model = ?
VALUES (?,?)

");

I think the VALUES line is a problem anyhow. It does not belong in an
UPDATE query.

If you want to know what happens, enable the querylog (in my.cnf). I
assume you are developing on your local database, where enabling the
querylog is not a space/performance issue.
To check the log "live" there are quite nice log viewers, like multitail
(linux) or BareTail (windows or wine). You can then see what is really
sent to the server.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

[Gordon]

On Sep 2, 4:37 pm, Al Moodie <[e-mail]:

Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

@products_param = (

['11.90', '1034K'],
['22.90', '1034R'],
['43.90', '1034U']

); # no comma after last entry

I run the following from a file on the server:

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

####### Start products_param FOR loop

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

###### Start products_param FOR loop ######

$compiled = $dbh->prepare("

UPDATE products
SET products_price = ?
WHERE products_model = ?
VALUES (?,?)

");

foreach ($products_param as $products) {

$dbh->execute($compiled, $products);

};

This gives me an "Internal Server" error. I have several PHP and MySQL
books but cannot find the correct syntax.

Any help appreciated.

Al Moodie.

What's with the VALUES line? i've never seen that in a update
statement before.

If you want to update wveral rows at once so that a column holds the
same value you'd do UPDATE table SET column = ? WHERE key IN (?); KEY
would then take a list of keys seperated by commas. For example to
update rows with key values 3, 7 and 10 you'd pass 3,7,10 to the
prepared query.

[C. (http://symcbean.blogspot.com/)]

On 2 Sep, 16:37, Al Moodie <[e-mail]:

Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

Hi Al,

I'd suggest re-writing your code in PHP and SQL - cos whatever you are
writing looks like neither:

@products_param = (

['11.90', '1034K'],
['22.90', '1034R'],
['43.90', '1034U']

); # no comma after last entry

I guess this is meant to be:

$products_param = array (
array ('11.90', '1034K'),
array ('22.90', '1034R'),
array ('43.90', '1034U')
); // no comma after last entry
(PHP will happily accomodate a trailing , in the array params - some
codingstandards even require it).

UPDATE products
SET products_price = ?
WHERE products_model = ?
VALUES (?,?)

WTF?

Do you mean
UPDATE products
SET products_price = ?
WHERE products_model = ?

C.