build dynamic columns for php table

[elamigosam]

hi, I am stuck trying to figure out how to build a table to display agents names, (that I already have, mysql table etc...) but now I need to add columns dynamicaly. I need to add (programs in witch agents are enroled, and a status of the program ( in-progress, not-done, etc). The part that I can figure out is where would I save the status of the agent in the program.
I have
enroled-agents table: (id, name, email) displays a basic mysql-php-html table;
then I have
programs table: (id, name, description, avaiable-status)
When i place them together, where how do I save the status (in-progress) of agent Smith in the program-1?
basicaly programs is going to be something that the agent needs to acomplish, like a class.

I was thinking of adding more rows to the agents mysql table, but I would like to have more than just one value in the program like (name, description, status).

Thank you

[alan.davies]

You need what is often called a link table. This could be called agent_programs. It would just contain fields agentid, programid, status. The agentid and programid are foreign keys.

[elamigosam]

Can you elaborate on Link Table, i googled it and nothing came up.
is that the same as JOIN?

[alan.davies]

I wrote a tutorial on aspects of this (when I was known as "diafol"): https://www.daniweb.com/programming/web-development/tutorials/499320/common-issues-with-mysql-and-php Items #8 and #9

However, as you are using a relational db - you can leverage its power by creating tables, like so (just minimal datafields for now):

TABLE agents
agent_id (PK, int)
firstname (varchar,50)
surname (varchar,50)

TABLE projects
project_id (PK, int)
projectname (varchar, 50)
availability (tinyint, 1) - 0 = unavailable, 1 = available

TABLE agent_projects
agent_id (FK, int)
project_id (FK, int)
status (tinyint, 1) - 0 = not started, 1 = in progress, 2 = complete [for example]
startdate (date)
completedate (date)

As you can see the data you're interested in, for a query needing to know which agents have started but not completed a project, needs to come from at least 2 tables - 3 tables if you're only interested in "available projects" or you need the name of the project as well.

This is done by a JOIN, as you mentioned in your post. The specific join required here will be an INNER JOIN, although a plain "JOIN" defaults to INNER JOIN. I will use the full syntax for clarity:

So, to get a list of ALL agents with projects either not started or in progress, but only for those projects "available", I'd do this:

SELECT a.`firstname`, a.`surname`, p.`projectname`, ap.`status`, ap.`startdate` FROM `agent_projects` AS ap 
    INNER JOIN `agents` AS a ON a.`agent_id` = ap.`agent_id` 
    INNER JOIN `projects` AS p ON p.`project_id` = ap.`project_id` 
        WHERE ap.`status` < 2 AND p.`availability` = 1 
        ORDER BY p.`projectname`, p.`startdate`

Each table has an "ALIAS", so we can use the shorthand instead of specifying the full name every time in the query. So a, p and ap are the aliases declared after the (optional) keyword "AS".
We join the fields from different tables with the "ON" clause - simply give the related fields in both tables.
The WHERE clause filters out any unavailable projects and agent projects that have been completed.
The ORDER clause specified that we want the data ordered alphabetically by projectname, then by the date the project was started (rather than alphabetically by agent name, for example).

Apologies if you are familiar with most of the above.

[elamigosam]

Thank you for your response, I have not worked with the JOIN sql statements yet, I will go through and learn more about it.