asked on

MySQLi, the basics

Hi,

I have been reading up on mysqli... about time, i know.
However, different tutorials uses different "methods" to reach the same goal.
Do you guys recommend a great article/guide to get started with mysqli? How to connect, get single results etc?

I tried to make a basic function, but it does not work.

$mysqli = new mysqli('123.123.123.123', 'user', 'pass', 'db');
$mysqli->set_charset("utf8");

if($mysqli->connect_errno > 0){
    die('Unable to connect to database [' . $mysqli->connect_error . ']');
}

function sysinfo($type) {

	$query = $mysqli->prepare("SELECT value FROM `system` WHERE `type`=? ");
	$query->escape_string($type);
	$query->bind_param('s', $type);
	$query->execute();
	
	$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
	
	$row = $result->fetch_assoc()
	
	$query->close();
	
	return $row['value'];

}

Open in new window

Ray Paseur

Here's one that should help:
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Also, this:
http://iconoun.com/mysql_mysqli_pdo_function_map.php

hielo

>> $query->escape_string($type);
escape_string() is not needed if you are using prepared statements. You use it in conjuction with the query() method.

>> $query->execute();
That is already executing the prepared SELECT statement. No need to call $mysqli->query(). Since you are using prepared statements, you do need to use execute().

function sysinfo($type) {

	$stmt= $mysqli->prepare("SELECT `value` FROM `system` WHERE `type`=? ");

	$stmt->bind_param('s', $type);

	$stmt->execute();

        // specify which variable should be "stuffed" with the column value when you call fetch()
	$stmt->bind_result($value);
	
        // here fetch() "stuffs" the column value into the bound variable $value
	$stmt->fetch();

	$stmt->close();
	
return $value;
}

Open in new window

Dave Baldwin

The PHP page for 'mysqli' has all the info and examples. http://php.net/manual/en/class.mysqli.php

kgp43

ASKER

Thanks guys.... love your tutorials Ray :)

I get an odd error though.
Mysqli connection is included on top all my pages.
This function fail when I try to run it:

# System
function sysinfo($type) {

	$safe_type = $mysqli->real_escape_string($type);

	$sql = "SELECT value FROM system WHERE type='$safe_type' LIMIT 1";
	$res = $mysqli->query($sql);
	
	$row = $res->fetch_assoc();
	
	return $row['value'];
}

Open in new window

Error:

Notice: Undefined variable: mysqli in /home/d/i/ftp_dinethdk/include/functions.php on line 52

Fatal error: Call to a member function real_escape_string() on a non-object in /home/d/i/ftp_dinethdk/include/functions.php on line 52

Open in new window

"Undefined variable: mysqli"... So I cannot use existing variables in my function?

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kgp43

ASKER

Just what i needed :)

Already replacing old queries with new ones.... takes quite a lot of rewriting though (specially when not experienced with OOP).

Thanks for your help.

Ray Paseur

Thanks for the points and thanks for using E-E, ~Ray

kgp43

ASKER

Got a small questions regarding "safety", specially on user input.

I use this now:

$safe_title = $mysqli->real_escape_string($_GET['title']);

Open in new window

But it does not add slashes to my strings, why is that (not after being saved to mysql that is)?
No slashes being added to '

Ray Paseur

Good question! The slashes are added in the PHP variable. You can see this with something like var_dump($safe_title). The SQL engine recognizes these as escape characters and removes them before storing the data in the database.

kgp43

ASKER

Ahhh, that makes sense :)
Thanks again.