How to write a registering script in mySQLi?

Question

I'm attempting to convert my script that I use for registering a user on my website from SQL to SQLi. I have some code and wondered if it was correct. Thanks.

$members = new mysqli("localhost", "root", "pass", "members");
$check = $members->prepare("select email from users where email = ?");
$check->bind_param('s', $_POST['r_email']);
$check->execute();
$check->store_result();
if ($check->num_rows > 0) {
echo "user already registered";
} else {
$user_id = mt_rand(100000000, 999999999);
$add_user = $members->prepare("insert into users(email, password, user_id) values(?, ?, ?)");
$add_user->bind_param('ssi', $r_email, $r_password, $user_id);
$r_email = $_POST['r_email'];
$r_password = md5($_POST['r_password']);
$add_user->execute();
$add_user->close();
}
$check->close();
$members->close();

Do you have a specific problem? Did you test it and receive an error we can help you with? Or are you simply looking for validation that your code is not horribly incorrect? — , Dec 31 '11 at 21:04
hi yeh i just tried it and got the error 'All data must be fetched before a new statement prepare takes place' — Andy Lobel, Dec 31 '11 at 21:06
Take a look at PDO for database access, IMHO it's better and easier to use than mysqli. — Gary Willoughby, Dec 31 '11 at 21:40

score 0 · Answer 1 · edited May 23 '17 at 12:12

0

You need to call mysqli_stmt::store_result before you check mysqli_stmt::num_rows (as described at mysqli_stmt::num-rows). After that, you need to close the statement using mysqli_stmt::close (mysqli_stmt::close).

Edit: Also, using md5 for password hashing (especially without a salt) is very insecure. Take a look at https://stackoverflow.com/a/1581919/140827 for suggestions on more secure solutions (bcrypt, salt, etc.)

edited May 23 '17 at 12:12

Community

1
1

answered Dec 31 '11 at 21:10

erjiang

44,417
10
64
100

i edited it to the way you said, is that the best way to achieve what i want to do? – Andy Lobel Dec 31 '11 at 21:16

score 0 · Accepted Answer · answered Dec 31 '11 at 21:18

0

Dealing with the error message you noted in your comment, 'All data must be fetched before a new statement prepare takes place'' ...

The error means exactly what it says: You're trying to prepare a new statement before you've fetched all the data from the previous statement. From the manual entry on mysqli::use_result^docs ...

Used to initiate the retrieval of a result set from the last query executed using the mysqli_real_query() function on the database connection.

Either this or the mysqli_store_result() function must be called before the results of a query can be retrieved, and one or the other must be called to prevent the next query on that database connection from failing.

Further, from the manual entry on mysqli_stmt::num_rows^docs ...

Returns the number of rows in the result set. The use of mysqli_stmt_num_rows() depends on whether or not you used mysqli_stmt_store_result() to buffer the entire result set in the statement handle.

answered Dec 31 '11 at 21:18

difference between use and store result ? lol and also if i wanted the values would it be something like while($result = $check->fetch_row){} or sommin like that lol – Andy Lobel Dec 31 '11 at 21:35
I was basically referencing the fact that your problems can often be solved by reading the manual entries on the functions you're using and when you do it you'll get smarter ... teach a man to fish ... :) – Dec 31 '11 at 21:46
yeh lol orite i wish someone could actually be good at explaning tho the php website sucks at that aswell – Andy Lobel Dec 31 '11 at 21:54
The PHP manual exists to explain how the language works. Programming University it is not. – Dec 31 '11 at 23:05

How to write a registering script in mySQLi?

2 Answers2