mysqli php prepare not working

General PHP Help
#1

Hi

I changed my script to use prepared statement., but it does not work. Don’t get any error or success message. Can someone help me with this?

Thank You

[php]

<?php include "lib/configuration.php"; include "language/frontend/portuguese.php"; if ($_POST) { $f_fname = $_POST['fname']; $f_lname = $_POST['lname']; $f_email = $_POST['email']; $f_user = $_POST['user']; $f_pass1 = $_POST['pass1']; $f_pass2 = $_POST['pass2']; $f_type = 0; if ($f_pass2 == $f_pass1) { $cryptpass = md5($f_pass1); } else { echo $registererror['password']; /* Returns to register page */ $link = mysqli_connect($host, $user, $pass, $db); /* check connection */ if (mysqli_connect_errno()) { echo $error['dbconnection']; exit(); } $query = "SELECT * FROM users WHERE c_name = $f_user"; if ($result = mysqli_query($link, $query)){ $rowcount = mysqli_num_rows($result); if ($rowcount > 1){ mysqli_free_result($result); echo $registererror['duplicate']; /* Returns to register page */ } } else { $query = "INSERT INTO users (first_name, last_name, email, c_user, c_pass, c_type) VALUES (?,?,?,?,?,?)"; $stmt = mysqli_prepare($link, $query); mysqli_stmt_bind_param($stmt, "ssssss", $f_fname, $f_lname, $f_email, $f_user, $cryptpass, $f_type); /* Execute the statement */ mysqli_stmt_execute($stmt); if (mysqli_stmt_errno($stmt)){ echo $registererror['register'] . mysqli_stmt_error($stmt); /* Returns to index page (in the future may send an email to admin */ } else { echo $registerinfo['register']; /* Returns to index page */ } } /* close statement */ mysqli_stmt_close($stmt); /* close connection */ mysqli_close($link); } } else { include "templates/frontend/header.tpl"; include "templates/frontend/loginspace.tpl"; include "templates/frontend/registerlogin.tpl"; include "templates/frontend/searchspace.tpl"; include "templates/frontend/search.tpl"; include "templates/frontend/hmenuspace.tpl"; include "templates/frontend/menubar.tpl"; include "templates/frontend/leftspace.tpl"; include "templates/frontend/leftside.tpl"; include "templates/frontend/contentspace.tpl"; include "templates/frontend/register.tpl"; include "templates/frontend/rightspace.tpl"; include "templates/frontend/rightside.tpl"; include "templates/frontend/footerspace.tpl"; include "templates/frontend/footerdiv.tpl"; include "templates/frontend/footer.tpl"; } ?>

[/php]

#2

Before going to the MySQLi prepared statement, can I ask you a question?

[php]$query = “SELECT * FROM users WHERE c_name = $f_user”;[/php]

If $f_user is not an integer, this query will fail. You need to either:

  • Parametrize that query too
  • Escape $f_user and encase it in quotes for MySQL to pick it as a string

A single non-numeric character in $f_user will cause MySQL to error on that query. If it errors there, it never gets to the prepared statement, which means that you never get the success or the error message. So… is $f_user an integer?

#3

The c_name was wrong anyway. It should be c_user. The statement now looks like this:

$query = "SELECT * FROM users WHERE c_user = '$f_user'";

But I still get the same problem

#4

Well… The logic was wrong. This is my new code:

[php]

<?php include "lib/configuration.php"; include "language/frontend/portuguese.php"; if ($_POST) { $f_fname = $_POST['fname']; $f_lname = $_POST['lname']; $f_email = $_POST['email']; $f_user = $_POST['user']; $f_pass1 = $_POST['pass1']; $f_pass2 = $_POST['pass2']; $f_type = 0; if ($f_pass2 <> $f_pass1) { echo $registererror['password']; /* Returns to register page */ } else { $cryptpass = md5($f_pass1); $link = mysqli_connect($host, $user, $pass, $db); /* check connection */ if (mysqli_connect_errno()) { echo $error['dbconnection']; exit(); } $query = "SELECT * FROM users WHERE c_user = '$f_user' OR first_name = '$f_fname' AND last_name = '$f_lname' OR email = '$f_email'"; if ($result = mysqli_query($link, $query)){ $rowcount = mysqli_num_rows($result); if ($rowcount > 1){ mysqli_free_result($result); echo $registererror['duplicate']; /* Returns to register page */ } } else { $query = "INSERT INTO users (first_name, last_name, email, c_user, c_pass, c_type) VALUES (?,?,?,?,?,?)"; $stmt = mysqli_prepare($link, $query); mysqli_stmt_bind_param($stmt, "ssssss", $f_fname, $f_lname, $f_email, $f_user, $cryptpass, $f_type); /* Execute the statement */ mysqli_stmt_execute($stmt); if (mysqli_stmt_errno($stmt)){ echo $registererror['register'] . mysqli_stmt_error($stmt); /* Returns to index page (in the future may send an email to admin */ } else { echo $registerinfo['register']; /* Returns to index page */ } } /* close statement */ mysqli_stmt_close($stmt); /* close connection */ mysqli_close($link); } } else { include "templates/frontend/header.tpl"; include "templates/frontend/loginspace.tpl"; include "templates/frontend/registerlogin.tpl"; include "templates/frontend/searchspace.tpl"; include "templates/frontend/search.tpl"; include "templates/frontend/hmenuspace.tpl"; include "templates/frontend/menubar.tpl"; include "templates/frontend/leftspace.tpl"; include "templates/frontend/leftside.tpl"; include "templates/frontend/contentspace.tpl"; include "templates/frontend/register.tpl"; include "templates/frontend/rightspace.tpl"; include "templates/frontend/rightside.tpl"; include "templates/frontend/footerspace.tpl"; include "templates/frontend/footerdiv.tpl"; include "templates/frontend/footer.tpl"; } ?>

