newbie help - please

[pitsta]

Hi there....some great stuff in this forum....think its going to be my new bible.

 

I'm building a course registration system, where people create an account, then view the available courses, choose one and book for it.

 

What I have are three tables (actually a few more, but these 3 are what i need to refer to). Students, Courses & Bookings. I have a back-end for the admin to add new 'courses'. One of the fields is 'Number of Students' (which is the total number of students allowed to book in).

 

In the 'Bookings' table, i have 'courseID', 'studentID', 'Enrollment Position' and 'Enrollment Status' (as well as a few others not related to this).

 

What i am trying to do is calculate how many 'students' are currently booked in, within a particular course (courseID) that they are trying to enroll in, and if their 'Enrollment Position' is =< 'Num of Students' (from 'Courses' table) then they will have an 'Enrollment Status' of 'IN'. Else if 'Enrollment Position' is > 'Num of Students', 'Enrollment Pos' will = 'WAITING LIST'.

 

Hope this makes sense.....I think there is a couple of things i need to do in there. As a start i'd like to be able to at least calculate how many records in the 'Bookings' table that is equal to a particular 'courseID'.

 

Even if someone could just direct me in the right way, off site tut etc......would be very greatful. thanks

 

add a couple of images for visuals

 

 

 

thanking you in advance

 

regards

Shaun

[chronister]

Here is a step in the right direction. The count() function in MySql is great for simply counting items straight from a query. The query below will return 1 row and it will be the total number of records where the course id matches in both tables and it matches a particular id. You have to add the && courses.courseid='$courseid' to get the results for a particular course. Simply pass the course id into the query by setting a var called $courseid and it should work for you.

 

I assume the course id field is called courseid in both tables. You may need to change the names of fields or tables below to match exactly what you have, but it should get ya started.

 

<?php
$query="SELECT count('courseid') as total FROM Bookings WHERE Bookings.courseid=courses.courseid && courses.courseid='$courseid'";
$result=mysql_query($query);
$row=mysql_fetch_object($result); // since I am only dealing with 1 row, I did not run a while loop here
$total_booked=$row->total;
echo $total; // this should display a number
?>

 

Post back and I will try to help with the other pieces too once we get this query working properly.

 

Nate

[pitsta]

Thanks Nate.....

 

just about to try this out....appreciate ur time.

 

Shaun

[pitsta]

hey Nate...

 

Gave that a go......and had a few errors. Hope u dont mind if i post the errors.....

 

Notice: Undefined variable: courseid in C:\Inetpub\wwwroot\otm_updated\addBookings_1.php on line 114

 

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\otm_updated\addBookings_1.php on line 116

 

Notice: Trying to get property of non-object in C:\Inetpub\wwwroot\otm_updated\addBookings_1.php on line 117

 

Notice: Undefined variable: total in C:\Inetpub\wwwroot\otm_updated\addBookings_1.php on line 118

 

also, here is how i modified ur code....

 

            
<?php do { ?>
   <tr>
    <td class="table_pad"><?php echo $row_coursesRS['courseName']; ?></td>
    <td width="75" class="table_pad"><?php echo KT_formatDate($row_coursesRS['dateStart']); ?></td>
    <td width="50" align="center" class="table_pad"><?php echo $row_coursesRS['numofstudents']; ?></td>
   <td width="50" align="center" class="table_pad"> 
          
<?php
$query="SELECT count('courseID') as total FROM Bookings WHERE bookings.courseID=courses.ID && courses.ID='$courseid'";
$result=mysql_query($query);
$row=mysql_fetch_object($result); // since I am only dealing with 1 row, I did not run a while loop here
$total_booked=$row->total;
echo $total; // this should display a number              
?>

</td>
<td width="50" align="center" class="table_pad"><a href="addBookings_2.php?ID=<?php echo $row_coursesRS['ID']; ?>">book now</a></td>
</tr>
<?php } while ($row_coursesRS = mysql_fetch_assoc($coursesRS)); ?>
</table>

 

thanks heaps.....any pointers would be so appreciated.

 

cheers

Shaun

[AdRock]

I think one of your errors occurs here

 

Bookings WHERE bookings.courseID=courses.ID

 

Bookings and bookings are not the same

[chronister]

AdRock is right, Bookings is not the same as bookings, so you need to change that to reflect your actual table name.

 

 

Notice: Undefined variable: courseid in C:\Inetpub\wwwroot\otm_updated\addBookings_1.php on line 114

 

You did not define the $courseid variable. You must pass that into the query somehow... either through the url and then do $courseid=$_GET['courseid']; or using sessions

 

 

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\otm_updated\addBookings_1.php on line 116

 

You must initiate a connection to the database before this will run properly.

 

Notice: Trying to get property of non-object in C:\Inetpub\wwwroot\otm_updated\addBookings_1.php on line 117

 

Make a connection to the db and this one will probably go away

 

Notice: Undefined variable: total in C:\Inetpub\wwwroot\otm_updated\addBookings_1.php on line 118

 

I goofed up there, $total_booked is what it should be as per the line above it.

 

Give those a go and see what you get.

 

Nate

 

[pitsta]

G'day Nate.....

 

Just want to say (without sounding to patronising)......you are an absolute champ!!! Everything worked great!!! Here is the modified code.....with your terrific guidance.

 

 <?php
$courseid=$row_coursesRS['ID'];
$query="SELECT count('courseID') as total FROM bookings, courses WHERE bookings.courseID = courses.ID && courses.ID = '$courseid'";
$result=mysql_query($query);
$row=mysql_fetch_object($result); 
$total_booked=$row->total;
echo $total_booked; // this should display a number              
?>   

 

also added this to my 'Course Details Page'

 

 <?php
$courseid=$_GET['recordID'];
$total_students=$row_DetailRS1['numofstudents'];
$query="SELECT count('courseID') as total FROM bookings, courses WHERE bookings.courseID = courses.ID && courses.ID = '$courseid'";
$result=mysql_query($query);
$row=mysql_fetch_object($result); // since I am only dealing with 1 row, I did not run a while loop here
$total_booked=$row->total;
$total_places=$total_students-$total_booked;
echo $total_places; // this should display a number
?>  

 

Little story.....why i am so overjoyed......The day i posted the message, I was under the pump to get this done (and had a heap of things to do). Was due to fly to Queensland (Australia) the next day, for a short holiday (bit of sun, surf and golf). And hadn't done much PHP work before (ASP....is generally what i code in).....so without going on and on, got a small reprieve, and while i was away read bits and pieces of 'The Dummies Guide' (as that's how i felt) and that help me understand a little more about what you were trying to help me with.

 

So many thanks again.....'and definitely not forgetting AdRock....thank u too!!!

 

It has reignited....my enthusiasm to learn PHP. cheers

 

regards

Shaun