PHP MySQL connections

Question

Is it possible to have two types of MySQL connection on a PHP page?

Currently there is a $link = mysql_connect and a $mysqli = new mysqli connections that are accessed via two seperate include files in PHP.

They both pull data down from a MySQL database but if they are both in the same PHP page, the second connection doesn't work.

Am I missing something really obvious?

mysql

$link = mysql_connect("localhost", "root", "root", "abc");
if(!$link)
{
    die('There was a problem connection to the database.  Please contact your survey administrator.');
}

mysql_select_db("root");

$query = "SELECT * FROM tresults";
$result = mysql_query($query);
$total = mysql_num_rows($result);

$query1 = "SELECT * FROM trespondent";
$result1 = mysql_query($query1);
$total1 = mysql_num_rows($result1) - 1;

    $percent = number_format(($total * 100) / $total1);

mysql_close($link);

}

mysqli

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


/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$a = 'q';
$aa = 1;
$r = "$a$aa";
$q = 55;

while($aa <= $q){
$query  .= "SELECT COUNT(". $r .") as Responses, (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = -1 ) as NA, (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 1 ) as SD, (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 2 ) as D, (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 3 ) as A, (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 4 ) as SA, ((SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 3 ) + (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 4)) as Pos, ((SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 1 ) + (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 2)) as Neg, (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." >= 1) AS Total, ( ((SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 3 ) + (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." = 4)) / (SELECT COUNT(". $r .") FROM tresults WHERE ". $r ." >= -1)  ) *100 AS percentage FROM tresults;";
$aa = $aa + 1;
$r = "$a$aa";

NOTE: The rest of the code and also the close is handled within the PHP page using: $mysqli->close();

if ($mysqli->multi_query($query)) {
                                $n = 0;
                                do {
                                    /* store first result set */

                                    if ($result = $mysqli->store_result()) {
                                        $i = 1;
                                        $p = 1;

                                        while ($row = $result->fetch_row()) {
                                            // print_r($row);
                                            $n++;

Answer 1

yes, you can.

<?php
error_reporting(-1);

$l1 = mysql_connect('127.0.0.1', 'webrunner', 'XXXXXXXXXXX');
mysql_select_db('iceline', $l1);
$l2 = new mysqli('127.0.0.1', 'webrunner', 'XXXXXXXXX', 'iceline' );

var_dump($l1, $l2);

var_dump( mysql_query("select sysdate() from dual", $l1));
var_dump($l2->query("select sysdate() from dual"));

have you looked at the "new link flag" ? You can have several connections to the same DB, as long as you set the new connection each time.

Obviously the code structure is less than ideal.

This answer clashes with the response from @touch. If you posted more about the faulty code, this may help.

Answer 2

Ok. I am not really sure what's the problem you are having so I tried to recreate the situation on windows and my code worked using both connections in the same script.

Here is my table.

Here is the code I ran

<?php

/**
 *     I created a table in test (database). The table has a simple name and
 *     age. 
 *
 */

$mysql = mysql_connect('localhost','root','');
$mysqli = mysqli_connect('localhost','root','');

if (!$mysql || !$mysqli) {
   die( 'Sorry, one of them the is not working');
} else {
   echo 'Both connections are a success<hr>';
}

//Test the $mysql
echo '<h3>mysql_connect</h3>';
mysql_select_db('test',$mysql);
$query = 'SELECT * from names';
$results = mysql_query($query,$mysql);

$data1 = array();
while ($row = mysql_fetch_assoc($results)) {
   $data1[] = $row; 
}

mysql_close($mysql);

print_r($data1);

echo '<hr>';
echo '<h3>mysqli_connect</h3>';
//Test the $mysqli
mysqli_select_db($mysqli,'test');
$query2 = 'SELECT * from names';
$results2 = mysqli_query($mysqli,$query2);

$data2 = array();
while ($row = mysqli_fetch_assoc($results2)) {
   $data2[] = $row; 
}

print_r($data2);
mysqli_close($mysqli);
?>

Here is my ouput:

I was able to use both types of connections in one script. I don't know if that is what you were asking but mine probably worked because when I know that I have more than one connection in a script, I tell php which one to use.

Is it possible to have two types of MySQL connection on a PHP page?

I just did it. Unless there is something I'm not getting from you question.

I don't know if this answers your question, but if not, I'll gladly remove it. I use PDO by the way.

Answer 3

Did you close the first connection after calling the second one?

mysql_close($link);

Nevertheless it's not really a good example to use 2 different connections on 1 page. Do you have a specific reason to use it like that?

Read more: link