Update multiple rows using PHP SQLBuilder

Question

I need to edit some PHP that wasn't written by me, I've gotten most of it done but there's a bit I can't figure out the correct syntax for.

Basically we grab data from a Google Fusion Table and populate a table with it, once we've edited the data in the table we can update that information to a production version of the same table. One of the update queries looks like this:

if($table_row[12]=="New"){
  $tableid1 = '1bsSleKDBdkhQZfn-oADx0tUtoOc32RqIyiX05Bo';
 $insertresults = $ftclient->query(SQLBuilder::insert($tableid1, 
    array('SUBURB'=> $table_row[1],
    'ROAD_NAME' => $table_row[2],
    'DESCRIPTION' => $table_row[3],
    'DIRECTION' => $table_row[4],
    'STATUS' => $table_row[5],
    'SITE_ID' => $table_row[6],
    'COMMON_NAME' => $table_row[7],
    'Lat' => $table_row[8],
    'Long' => $table_row[9],
        'OPERATING_DAY' => $table_row[10],
    'OPERATING_HOURS' => $table_row[11],
    'Version' => "current")));
  $insertresults = explode("\n", $insertresults);
  $rowid1 = $insertresults[1];
     $updateresults = $ftclient->query(SQLBuilder::update($tableid, 
    array('SUBURB'=> $table_row[1],
    'ROAD_NAME' => $table_row[2],
    'DESCRIPTION' => $table_row[3],
    'DIRECTION' => $table_row[4],
    'STATUS' => $table_row[5],
    'SITE_ID' => $table_row[6],
    'COMMON_NAME' => $table_row[7],
    'Lat' => $table_row[8],
    'Long' => $table_row[9],
        'OPERATING_DAY' => $table_row[10],
    'OPERATING_HOURS' => $table_row[11],
    'Version' => "current",
    'Edited_By' => $table_row[13],
    'Date_Edited' => $table_row[14]),$table_row[0]));
  $updateresults = explode("\n", $updateresults);
  $rowid2 = $updateresults[1]; 
  }

What I need to do is write a similar type of SQLBuilder query that will update every record with the same SITE_ID when SUBURB is changed in one of them (i.e., if a record has SUBURB Sydney, SITE_ID 1, each record with SITE_ID 1 must be changed if one record's SUBURB value is changed from Sydney to Melbourne). Not sure exactly how to phrase this syntactically or how I would go about writing the query using SQLBuilder. Any helpw would be appreciated!

Captain Payalytic · Accepted Answer · 2013-03-07 15:14:34Z

0

SQLBuilder is obviously a locally included class. You will need to look at its documentation or the update method itself in order to see how it expects a where clause to be coded.

The SQL for this would be:

"UPDATE tableid SET SUBURB = '{$table_row[1]}' WHERE SITE_ID = '{$table_row[6]}'"

If you are able to run pure SQL then that will do it. Otherwise I'd need to see the Class libraries.

edited Mar 7, 2013 at 15:14

answered Mar 7, 2013 at 0:20

Captain Payalytic

1,0718 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matthew Paxman Over a year ago

Ah, it appears it's a helper class for Fusion Tables. Any chance you could help me syntactically? As in a way to phrase what I want (if SUBURB is changed update all records where SITE_ID is equal to the SITE_ID of the changed row)... I can't think of how to do it.

Captain Payalytic Over a year ago

How are you identifying the specific record whose SUBURB value you initially wish to change?

Matthew Paxman Over a year ago

There is a table displayed, and when you update a record the 'Version' field is automatically updated to "Update" (exactly the same as above, except the if statement reads if($table_row[12]=="Update"), so this would be the same when I want to change the suburb I believe (by this I mean as soon as you change the SUBURB value, version will change to "Update" to update that specific record.

Collectives™ on Stack Overflow

Update multiple rows using PHP SQLBuilder

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related