0

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++;
Homer_J
  • 3,277
  • 12
  • 45
  • 65
  • What doesn't work about it? Is there a specific error? You can't mix the `mysql_*` objects and the `mysqli_*` objects, they won't talk to each other effectively. But you can use both at the same time as long as you maintain the separation between them. One can retrieve its data and be used on the page, and the other can retrieve *its* data and be used on the page. (Though, of course, you *shouldn't* use `mysql_*`, but technically you *can*.) – David Aug 12 '13 at 22:24
  • Thanks David - basically the first connection `mysql_' fires and brings data back which is then populated in the PHP file. The issue is that the `mysqli` connection doesn't bring anything back if included on the same page. – Homer_J Aug 12 '13 at 22:25
  • Ok... That doesn't really describe any kind of problem. What's not working? Can you show the code that isn't working and explain how you observe the problem? – David Aug 12 '13 at 22:26
  • I observe the problem in two ways - when I place the code for the `mysqli` connection in a seperate PHP page - it works and brings data back. When I place the same code in the original page and remove the `mysql` include - it works. When I add the `mysql` back in the `mysqli` stops working. Make sense? – Homer_J Aug 12 '13 at 22:28
  • Instead of trying to describe the code, could you include it in the question? – David Aug 12 '13 at 22:30
  • Where are you using the `mysqli_*` connection? You build a query in `$query`, but you don't use it anywhere. Also, note that you seem to be *appending* a string to `$query` instead of overwriting it. If you do execute it, check any error coming back from the database. You might have two queries trying to act as one. – David Aug 12 '13 at 22:44
  • Apols David - added the other bit of code, which is executed within the original PHP page. It's a huge amount of code, just added the top part - this works, when either the `mysql` connected isn't running or it's placed in a different PHP page. – Homer_J Aug 12 '13 at 22:49
  • have you tried debugging the code? using [Xdebug](http://xdebug.org/) for example? – Songo Aug 12 '13 at 23:37
  • OFFTOPIC: Having read the second SQL statement, have you thought about storproc? – Owen Beresford Aug 14 '13 at 18:18

3 Answers3

1

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

user2362155
  • 15
  • 7
  • Yep, connection is closed `mysql_close($link);`. Agreed not ideal...legacy code. – Homer_J Aug 12 '13 at 22:27
  • [link](http://stackoverflow.com/a/274919/2362155) There is someone with the same problem which seems to be working with the answer – user2362155 Aug 12 '13 at 22:30
  • Thanks for replying - not sure that answer is relevant. It's the `same` database but being connected in two ways, one via `mysql` and one via `mysqli`. – Homer_J Aug 12 '13 at 22:43
  • @Homer_J: From the perspective of the code there's no difference. Two database connections are two database connections, regardless of whether or not they go to the same database. – David Aug 12 '13 at 22:48
  • OK, that makes sense - I still don't understand where I'm going wrong though. :-( – Homer_J Aug 12 '13 at 22:51
1

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.

enter image description here

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:

enter image description here

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.

Touch
  • 1,481
  • 10
  • 19
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.

Owen Beresford
  • 712
  • 4
  • 10