Showing recordsets from tables with one (main table) to many (secondary table) relationship

[Kostas Telias]

Hello, in a web page in php where main records are retrieved from a main table (tblinvoices) with:

$resultInvoices=$conn->query("SELECT * FROM tblinvoices ORDER BY date");
$resultInvoices->data_seek($_SESSION['pointer']);
$rowInvoices=$resultInvoices->fetch_row();

so, one invoice is shown in one page, and two buttons "Previous" and "Next" are included in the page for browsing through the main records.

But for each invoice, it's necessary to show its articles, which are stored in the database on another table: tblarticles, which is related with the previously mentioned table by a one-to-many relationship (tblinvoices is in side "one" and tblarticles is in side "many").

I only see a way of doing that:

$resultInvoices=$conn->query("SELECT * FROM tblinvoices ORDER BY date");
$resultInvoices->data_seek($_SESSION['pointer']);
$rowInvoices=$resultInvoices->fetch_row();

(... print all invoice data except articles...)

$resultArticles=$conn->query("SELECT * FROM tblarticles WHERE idinvoice=$rowInvoices[0] ORDER BY order");
while ($rowArticles=$resultArticles->fetch_row()){
printf(
"<span class='article'>%s %s %s</span><br>",
$rowArticles[1],$rowArticles[2],$rowArticles[3]
);

The whole proccess requires two queries to the database. Does anybody know if there's another more optimized way of doing that?

Thank you.