PDA

View Full Version : PHP Advice on updating multiple records.


HeadAche
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...

davidj
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

HeadAche
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...

davidj
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

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

HeadAche
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.

davidj
04-13-2006, 09:35 AM
ok

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

HeadAche
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...

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

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
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'];*/ ?>

davidj
04-13-2006, 09:47 AM
ok
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

HeadAche
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...

davidj
04-13-2006, 10:01 AM
ok

example...
$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.

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

recordset returned 3 ID's
234
246
307

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

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 !!!

HeadAche
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:

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

ill write a solution..

be back in a mo

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

davidj
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

HeadAche
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... :)

HeadAche
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).

www.felixone.it

davidj
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

HeadAche
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.