Page 1 of 1

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

Posted: Fri Oct 30, 2009 8:13 am
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!!!!!

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

Posted: Fri Oct 30, 2009 10:54 am
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>

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

Posted: Fri Oct 30, 2009 11:21 am
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????!!!!!!!!!!!!!

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

Posted: Fri Oct 30, 2009 3:14 pm
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.