transitioning to php's mysqli_query

Hi - I am transitioning an application from mysql_query to mysqli_query.  As such, after I connect to the database (via mysqli_connect upon loading a page), what is the best way to pass the corresponding database link to all of the underlying functions/queries.

I ask this because mysql_query simply used the most recent connection, so it was not an issue, but mysqli_query requires that the link actually be passed to it.

Is it possible to create some type of global variable to store the database connection when loading the page, such that it can be referenced within all of the underlying functions/queries?  or is there a better way to handle it?

Thanks in advance.

Pete
shafer23Asked:
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.

Dave BaldwinFixer of ProblemsCommented:
The example on the doc page is essentially the way most of us do it.  Do you have your connection variable contained in a function or class that can't be used?  You can also use 'Object oriented style' shown on the same page.
http://us1.php.net/manual/en/mysqli.query.php
0
Marco GasiFreelancerCommented:
You could simply create a file dbconnect.inc.php where you establish the database connection and then include this file at the top of all your scripts:

dbconnect.inc.php
<?php
$link = mysqli_connect("myhost","myuser","mypassw","mybd") or die("Error " . mysqli_error($link));
?>

Open in new window


your_script.php
<?php
include(dbconnect.inc.php);
$query = "SELECT name FROM mytable" or die("Error in the consult.." . mysqli_error($link));
$result = $link->query($query);
?>

Open in new window


Cheers
0
shafer23Author Commented:
Dave - I can access the connect function easily enough - I am just assuming that I should only access that once at the top of the script.

The issue, which is probably just due to a lack of knowledge, is how do I access that same link/variable without again calling the connect function within each underlying routine (that executes a query).

I am sure I am just missing something.  Thanks

Pete
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

shafer23Author Commented:
Also, can the link be stored within a class - if so, I guess I just need to read up on them, as I am not really familiar with them.
0
Marco GasiFreelancerCommented:
But using a global variable to grab the db connection can expose your site to security issues. If you don't implement a MVC-like structure, using a secured bootstrap file, I think the best thing is to include the file as I said above
0
shafer23Author Commented:
Thanks, I can do that - is there any performance issue, though, with respect to connecting to the database prior to each and every query - rather than just once at the beginning of the script?
0
Marco GasiFreelancerCommented:
if you use mysqli you can do something like this:

$mysqli = new mysqli("localhost", "my_user", "my_password", "my_db");

The problem arises when you have to make this class available through all your scripts and this takes us to the previous alternatives: a well structured site (like MVC) with a boot strap which instantiates all classes or an included file in each script (said that a global variable is not a good idea). And the best option is IMHO to include the file in all your scripts or using the line above which instantiates a mysqli object or using mysqli_connect function as you do.
0
Dave BaldwinFixer of ProblemsCommented:
Did you read the two examples on the php.net page?  It's all there.
http://us1.php.net/manual/en/mysqli.query.php
0
Ray PaseurCommented:
If you use the object-oriented version of MySQLi you will find that the transition is much easier.  I think the PHP Gods may have reversed the order of the arguments in the procedural version to provide a unsavory code smell to using the extension the wrong way.

This article maps the transition.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

After you read that, if you still have any questions please post back. ~Ray

PS: I believe, but have not verified, that you can simultaneously have a MySQL connection and a MySQLi connection to the same engine and data base.  That might make your transition easier, if it works as expected.

MySQL Connection
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $err <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $db_connection))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $err <br/>";
    trigger_error('NO DATA BASE', E_USER_ERROR);
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES

Open in new window

MySQLi Connection
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

Open in new window


MySQL Query
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}
$num = mysql_num_rows($res);

Open in new window

MySQLi Query
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
$num     = $res->num_rows;

Open in new window

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
Slick812Commented:
greetings  shafer23, , I understand your troubles, as using the Database connection inside functions was easy in the older MySQL but the newer requires the "Link"

This is the way I handle that sort of thing, the the PHP  $GLOBALS["dbc"] , more info here -
http://php.net/manual/en/reserved.variables.globals.php

$dbc = mysqli_connect("localhost","me","pass","dboc") or die("NO CONNECTION as Error :  " . mysqli_error($dbc));

fuction doSelect( ) {  // Inside fuctions use the $GLOBALS[ ]
$sql = "SELECT * from caps";
return mysqli_query ( $GLOBALS["dbc"] , $sql );
}

Open in new window

0
Ray PaseurCommented:
@Slick812: The newer MySQLi only requires you to include the "Link" if you insist on using the procedural version of MySQLi.  If you use the OOP version, you get the canonical link resource in the MySQLi object, and you never have to repeat yourself.  It's just much, much easier to use the OOP notation, even if your scripts are not designed in OO structure.  Minimal changes at most.

