MySQL or PHP with this?

[karatekid36]

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.

 

[revraz]

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?

[karatekid36]

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.

[p2grace]

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

[mem0ri]

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.

[haku]

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.

[revraz]

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.

[aschk]

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.