Simply converting from mysql to mysqli

Question

I was wondering if someone could please point me in the right direction for changing my script into mysqli? I built the script below fully with mysql and it works perfectly but I have since been told that mysql_ is depreciated and I now need to use mysqli. (before mysql_ dies out and I'm left with an error fuelled website).

script:

<?php

//This is the directory where images will be saved
$target = "images/";
$target = $target . basename( $_FILES['photo']['name']);

//This gets all the other information from the form
$name= (isset($_POST['image_author']));
$description= ($_POST['image_description']);
$pic=($_FILES['photo']['name']);


// Connects to your Database
mysql_connect("localhost", "root", "root") or die(mysql_error()) ;
mysql_select_db("image_gallery") or die(mysql_error()) ;

//Writes the information to the database
mysql_query("INSERT INTO images (image_author, image_description, image_pathname)
VALUES ('$name', '$description', '$pic'");

//Writes the photo to the server
if(move_uploaded_file($_FILES['photo']['tmp_name'], $target))
{

//Tells you if its all ok
echo "The file has been uploaded, and your information has been added to the directory <p> <a href='upload.php'> Go back</a>";
}
else {

//Gives and error if its not
echo "Sorry, there was a problem uploading your file.";
}
?>

See over there on the right where it says "Related"? There are about half a dozen questions about the same thing. Have you read them? — j08691, Jan 20 '15 at 15:52
Start by reading this page about how to avoid SQL injection in PHP. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Andy Lester, Jan 20 '15 at 16:18

score 3 · Answer 1 · answered Jan 20 '15 at 15:51

You have two options.

1- First, fix your code to work properly with mysql_* functions. You are wide open to SQL injections right now. Then upgrade.

2- (preferred) Scrap the code, because it is wrong and vulnerable. Start over with something better, like PDO and prepared queries, and never worry about injection again.

Martin · Answer 2 · 2015-01-20T16:09:42.637

I agree entirely with Niet the Dark Absol, regarding a preferred method such as Object Orientated MySQLi (or PDO)

But, I was in a similar position to you not so long ago- and here's the quick and dirty solution:

1) Build a MySQLi connection to the database, replace (comment out) your current connection.

example:

$dbuser="XXX";
$db="YYY";
$dbpass="TTTTT";
$MysqliLink = mysqli_connect("localhost", $dbuser, $dbpass, $db);
if ( ! $MysqliLink )
    {
    die("Connection Error (" . mysqli_connect_errno() . ") ". mysqli_connect_error());
    mysqli_close($MysqliLink);
    }
mysqli_set_charset($MysqliLink, "utf8");

The only bit that might be new to you is to use a variable that is COMPLETELY UNIQUE to your website, and unlikely to be accidentally overwritten, for $MysqliLink . Once this is done and set you can move onto stage 2:

2) Search and replace your entire site with the following:

mysql_query(" --> becomes --> mysqli_query($MysqliLink, "

mysql_error() --> becomes --> mysqli_error($MysqliLink)

mysql_ --> becomes --> mysqli_

There are a couple of functions that change, such as: mysql_num_rows() --> becomes --> mysqli_num_rows($MysqliLink)

3) As an additional note it is a good idea to get into the habit of using

$valueToInputIntoSql = mysqli_real_escape_string($MysqliLink,$value);

to escape apostraphes and suchlike, although it's not quite perfect, it goes a long way to make inputs more safe.

4) I think that is about it, from my memory of doing this to about 12 different sites I inherited, search and replace site wide is a massive time saver, If anyone nudges my memory for other replaces to do I'll add them in here.

Good luck. And then read up on OO DB connections too.

If my solution works you can tick the tick beside my answer at the top, or the topic. and/or click the up arrow to add credit to my answer. Cheers. — Martin, Jan 20 '15 at 16:28

koc · Accepted Answer · 2015-01-20T16:37:39.873

0

Try this, I used it as below. Here change db_name = your original database ame

db.php (create a separate connection file)

$db_hostname = "localhost";  //usually "localhost be default"
$db_username = "root";  //your user name
$db_pass = "root";  //the password for your user
$db_name = "yourdatabasename";  //the name of the database

// connect to database
$db = new mysqli ($db_hostname, $db_username, $db_pass, $db_name);
if ($db->connect_error) {
trigger_error('Database connection failed: '  . $db->connect_error,   E_USER_ERROR);
}

Now this is your file

<?php
include('db.php');
//This is the directory where images will be saved
$target = "images/";
$target = $target . basename( $_FILES['photo']['name']);

//This gets all the other information from the form
$name= (isset($_POST['image_author']));
$description= ($_POST['image_description']);
$pic=($_FILES['photo']['name']);


// Connects to your Database
mysqli_select_db($db,"image_gallery") or die(mysqli_error($db)); 

//Writes the information to the database
$result = mysqli_query($db,"INSERT INTO images (image_author, image_description, image_pathname) VALUES ('$name', '$description', '$pic')");

//Writes the photo to the server
if(move_uploaded_file($_FILES['photo']['tmp_name'], $target))
{

//Tells you if its all ok
echo "The file has been uploaded, and your information has been added to the directory <p> <a href='upload.php'> Go back</a>";
}
else {

//Gives and error if its not
echo "Sorry, there was a problem uploading your file.";
}
?>

edited Jan 20 '15 at 16:37

answered Jan 20 '15 at 16:10

koc

955
8
26

sorry @koc but there are errors here that won't work - `mysqli_error()` – Martin Jan 20 '15 at 16:12
you want to make a separate connection file see at above my 1st code, which name is db.php, and $db comes out from this connection file. – koc Jan 20 '15 at 16:17
sorry my bad, I overlooked db.php – Martin Jan 20 '15 at 16:18
You must change 4th line of db.php with your database name. Go ahead. – koc Jan 20 '15 at 16:20
Thank you very much!!! I uploaded and everything is working perfect, the only issue is the warning that appears "Warning: mysqli_select_db() expects exactly 2 parameters, 1 given", which I'm not fully understanding as it states about one database, should there be another connect function within the db.php? – Coli-Eza Jan 20 '15 at 16:27
you need to tell MySQL which database connection to use: 'mysqli_select_db($db,"image_gallery") or die(mysqli_error($db)) ;' – Martin Jan 20 '15 at 16:29
I have a few mistake. Please paste this line: $result = mysqli_query($db,"INSERT INTO images (image_author, image_description, image_pathname) VALUES ('$name', '$description', '$pic')"); – koc Jan 20 '15 at 16:32
I mean replace with previous line with current edited line. – koc Jan 20 '15 at 16:33
Martin you are right. I overlooked it also. Thank you. – koc Jan 20 '15 at 16:36
It works! My only issue now is that the 'author' comes up as a number and not the text typed in but I'm sure that's for another day - phew! Thank you both so much, you have no idea how much I was stressing over this. Thank you both, again. – Coli-Eza Jan 20 '15 at 16:38
I've just literally fixed the author issue, all complete. Thank you!!! :D – Coli-Eza Jan 20 '15 at 16:39
Thank you too. If this solution works you can tick the tick beside my answer at the top, or the topic. and/or click the up arrow to add credit to my answer. . – koc Jan 20 '15 at 16:42

Simply converting from mysql to mysqli

3 Answers3

Linked