You can even make the connection object a Singleton!  (if you're into that sort of thing)
<?php // RAY_database_singleton.php
error_reporting(E_ALL);


// SINGLETON DATA BASE CONNECTION CLASS
class Database
{
    // CLASS PROPERTIES ARE ALL PRIVATE
    private static $connection;
    private static $instance;

    // CONNECTION VALUES
    const DB_HOST = 'localhost';
    const DB_USER = '??';
    const DB_PASS = '??';
    const DB_NAME = '??';

    // NULLIFY THE CLONE
    final private function __clone() {}

    // OUR ONLY PUBLIC METHOD RETURNS THE CONNECTION
    public static function getConnection()
    {
        if (!self::$instance) self::$instance = new self();
        return self::$connection;
    }

    // CONSTRUCTOR RETURNS THE CONNECTION
    private function __construct()
    {
        self::$connection
        = new mysqli
        ( self::DB_HOST
        , self::DB_USER
        , self::DB_PASS
        , self::DB_NAME
        )
        ;
        if (self::$connection->connect_error)
        {
            trigger_error(self::$connection->connect_error, E_USER_ERROR);
        }
    }
}

$mysql1 = database::getConnection();
$mysql2 = database::getConnection();


// PROVE THAT THESE ARE THE SAME OBJECT http://php.net/manual/en/language.oop5.object-comparison.php
if ($mysql1 === $mysql2) echo 'EQUAL';

// SHOW THE OBJECT
var_dump($mysql1);

Open in new window

Best to all, ~Ray
0
Slick812Commented:
@ Ray_Paseur  but this question did not ask anything about Object Oriented Code, , , , and to you and me, that already know OOC, your "easier" may or may not be  true, but to someone that has no experience in Object Oriented Code, this is a real time consuming effort to learn the Object Oriented Code structures and usage and is anything but easy, it is sometimes overwhelming hard. But that's just my dumb opinion. My comment was NOT degrading Object Oriented Code at all, but simply try to have a minimal learning curve for an important and necessary transition to the new MYSQLI database usage.


shafer23, shafer23  PLEASE PLEASE, do not let the Experts Conversations about OUR OPINIONS , keep you from asking further questions here to solve your problem ! !
0
Ray PaseurCommented:
@Slick812:  I understand and that's why I wrote the article.  This is not about object-oriented design, which is an advanced topic in computer science.  It's only about object-oriented notation, which anyone can pick up in a moment of study.  The differences are minimal and with a short example you can readily see that the OOP notation uses fewer quotes and apostrophes, making it easier to write and less subject to the needs for escaping.  Example: You cannot use array notation in HEREDOC templates unless you add curly-bracket escape characters, but OOP notation fits right in without any fiddly punctuation.

The ideas behind OOP notation don't get any more complicated than this example until you start creating your own objects with methods, and then the OOP concepts have to be learned.  But at this level, all you need to do is use the OOP equivalent notation to use objects in the familiar way that we all use arrays.

Why would you want to fetch the rows of a query results set in the form of objects?  Because, first, the notation is easier to write and less prone to parse errors.  And someday you may want to instantiate your own object for each row of results.  If you're still using the fetch_array() functions, you've precluded yourself from an optional but very powerful programming technique!

See http://www.laprbass.com/RAY_oop_example_3.php

<?php // RAY_oop_example_3.php
error_reporting(E_ALL);
echo "<pre>" . PHP_EOL; // MAKE IT EASY TO READ THE OUTPUT


// COMPARE OBJECTS TO ARRAYS
// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28271374.html#a39585046
// MAN PAGE http://php.net/manual/en/language.oop5.php


// FIRST THE ARRAY
$arr = array();
$arr['One'] = 'Array One';
$arr['Two'] = 'Array Two';

// THEN THE OBJECT
$obj = new stdClass;
$obj->One = 'Object One';
$obj->Two = 'Object Two';

// PRINT THEM BOTH
print_r($arr);
print_r($obj);

// ITERATE OVER THEM
foreach ($arr as $key => $val)
{
    echo PHP_EOL . "ARRAY KEY $key POINTS TO $val";
}
echo PHP_EOL;

foreach ($obj as $key => $val)
{
    echo PHP_EOL . "OBJECT PROPERTY $key POINTS TO $val";
}
echo PHP_EOL;

// REMOVE A DATA ELEMENT
unset($arr['One']);
unset($obj->One);

// SHOW THE FINAL POSITION
var_dump($arr, $obj);

Open in new window

0
shafer23Author Commented:
Ray - excellent write-up.  I am going to follow your recommendation and use the OOP notation.  Thanks!  Also, thanks everyone else for all of your comments, they are very helpful.

Best regards,
Pete
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.