CODEWITHSUNDEEP
phpmysql
southpaw93
southpaw93

Reputation: 1961

Retrieving records from multiple tables

So like the title suggests, I am trying to retrieve records from multiple tables. I have the following tables : ads1 , ads2 , ads3 ,.... each tables containes 1 or more records with auto_increment id and names of people under the column named names.

Let's say I have 10 records in each tables, and I have a pagination script that shows 5 records per page, that means 2 pages for each table, so I will have pages 1,2,3,4,5,6. My algorithm doesn't do it correctly, on my first page it shows 5 record from the first table, 5 records from the second table , 5 records from the third table and so on... but I only want 5 records to be shown per page , not 5 from each table that I have, I want them to be shown correctly on pages 1 and 2 records from ads1 , on pages 3 and 4 records from ads2 and so on, hope you get the idea. Can you please help me ?

here is my alogorithm :

for ($i=1;$i<=$kay;$i++)
{
    $counter = 0;
    $tablenow = 'ads'.$i;
    $result = mysql_query("SELECT id FROM ".$tablenow." ");

    $counter = $counter + mysql_num_rows($result);
    $x=ceil($counter / $per_page);
    $page = (isset ($_GET['page']) AND (int)$_GET['page'] > 0 AND (int)$_GET['page'] <= $x) ? (int)$_GET['page'] : 1;
    $start = ($page - 1) * $per_page;
    $sql = mysql_query("SELECT * FROM ".$tablenow." ORDER BY id DESC  LIMIT 
    $start,$per_page ")or die(mysql_error());
    while ($sqlg=mysql_fetch_assoc($sql))
    {
        // this is where I show the records.
    }
}

PS: For only one table , the algorithm works exactly as it should, but when I have 2 or more tables it stops working correctly.

Upvotes: 0

Views: 177

Answers (2)

David
David

Reputation: 741

Here's one way to do it:

$count_tables = 10;
$query        = '';
for ($table_no = 1; $table_no <= $count_tables; $table_no++ ) {
  $query .= "SELECT * FROM ads" . $table_no . " UNION ";
}
// Remove trailing UNION
$query = preg_replace( '/ UNION \Z/', '', $query );

$result     = mysql_query("SELECT id FROM ".$tablenow." ");
$total_rows = mysql_num_rows($result);
$max_page   = ceil( $total_rows / $per_page );

// Fetch rows for the page
$query_for_page .= $query . " ORDER BY id DESC LIMIT $start, $per_page";
$page = ( isset ($_GET['page'])
          AND (int)$_GET['page'] > 0
          AND (int)$_GET['page'] <= $max_page ) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $per_page;

$page_results = mysql_query( $query_for_page ) or die( mysql_error() );
while ( $row = mysql_fetch_assoc( $page_results ) ) {
  // this is where I show the records.
}

Hope this helps.

Upvotes: 2

Doug
Doug

Reputation: 6442

You have to execute only one query, which will be an UNION of the results of all desired tables. If you execute SELECTs in each table individually, you cannot track the total results in an accurate way.

Upvotes: 1

Related Questions