Pagination from two SQL Tables with one query. Fastest Way

Question

I have a simple Database:

Table 1: Object                    Table 2: Data
id | type | added                  object_id | key     | value
------------------------           -----------------------------
1  | app  | 2017                   1         | name     | ...
2  | app  | 2017                   2         | name     | ...
3  | win  | 2017                   2         | version  | ...
                                   2         | dev_name | ...
                                   2         | lang     | ...

i created a simple pagination to show only 5 object from type apps with their infomation from the data table:

example code:
$sth = $dbh->prepare("SELECT * FROM object WHERE type = 'app' LIMIT 5");
$sth->execute;
$object = $sth->fetchAll(PDO::FETCH_ASSOC);

foreach($object as $rows) {
    $sth = $dbh->prepare("SELECT * FROM data WHERE id = $rows['id']");
    $sth->execute;
    $data = $sth->fetchAll(PDO::FETCH_ASSOC);

    echo $data['name'];
    echo $data['version'];
    echo $object['added'];
    ...

Is there a better way? because this is a very bad solution, i want to query only one time where i get an fetch array like this:

array(
       // app with id = 1 in object table
       [1] => array(
                     // data from the app with id = 1 in data table
                     [name] => ...
                     [version] => ...
                     [dev_name] => ...
                     [lang] => ...
       // app with id = 2 in object table
       [2] => array(
                     // data from the app with id = 2 in data table
                     [name] => ...
                     [version] => ...
                     [dev_name] => ...
                     [lang] => ...
...

JMTA · Answer 1 · 2017-11-01T13:10:25.103

0

one query:

SELECT * FROM object inner join data on data.id_object=object.id WHERE object.type = 'app' LIMIT 5

This query return information from two table with associate data. I think that's what you want

If you want to group by id of the first table you can also execute this query

SELECT * FROM object inner join data on data.id_object=object.id WHERE object.type = 'app' group by object.id LIMIT 5

or

SELECT Distinct(object.id), object.type, .... FROM object inner join data on data.id_object=object.id WHERE object.type = 'app'  LIMIT 5

Consider for string comparison use LIKE instead of =

edited Nov 01 '17 at 13:10

answered Nov 01 '17 at 13:02

JMTA

31
3

this give me an array where every data row has an object id and a object create = array( [0] => array([id] => 2 [type] => app [key] => 2 [name] => ... [1] => array([id] => 2 [type] => app [key] => 2 [lang] => ... ) this is not what i wanted. – andreaslacza Nov 01 '17 at 13:59
and i will only LIMIT the object id's to print out like " **app** (with id 1) has the **name** and **date** from developer **dev_name**" - " **app** (with id 2) has the **name** and **date** from developer **dev_name**" – andreaslacza Nov 01 '17 at 14:17
I had not understood the question. probably, you need the sub-query. see also this, you may need. https://stackoverflow.com/questions/28145064/sql-limit-by-id-column – JMTA Nov 01 '17 at 22:29

Pagination from two SQL Tables with one query. Fastest Way

1 Answers1