MySQL Forums :: PHP :: Showing recordsets from tables with one (main table) to many (secondary table) relationship


Advanced Search

Showing recordsets from tables with one (main table) to many (secondary table) relationship
Posted by: Kostas Telias ()
Date: December 30, 2010 07:35AM

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.

Options: ReplyQuote


Subject Written By Posted
Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 12/30/2010 07:35AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 12/31/2010 08:18PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/06/2011 12:32PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/06/2011 09:00PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/11/2011 08:34AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/11/2011 11:13AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/11/2011 12:45PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/11/2011 07:55PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/12/2011 07:49AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/12/2011 11:01AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/12/2011 02:23PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/12/2011 08:22PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/13/2011 04:28AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/13/2011 11:37AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/13/2011 01:11PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/13/2011 07:44PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/14/2011 08:54AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/15/2011 12:28AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/15/2011 01:18PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/16/2011 02:28PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/16/2011 06:59PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/16/2011 11:41PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/17/2011 07:49AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/17/2011 06:27PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/18/2011 04:30AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/18/2011 09:34AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/19/2011 10:32AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Rick James 01/20/2011 10:17PM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/22/2011 10:10AM
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship Kostas Telias 01/18/2011 04:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.