MySQLi not working after converting from MySQL

[NovaPark]

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 MySQLi. Please advise me how to modify this script, I would prefer the procedural style.

The MySQLi coding is not working because it would notg 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)< {
            $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 AFTER CONVERTING)

<?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)< {
            $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 ();
        }
    }
}
?>

[QuickOldCar]

Do your connection first, then mysqli_real_escape_string() or the values will be empty strings.

[Jacques1]

It makes no sense to merely append an “i” to all function names. This doesn't improve your code one bit, and it will lead to a lot of bugs (as you just saw).

 

Is this new code you wrote for yourself? Then I recommend you throw it away, learn PDO (not MySQLi) and actually use its new features. When you're a beginner, your first attempts are naturally poor, so don't waste time trying to save them. Take it as a learning experience and start over with your new, improved skills.

 

Or is this some big application from somebody else? Then either leave the code as it is or reserve the next weeks/months/years for a complete rewrite. I strongly recommend the former. For a beginner, rewriting a big legacy application is really too much and likely to end in a disaster: You'll waste enormous amounts of time, leave the entire application in an unstable state, get bugs after bugs etc.

[Frank_b]

Starting with the first function: mysqli_connect() ..

 

In a perfect world the connection to the mysql server would never fail but this does not sound realistic. In reality i could fail couse of many reasons. That's why functions gives data back.

 

Take a look here: http://php.net/manual/en/function.mysql-connect.php and then under the section Return Values. You will learn that this function can give a FALSE if something went wrong.

 

You should test that and if it gives FALSE you could use mysql_error() to show an error.

 

Same story for other functions like mysqli_query() for example.

[ginerjm]

Try turning on php error checking properly for a development environment.   See my signature.

 

And as Frank B says - you should always do a check of the result of what I call 'external' function calls - such as interfacing to the MySQL db, or with the file I/o system, etc.  Programming (good programming) designs in the means to handle any situation.  The failure to handle possible errors in these cases leaves a big hole in your code.

[Jacques1]

Manually checking every single return value for an error status is obsolete and should not be done.

 

Yes, it used to be necessary back in the days of the old MySQL extension. But both PDO and MySQLi support modern ways of error handling. For example, you can make them throw an exception whenever a query fails. In that case, the script automatically stops, activates the standard error procedure and gracefully shuts down. No need for any manual intervention.

[Frank_b]

Jacques,

 

What you write is absolutely true but don't forget that PDO uses OOP techniques that not suit for a lot of hobbyist programmers.

[Jacques1]

Using PDO has nothing to do with “object-oriented programming”. It's merely a different syntax, and it's easy to understand without any prior knowledge whatsoever. I mean, $database->query($sql) is literally pseudo code. You can hardly make that more obvious.

 

Actually, I wonder how you came to the conclusion that MySQLi is great for beginners. It's frigging complicated. Even I constantly have to look up the details in the manual. Take a prepared statement, for example:

<?php

$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

$database = mysqli_connect('localhost', 'foo', 'bar', 'test');
mysqli_set_charset($database, 'utf8mb4');



$author_id = 1234;
$publication_date = '2014-12-23';

$blog_posts = mysqli_prepare($database, '
    SELECT
        post_id,
        content
    FROM
        blog_posts
    WHERE
        author_id = ?
        AND publication_date = ?
');
mysqli_stmt_bind_param($blog_posts, 'is', $author_id, $publication_date);
mysqli_stmt_execute($blog_posts);

mysqli_stmt_bind_result($blog_posts, $post_id, $post_content);
while (mysqli_stmt_fetch($blog_posts))
{
    var_dump($post_id, $post_content);
}

It takes four(!) function calls plus a while loop only to make a simple query. This parameter binding stuff is also very hard to grasp for a beginner (I've personally experienced that).

 

When using PDO, you just call prepare(), execute() and loop through the result set with foreach like with any other query:

<?php

$database = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'foo', 'bar', array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));



$author_id = 1234;
$publication_date = '2014-12-23';

$blog_posts = $database->prepare('
    SELECT
        post_id,
        content
    FROM
        blog_posts
    WHERE
        author_id = :author_id
        AND publication_date = :publication_date
');
$blog_posts->execute(array(
    'author_id' => $author_id,
    'publication_date' => $publication_date,
));

foreach ($blog_posts as $blog_post)
{
    var_dump($blog_post);
}

How is that more complicated? I think the only reason why people use MySQLi at all is because the name sounds familiar. That's understandable, but it's hardly a good reason.

Edited December 23, 2014 by Jacques1