karatekid36 Posted February 8, 2008 Share Posted February 8, 2008 Howdy Everyone, In the image below, there is a piece of a front page of a website I am making that I can not figure out how to construct. At the top of the page(not pictured) there is a list of projects that the user is involved with and at the bottom, I would like to display 4 "assets" for each project. A user can be on 1 or 100 projects(there is no limit) but I can not think of a way to do this. I have already built the proper display method to actually display the assets in a form that I can work with, but my trouble is, is displaying 4 assets for each project. What I mean is that I do not know how to write " For each project the user is associated with, build an html table with 4 assets from each project." I have a projects table, a users table, a project assets table, and a user_proj_relation table. I am not asking how I should exactly do this, I am looking for more of an overview of the way this should go, be it mysql or php. I do not know how to achieve my desired result. Can anyone point me in the right direction? Thank you. Quote Link to comment Share on other sites More sharing options...
revraz Posted February 8, 2008 Share Posted February 8, 2008 Do a query for each and use LIMIT 4 to only return 4. Do you want them ordered by date? What defines what 4 to show? Quote Link to comment Share on other sites More sharing options...
karatekid36 Posted February 8, 2008 Author Share Posted February 8, 2008 I understand using the limit, but I want to display 4 for each project. Not just one project. I do not understand which way to do that so that it displays four per project for all the projects that the user is a part of. Quote Link to comment Share on other sites More sharing options...
p2grace Posted February 8, 2008 Share Posted February 8, 2008 You query for the projects first, then query the 4 assets. $query = "SELECT ..."; // query the projects $run = mysql_query($query); while($arr = mysql_fetch_assoc($run)){ extract($arr); $query2 = "SELECT ... FROM .... WHERE `proj_id` = '$proj_id' LIMIT 0,4"; // assuming there's a field called proj_id, otherwise substitute for the project id field $run2 = mysql_query($query2); while($arr2 = mysql_fetch_assoc($run2)){ extract($arr2); echo $asset."<br />"; } } Obviously just a broad outline Quote Link to comment Share on other sites More sharing options...
mem0ri Posted February 8, 2008 Share Posted February 8, 2008 I'd probably do the "show four" in PHP myself as I like to run a single DB query and then organize the information display via PHP. I especially wouldn't want to be running 100 different SELECT statements limited to 4 results each if I had 100 projects...especially since the innoDB engine for MySQL can really only handle about 200 qps. Quote Link to comment Share on other sites More sharing options...
haku Posted February 8, 2008 Share Posted February 8, 2008 That would depend on the amount of information in the database. Pulling everything out of the database in one shot and evaluating it with php could potentially be slower than running 100 small queries. Quote Link to comment Share on other sites More sharing options...
revraz Posted February 8, 2008 Share Posted February 8, 2008 Hard to comment on without knowing how the table is laid out and what constitutes a "project". I understand using the limit, but I want to display 4 for each project. Not just one project. I do not understand which way to do that so that it displays four per project for all the projects that the user is a part of. Quote Link to comment Share on other sites More sharing options...
aschk Posted February 8, 2008 Share Posted February 8, 2008 What do you mean by "asset"? Do you mean a task that someone has to perform. Going by what i'm seeing in your image it would appear that this is the case. Do you want 4 tasks for each project listed? (This will be a lot of tasks I would imagine). Or do you mean you want to display the last 4 tasks assigned? What about a page listing the projects (ordered however the user chooses, but defaulting the latest first?). Then select one of those projects from the list to see what tasks that project has on it. That would seem a more sensible logic option to me. Clarify some of your "project", "asset" terminology for us and i'm sure we can help further. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.