View Full Version : PHP Advice on updating multiple records.

04-12-2006, 03:26 PM
Yibbety Yibetter folks.

I really badly need some advice on how I go about doing something. Ok the bg is that (this is really complicated but I'll try my best) there are records in the the base and some of them can share a 'productCode'.

The ins and outs are really complicated - the system is for an auction house, basicly, could anyone be so kind as to tell me how I might go about updating multiple records at once? i found an extension that might help me but they want 30 squids for it and I think its abit sharp.

Thanks, and be kind to fish...

04-12-2006, 03:31 PM
there are a number of ways you can do this but first you need to know some basic PHP and SQL

do you understand these languages

04-12-2006, 03:34 PM
I know very little PHP, my work mate is a VB and SQL expert though... We might beable to get through it together if you give us a few pointers m8...

04-12-2006, 03:36 PM
you need to first follow dreamweavers tuts for updating a database

once you understand that then come back and we will move on to the next part

04-12-2006, 03:43 PM
yeah mate, i know how to do all that... update, delete, make a record set filter...

04-13-2006, 09:09 AM
Morning guys. Can anyone else offer me a few pointers ? I know my way around the server behaviours and what have you, just this updating mulpitle records at once is really stressing my brain out.

04-13-2006, 09:35 AM

post your current update statement

if your id in the database is the same for all your records that need updating then that is simple and wont take long to explain

db table .....

data1 data2 data3
1 test test
2 test test
3 test test
1 test test
3 test test
1 test test
1 test test
1 test test

as you can see from the example table structure then it will be easy to update all records with an ID of 1 or 3

04-13-2006, 09:44 AM
Ok dude, here she is... its basicly just your standard update behaviour... in the last cell theres a button in the form with some hidden fields containing the info from sessions and url elements which i intend to pass into the base... One other thing Im doing is setting the repeat region number by a session value which is passed from the last page...

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
return $theValue;

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "sellMoreForm")) {
$updateSQL = sprintf("UPDATE stock SET SoldPrice=%s, AuctionDateSold=%s, Sold=%s, ClientID=%s WHERE Id=%s",
GetSQLValueString($_POST['SoldPrice'], "double"),
GetSQLValueString($_POST['AuctionDateSold'], "date"),
GetSQLValueString($_POST['Sold'], "text"),
GetSQLValueString($_POST['ClientID'], "int"),
GetSQLValueString($_POST['Id'], "int"));

mysql_select_db($database_auction, $auction);
$Result1 = mysql_query($updateSQL, $auction) or die(mysql_error());

$colname_rst_sellMore = "-1";
if (isset($_GET['ItemID'])) {
$colname_rst_sellMore = (get_magic_quotes_gpc()) ? $_GET['ItemID'] : addslashes($_GET['ItemID']);
$coldate_rst_sellMore = "-1";
if (isset($_GET['AuctionHeldIn'])) {
$coldate_rst_sellMore = (get_magic_quotes_gpc()) ? $_GET['AuctionHeldIn'] : addslashes($_GET['AuctionHeldIn']);
$colzone_rst_sellMore = "-1";
if (isset($_GET['Zone'])) {
$colzone_rst_sellMore = (get_magic_quotes_gpc()) ? $_GET['Zone'] : addslashes($_GET['Zone']);
$collotno_rst_sellMore = "-1";
if (isset($_GET['LotNo'])) {
$collotno_rst_sellMore = (get_magic_quotes_gpc()) ? $_GET['LotNo'] : addslashes($_GET['LotNo']);
mysql_select_db($database_auction, $auction);
$query_rst_sellMore = sprintf("SELECT * FROM stock WHERE ItemID = '%s' AND AuctionHeldIn = '%s' AND Zone = '%s' and lotno > '%s' and sold = 'NotSold'", $colname_rst_sellMore,$coldate_rst_sellMore,$colzo ne_rst_sellMore,$collotno_rst_sellMore);
$rst_sellMore = mysql_query($query_rst_sellMore, $auction) or die(mysql_error());
$row_rst_sellMore = mysql_fetch_assoc($rst_sellMore);
$totalRows_rst_sellMore = mysql_num_rows($rst_sellMore);

isset($startRow_rst_sellMore)? $orderNum=$startRow_rst_sellMore:$orderNum=0;
/*$quantValue = $_POST['available'];*/ ?>

04-13-2006, 09:47 AM
you have a where clause in your update statement

what values are you looking for when doing multi updates??

example ...

where id = '2' or id ='3' etc

is this what you mean

04-13-2006, 09:57 AM
Thats it mate. the Id isnt consecuative, the VB / MySQL programmer told me to tell you this !?!?! But yeah, if the ItemId = 'coffee' then update with these values...

04-13-2006, 10:01 AM

$updateSQL = sprintf("UPDATE stock SET SoldPrice=%s, AuctionDateSold=%s, Sold=%s, ClientID=%s WHERE Id='apple'",

please note the where clause ID='apple'. This will update every record that has apple as an ID.

04-13-2006, 10:17 AM
select id from table1 where date = '2006-06-06'

recordset returned 3 ID's

update table1 set field4 = 'hello' where id = $id
next record

We need it to use the results from the first select query
and update each record in the table with the same ID.
so get the first ID (234)... update then move to next id (246)...update, then next id(307) update...etc
Hi mate, the VB programer has typed the above, hope it make sense to you !!!

04-13-2006, 10:25 AM
Or would the bit you posted in effect do just that what the VB programmer guy is getting at? :roll:

04-13-2006, 10:42 AM
i understand now...

ill write a solution..

be back in a mo

04-13-2006, 11:10 AM
thanks mate, your a star...

04-13-2006, 12:15 PM
ok here it is..
ignore the 1st recordset. this is what you use to set the update WHERE clause..

mysql_select_db($database_mytestdata, $mytestdata);
$query_Recordset1 = "SELECT * FROM pfm_work WHERE pfm_work.pfm_work_id between '2010' and '2020'";
$Recordset1 = mysql_query($query_Recordset1, $mytestdata) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

/////// this is where it starts ////
/////// your programmer should understand this ////

do {

$arrayform = $arrayform.$row_Recordset1['pfm_work_id'].',';

} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));

$exploder = explode(',',$arrayform);

$count = count($exploder)-1;

for ($i=0; $i < $count; $i++){
if ($i > 0){$where = '';}

$statment = $statment. ' \''.$exploder[$i]. '\' and db_field =' ;


$where = 'WHERE db_field ='.$statment;

$updateThis = trim($where, 'and db_field =');

if you use $updateThis variable in your update statement then this will work.

$updateThis contains a full WHERE Clause so just drop that into your update statement where your WHERE clause will be written

04-13-2006, 01:07 PM
Thanks david for all your help mate... I owe ya one, Ill let you know how I get on... :)

04-18-2006, 10:10 AM
Dave mate, I was well out of my depth with this code, I ended up buying a new extension which does insert / update and delete multiple records at once for PHP and ASP models. It cost 27 but worth every penny and is as far as im concerned one of them missing dreamweaver tools. My sanity is restored (for now atleast).


04-19-2006, 07:32 AM
all you had to do was post your code and i would have written it for you saving 27

oh well.. next time maybe

04-19-2006, 09:51 AM
Post you my code, lol, I felt like a cheeky enough of a b*stard you been kind enough to do what you did. Ok mate, next time I'll try and do that if the offer is still open then...

The boss said hes going to cough up for the .mxp anyway, and so he should(just a case of when). I think its a good extension though, even for someone like yourself who can write the code by hand - I think this would save you alot of time. Again, Macromedia should have included something like this behaviour as standard in DWMX as I think its an essential function.