[/php]

Now I’m getting an error:

Notice: Undefined variable: stmt in D:\Web Data\divdev\register.php on line 49 Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, null given in D:\Web Data\divdev\register.php on line 49

Line 49 is mysqli_stmt_close($stmt);

#5

Now that this is done and dealt with, you also need to mysqli_free_result() on the branch of your script that runs the second query. MySQLi will not allow you to run additional queries until the first set of results is freed.

Side note: error_reporting(E_ALL) and display_errors set to 1 will tell you most of what I am telling you.

#6

I free the only result on the script. The second query is an Insert and does not have a result. Don’t understand what you mean.

error_reporting = E_ALL
display_errors = On

from php.ini

#7

The logic was still wrong. It is working now. here is the working script:

[php]

<?php include "lib/configuration.php"; include "language/frontend/portuguese.php"; if ($_POST) { $f_fname = $_POST['fname']; $f_lname = $_POST['lname']; $f_email = $_POST['email']; $f_user = $_POST['user']; $f_pass1 = $_POST['pass1']; $f_pass2 = $_POST['pass2']; $f_type = 0; if ($f_pass2 <> $f_pass1) { echo $registererror['password']; /* Returns to register page */ } else { $cryptpass = md5($f_pass1); $link = mysqli_connect($host, $user, $pass, $db); /* check connection */ if (mysqli_connect_errno()) { echo $error['dbconnection']; exit(); } $query = "SELECT * FROM users WHERE c_user = '$f_user' OR first_name = '$f_fname' AND last_name = '$f_lname' OR email = '$f_email'"; if ($result = mysqli_query($link, $query)){ $rowcount = mysqli_num_rows($result); if ($rowcount > 0){ mysqli_free_result($result); echo $registererror['duplicate']; /* Returns to register page */ } else { $query = "INSERT INTO users (first_name, last_name, email, c_user, c_pass, c_type) VALUES (?,?,?,?,?,?)"; $stmt = mysqli_prepare($link, $query); mysqli_stmt_bind_param($stmt, "ssssss", $f_fname, $f_lname, $f_email, $f_user, $cryptpass, $f_type); /* Execute the statement */ mysqli_stmt_execute($stmt); if (mysqli_stmt_errno($stmt)){ echo $registererror['register'] . mysqli_stmt_error($stmt); /* Returns to index page (in the future may send an email to admin */ } else { echo $registerinfo['register']; /* Returns to index page */ } /* close statement */ mysqli_stmt_close($stmt); /* close connection */ mysqli_close($link); } } } } else { include "templates/frontend/header.tpl"; include "templates/frontend/loginspace.tpl"; include "templates/frontend/registerlogin.tpl"; include "templates/frontend/searchspace.tpl"; include "templates/frontend/search.tpl"; include "templates/frontend/hmenuspace.tpl"; include "templates/frontend/menubar.tpl"; include "templates/frontend/leftspace.tpl"; include "templates/frontend/leftside.tpl"; include "templates/frontend/contentspace.tpl"; include "templates/frontend/register.tpl"; include "templates/frontend/rightspace.tpl"; include "templates/frontend/rightside.tpl"; include "templates/frontend/footerspace.tpl"; include "templates/frontend/footerdiv.tpl"; include "templates/frontend/footer.tpl"; } ?>

[/php]

Thank You for your help

Sponsor our Newsletter | Privacy Policy | Terms of Service