PHP - Convert my deprecated MySQL to MySQLi

Question

I'm a beginner here and i am learning the basic in converting from MySQL to MySQLi. I am currently working on this registration page which I would want to convert to the new MySQLi. Please advise me how to modify this script, I would prefer the procedural style.

UPDATE - The MySQLi coding is not working because it would insert into the database like the MySQL coding would, would appreciate if your can help me.

MYSQL

<?php
error_reporting(1);
$submit = $_POST['submit'];
//form data
$name = mysql_real_escape_string($_POST['name']);
$name2 = mysql_real_escape_string($_POST['name2']);
$email = mysql_real_escape_string($_POST['email']);
$password = mysql_real_escape_string($_POST['password']);
$password2 = mysql_real_escape_string($_POST['password2']);
$email2 = mysql_real_escape_string($_POST['email2']);
$address = mysql_real_escape_string($_POST['address']);
$address2 = mysql_real_escape_string($_POST['address2']);
$address3 = mysql_real_escape_string($_POST['address3']);
$address4 = mysql_real_escape_string($_POST['address4']);
$error = array();
if ($submit) {
    //open database
    $connect = mysql_connect("localhost", "root", "Passw0rd") or die("Connection Error");
    //select database
    mysql_select_db("logindb") or die("Selection Error");
    //namecheck
    $namecheck = mysql_query("SELECT * FROM users WHERE email='{$email}'");
    $count = mysql_num_rows($namecheck);
    if($count==0) {
    }
    else
    {
        if($count==1) {
            $error[] = "<p><b>User ID taken. Try another?</b></p>";
        }
    }
    //check for existance
    if($name&&$name2&&$email&&$password&&$password2&&$email2&&$address&&$address2&&$address3&&$address4) {
        if(strlen($password)<8) {
            $error[] = "<p><b>Password must be least 8 characters</b></p>";
        }
        if(!preg_match("#[A-Z]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 upper case characters</b></p>";
        }
        if(!preg_match("#[0-9]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 number</b></p>";
        }
        if(!preg_match("#[\W]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 symbol</b></p>";
        }
        //encrypt password
        $password = sha1($password);
        $password2 = sha1($password2);
        if($_POST['password'] != $_POST['password2']) {
            $error[] = "<p><b>Password does not match</b></p>";
        }
        //rescue email match check
        if($_POST['email2'] == $_POST['email']) {
            $error[] = "<p><b>Rescue Email must not be the same as User ID</b></p>";
        }
        //generate random code
        $random = rand(11111111,99999999);
        //check for error messages
        if(isset($error)&&!empty($error)) {
            implode($error);
        }
        else
        {
            //Registering to database
            $queryreg = mysql_query("INSERT INTO users VALUES ('','$name','$name2','$email','$password','$password2','$email2','$address','$address2','$address3','$address4','$random','0')");
            $lastid = mysql_insert_id();
            echo "<meta http-equiv='refresh' content='0; url=Activate.php?id=$lastid&code=$random'>";
            die ();
        }
    }
}
?>

MYSQLI (NOT WORKING)

<?php
error_reporting(1);
$submit = $_POST['submit'];
//form data
$name = mysqli_real_escape_string($connect, $_POST['name']);
$name2 = mysqli_real_escape_string($connect, $_POST['name2']);
$email = mysqli_real_escape_string($connect, $_POST['email']);
$password = mysqli_real_escape_string($connect, $_POST['password']);
$password2 = mysqli_real_escape_string($connect, $_POST['password2']);
$email2 = mysqli_real_escape_string($connect, $_POST['email2']);
$address = mysqli_real_escape_string($connect, $_POST['address']);
$address2 = mysqli_real_escape_string($connect, $_POST['address2']);
$address3 = mysqli_real_escape_string($connect, $_POST['address3']);
$address4 = mysqli_real_escape_string($connect, $_POST['address4']);
$error = array();
if ($submit) {
    //open database
    $connect = mysqli_connect("localhost", "root", "Passw0rd", "logindb") or die("Connection Error");
    //namecheck
    $namecheck = mysqli_query($connect, "SELECT * FROM users WHERE email='{$email}'");
    $count = mysqli_num_rows($namecheck);
    if($count==0) {
    }
    else
    {
        if($count==1) {
            $error[] = "<p><b>User ID taken. Try another?</b></p>";
        }
    }
    //check for existance
    if($name&&$name2&&$email&&$password&&$password2&&$email2&&$address&&$address2&&$address3&&$address4) {
        if(strlen($password)<8) {
            $error[] = "<p><b>Password must be least 8 characters</b></p>";
        }
        if(!preg_match("#[A-Z]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 upper case characters</b></p>";
        }
        if(!preg_match("#[0-9]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 number</b></p>";
        }
        if(!preg_match("#[\W]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 symbol</b></p>";
        }
        //encrypt password
        $password = sha1($password);
        $password2 = sha1($password2);
        if($_POST['password'] != $_POST['password2']) {
            $error[] = "<p><b>Password does not match</b></p>";
        }
        //rescue email match check
        if($_POST['email2'] == $_POST['email']) {
            $error[] = "<p><b>Rescue Email must not be the same as User ID</b></p>";
        }
        //generate random code
        $random = rand(11111111,99999999);
        //check for error messages
        if(isset($error)&&!empty($error)) {
            implode($error);
        }
        else
        {
            //Registering to database
            $queryreg = mysqli_query($connect, "INSERT INTO users VALUES ('','$name','$name2','$email','$password','$password2','$email2','$address','$address2','$address3','$address4','$random','0')");
            $lastid = mysqli_insert_id();
            echo "<meta http-equiv='refresh' content='0; url=Activate.php?id=$lastid&code=$random'>";
            die ();
        }
    }
}
?>

were you having a particular problem , or would you like someone to do the whole conversion for you? — Mitch Wheat, Dec 21 '14 at 07:22
@MitchWheat tried converting to the new mysqli but i won't work, would you like me to post the converted but not working code — Paper9oll, Dec 21 '14 at 07:23
What do you mean by the code isn't working? *how* is it not working? — Jhecht, Dec 21 '14 at 07:32
apparently the mysqli coding won't insert the data into the database. — Paper9oll, Dec 21 '14 at 07:35
possible duplicate of [Can I blindly replace all mysql\_query functions with mysqli\_query?](http://stackoverflow.com/questions/26476162/can-i-blindly-replace-all-mysql-query-functions-with-mysqli-query) — worldofjr, Dec 21 '14 at 07:36
@worldofjr my mysqli_query seem to be correct, can you help me point out there is a problem. — Paper9oll, Dec 21 '14 at 07:42

score 0 · Answer 1 · answered Dec 21 '14 at 07:48

I noticed one error in your script (mysqli script):

Instead of

$count = mysql_num_rows($namecheck);

do

$count = mysqli_num_rows($namecheck);

You can also check for errors in your query, like this (from w3schools - http://www.w3schools.com/php/func_mysqli_error.asp):

if (!mysqli_query($con,"INSERT INTO Persons (FirstName) VALUES ('Glenn')"))
  {
  echo("Error description: " . mysqli_error($con));
  }

Also try to do some debugging (echo some results) in your script to find errors.

i tried your error checking coding but no errors was printed out — Paper9oll, Dec 21 '14 at 08:02

score 0 · Answer 2 · answered Dec 21 '14 at 09:55

0

Pass connection parameter inside

$lastid = mysqli_insert_id();

like

$lastid = mysqli_insert_id($connect);

answered Dec 21 '14 at 09:55

jay.jivani

1,560
1
16
33

score 0 · Accepted Answer · answered Dec 21 '14 at 15:58

Converting to mysqli is not about adding i to the old library.

The main difference is that mysqli offers prepared statement feature.

This saves you from the tedious task of manually escaping values with mysqli_real_escape_string.

The proper way to do it is to prepare your query:

$query = "INSERT INTO users VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
if ($stmt = mysqli_prepare($connect, $query)) {   
    mysqli_stmt_bind_param($stmt,'sssssssssssss', $name,$name2,$email,$password,$password2,$email2,$address,$address2,$address3,$address4,$random,'0');
    /* execute prepared statement */
    mysqli_stmt_execute($stmt);   
    /*Count the rows*/
    if( mysqli_stmt_num_rows($stmt) > 0){
       echo"New Record has id = ".mysqli_stmt_insert_id($stmt);
    }else{
      printf("Errormessage: %s\n", mysqli_error($connect));
      die();
    }   
    /* close statement */
    mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);

In addition to prepared statement, another advantage is the coding style, mysqli introduces OOP style, here is the same code in that style:

$query = "INSERT INTO users VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
if ($stmt = $connect->prepare($query)) {   
    $stmt->bind_param('sssssssssssss', $name,$name2,$email,$password,$password2,$email2,$address,$address2,$address3,$address4,$random,'0');
    /* execute query */
    $stmt->execute();    
    /*Count the rows*/
    if($stmt->num_rows > 0){
       echo"New Record has id = ".$connect->insert_id;
    }else{
      var_dump($connect->error);
      die();
    }   
    /* close statement */
    $stmt->close();
}   
/* close connection */
$connect->close();

Both would achive the same. Good luck

wow didn't know the improved version of mysql is so complicated but anyway thanks — Paper9oll, Dec 22 '14 at 06:32
its more things to learn but not necessarily complicated, Look at `mysqli` would be a lot less code. If you are willing to make a switch then I recommend PDO its a lot simpler — meda, Dec 22 '14 at 06:39

PHP - Convert my deprecated MySQL to MySQLi

3 Answers3