Hello,
I noticed somebody had had a bit similar problem, but the answers provided for him ware a bit too vague for helping me out.
I have battling with this problem for some time now. I have to run an update query to mysql and the mysql_query returns false. When I catch this and try to print out mysql_error and mysql_errno they both are empty. I print the sql out and I have tested it directly with MySQL Query Browser where it works without any problems.
The sql (after being parsed from several components) looks somthing like this:
UPDATE statistics SET sessionid='20080417123929d03164', no='0123456789', start='2008-04-17 12:39:42', end='2008-04-17 12:39:42', duration='0', mob='0', inter='0', success='0', exit='', trans='', cust='DSE212', noest='1', hup='' WHERE id=8
(I have changed some of the names of the columns for NDA reasons sorry... )
The table looks like this:
id int AUTO_INCREMENT
sessionid varchar(45)
no varchar(16)
start datetime
end datetime
duration int
mob tinyint(1)
inter tinyint(1)
success tinyint(1)
exit varchar(45)
trans varchar(45)
cust varchar(45)
noest tinyint(1)
hup varchar(45)
I have tested the sql with and without single quetas around integers and this does not have any effect.
The code is distributed to multiple classes which makes pasting the complete code surrounding the the query here quite complex but lets have a try:
First level calling an statistics object method to save its contents to the db:
main class
if(!$oStats->save()) {
[Print some junk]
}Stat class
public function save()
{
if(!$this->id) {
$this->message = "No id defined";
return false;
}
$sSet = '';
$counter = 0;
while(list($key, $type) = each($this->_aValidKeys)) {
if($key == "id") continue;
$newCol = "$key=";
$value = $this->_hCallStats[$key];
switch($type) {
case "bool":
if($value) {
$value = "1";
} else {
$value = "0";
}
//$newCol .= "'".$value."'";
$newCol .= $value;
break;
case "string":
case "datetime":
$newCol .= "'".$value."'";
break;
default:
//$newCol .= "'".$value."'";
$newCol .= $value;
break;
}
if($counter > 0) $sSet .= ",";
$sSet .= " ". $newCol;
$counter++;
}
$sql = "UPDATE statistics SET".$sSet." WHERE id=".$this->id.'';
if(!$this->_db->query($sql)) {
$this->message = $this->_db->message;
if($this->_db->results === FALSE) {
$this->message .= " :: RESULTS: FALSE";
}
return false;
}
$this->message = $sql;
return true;
}
The query method in db class looks like this:
public function query($sql, $type=MYSQL_BOTH)
{
$this->error = FALSE;
$this->message = NULL;
if($this->open()) {
$command = strtoupper(substr($sql, 0, strpos($sql, ' ')));
$fetch = FALSE;
if(($command == "SELECT") || ($command == "SHOW")) {
$fetch = TRUE;
}
$results = mysql_query($sql, $this->_dbHandler);
if($results === FALSE) {
$msg = "DBER01:" .mysql_errno($this->_dbHandler) . ":". mysql_error($this->_dbHandler).":".$sql;
$this->message = $msg;
$this->results = $results;
$this->error = TRUE;
mysql_free_result($results);
$this->close();
return false;
}
if($results && $fetch) {
$this->results = array();
while($aRow = mysql_fetch_array($results, $type)) {
$this->results[] = $aRow;
}
if(is_array($this->results)) {
$this->affectedRows = count($this->results);
} else {
$this->results = FALSE;
$this->affectedRows = 0;
}
mysql_free_result($results);
} else {
$this->affectedRows = mysql_affected_rows($this->_dbHandler);
if($command == "INSERT") {
$this->lastId = mysql_insert_id($this->_dbHandler);
if($this->affectedRows == 0) {
$this->message = "DBER02: 0 rows inserted! SQL: $sql";
$this->error = TRUE;
$this->close();
return false;
}
}
mysql_free_result($results);
}
mysql_free_result($results);
if($this->affectedRows == -1) {
$msg = "DBER03: ".mysql_errno($this->_dbHandler) . ":". mysql_error($this->_dbHandler). ":".$sql;
$this->message = $msg;
$this->error = TRUE;
$this->close();
return false;
}
$this->close();
return true;
}
$this->error = TRUE;
$this->message = "DBER04: Cannot connect to db :$sql";
$this->results = NULL;
$this->affectedRows = NULL;
return false;
}
I pressed the enter accidentaly the message was sent incomplete. Sorry about that. Any help is appriciated.
Cheers!
