I inherited a website that had a series of database queries that were originally coded using mysql_query. Due to updates to zen cart, moving from mysql to mysqli these queries had to be updated. Since changing the queries to mysqli the function has broken.
The original queries were:
function insert_FENQUIRY() {
$sql = sprintf('INSERT INTO FENQUIRY
(ENREGION, ENNAME, ENCOMP, ENADD1, ENADD2, ENADD3, ENCNTY, ENPCODE, ENCNTRY, ENTEL, ENDATE, ENDATELCON, ENCAT, ENCONSRS, ENADVER, ENTYPE, EN_CUSTOMER_ID)
SELECT
(SELECT region FROM postcode2region WHERE ab.entry_postcode REGEXP CONCAT("^", postcode_prefix, "[:digit:]*") ORDER BY postcode_prefix DESC LIMIT 1) AS ENREGION,
c.ENCONNAME AS ENNAME,
IF(ab.entry_company <> "", ab.entry_company, CONCAT(c.ENCONNAME, " - online")) AS ENCOMP,
ab.entry_street_address AS ENADD1,
ab.entry_suburb AS ENADD2,
ab.entry_city AS ENADD3,
ab.entry_state AS ENCNTY,
ab.entry_postcode AS ENPCODE,
cc.countries_name AS ENCNTRY,
c.ENCONTEL AS ENTEL,
CURDATE() AS ENDATE,
CURDATE() AS ENDATELCON,
"X" AS ENCAT,
1 AS ENCONSRS,
"WWW" AS ENADVER,
"70" AS ENTYPE,
c.ENCON_ID AS EN_CUSTOMER_ID
FROM FENQCON c
LEFT JOIN address_book ab ON ab.address_book_id = c.ENCON_ZEN_DEFAULT_ADDRESS_ID
LEFT JOIN countries cc ON cc.countries_id = ab.entry_country_id
WHERE c.ENCON_ID = %d
LIMIT 1',
$_SESSION['customer_id']);
mysql_query($sql) or error_log("KG insert_FENQUIRY() : ". mysql_error());
return mysql_insert_id();
}
function insert_FENQCON() {
global $firstname, $lastname, $password, $gender, $newsletter, $email_format, $email_address,
$telephone, $fax, $company, $postcode;
$q = mysql_query(sprintf('
INSERT INTO FENQCON
SET
ENCONFNAME = "%s",
ENCONLNAME = "%s",
ENCON_ZEN_PASSWORD = "%s",
ENCON_ZEN_GENDER = "%s",
ENCON_ZEN_NEWSLETTER = "%s",
ENCON_ZEN_EMAIL_FORMAT = "%s",
ENCON_ZEN_AUTHORIZATION = "%s",
ENCONEMAIL = "%s",
ENCONTEL = "%s",
ENCONFAX = "%s",
ENCONDATE = CURDATE(),
ENCONNAME = "%s",
ENCON_OPTIN = 1,
ENCON_ZEN_ISLOGIN = 1
',
mysql_escape_string($firstname),
mysql_escape_string($lastname),
zen_encrypt_password($password),
mysql_escape_string($gender),
(int) $newsletter,
mysql_escape_string($email_format),
(int) CUSTOMERS_APPROVAL_AUTHORIZATION,
mysql_escape_string($email_address),
mysql_escape_string($telephone),
mysql_escape_string($fax),
mysql_escape_string("$firstname $lastname")
)) or die(mysql_error());
return mysql_insert_id();
}
function update_FENQCON_with_login() {
global $firstname, $lastname, $password, $gender, $newsletter, $email_format;
$q = mysql_query(sprintf('
UPDATE FENQCON
SET
ENCONFNAME = "%s",
ENCONLNAME = "%s",
ENCON_ZEN_PASSWORD = "%s",
ENCON_ZEN_GENDER = "%s",
ENCON_ZEN_NEWSLETTER = "%s",
ENCON_ZEN_EMAIL_FORMAT = "%s",
ENCON_ZEN_AUTHORIZATION = "%s",
ENCON_ZEN_ISLOGIN = 1,
ENCONCUSTOMER_ID = %d
WHERE ENCON_ID = %d',
mysql_escape_string($firstname),
mysql_escape_string($lastname),
zen_encrypt_password($password),
mysql_escape_string($gender),
(int) $newsletter,
mysql_escape_string($email_format),
(int) CUSTOMERS_APPROVAL_AUTHORIZATION,
$_SESSION['customer_id'],
$_SESSION['customer_id'] // WHERE
)) or die(mysql_error());
}
// FENQUIRY FLAG
$flag_insert_into_FENQUIRY = false;
// Check if email exists in FENQCON
$sql = sprintf('
SELECT
c.ENCON_ID,
c.ENCONCODE,
c.ENCONFNAME,
c.ENCONLNAME,
c.ENCONCSUNIQUE,
q.ENPCODE,
q.ENCOMP,
(SOUNDEX(c.ENCONFNAME) = SOUNDEX("%1$s")) * 1
+(SOUNDEX(c.ENCONLNAME) = SOUNDEX("%2$s")) * 1
+ROUND(levenshtein_ratio(REPLACE(q.ENPCODE, " ", ""), REPLACE("%4$s", " ", "")) / 100 * 2)
+ROUND(levenshtein_ratio(q.ENCOMP, "%5$s") / 100 * 2)
AS Score
FROM FENQCON c
LEFT JOIN FENQUIRY q ON q.ENCSUNIQUE = c.ENCONCSUNIQUE
WHERE c.ENCONEMAIL = "%3$s"
ORDER BY Score DESC, c.ENCON_ID ASC
LIMIT 1',
mysql_escape_string($firstname), // %1
mysql_escape_string($lastname), // %2
mysql_escape_string($email_address), // %3
mysql_escape_string($postcode), // %4
mysql_escape_string($company) // %5
);
$q = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($q) == 1) { // We have a matching existing FENQCON row
$FENQCON = mysql_fetch_object($q);
mysql_free_result($q);
error_log("KG FENQCON exists: ". print_r($FENQCON, true));
$_SESSION['customer_id'] = $FENQCON->ENCON_ID ;
$FENQUIRY = null;
if ($FENQCON->ENCONCSUNIQUE > 0) { // Fetch a linked FENQUIRY row if it exists and is linked!
$q = mysql_query(sprintf('SELECT * FROM FENQUIRY WHERE ENCSUNIQUE = %d', $FENQCON->ENCONCSUNIQUE));
$FENQUIRY = mysql_fetch_object($q);
mysql_free_result($q);
}
update_FENQCON_with_login();
if ($FENQUIRY) {
// Nothing to do
} else {
$flag_insert_into_FENQUIRY = true;
}
} else { // We need to create a new FENQCON and FENQUIRY rows
error_log("KG No matching FENQCON found.");
$_SESSION['customer_id'] = insert_FENQCON();
error_log("KG New FENQCON added with ENCON_ID: ". $_SESSION['customer_id']);
$flag_insert_into_FENQUIRY = true;
}
All was working fine at this point. Having updated the queries to use mysqli as shown below, it only partly functions now.
function insert_FENQUIRY() {
$connect = mysqli_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);
$sql = sprintf('INSERT INTO FENQUIRY
(ENREGION, ENNAME, ENCOMP, ENADD1, ENADD2, ENADD3, ENCNTY, ENPCODE, ENCNTRY, ENTEL, ENDATE, ENDATELCON, ENCAT, ENCONSRS, ENADVER, ENTYPE, EN_CUSTOMER_ID)
SELECT
(SELECT region FROM postcode2region WHERE ab.entry_postcode REGEXP CONCAT("^", postcode_prefix, "[:digit:]*") ORDER BY postcode_prefix DESC LIMIT 1) AS ENREGION,
c.ENCONNAME AS ENNAME,
IF(ab.entry_company <> "", ab.entry_company, CONCAT(c.ENCONNAME, " - online")) AS ENCOMP,
ab.entry_street_address AS ENADD1,
ab.entry_suburb AS ENADD2,
ab.entry_city AS ENADD3,
ab.entry_state AS ENCNTY,
ab.entry_postcode AS ENPCODE,
cc.countries_name AS ENCNTRY,
c.ENCONTEL AS ENTEL,
CURDATE() AS ENDATE,
CURDATE() AS ENDATELCON,
"X" AS ENCAT,
1 AS ENCONSRS,
"WWW" AS ENADVER,
"70" AS ENTYPE,
c.ENCON_ID AS EN_CUSTOMER_ID
FROM FENQCON c
LEFT JOIN address_book ab ON ab.address_book_id = c.ENCON_ZEN_DEFAULT_ADDRESS_ID
LEFT JOIN countries cc ON cc.countries_id = ab.entry_country_id
WHERE c.ENCON_ID = %d
LIMIT 1',
$_SESSION['customer_id']);
mysqli_query($connect,$sql) or error_log("KG insert_FENQUIRY() : ". mysql_error());
return mysqli_insert_id($connect);
}
function insert_FENQCON() {
global $firstname, $lastname, $password, $gender, $newsletter, $email_format, $email_address,
$telephone, $fax, $company, $postcode;
$connect = mysqli_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);
$q = mysqli_query($connect,sprintf('
INSERT INTO FENQCON
SET
ENCONFNAME = "%s",
ENCONLNAME = "%s",
ENCON_ZEN_PASSWORD = "%s",
ENCON_ZEN_GENDER = "%s",
ENCON_ZEN_NEWSLETTER = "%s",
ENCON_ZEN_EMAIL_FORMAT = "%s",
ENCON_ZEN_AUTHORIZATION = "%s",
ENCONEMAIL = "%s",
ENCONTEL = "%s",
ENCONFAX = "%s",
ENCONDATE = CURDATE(),
ENCONNAME = "%s",
ENCON_OPTIN = 1,
ENCON_ZEN_ISLOGIN = 1
',
mysqli_escape_string($connect,$firstname),
mysqli_escape_string($connect,$lastname),
zen_encrypt_password($password),
mysqli_escape_string($connect,$gender),
(int) $newsletter,
mysqli_escape_string($connect,$email_format),
(int) CUSTOMERS_APPROVAL_AUTHORIZATION,
mysqli_escape_string($connect,$email_address),
mysqli_escape_string($connect,$telephone),
mysqli_escape_string($connect,$fax),
mysqli_escape_string($connect,"$firstname $lastname")
)) or die(mysqli_error());
return mysqli_insert_id($connect);
}
function update_FENQCON_with_login() {
global $firstname, $lastname, $password, $gender, $newsletter, $email_format;
$connect = mysqli_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);
$q = mysqli_query($connect,sprintf('
UPDATE FENQCON
SET
ENCONFNAME = "%s",
ENCONLNAME = "%s",
ENCON_ZEN_PASSWORD = "%s",
ENCON_ZEN_GENDER = "%s",
ENCON_ZEN_NEWSLETTER = "%s",
ENCON_ZEN_EMAIL_FORMAT = "%s",
ENCON_ZEN_AUTHORIZATION = "%s",
ENCON_ZEN_ISLOGIN = 1,
ENCONCUSTOMER_ID = %d
WHERE ENCON_ID = %d',
mysqli_escape_string($connect,$firstname),
mysqli_escape_string($connect,$lastname),
zen_encrypt_password($password),
mysqli_escape_string($connect,$gender),
(int) $newsletter,
mysqli_escape_string($connect,$email_format),
(int) CUSTOMERS_APPROVAL_AUTHORIZATION,
$_SESSION['customer_id'],
$_SESSION['customer_id'] // WHERE
)) or die(mysqli_error());
}
// FENQUIRY FLAG
$flag_insert_into_FENQUIRY = false;
// Check if email exists in FENQCON
$connect = mysqli_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);
$sql = sprintf('
SELECT
c.ENCON_ID,
c.ENCONCODE,
c.ENCONFNAME,
c.ENCONLNAME,
c.ENCONCSUNIQUE,
q.ENPCODE,
q.ENCOMP,
(SOUNDEX(c.ENCONFNAME) = SOUNDEX("%1$s")) * 1
+(SOUNDEX(c.ENCONLNAME) = SOUNDEX("%2$s")) * 1
+ROUND(levenshtein_ratio(REPLACE(q.ENPCODE, " ", ""), REPLACE("%4$s", " ", "")) / 100 * 2)
+ROUND(levenshtein_ratio(q.ENCOMP, "%5$s") / 100 * 2)
AS Score
FROM FENQCON c
LEFT JOIN FENQUIRY q ON q.ENCSUNIQUE = c.ENCONCSUNIQUE
WHERE c.ENCONEMAIL = "%3$s"
ORDER BY Score DESC, c.ENCON_ID ASC
LIMIT 1',
mysqli_escape_string($connect,$firstname), // %1
mysqli_escape_string($connect,$lastname), // %2
mysqli_escape_string($connect,$email_address), // %3
mysqli_escape_string($connect,$postcode), // %4
mysqli_escape_string($connect,$company) // %5
);
$q = mysqli_query($connect,$sql) or die(mysqli_error());
if (mysqli_num_rows($q) == 1) { // We have a matching existing FENQCON row
$FENQCON = mysqli_fetch_object($q);
mysqli_free_result($q);
error_log("KG FENQCON exists: ". print_r($FENQCON, true));
$_SESSION['customer_id'] = $FENQCON->ENCON_ID ;
$FENQUIRY = null;
if ($FENQCON->ENCONCSUNIQUE > 0) { // Fetch a linked FENQUIRY row if it exists and is linked!
$q = mysqli_query($connect,sprintf('SELECT * FROM FENQUIRY WHERE ENCSUNIQUE = %d', $FENQCON->ENCONCSUNIQUE));
$FENQUIRY = mysqli_fetch_object($q);
mysqli_free_result($q);
}
update_FENQCON_with_login();
if ($FENQUIRY) {
// Nothing to do
} else {
$flag_insert_into_FENQUIRY = true;
}
} else { // We need to create a new FENQCON and FENQUIRY rows
error_log("KG No matching FENQCON found.");
$_SESSION['customer_id'] = insert_FENQCON();
error_log("KG New FENQCON added with ENCON_ID: ". $_SESSION['customer_id']);
$flag_insert_into_FENQUIRY = true;
}
Error logs show
PHP Warning: mysqli_error() expects exactly 1 parameter, 0 given
This error is occuring at
$q = mysqli_query($connect,$sql) or die(mysqli_error());
of the following code section
$connect = mysqli_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);
$sql = sprintf('
SELECT
c.ENCON_ID,
c.ENCONCODE,
c.ENCONFNAME,
c.ENCONLNAME,
c.ENCONCSUNIQUE,
q.ENPCODE,
q.ENCOMP,
(SOUNDEX(c.ENCONFNAME) = SOUNDEX("%1$s")) * 1
+(SOUNDEX(c.ENCONLNAME) = SOUNDEX("%2$s")) * 1
+ROUND(levenshtein_ratio(REPLACE(q.ENPCODE, " ", ""), REPLACE("%4$s", " ", "")) / 100 * 2)
+ROUND(levenshtein_ratio(q.ENCOMP, "%5$s") / 100 * 2)
AS Score
FROM FENQCON c
LEFT JOIN FENQUIRY q ON q.ENCSUNIQUE = c.ENCONCSUNIQUE
WHERE c.ENCONEMAIL = "%3$s"
ORDER BY Score DESC, c.ENCON_ID ASC
LIMIT 1',
mysqli_escape_string($connect,$firstname), // %1
mysqli_escape_string($connect,$lastname), // %2
mysqli_escape_string($connect,$email_address), // %3
mysqli_escape_string($connect,$postcode), // %4
mysqli_escape_string($connect,$company) // %5
);
$q = mysqli_query($connect,$sql) or die(mysqli_error());
Data is correctly being written to FENQCON table, but nothing is being written to the FENQUIRY table. I can see this is due to the failure of the above query which leads to $flag_insert_into_FENQUIRY = true; never getting run.
Can any mysqli experts point me in the right direction here as I can't see the reason for the code failing. I've read countless posts on here regarding mysql to mysqli conversion and I think I've done it correctly.
