query statements

[chiprivers]

Is it good practice to place database query statements within a loop, this resulting in many queries within a script just to pull one record at a time?  This may seem like a strange question but I am working on quite a complex script that I am trying to structure my database for and the simplest way to structure the data is in several different tables.  This resulting in the possibility of querying data from a different table on each occurence in a loop depending on other input factors.

Is this the norm or should it be avoided?

[Mattyspatty]

well i use this method alot to read everything in a table

[code]
$query = mysql_query("SELECT `username` FROM `User_Table`");
while($row = mysql_fetch_array) {
    echo $row['username'] . "<br/>";
}
[/code]

which would print out the username in each row, within the loop you [b]could [/b] do another query (but remember to use different names to $row, and $query. Such as $row2) but i guess you could just inner join

[trq]

[quote]Is it good practice to place database query statements within a loop[/quote]

No it is NOT good practice to execute database queries within loops, however, just because your data is stored in multiple tabes does not meen you need to do so.

Take a look at using JOINS in your sql statements. Joins enable you to pull data from multiple tables in one query.

[heckenschutze]

^ True..

Doing such can lead to query timeouts, memory bloating, ect... And for example, what happend if a value in your MySQL table changed while you were running throught the loop inserting values, or somesuch ? :)

[chiprivers]

Maybe if I explain further what I am trying to do, you might be able to suggest an alternative.  I am building a script to store and display the results of my research into my family history.  On one of the display pages I want to list events that have occurred for an individual, ie their birth, marriage, death etc.

I was thinking that I would have a table that stores basic name details for individuals, a table that stores basic life event details and then tables for each type of life event and their relevent details.

I was then planning to query the basic life event table to find records for the selected individual, then loop through the results and for each life event, query the relevant table to find the information related to that event.

Is this too much querying throughout the script and in loops?  Is there an alternative without creating a table that has a lot of irrelevant fields for each record?

[heckenschutze]

In a case that small it would be fine, since mysql_query is not asynchronous (since it waits for the query to execute and return before giving a return value... /same thread :))...

Doing what your doing is fine in other words...

So, Have a table for individuals, a table for basic life event details... ect... :)

[Mattyspatty]

ah, i misunderstood your question, i know what you are trying to do now. take a look at this, it should tell you what you need

[url=http://www.w3schools.com/sql/sql_join.asp]http://www.w3schools.com/sql/sql_join.asp[/url]