I have the following new function that's giving me trouble. It should insert a row in a table (siteHits) if that data doesn't already exist. I see two problems:
- There is apparently something wrong the the PREPARE (which returns a FALSE from mysqli->prepare(), though I don't see what the problem is
- When the Prepare fails, the mysqli::error variable is not updated.
The larger concern is the last one. It's good to be able to eliminate the error, but to me it's more important to understand why no error message is returned.
siteHits looks like this:
mysql> describe siteHits;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| pageUrl | varchar(80) | YES | | NULL | |
| hitDate | date | YES | | NULL | |
| referFrom | varchar(80) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
Here is the code as it sits today (tons of logging to try to pin down the problem). I've included the connection function for additional reference :
// connectMySQL attempts to connect to the database. Returns mysqli object if no error.
public function connectMySql($server = null
, $user = null
, $pwd = null
, $dbName = null
) {
if ($server === null) $server = $this->dbServer ;
if ($user === null) $user = $this->dbUser ;
if ($pwd === null) $pwd = $this->dbPassword ;
if ($dbName === null) $dbName = $this->dbName ;
$ms = new mysqli($server, $user, $pwd, $dbName) ;
if ($ms->connect_errno) {
$this->sqlError = @mysqli_connect_error();
writeLogLine("connectMySQL() resulted in $this->sqlError") ;
writeLogLine("Server: $this->dbServer User: $this->dbUser Pwd: $this->dbPassword dbname: $this->dbName") ;
$ms = NULL;
}
return ($ms);
}
function updateHitTable($pageURL = Null) {
$count = 0 ;
$allOK = false ;
if ($mySql = $this->connectMySql()) {
$this->writeLogLine("000 Connected to MySQL"
. " SQL error = " . $mySql->error) ;
$query = "SELECT COUNT(*) FROM siteHits WHERE pageURL = ? "
. "AND hitDate = CURRENT_DATE()" ;
$stmt = $mySql->prepare($query) ;
if ($stmt) {
$this->writeLogLine("010 Prepare result is good."
. " SQL error = " . $mySql->error) ;
$escapedPageURL = $mySql->escape_string($pageURL) ;
$sqlResult = $stmt->bind_param("s", $escapedPageURL) ;
$this->writeLogLine("020 bind_param result = $sqlResult"
. " SQL error = " . $mySql->error) ;
}
if ($stmt) {
$this->writeLogLine("030 \$stmt is still good."
. " SQL error = " . $mySql->error) ;
$result = $stmt->execute() ;
$this->writeLogLine("040 \$result is $result; \$stmt->num_rows is " . $stmt->num_rows
. " SQL error = " . $mySql->error) ;
if ($result && $stmt->num_rows > 0) { // This should ALWAYS be true
$stmt->store_result() ;
while ($count += $result->fetch_assoc()) { } // Get the count
$this->writeLogLine("050 \$count = $count"
. " SQL error = " . $mySql->error) ;
}
if ($count < 1) { // No matching value found. OK, let's insert one
$query = "INSERT INTO siteHits (pageUrl, hitDate) "
. "VALUES(?, ?)" ;
$this->writeLogLine("060 \$query = $query"
. " SQL error = " . $mySql->error) ;
$stmt = $mySql->prepare($query) ;
$this->writeLogLine("070 Prepare error: "
. " SQL error = " . $mySql->error) ;
if ($stmt) {
$stmt->bind_param("ss", $escapedPageURL, null) ;
$this->writeLogLine("080 Prepared OK"
. " SQL error = " . $mySql->error) ;
}
else {
$this->writeLogLine("090 Prepare failed."
. " SQL error = " . $mySql->error) ;
}
if ($stmt) {
if ($stmt->execute()) $allOK = True ;
if ($allOK) {
$this->writeLogLine("100 Success."
. " SQL error = " . $mySql->error) ;
}
}
}
}
if (! $allOK) {
$this->sqlError = $mySql->error ;
$this->writeLogLine("SQL error '" . $this->sqlError
. "' in ->updateHitTable())") ;
}
$mySql->close();
}
} // End of function miniFunctions->updateHitTable
And the log that gets written when the function runs:
000 Connected to MySQL SQL error =
010 Prepare result is good. SQL error =
020 bind_param result = 1 SQL error =
030 $stmt is still good. SQL error =
040 $result is 1; $stmt->num_rows is 0 SQL error =
060 $query = INSERT INTO siteHits (pageUrl, hitDate) VALUES(?, ?) SQL error =
070 Prepare error: SQL error =
090 Prepare failed. SQL error =
SQL error '' in ->updateHitTable())
The code is kind of ugly compared to what I'd like to eventually do with it, but I hope that its logging is thorough enough to help understand the problem. (By the way, I have already verified that all the space-looking characters are actually ascii 0x20 [space], no unprintables in the mix.)
Again, my primary interest is in figuring out why I get no error, but if you happen to notice where the code is wrong, I'd appreciate that too.
Additionally, note that there are no rows returned from SELECT COUNT(*). I would expect exactly one row. Just another mystery to puzzle over.
This happens on my local machine (PHP7.3, MySQL 8.0.23, and on my ISP's system (Php 5.3.20, MySQL 5.6) Thanks.
