I am completely confused by mySQLi. Although I have been using mysql procedural calls for many years, I want to get used to preparing prepared instructions for the db security / mySQL protection that it offers. I am trying to write a simple select statement (yes, I know that a procedure call procedure for this offers a performance improvement). When I start, I get all the echoes until I hit the component $result = $stmt->get_result(); . It all seems pretty simple to me, but I'm wasting time after reading mySQLi instructions. Any ideas why this would be unsuccessful?
* note: this is a test environment, and although character processing / escaping is not performed, I only pass valid content to the $ username and $ email variables. In addition, I went through all the SOs to find a solution to my problem.
function checkUsernameEmailAvailability($username, $email) { //Instantiate mysqli connection @$mysqli = new mysqli(C_HOST,C_USER,C_PASS,C_BASE) or die("Failed to connect to MySQL database..."); if (!$mysqli) { echo 'Error: Could not connect to database. Please try again later...'; exit; } else { echo 'mysqli created'; } /* Create a prepared statement */ if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) { echo '<br />MYSQLi: '; /* Bind parameters s - string, b - boolean, i - int, etc */ $stmt -> bind_param("ss", $username, $email); echo '<br />paramsBound...'; /* Execute it */ $stmt -> execute(); echo '<br />Executed'; $result = $stmt->get_result(); echo '<br />Result acquired'; /* now you can fetch the results into an array - NICE */ $myrow = $result->fetch_assoc(); echo '<br />Fetched'; /* Close statement */ /$stmt -> close(); echo '<br />Done mysqli'; } }
Also, do I need to instantiate mysqli every time I call a function? I assume that they are not persistent db connections, as in procedural mysql. Yes, I know that this is a scope problem, and no, I could not understand the scope of this class variable. When I declared it outside the function, it was not available when I entered the function.
UPDATE if I change line 12 from:
if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) {
in
$stmt = $mysqli->stmt_init(); if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) { if(!stmt) echo 'Statement prepared'; else echo 'Statement NOT prepared';
I get instructions NOT prepared. Now I'm even more confused ....
UPDATE: I contacted my hosting provider and apparently supports mySQLi and the mysqlnd driver is present. Perhaps there is a way to just check this out? Although in the past they used to give me fairly well-informed answers.
UPDATE ... AGAIN: I checked the capabilities of my server myself and found that while mysqli and PDO are present, mysqlnd is not. Thus, I understand why get_result () will not work (mysqlnd is required, I think), I still do not understand why the prepared statement itself will not work.