How to convert mysql to mysqli?

Question

I tired to convert my mysql to mysqli but seems to be getting a lot of errors and warnings i got no problem connecting to the data base but the rest of the code seems wrong what am i doing wrong?

sql:

<?php
mysql_connect("localhost", "root", "");
mysql_select_db("searchengine");

$sql = mysql_query(sprintf(
    "SELECT * FROM searchengine WHERE pagecontent LIKE '%s' LIMIT 0,%d",
        '%'. mysql_real_escape_string($_GET['term']) .'%',
        $_GET['results']));

while($ser = mysql_fetch_array($sql)) {
    echo "<h2><a href='$ser[pageurl]'>$ser[pageurl]</a></h2>";
}

// don't forget to close connection
mysql_close();


?>

mysqli

<?php
mysqli_connect("localhost","root","","searchengine") or die("Error " . mysqli_error($link));

$result = mysqli_query(sprintf(
    "SELECT * FROM searchengine WHERE pagecontent LIKE '%s' LIMIT 0,%d",
        '%'. mysqli_real_escape_string($_GET['term']) .'%',
        $_GET['results']));

while($ser = mysqli_fetch_array($result)) {
    echo "<h2><a href='$ser[pageurl]'>$ser[pageurl]</a></h2>";
}

mysqli_close();


?>

Add error reporting to the top of your file(s) `error_reporting(E_ALL); ini_set('display_errors', 1); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` — Funk Forty Niner, May 08 '14 at 12:53
`$link = mysqli_connect("localhost","root","","searchengine") or die("Error " . mysqli_error($link));` if you're going to use `mysqli_error($link)` — Funk Forty Niner, May 08 '14 at 12:54
See also [mysqli or PDO - what are the pros and cons?](http://stackoverflow.com/q/13569) — mario, May 08 '14 at 12:54
It's not quite as simple as substituting "msqli_" for "mysql_" and including the connection string, because in addition you should really be taking advantage of prepared statements. — Strawberry, May 08 '14 at 15:00

score 1 · Answer 1 · answered May 08 '14 at 12:56

1

you can try it by creating a mysqli object like described here: http://www.php.net/manual/en/class.mysqli.php

or simply like this:

$db = new mysqli($hostname, $username, $password, $database);

and then query it like this:

$result = $db->query('SQL HERE');

in your case the code for mysqli would look like this

$db = new mysqli("localhost","root","","searchengine");

$result = $db->query(sprintf(
  "SELECT * FROM searchengine WHERE pagecontent LIKE '%s' LIMIT 0,%d",
  '%'. mysqli_real_escape_string($_GET['term']) .'%',
  $_GET['results'])
);

while($ser = mysqli_fetch_array($result)) {
    echo "<h2><a href='$ser[pageurl]'>$ser[pageurl]</a></h2>";
}

answered May 08 '14 at 12:56

Friedrich

2,211
20
42

thank you but I still get an error saying mysqli_real_escape_string expects exactly 2 parameters and when i search for something random it shows all the search terms? – user3615487 May 08 '14 at 13:52
you can try `$con=mysqli_connect("localhost","my_user","my_password","my_db");` to connect and change `mysqli_real_escape_string($_GET['term'])` to `mysqli_real_escape_string($con, $_GET['term'])` – Friedrich May 08 '14 at 13:55

EquinoxMatt · Answer 2 · 2014-05-08T13:07:07.763

Try using OOP style instead of procedural, it is much cleaner and more readable:

    $mysqli = new mysqli("localhost", "root", "", "searchengine");

    $result = mysqli->query(sprintf(
"SELECT * FROM searchengine WHERE pagecontent LIKE '%s' LIMIT 0,%d",
    '%'. mysqli_real_escape_string($_GET['term']) .'%',
    $_GET['results']));

May I also suggest you read some articles about how to use mysqli and preparted statements, instead of just hacking away and not reading the documentation. Using prepared statements removes the need for sprintf. Here are some useful links:

PHP Website - http://www.php.net/manual/en/book.mysqli.php

An article I found on google in about 5 seconds and looks quite good -http://mattbango.com/notebook/code/prepared-statements-in-php-and-mysqli/

meda · Answer 3 · 2014-05-08T15:03:05.910

In mysql, we used mysql_real_escape_string because you couldn't prepare statement.

Now with mysqli, you have the ability to prepare statements which is the preferred way.

<?php
$mysqli = new mysqli("localhost", "root", "password", "searchengine");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") ";
}

$query = "SELECT * FROM searchengine WHERE pagecontent LIKE ? LIMIT 0,?";
$stmt = $mysqli->prepare($query);

if (!$stmt) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$term = '%'.$_GET['term'].'%';
$result = $_GET['results'];

$stmt->bind_param("si", $term, $result);

$stmt->execute();

while ($ser = $stmt->fetch_assoc()) {
    echo "<h2><a href='".$ser['pageurl']."'>".$ser['pageurl']."</a></h2>";
}


$mysqli->close();
?>

How to convert mysql to mysqli?

3 Answers3

Linked

Related