Two connections to the same database

anon80411031 · April 10, 2011, 1:13pm

I have a problem making two different nested SQL queries. While I’m echoing the results of the first one I would like to execute a second query based on some condition. The problem is that, when I execute the second query, the first one stops and the rest of the while loop is skipped.

Here is how the code looks like:


$sql = "SELECT * FROM table1 WHERE conditions";	
$result = mysqli_query($conn,$sql);
while ($row = mysqli_fetch_array($result)) {
    .
    .
    .
        echo '<td>' . $row['column1'] . '</td>';
        .
        .
        .
        if (a condition is true) {
	    echo '<td>' . $row['column_k'] . '</td>';
	} else {
	    $x = $row['column_k'];
	    $conn1 = mysqli_connect('localhost', 'root', 'root', 'database'); // the same database of $conn
	    $sql1 = "SELECT name
	    FROM table2
	    WHERE boss = '$x'";
	    $result1 = mysqli_query($conn1,$sql1);
	    while ($row = mysqli_fetch_array($result1)) {
		$boss = $row['boss'];
	    }
	    echo '<td>Some different text here ' . $boss . '</td>';
	}
        echo '<td>' . $row['column_k+1'] . '</td>';
        .
        .
        .
	echo ...				
} //while*/

When the condition is true everything works correctly: everything after the if then else statement is correctly echoed.
The problem appears when the condition is false (when the second query is executed). Everything works correctly until the if then else statement, the last echo instruction that works is this one:


echo '<td>Some different text here ' . $boss . '</td>';

All the other echo instruction are not executed. This makes me think that there is a problem with how I set up the database connections, somehow the second one interferes with the first one. At least this is what I think, but I can’t understand how to fix this.

centered_effect · April 10, 2011, 1:50pm

(I didn’t look at the code)
Why do you need to reconnect to the DB?
Also can you user 1 query instead of a query within a loop? Why not use a JOIN?

rpkamp · April 10, 2011, 1:58pm

The problem is that you’re using $row in both the outer loop and the inner loop. So in the first run of the inner loop the result of the outer loop is overwritten.

To fix this, use something like $innerRow, or $row2, or something for the inner loop


// ..
while ($row=mysql_fetch_assoc($res))
{
   $res2=some_other_query_related_to_row();
   while ($row2=mysql_fetch_assoc($res2))
   {
      // ...
   }
}

But, as centered effect points out, using a JOIN to grab everything at once would (probably, depends on the query) be better!

anon80411031 · April 10, 2011, 3:14pm

Yes, it’s probably a better solution, I didn’t think about that

rpkamp:

The problem is that you’re using $row in both the outer loop and the inner loop. So in the first run of the inner loop the result of the outer loop is overwritten.

To fix this, use something like $innerRow, or $row2, or something for the inner loop
// ..
while ($row=mysql_fetch_assoc($res))
{
   $res2=some_other_query_related_to_row();
   while ($row2=mysql_fetch_assoc($res2))
   {
      // ...
   }
}
But, as centered effect points out, using a JOIN to grab everything at once would (probably, depends on the query) be better!

That was the problem indeed. Thank you very much!

cranial_bore · April 10, 2011, 10:42pm

Don’t drink drain cleaner, don’t poke a lion with a stick and don’t nest your queries!

I’m sure a basic JOIN could solve your problem. Can you post your table structure and the data you want (use SHOW CREATE TABLE your_table_name to get the SQL)

anon80411031 · April 12, 2011, 2:29pm

My mistake, I had never heard of the last one

I’m sure a basic JOIN could solve your problem. Can you post your table structure and the data you want (use SHOW CREATE TABLE your_table_name to get the SQL)

Sure, this is the first table:


