resourcename : The name of the technical person ( Bob, Jane )
dateavailable : Date on which the technical person will be available ( in format YYYY-MM-DD )
productknowledge : This is field holds the 'products' which the technical person is proficient in. It ties in with the 'productcode' that is located inside the product table.
Here's an example :
Lets say you have a product called 'NotOffice'. You then assign this a product code, that goes in the product table. The entry will then look like this :
----------------------------------------------------------------------
product | productcode | vendorcode
----------------------------------------------------------------------
NotOffice | NOF | VC
----------------------------------------------------------------------
Inside your 'resource table' you have the following entry
----------------------------------------------------------------------
resourcename | dateavailable | productknowledge
----------------------------------------------------------------------
Joe Officeguy | 2010-01-01 | NOF
----------------------------------------------------------------------
Once the calendar is displayed, the query (below) selects the resource (technical person) that has a similar productcode (NOF in this case) AND is matches the dateavailable against the dates of the calendar. If those two conditions are met, the name of the resource is displayed on the date which he/she is available.
Code: Select all
<?php
$checkQuery = @mysql_query("SELECT resourcename, dateavailable FROM resourcetbl WHERE
dateavailable LIKE '%".mysql_real_escape_string($currentYMD)."%' AND productknowledge LIKE '%".mysql_real_escape_string($productCode)."%' ");
echo 'Available technical personnel :';
?>
Attached is the demonstration information i tested with.