CODEWITHSUNDEEP
phpmysqlsqlselectpagination
amir.shariflou
amir.shariflou

Reputation: 26

how to select all table in database

I have Two hundred table in database and want to search all of them at the time I choose to use the pagination Unfortunately, encountered the error

database:demo

table:sura1,sura2,...,sura200

Code:

 $i=0;
 while($i<=200){

 $constructs ="SELECT * FROM sura$i WHERE message  like '%$keywords%'";

 $run = mysql_query($constructs);

  $foundnum = mysql_num_rows($run);
 $i++;
    }   

if ($foundnum==0)
echo "Sorry";

else
{ 

echo "$foundnum results found !<p>";

$per_page = 5;
if(isset($_GET['start'])){$start = $_GET['start'];}else{$start=0;}

$max_pages = ceil($foundnum / $per_page);
if(!$start)
$start=0; 

 $i=0;
 while($i<=200){
 $getquery = mysql_query("SELECT * FROM sura$i WHERE LIMIT $start, $per_page");
 $i++;
 }
while($runrows = mysql_fetch_assoc($getquery))
{
$title = $runrows ['title'];
$desc = $runrows ['name'];
$url = $runrows ['id'];

echo "
<a href='$url'><b>$title</b></a><br>
$desc<br>
<a href='$url'>$url</a><p>
";

}

Upvotes: 0

Views: 124

Answers (3)

Bill Province
Bill Province

Reputation: 634

So a couple comments here:
First, you typically need to have a process that runs multiple threads. The intent, I suspect for the partitioned design is simply to keep each table small so that you can benefit from parallelism. To properly take advantage of this, you need to invoke the queries in parallel, and that means having multiple threads.

The script you are using -- and I could be wrong, because I am not really sure which language you are using -- does not appear to have any semantics in place for multi-threading. (If you are using bash or similar UNIX shell, then look for documentation for 'fork').

Second, if for whatever reason, parallel invocation is not an option, consider invoking the queries in chunks of 10 tables as follows:

$i = 1;
$k = 1;
$foundnum = 0;
while($i<=20){
   $j=1;
   while($j<=10) {
      $sql1 = "SELECT * FROM sura$k where message like '%keywords%'";
      $k++;
      $sql2 = "SELECT * FROM sura$k where message like '%keywords%'";
      $k++;
        ...
      $k++;
      $sql10 = "SELECT * FROM sura$k where message like '%keywords%';
      sql_chunk = "$sql1 UNION ALL $sql2 UNION ALL ... $sql9 UNION ALL $sql10";
      $run = mysql_query($sql_chunk);
      $foundnum += mysql_num_rows($run);
      $j++;
   }
   // add logic to control pagination display here...
   $i++;
}

You will probably need to control the pagination logic on your own, but the basic idea is as I indicate above. Notice that you are incorporating two ideas here: first, the idea suggested by jeroen, and second, the idea of using UNION operations to perform parallelism. In this case, we are using the SQL engine itself to do the parallel execution and threading.

Good luck!

Upvotes: 0

developerwjk
developerwjk

Reputation: 8659

You have 200 tables all of the same structure, right? So just turn them into one table:

create table all_suras like sura1;
alter table all_suras add sura_number int;
insert into all_suras select *, 1 from sura1;
insert into all_suras select *, 2 from sura2;
insert into all_suras select *, 3 from sura3;
...
insert into all_suras select *, 200 from sura200;

Now you have them all in one table. And you can look them up by the new field sura_number. Enjoy.

Upvotes: 0

jeroen
jeroen

Reputation: 91734

If you really cannot avoid using a huge amount of tables and queries, you should fix the way you count.

Initialize the counter before the loop and increase it in the loop:

$foundnum = 0;
while($i<=200){

  $constructs ="SELECT * FROM sura$i WHERE message  like '%$keywords%'";

  $run = mysql_query($constructs);

  $foundnum += mysql_num_rows($run);
  $i++;
}

But 400 queries for a single search results page is a really bad idea, not to mention that you cannot order your pages and your pagination is not going to work as you will have 200 times the desired number of results on one page.

Upvotes: 1

Related Questions