merging 2 queries in SQL

Question

I have 40k records in my Database. i have written 2 SQL queries to achieve some result ! Unfortunately its taking to much time to run and server giving timeout error !

i am new to SQL so can any one help to merging my queries so that it will take less time to execute !

here is my code

$sql="SELECT Website, COUNT(*) FROM entry GROUP BY Website HAVING COUNT(*) = 2 "; 
        $run = mysql_query($sql);
        while($row=mysql_fetch_array($run))
        {
              $Website = $row['Website']; 
              $sql2 = "SELECT * FROM entry where Website= '$Website' GROUP BY Address ";
              $run2 = mysql_query($sql2);
              while($row=mysql_fetch_array($run2))
              {
                $id = $row['id'];
                $Website = $row['Website'];
                $Name = $row['Name'];
                $Address = $row['Address'];
                $Country = $row['Country'];
                $City = $row['City'];
              }  
         }

Suggest me !

Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Feb 06 '20 at 08:33

score 1 · Answer 1 · answered Feb 06 '20 at 05:29

1

One key rule when you run SQL queries on server side (PHP) is that you should never execute a SQL query inside a loop. What happens is that for each iteration of the loop the server connects and fetches data from the database. This increases the Input Output Operations on the database resulting in the slow fetching of data which could even lead to server down if the data size is huge.

Consider using proper query and sort out things from the query itself and avoid using PHP for that.

$sql2 = "SELECT * FROM entry where Website in (SELECT Website FROM entry GROUP BY Website HAVING COUNT(*) = 2) ";

Don't worry this is how everyone learns. Happy Coding!

answered Feb 06 '20 at 05:29

Nithin Suresh

360
1
7

Yea but when i run this in Phpmyadmin i am getting error --->Error SQL query: Documentation SELECT * FROM entry where Website in (SELECT Website FROM entry GROUP BY Website HAVING COUNT(*) = 2) LIMIT 0, 25 MySQL said: Documentation #2013 - Lost connection to MySQL server during query – – manikanta k j gowda Feb 06 '20 at 05:45
Since I'm not aware of what's in the entry table, I may not be able to provide the right solution. But I guess you must try to replace the * from the query and put something that is more accurate. This could solve the issue and even make the query faster. Using * is not a professional approach unless you really want all the columns. – Nithin Suresh Feb 06 '20 at 16:13

score 0 · Answer 2 · answered Feb 06 '20 at 06:14

0

You can work with this. use in into where condition.

$sql="SELECT * FROM entry where Website in (SELECT Website FROM entry GROUP BY Website HAVING COUNT(*) = 2) "; 
        $run = mysql_query($sql);
        while($row=mysql_fetch_array($run))
        {
                $id = $row['id'];
                $Website = $row['Website'];
                $Name = $row['Name'];
                $Address = $row['Address'];
                $Country = $row['Country'];
                $City = $row['City'];              
         }

answered Feb 06 '20 at 06:14

Tonmoy pramanik

21

Yea but when i run this in Phpmyadmin i am getting error --->Error SQL query: Documentation SELECT * FROM entry where Website in (SELECT Website FROM entry GROUP BY Website HAVING COUNT(*) = 2) LIMIT 0, 25 MySQL said: Documentation #2013 - Lost connection to MySQL server during query – – – manikanta k j gowda Feb 06 '20 at 06:36

merging 2 queries in SQL

2 Answers2