[RESOLVED] Strange behaviour using UPDATE with PHP5/MySQL5

[mercury7]

Hi All

Had some strange behaviour develop in some code I'm working on & wondering if anyone has some ideas how to deal with this as its got me stumped?

My code generates an SQL statement:

UPDATE inter_address SET amender = 'mark', street = '5 The Street' WHERE id = 155

but it fails to write to the database (SELECT & INSERT statements use the same class method to access the database with no problems).

Now here's the strange bit - if I copy & paste the statement into the mysql client the statement runs fine & the table updates OR if I copy & paste the statement into the code after the statment has been generated eg.

$stmt = "UPDATE inter_address SET amender = 'mark', street = '5 The Street' WHERE id = 155";

// run SQL query
$result = $this->dBLink()->Query($stmt);

& then run it through the class method to access the database the table also updates.

Any insight into whats happening here I how I can resolve it would be gratefully received as this is driving me nuts!

Thanks

Mark

[NogDog]

Maybe I'm confused, but you do not appear to have shown us the code that does not work, so it's a bit difficult for me to debug it.

[mercury7]

Hi NogDog

Thanks for responding - don't think the code will help with this problem but here it is:

This is the class method which creates the SQL statement then calla a separate class method to run it:

// amend table data from an array or stdClass object - only where data modified
public function TableAmend($tableName, $recordID, $dataSource, $modified)
{
	// initialise properties 
	$this->_arData = $dataSource;
	$this->_arModified = $modified;
	$this->_rowID = $recordID;
	$this->_table = $tableName;

// begin statement with all-tables administrative field identifier 
$stmt = "UPDATE $this->_table SET amender = '$this->_user', ";

// each input form value - where field value has been amended - absent value stored as NULL, 
// otherwise store entered values
foreach($this->_arData as $field => $value)
	if($this->_arModified[$field])
		if($value == "")
			$stmt .= "$field = NULL, ";
		else
			$stmt .= "$field = '$value', ";

// remove last comma and space
$stmt = substr($stmt, 0, strlen($stmt)-2);

// complete statement with record identifier
$stmt .= " WHERE id = $recordID";

// run SQL query
$result = $this->dBLink()->Query($stmt);

return	$result;
}

In a test run the above code produces the following SQL statement:

UPDATE inter_address SET amender = 'mark', street = '5 The Street' WHERE id = 155

$this->dBLink()->Query($stmt)

is a class method to initialise a separate class method utilised for SQL interaction with the database - as:

// run a dB query utilising the SQL statement passed as an argument
public function Query($sql)
{
	// clear any previous query result data
	$this->_resultRows = $this->_resultSet = $return_array = NULL;

// store result set in member variable
$this->_resultSet = $this->_linkIdent->query($sql);

// where result set created, store each record of result set as an object in an array member
if(!is_bool($this->_resultSet))
{
	// until end of result records - 
	while($obj = $this->_resultSet->fetch_object())
		$return_array[] = $obj;

	$this->_resultRows = $return_array;

	return 1;
}
// where no result set
else
	return 0;
}

As stated in the original posting the above code produces a valid SQL statement:

UPDATE inter_address SET amender = 'mark', street = '5 The Street' WHERE id = 155

which works when pasted into a mysql client or when is pased into the original code eg.

// remove last comma and space
$stmt = substr($stmt, 0, strlen($stmt)-2);

// complete statement with record identifier
$stmt .= " WHERE id = $recordID";

// copy of generated statement passed to method for execution - THIS WORKS!!
$stmt = "UPDATE inter_address SET amender = 'mark', street = '5 The Street' WHERE id = 155";

// run SQL query
$result = $this->dBLink()->Query($stmt);

This is why I'm not sure if the code is helpful - the SQL statement is valid but doesn't work when passed directly to the SQL execution class method - but does when a copy of it is passed - that's what's got me baffled.

Hope this helps

Mark

[mercury7]

Problem now resolved - issue was the parameter containing the record identifier being passed to the TableAmend method as a string rather than an integer - hence the failure of the query initially but its succes when pasted into mysql client or back into the code.

My thanks to anyone who spent time looking at this - guess its a slutory lesson in keeping trck of where data is generated from when re-using later in the code :o

Mark