UI Progress

The following use cases are now functional: User login/logout, user create account, user view/edit account information, salon search (with filters by name, city, or zip code), promotions search (with filters by salon name, salon city, salon zip, and by beauty or hair services being promoted/discounted), viewing user point total, salon login/logout, salon create account, salon view/edit account, salon add new stylist, salon remove stylist, salon add/remove services offered by each stylist, salon add/remove beauty services that they offer, salon create promotions, salon view promotions, salon edit promotions (promotion note, start date, expiration date, and services applicable), and salon view customer ratings of stylists at their salon.

Additionally, I've spent time editing the CSS of the page layout and the spry menu bars in order to get a better look and feel, even though the design is still fairly basic, as I have spent much more time wrestling with PHP and SQL queries trying to build the functionality. Here are some screenshots of the interface:

User Dashboard:

View/Edit Account Info:

Promotions Search/Filter:

One interesting problem that arose was the way to retrieve and pass information from the database to and from the client end. I designed my database to be flexible in the salon-stylist relationships it could represent. Essentially, my database is designed to allow for the possibility that a stylist belong to multiple salons, AND that they offer a different subset of services at each salon. I did this by having a many-to-one relationship table of salon/stylist/service so that while there will be multiple records for each salon, stylist, or service, each combination of stylist-salon-service is unique. While this is great for richness of information, it makes retrieval of information much more difficult. In order to allow salons to add/edit stylists and services they offer, I would have to be looking up each stylist and each service for that salon. The solution was this:

$colname_Stylist = "-1";

if (isset($_GET['id'])) {

  $colname_Stylist = $_GET['id'];

}

//find stylist in database from URL variable passed through link on previous page

mysql_select_db($database_test, $test);

$query_Stylist = sprintf("SELECT * FROM stylist WHERE id = %s", GetSQLValueString($colname_Stylist, "int"));

$Stylist = mysql_query($query_Stylist, $test) or die(mysql_error());

$row_Stylist = mysql_fetch_assoc($Stylist);

$totalRows_Stylist = mysql_num_rows($Stylist);

//check to see if stylist currently offers women's haircut

$colname_Services = "-1";

if (isset($_GET['id'])) {

  $colname_Services = $_GET['id'];

}

$womensCut = 1;

mysql_select_db($database_test, $test);

$query_Services = sprintf("SELECT * FROM salon_stylist_h_service WHERE `stylist id` = %s AND `salon id` = %s AND `hair service id` = %s" , GetSQLValueString($colname_Services, "int"),  GetSQLValueString($salonID, "int"),   GetSQLValueString($womensCut, "int"));

$Services = mysql_query($query_Services, $test) or die(mysql_error());

$row_Services = mysql_fetch_assoc($Services);

$totalRows_Services = mysql_num_rows($Services);

//check to see if stylist currently offers mens cut

$mensCut = 2;

mysql_select_db($database_test, $test);

$query_Services2 = sprintf("SELECT * FROM salon_stylist_h_service WHERE `stylist id` = %s AND `salon id` = %s AND `hair service id` = %s" , GetSQLValueString($colname_Services, "int"),  GetSQLValueString($row_Salon['id'], "int"),   GetSQLValueString($mensCut, "int"));

$Services2 = mysql_query($query_Services2, $test) or die(mysql_error());

$row_Services2 = mysql_fetch_assoc($Services2);

$totalRows_Services2 = mysql_num_rows($Services2);

//delete womens haircut script

$womensCut=1;

  $deleteSQL = sprintf("DELETE FROM salon_stylist_h_service WHERE `stylist id`=%s AND `salon id`=%s AND `hair service id`=%s",

                       GetSQLValueString($_GET['id'], "int"), GetSQLValueString($salonID, "int"), GetSQLValueString($womensCut, "int"));

  mysql_select_db($database_test, $test);

  $Result1 = mysql_query($deleteSQL, $test) or die(mysql_error());

//delete mens haircut script

$mensCut=2;

  $deleteSQL2 = sprintf("DELETE FROM salon_stylist_h_service WHERE `stylist id`=%s AND `salon id`=%s AND `hair service id`=%s",

                       GetSQLValueString($_GET['id'], "int"), GetSQLValueString($salonID, "int"), GetSQLValueString($mensCut, "int"));

  mysql_select_db($database_test, $test);

  $Result2 = mysql_query($deleteSQL2, $test) or die(mysql_error());

//insert script

//insert womens cut if checked

 if ($_POST["Services1"] == 1) { 

 $insertSQL = sprintf("INSERT INTO salon_stylist_h_service (`salon id`, `stylist id`, `hair service id`) VALUES (%s, %s, %s)",

                       GetSQLValueString($salonID, "int"),

                       GetSQLValueString($_GET['id'], "int"),

                      // GetSQLValueString(isset($_POST['Services1']) ? "true" : "", "defined","1","0"));

  GetSQLValueString($womensCut, "int"));

  mysql_select_db($database_test, $test);

  $Result1 = mysql_query($insertSQL, $test) or die(mysql_error());

 }

//insert mens cut if checked

if ($_POST["Services2"] == 2) { 

$insertSQL2 = sprintf("INSERT INTO salon_stylist_h_service (`salon id`, `stylist id`, `hair service id`) VALUES (%s, %s, %s)",

                       GetSQLValueString($salonID, "int"),

                       GetSQLValueString($_GET['id'], "int"),

                      // GetSQLValueString(isset($_POST['Services2']) ? "true" : "", "defined","1","0"));

GetSQLValueString($mensCut, "int"));

  mysql_select_db($database_test, $test);

  $Result2 = mysql_query($insertSQL2, $test) or die(mysql_error());

}

//go to:

$deleteGoTo = "Salon_Dashboard.php";

  if (isset($_SERVER['QUERY_STRING'])) {

    $deleteGoTo .= (strpos($deleteGoTo, '?')) ? "&" : "?";

    $deleteGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $deleteGoTo));

Basically, what this script does is for the selected stylist (and logged in salon), populate the forms on the screen to the current state of the world. Then, once the form is submitted, delete all entries in the salon/stylist/service table. Then add new entries into this table for the stylist and salon for the selected services. It is a nice solution because it adds and deletes in batches, thus keeping related information together in the data tables as well, rather than checking and adding/removing each separately.

User viewing and filtering Promotions by Salon name, city, or zip code was also challenging. I could create a recordset that would pull all salons that matched the criteria of the name, city, zip code fields. I could also pull promotions from the database that matched the beauty/hair service filter. The problem was integrating the salon filter to the promotions filter. The promotions table only stores the salon id of the salon that created a given promotion. So I could not use the filters as direct search criteria into the promotions table. I had to use embedded do-while loops and a new array to store the results in order to later print to screen. Below is most of the code that populates the array with the proper promotions:

$colname_city = "-1";

if (isset($_POST['city'])) {

  $colname_city = $_POST['city'];

}

$colname_name = "-1";

if (isset($_POST['name'])) {

  $colname_name = $_POST['name'];

}

$colname_zip = "-1";

if (isset($_POST['zip'])) {

  $colname_zip = $_POST['zip'];

}

$name="Josh's Salon";

mysql_select_db($database_test, $test);

$query_Salons = sprintf("SELECT * FROM salon WHERE `zip code` = %s OR (`city id` = %s AND name LIKE %s) ORDER BY name DESC", GetSQLValueString($colname_zip, "int"), GetSQLValueString($colname_city, "int"), GetSQLValueString("%" . $colname_name . "%", "text"));

$Salons = mysql_query($query_Salons, $test) or die(mysql_error());

$row_Salons = mysql_fetch_assoc($Salons);

$totalRows_Salons = mysql_num_rows($Salons);

mysql_select_db($database_test, $test);

$query_HairServices = "SELECT * FROM `hair service`";

$HairServices = mysql_query($query_HairServices, $test) or die(mysql_error());

$row_HairServices = mysql_fetch_assoc($HairServices);

$totalRows_HairServices = mysql_num_rows($HairServices);

mysql_select_db($database_test, $test);

$query_BeautyServices = "SELECT * FROM `beauty service`";

$BeautyServices = mysql_query($query_BeautyServices, $test) or die(mysql_error());

$row_BeautyServices = mysql_fetch_assoc($BeautyServices);

$totalRows_BeautyServices = mysql_num_rows($BeautyServices);

$colname_beauty = "-1";

if (isset($_POST['beautyservice'])) {

  $colname_beauty = $_POST['beautyservice'];

}

$colname_hair = "-1";

if (isset($_POST['hairservice'])) {

  $colname_hair = $_POST['hairservice'];

}

$date = date(y-m-d);

$promos = array();

$i=0;

do{

$thisID = $row_Salons['id'];

//$thisID = 2;

mysql_select_db($database_test, $test);

if($colname_hair == 0 && $colname_beauty == 0){

$query_Promotions = sprintf("SELECT * FROM Promotions WHERE `salon id` = %s ORDER BY `end date` ASC", GetSQLValueString($thisID, "int"));

}

if($colname_hair != 0 && $colname_beauty != 0){

$query_Promotions = sprintf("SELECT * FROM Promotions WHERE `salon id` = %s AND (`beauty service id` = %s  OR `hair service id` = %s) ORDER BY `end date` ASC", GetSQLValueString($thisID, "int"), GetSQLValueString($colname_beauty, "int"), GetSQLValueString($colname_hair, "int"));

}

if($colname_hair != 0 && $colname_beauty == 0){

$query_Promotions = sprintf("SELECT * FROM Promotions WHERE `salon id` = %s AND `hair service id` = %s ORDER BY `end date` ASC", GetSQLValueString($thisID, "int"), GetSQLValueString($colname_hair, "int"));

}

if($colname_hair == 0 && $colname_beauty != 0){

$query_Promotions = sprintf("SELECT * FROM Promotions WHERE `salon id` = %s AND `beauty service id` = %s ORDER BY `end date` ASC", GetSQLValueString($thisID, "int"), GetSQLValueString($colname_beauty, "int"));

}

$Promotions = mysql_query($query_Promotions, $test) or die(mysql_error());

$row_Promotions = mysql_fetch_assoc($Promotions);

$totalRows_Promotions = mysql_num_rows($Promotions);

do{

if($totalRows_Promotions > 0){

$promos[$i] = $row_Promotions;

$i++;

}

} while($row_Promotions = mysql_fetch_assoc($Promotions));

} while($row_Salons = mysql_fetch_assoc($Salons));

For each result in the salon recordset based on the name/city/zip filter, the script uses the salon id from each salon and queries the promotions table with the salon id, beauty service id, and hair service id. It's likely that this too will pull multiple matching promotions. So, now a second do-while loop puts each resulting promotion from this query into an array. Then the outside loop moves to the next resulting salon from the salon filters, uses that id and repeats the promotions search, and so on until the result sets are null.

The next big step is to add the functionality of actually scheduling appointments. I've been in a dialogue with Professor Nimeroff about the best way to do this. The goal is to use Google Calendars, but there are conceptual issues with authentication, calendar ownership, and limitations on embedded calendars that need to be worked out. The zend framework and gData libraries are very robust and allow me to create calendars, add events, remove events, query events, and set availability in a fairly straightforward manner. The problem is how and who to authenticate into google (behind the scenes) and also how to enable more than read-only embedded calendars so that the Google Calendars interface can be utilized.