CREATE TABLE `appuntamenti` (
 `nome` varchar(255) NOT NULL,
 `tipo` varchar(12) NOT NULL,
 `indirizzo` varchar(255) NOT NULL,
 `cf` varchar(20) NOT NULL,
 `piva` varchar(20) NOT NULL,
 `referente` varchar(255) NOT NULL,
 `telefono` varchar(12) NOT NULL,
 `telefono2` varchar(12) NOT NULL,
 `referente2` varchar(255) NOT NULL,
 `telefono3` varchar(12) NOT NULL,
 `telefono4` varchar(12) NOT NULL,
 `via` varchar(255) NOT NULL,
 `locale` varchar(10) NOT NULL,
 `comune` varchar(255) NOT NULL,
 `provincia` varchar(2) NOT NULL,
 `cap` int(5) NOT NULL,
 `regione` varchar(255) NOT NULL,
 `data` date NOT NULL,
 `ora` varchar(5) NOT NULL,
 `datafissaggio` date NOT NULL,
 `organizzatore` varchar(255) NOT NULL,
 `relatore` varchar(255) NOT NULL,
 `relatoreinaffiancamento` varchar(255) NOT NULL,
 `omaggistica` text NOT NULL,
 `numomaggi` int(11) NOT NULL,
 `coppie` int(11) NOT NULL,
 `persone` int(2) NOT NULL,
 `contributoacoppia` decimal(10,2) NOT NULL,
 `contributoserata` decimal(10,2) NOT NULL,
 `contributototale` decimal(10,2) NOT NULL,
 `pagamento` varchar(255) NOT NULL,
 `postdatato` decimal(10,2) NOT NULL,
 `storno` decimal(10,2) NOT NULL,
 `iban` varchar(50) NOT NULL,
 `beneficiario` varchar(255) NOT NULL,
 `numordini` int(2) NOT NULL,
 `affarilordo` decimal(10,2) NOT NULL,
 `imponibile` decimal(10,2) NOT NULL,
 `note` text NOT NULL,
 `privilegio` int(11) NOT NULL,
 `responsabile` varchar(255) NOT NULL,
 `datainserimento` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `modificato_relatore` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And this is the second one:


CREATE TABLE `collaboratori` (
 `responsabile` varchar(255) NOT NULL,
 `collaboratore` varchar(255) NOT NULL,
 PRIMARY KEY (`responsabile`,`collaboratore`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The data that I fetch in the second database connection in my code is the field ‘responsabile’ from the second table.

cranial_bore · April 12, 2011, 11:26pm

The data that I fetch in the second database connection in my code is the field ‘responsabile’ from the second table.

What is the field in the appuntamenti table that you use to relate the records?
I gather this is supposed to be a 1:Many relationship, where 1 record in appuntamenti can relate to many (or none) records in the collaboratori table?

anon80411031 · April 13, 2011, 11:34am

No, every row in appuntamenti can relate to only one row in collaboratori. The fields that should be connected are organizzatore in the appuntamenti table and collaboratore in the collaboratori table. What I want is the value of the field responsabile

cranial_bore · April 13, 2011, 11:52am


SELECT a.nome, a.locale, a.organizzatore, c.responsabile
FROM appuntamenti a LEFT JOIN collaboratori c 
ON a.organizzatore = c.collaboratore 
WHERE a.nome = 'Your Value'

Change the WHERE condition to your needs. This will return the rows that match your conditions, and if there is a corresponding row in collaboratori it’s values will be in the result set.
a and c are alias for the full table names to make the query more compact.

You should also define an index on appuntamenti.organizzatore and collaboratori.collaboratore otherwise MySQL will have to do full table scans to run this query. And if those fields don’t need to be 255 chars long, you should define them with shorter lengths to keep the index size smaller.

anon80411031 · April 13, 2011, 4:34pm

First of all, thank you very much for your help

But is a JOIN much more efficient than two different queries?

StarLion · April 13, 2011, 6:13pm

Yes. JOIN queries execute a lot faster than individual queries (and faster than WHERE clause ‘joins’ too). It’s definitely worth your time to learn about joins if you’re going to be doing database work.

anon80411031 · April 13, 2011, 6:29pm

I know how JOINS work, the fact is just that in the code I am working on I didn’t think it was the best solution. Let me show you a schema of the code:


if (condition 1) {
    // execute query 1
} else if ... {
    . . .
} else {
    // execute query n
}

$result = mysqli_query($conn,$sql);
while ($row = mysqli_fetch_array($result)) {
    echo $row['field1'];
    echo $row['field2'];
    if (a condition about $row['field3'] is true) {
        // echo the information stored in the table collaboratori
    } else {
        echo $row['field3'];
    }
    . . .
    echo $row['fieldn'];
}

I thought that since I need the information stored in the table collaboratori just if a particular condition on the set of results holds, I was better off not doing useless JOINS in all the queries.

I hope you understand what I mean

StarLion · April 13, 2011, 6:45pm

Well lets take into consideration this:
If i can take…


        echo '<td>' . $row['column1'] . '</td>';
        .
        .
        .
        if (a condition is true) {
	    echo '<td>' . $row['column_k'] . '</td>';
	} else {
	    $x = $row['column_k'];
	    $conn1 = mysqli_connect('localhost', 'root', 'root', 'database'); // the same database of $conn
	    $sql1 = "SELECT name
	    FROM table2
	    WHERE boss = '$x'";
	    $result1 = mysqli_query($conn1,$sql1);
	    while ($row = mysqli_fetch_array($result1)) {
		$boss = $row['boss'];
	    }
	    echo '<td>Some different text here ' . $boss . '</td>';
	}
        echo '<td>' . $row['column_k+1'] . '</td>';

from you, and reduce it to


while($row = mysql_query($sql)) {
echo "<tr>";
foreach($row AS $value) {
echo "<td>".$value."</td>";
}
echo "</tr>";

I’ve improved the readability of the code by a factor of N. Even if I have to throw a special case…


while($row = mysql_query($sql)) {

$row['column_k'] = ((Test) ? $row['column_k'] : "Some Text".$row['boss']);
unset($row['boss']);
echo "<tr>";
foreach($row AS $key => $value) {
echo "<td>".$value."</td>";
}
echo "</tr>";
}

anon80411031 · April 13, 2011, 6:56pm

You are definitely right

Again, I just though (don’t ask me why ) that it was better to have one extra database connection in a single occasion than performing JOINS everywhere and not using that extra information.

And btw, it looks like I am the only one that is not able to improve the readability of my code asd.

StarLion · April 13, 2011, 7:13pm

No, it’s just a question of learning the tricks to make things easier for yourself; You certainly COULD write each line out separately, there’s nothing against the syntax of doing that. It’s just a lot more writing for you.

It’s also why these forums exist

It is entirely possible, by the way, to do it in 2 queries instead of 1+N, if you were that concerned about the bandwidth. (SELECT… WHERE … name IN(…))

anon80411031 · April 13, 2011, 7:54pm

What do you mean with this?

StarLion · April 13, 2011, 7:59pm

Okay; First and foremost. JOIN is the best solution.

But if you absolutely refused to do a join…
Query 1: Select table 1 without the join.
Use PHP to collect a list of names/ids/whatever you need the extra information for
Query 2: Select table 2 for those names using MySQL’s in-list where commands (or a whole bunch of OR’s).
Use PHP to update the data where needed.
Output data.

It’s not as efficient as doing it in one query, but it gets it done in 2 instead of 1+N (N = the number of people you need the extra information for)

anon80411031 · April 13, 2011, 8:07pm

Ahaha no, I don’t have any reason to

Thanks for your help!

cranial_bore · April 13, 2011, 9:51pm

Remember, originally you weren’t just executing 2 queries, you were executing (and attempting to connect again!) for every row found from the first query. It’s terrible coding practice, so the JOIN is the only option.

anon80411031 · April 13, 2011, 10:07pm

You mean for every row from the first query that satisfies the condition?

Topic		Replies	Views
Retrieve content from database - nested query problem PHP	6	1424	October 8, 2014
Running query two times Databases	3	1083	October 3, 2019
Problem with loops PHP	3	373	May 31, 2010
Mysql selecting data from two databases Databases	10	18445	May 21, 2010
Two queries in one Databases	3	575	February 2, 2011

Two connections to the same database

Related topics