I have a question related to SQL queries and data manipulation. In some projects I have created I have several 1-to-many relationships. I am starting to think there is a better way to do it. Lets say we have these tables: accounts > account_id | account_name | account_password projects > project_id | account_id | project_name project_items > project_item_id | project_id | account_id | project_item_name Now. Lets say I wanted to display all the project items for account ID 1. I use to do this: $query1 = mysql_query("SELECT project_id,project_name FROM projects WHERE account_id = '1'"); while ($row = mysql_fetch_assoc($query1)) { echo "Project:" . $row['project_name'] . "<br/>"; // Get the project_items $query2 = mysql_query("SELECT project_item_name FROM project_items WHERE project_id = '{$row['project_id']}' AND account_id = '1'"); while ($row2 = mysql_fetch_assoc($query2)) { echo "Project Item:" . $row['project_item_name'] . "<br/>"; } } PHP: Yes, this works. It is a form of a subquery. I will get Project 1 - project_item 1 - project_item 2 Project 2 etc etc HOWEVER, it is very ineffecient. About a year ago I was introduced to JOINS The above can now be queried like this: $query = mysql_query("SELECT projects.project_name,project_items.project_item_name FROM projects LEFT JOIN project_items ON project_items.project_id = projects.project_id WHERE projects.account_id = '1'"); PHP: The database output looks like this: Project 1 | project_item_1 Project 1 | project_item_2 Project 1 | project_item_3 Project 2 | project_item_1 Project 2 | project_item_2 etc etc As you can see, the project name repeats. While this is fine and dandy for displaying raw data, when it comes time to populate it into a table, it can be a real mess. How does everyone handle this particular situation? The first example is more inefficient, but easiest to make the table displays organized (without having rolling checks to make sure "Project #" isn't displayed more then once). Dunno, I am not sure. How do everyone here handle the many-to-1 or 1-to-many relationships when outputing data?
I would have done my tables slightly differently: accounts > account_id | account_name | account_password projects > project_id | account_id | project_name project_items > project_item_id | project_id | project_item_name For simple 1-Many joins you only need the FK from the imediate parent table. Both of your code examples have their place. It really depends on the situation. I typically use your first approach when writing code. Sure it requires a little more CPU power but it does make for easier to understand code. Easier to understand code always wins out in my book.
I use brackets to signify out variables. If you are using an array variable, for instance $array['item_1'] it will not work in a string unless it is enclosed with brackets. If it does not have the brackets it will throw a warning.
I'm not really sure I understand the problem with the join?! If you don't want something to repeat, don;t include it in the query (i.e. leave out the projects.project_name) but if it's not repeating then the data is different on a per row basis so what might you replace it with instead? What would be the perfect outcome? Matt
here is what i just did on a similar problem. this will output something like <th>project 1</th> <td>item 1</td> <td>item 2</td> <td>item 3</td> <th>project 2</th> <td>item 1</td> <td>item 2</td> ... $sql = "your query here"; $out = '<table>'."\n"; $erg = $db->query($sql); while ($res = $db->getdata($erg)) { if($res['project_id']!=$project_id) { $out .= '<tr>'."\n"; $out .= '<th>'.$res['project_name'].'</th>'."\n"; $out .= '</tr>'."\n"; $auftrag_id=$res['auftrag_id']; } $out .= '<tr>'."\n"; $out .= '<td>'.$res['project_item'].'</td>'."\n"; $out .= '</tr>'."\n"; } $out .='</table>'."\n"; return $out; Code (markup):
Ok, so you used....... what do they call it. Well anyways, basically checking to see if a value exists before out putting it and if it already does, it doesn't display it, but if it is a new value that does not equal the old value, display it. Now that is going to drive me nuts as to what that is called. Column repetiion or something, dunno.
The repetion is caused by the join. If you have a one to many relationship, and it truly is that way as their is only one "one's and their are alot of "many's". This causes the database output to repeat the "one's" to each of the "many's". It has nothing to do with removing or data matching, it is just the way SQL was designed. The problem is is that I want the data of the many's, but only want one of the "ones". Unfortunately this is not how SQL (and this includes Oracle, SQL Server etc) works. Would be nice though
hm im no so deep into sql, but maybe "union" can help you here. never really used it, but you can kinda mix different data who dont have a relation with each other into on resultset, like if you need your customers and your suppliers in a single table ordered by postcode (just as a basic example). http://dev.mysql.com/doc/refman/5.0/en/union.html oh and i had a small error in my code //$auftrag_id=$res['auftrag_id']; //wrong, change it to this: $project_id=$res['project_id']; Code (markup):
It is best to make each area query for the data independent of the others. This is for better scalability. Although it will issue more questions to the DB server each one is faster executing and will use less resources. In addition, if you are using the MySQL query cache the actual SQL won't be executed beyond the first data request. Bobby