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_para m as $products) {

$dbh->execute($compi led, $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@gmail.com]

Re: Updating multiple records

On Sep 2, 11:37 am, Al Moodie <nos...@nospam. comwrote:

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_para m as $products) {
>
$dbh->execute($compi led, $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]

Re: Updating multiple records

On 2 Sep, 16:37, Al Moodie <nos...@nospam. comwrote:

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_para m as $products) {
>
$dbh->execute($compi led, $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]

Re: Updating multiple records

On Tue, 2 Sep 2008 09:00:05 -0700 (PDT), Captain Paralytic
<paul_lautman@y ahoo.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]

Re: Updating multiple records

$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]

Re: Updating multiple records

On Sep 2, 4:37 pm, Al Moodie <nos...@nospam. comwrote:

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_para m as $products) {
>
$dbh->execute($compi led, $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/)]

Re: Updating multiple records

On 2 Sep, 16:37, Al Moodie <nos...@nospam. comwrote:

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.