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

kgp43Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
>> $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

0
Dave BaldwinFixer of ProblemsCommented:
The PHP page for 'mysqli' has all the info and examples.  http://php.net/manual/en/class.mysqli.php
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

kgp43Author Commented:
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?
1
Ray PaseurCommented:
This is an issue of variable scope.  The old MySQL connection was a resource, not a variable. The $mysqli is a variable that points to an object.  Some info on variables and references is available here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12310-PHP-Variables-and-References.html

This redesign of the PHP database connection may appear to be awkward at first, but it is actually a huge advantage.  Global variables are bad.  See the "D in SOLID" in this article for some background, then read on below.
http://www.experts-exchange.com/articles/18329/SOLID-Design-in-PHP-Applications.html

Since the $mysqli is a variable, it is not automatically a global variable.  You could make it a global variable, but that would be a mistake that would get you fired from any professional programming team.  Instead, the right thing to do is recognize $mysqli for what it is: a dependency (your program depends on it, hence the name).  Inject the $mysqli variable into the function call.  If you do this, you have created a design that allows you to substitute a mock object for $mysqli and that means you can run controlled tests without disrupting your database.

Dependency injection, inversion of control, and automated testing are somewhat advanced topics in computer science, and these things were not a part of the original design of PHP, but the language is catching up with the times.  Tools like phpunit are available to help us write better software today.  It's an area worthy of your time and exploration, I promise!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgp43Author Commented:
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.
0
Ray PaseurCommented:
Thanks for the points and thanks for using E-E, ~Ray
0
kgp43Author Commented:
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 '
0
Ray PaseurCommented:
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.
0
kgp43Author Commented:
Ahhh, that makes sense :)
Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.