Staff Induction System - No idea where to go from here!!!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bcart
Forum Newbie
Posts: 10
Joined: Fri Oct 30, 2009 8:09 am

Staff Induction System - No idea where to go from here!!!

Post by bcart »

Hi there, I'm pretty new to PHP and Mysql so could really do with being pointed in the right direction with this problem I have.

I am trying to set up a system induct new members of staff onto their training programme.

First I have created a table 'induction_course_dates' of all the course dates and induction_course_titles of all the different courses.

These are the 2 queries I have made on the database


///////////////////////// Make the course title query //////////////////////////////
$q_courses = "SELECT * FROM induction_course_titles";
$result_courses = mysqli_query($dbc, $q_courses);
$row_courses = mysqli_fetch_array($result_courses);


///////////////////////// Make the course date query //////////////////////////////
$q_date = "SELECT * FROM induction_course_dates WHERE DATE(course_date)>='$today' AND course='$courseID' ORDER BY course_date ASC LIMIT 4";
$result_date = mysqli_query($dbc, $q_date);
$row_date = mysqli_fetch_array($result_date);

Obviously in the second query '$courseID' does not have a value so won't actually return anything but I don't know how to loop it through all of the induction_course_titles id's.

For example if it the query read...

///////////////////////// Make the course date query //////////////////////////////
$q_date = "SELECT * FROM induction_course_dates WHERE DATE(course_date)>='$today' AND course='2' ORDER BY course_date ASC LIMIT 4";
$result_date = mysqli_query($dbc, $q_date);
$row_date = mysqli_fetch_array($result_date);

...then this would return the results I'm looking for but there are 25 different courses.


I want the final output to appear as below

The user will select a date from the next four available

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>Course 1</td>
<td>
<select name="select" id="select">
<option value="date1">Date 1</option>
<option value="date2">Date 2</option>
<option value="date3">Date 3</option>
<option value="date4">Date 4</option>
</select>
</td>
</tr>
<tr>
<td>Course 2</td>
<td><select name="select2" id="select2">
<option value="date1">Date 1</option>
<option value="date2">Date 2</option>
<option value="date3">Date 3</option>
<option value="date4">Date 4</option>
</select></td>
</tr>
<tr>
<td>Course 3</td>
<td><select name="select3" id="select3">
<option value="date1">Date 1</option>
<option value="date2">Date 2</option>
<option value="date3">Date 3</option>
<option value="date4">Date 4</option>
</select></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input type="submit" name="button" id="button" value="Submit" /></td>
</tr>
</table>

Can anybody help?!!!! PLEASE!!!!!
mischievous
Forum Commoner
Posts: 71
Joined: Sun Apr 19, 2009 8:59 pm

Re: Staff Induction System - No idea where to go from here!!!

Post by mischievous »

///////////////////////// Make the course query //////////////////////////////
$c_query = "SELECT * FROM induction_course_titles";
$c_query_result = mysqli_query($dbc, $c_query);

///////////////////////// Make the course date query //////////////////////////////
$cd_query = "SELECT * FROM induction_course_dates WHERE DATE(course_date)>='$today' AND course='2' ORDER BY course_date ASC LIMIT 4";
$cd_query_result = mysqli_query($dbc, $cd_query);


<table border="0" cellpadding="0" cellspacing="0">
<?php while($course = mysqli_fetch_array($c_query_result)){?>
<tr>
<td><?php $course['course_title'];?></td>
<td>
<select name="<?php $course['course_id'];?>" id="select">
<?php while($courseDate = mysqli_fetch_array($cd_query_result)){?>
<option value="<?php $courseDate['course_date'];?>"><?php $courseDate['course_date'];?></option>
<?php } ?>
</select>
</td>
</tr>
<?php } ?>
<tr>
<td>&nbsp;</td>
<td><input type="submit" name="button" id="button" value="Submit" /></td>
</tr>
</table>
bcart
Forum Newbie
Posts: 10
Joined: Fri Oct 30, 2009 8:09 am

Re: Staff Induction System - No idea where to go from here!!!

Post by bcart »

mischievous wrote:///////////////////////// Make the course query //////////////////////////////
$c_query = "SELECT * FROM induction_course_titles";
$c_query_result = mysqli_query($dbc, $c_query);

///////////////////////// Make the course date query //////////////////////////////
$cd_query = "SELECT * FROM induction_course_dates WHERE DATE(course_date)>='$today' AND course='2' ORDER BY course_date ASC LIMIT 4";
$cd_query_result = mysqli_query($dbc, $cd_query);


<table border="0" cellpadding="0" cellspacing="0">
<?php while($course = mysqli_fetch_array($c_query_result)){?>
<tr>
<td><?php $course['course_title'];?></td>
<td>
<select name="<?php $course['course_id'];?>" id="select">
<?php while($courseDate = mysqli_fetch_array($cd_query_result)){?>
<option value="<?php $courseDate['course_date'];?>"><?php $courseDate['course_date'];?></option>
<?php } ?>
</select>
</td>
</tr>
<?php } ?>
<tr>
<td>&nbsp;</td>
<td><input type="submit" name="button" id="button" value="Submit" /></td>
</tr>
</table>


I don't think this will work for what I want. you posted the following query...

///////////////////////// Make the course date query //////////////////////////////
$cd_query = "SELECT * FROM induction_course_dates WHERE DATE(course_date)>='$today' AND course='2' ORDER BY course_date ASC LIMIT 4";
$cd_query_result = mysqli_query($dbc, $cd_query);

which is fine however there are 25 different courses, each course has 4 different dates. The query above only states where course='2'.

Any more thoughts? Please????!!!!!!!!!!!!!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Staff Induction System - No idea where to go from here!!!

Post by califdon »

Are you asking how to not select a particular course? Simply don't include course in the WHERE clause. You may want to ORDER BY course, to group all records for the same course together. And then you would not want to LIMIT your query.
Post Reply