08-06-2008, 04:43 PM
I have two tables, articles (general info) and article_occurance (dates). A 1 to many relationship.
I would like to display all the rows in the articles table as a list but in addition I need to manipulate the associated records in article_occurance and display the word lapsed if the event has passed.
So I decided to use left join, the problem is that this lists all the info from articles and and associated records in article_occurance as a single list. It needs re-ordering and I don't want to echo any info from article_occurance.
$sql = "SELECT * FROM articles LEFT JOIN article_occur ON article_occur.id_article=articles.id_article ";
This is how I think it should work:
1. list info from table articles.
2. does articles have an associated record(s) / foriegn key in article_occurance?
3. if yes, process and print info from article_occurance
4. if no, continue listing article rows, (back to step 1)
It's like a loop within a loop.. or there may be an easier way..
Help kindly appreciated.:eek:
08-06-2008, 05:12 PM
LEFT JOIN should do this for you, but for each instance of the right table the data from the left will repeated. You also need to chose what you want to fetch: $sql = "SELECT a.*, ao.* FROM articles a LEFT JOIN article_occur ao ON ao.id_article=a.id_article ";. Gotta be careful though, since you need to control overwrite fields (any corresponding values will throw an error, so a* and ao.* won't work as you're choices).
This is where you need to decide how to balance it out and do what you want for work.
You can break it into two queries:
$osSQL = "SELECT * FROM articles";
$oQry = mysql_query($osSQL) || die(mysql_error());
while ($oRecord = mysql_fetch_assoc($oQry))
$isSQL = "SELECT * FROM article_occur WHERE id_article='" . $oRecord['id_article'] . "'";
$iQry = mysql_query($isSQL) || die("Inner query: " . mysql_error());
while($iRecord = mysql_fetch_assoc($iQry))
Another option that should work is a corelated nested subquery.
Its always a tough choice between what you do versus what sql should do. Using the joined tables is definitely possible, just create a multidimensional array for storing results, and index them with the article title and an inner array containing each associated article_occur. This is where datacollections shine.
08-07-2008, 10:31 AM
hi Fou - Lu
Thanks for your code. I don't understand the first bit but for now
I think the best and easiest way is how you've described.
Only problem is is this a bit inefficient, as I need to run a sub-query for every record in the outer-query?
Also, originally I had only one table, that contained all article info including date fields (6) e.g. publish1, unpublish1, publish2, unpublish2 etc but then I was limiting myself to the fields of the dates ( I could only have a maximum of occurances based on the pre-programmed date fields) and the sql was complex.
So I was to advised to dump this idea and normalise the database, this then created two tables, one for the article the other for date pairs as this would give me unlimited occurances. (in reallity I will only ever use a maximum of 8 date pairs), do you think it's worth the two table approach as it's caused so many programming problems?
Hope you can answer
08-07-2008, 04:08 PM
Don't let the appearance of it fool you. Consider what a table joined query does, foreach record in the primary table it also performs a lookup on the associated table. Though SQL is optimized for this since it does index, it follows a similar logic flow for fetching its data. It would still be faster overall to do a join, but minimally. The actual SQL is less efficient, you're looking at about 2x the time to select a joined query, but as you mentioned there is only a single loop instead of two logarithmic loops. This is why they approximately balance out.
Database normalization is the single most efficient way to optimize you're programs, so I would also recommend doing it. I've somehow absorbed an n-tier style programming into my PHP lately, so my selections rarely contain joins since each table has an associated PHP object. Overhead does exist compared to my older code, but the control I've gained makes it worthwhile.
As for the first part, I was referring to you're original query. The problem is if you just select * on a joined query, it should only produce results relating to the primary tables. This is another reason why you will see often in the forums here that you are recommended to search only for specific fields:
SELECT a.title, a.message, ao.publishdate, ao.unpublishdate FROM articles a LEFT JOIN article_occurance ao ON ao.articleid = a.articleid for example. I'm not exactly sure what you're fields contain, so this is unlikely to work.
The first step is to change you're query around to fetch all of the data you need. It will repeat it however, but thats not a problem. Once you've gotten all of the data you require, you then use PHP to determine how to combine these into an array (using the id is probably a good index choice). The pain comes because each of you're desired records is split across multiple rows - hence the breakdown in my first post for simplicity.
Personally, one to many or many to many style records I will break down using the two loops and take the hit on the overhead. Simplicity IMO beats any overcomplication.
I hope that helps to clear up a couple of things!