mysql to mysqli

[nitos]

Converting mysql to mysqli.
i need help av been scratching my head how to make necessary changes but its like am making more errors.

please help or give direction what to change..

<?php
    session_start();
    include("php/class.user.php");

    $msg = "";
    $msg2 = "";
    //$submit = $_POST['btnaddnew'];
     if(isset($_POST['btnaddnew'])){
        if(empty($_POST['txtUserId']) || empty($_POST['txtFname']) || empty($_POST['txtMname']) || empty($_POST['txtLname']) || empty($_POST['txtNoStreet']) || empty($_POST['txtCity']) || empty($_POST['txtDOB']) || empty($_POST['txtAge']) || empty($_POST['selGender']) || empty($_POST['txtUser']) || empty($_POST['txtPass']) || empty($_POST['txtConPass']) || empty($_POST['selUserType'])){
            $msg = "Some field is empty!";
        }else if($_POST['txtPass'] != $_POST['txtConPass']){
            $msg2 = "Password did not match!";
        }else{
            $sqlExistId = mysql_query("select user_id from tblusers where user_id = '$_POST[txtUserId]'") or die(mysql_error());
            $sqlExistUsername = mysql_query("select * from tblaccount where username = '$_POST[txtUser]'") or die(mysql_error());
            if(mysql_num_rows($sqlExistId) >= 1)
                $msg = "User id is already taken";
            else if(mysql_num_rows($sqlExistUsername))
                $msg2 = "Username is already taken!";
            else{
                mysql_query("insert into tblusers(user_id, fname, mname, lname, no_street, city, contact_no, dob, age, gender, user_type)
                            values('$_POST[txtUserId]','$_POST[txtFname]','$_POST[txtMname]','$_POST[txtLname]','$_POST[txtNoStreet]','$_POST[txtCity]','$_POST[txtContactNo]','$_POST[txtDOB]','$_POST[txtAge]','$_POST[selGender]','$_POST[selUserType]')") or die(mysql_error());
                mysql_query("insert into tblaccount(user_id, username, password) values('$_POST[txtUserId]','$_POST[txtUser]','$_POST[txtPass]')") or die(mysql_error());
                $msg = "Successfully added new user!";
            }
        }
    }
?>

[mattster]

Start by building some simple queries, most of which can be done by replacing functions (like L21 mysql_query() with $insert_row = $mysqli->query();). You should slowly see the transformation as not too difficult as soon as you're used to the mysqli_* functions.

Take a look at these to get going:

http://www.sanwebe.com/2013/03/basic-php-mysqli-usage
http://codular.com/php-mysqli

Also note, you should use validation and must use $mysqli->real_escape_string() to clean data, otherwise you're vunerable to SQL injection.

[Lau_1]

MySQLi Procedural or MySQLi Object-Oriented? Which one you want to use?

[mattster]

I would highly recommend the object orientated.

It's a far more efficient way to work, but granted it's slightly more to learn. I think it also neatens your code if you are using objects rather than tons of functions and variables: procedural is famously a 'cheap and cheerful' quick fix.

If ever given the choice, go the OOP way!

[diafol]

Also note, you should use validation and must use $mysqli->real_escape_string() to clean data, otherwise you're vunerable to SQL injection.

Not sure about that matt - you could use prepared statements and bind parameters/values.

The only mysql_* functions being used in the snippet I could see were:

mysql_query() and mysql_num_rows()

For many intents and purposes, mysqli functions just duplicate the mysql equivalent:

mysqli_query() and mysqli_num_rows()

This is not true for all - so you must check the manual. In addition, some of the function parameters may be different. I'm surprised you couldn't find this information in the php.net manual:

http://php.net/manual/en/mysqli.query.php

http://php.net/manual/en/mysqli-result.num-rows.php

ALso as matt has pointed out, never EVER use raw input (e.g. POST variables) in your queries. This is a recipe for disaster and you could end up with SQL injections. Game over.

So, either clean with mysqli_real_escape_string() as mentioned, or, if you're using input parameters in your SQL, create a prepared statement. This is really the way to go.

http://php.net/manual/en/mysqli.prepare.php

[mattster]

you could use prepared statements and bind parameters/values.

Apologies, forgot to mention those - point being that some form of housekeeping needed to be done.

[Lau_1]

mysqli_real_escape_string() and create a prepared statement, which one recommended?

[diafol]

If you have parameters, then prepared statement (PS), otherwise you can use a straight mysqli_query (or the OOP version). I don't think many people are using mysqli_real_escape_string(). I may be wrong, but it does seem pointless when you can used PSes.