Counting the number of times something is in the table

[Potatis]

I'll add that jcbones' code does calculate the right amount of lessons if the student is in the table once, but only the first user, it doesn't loop through the rest of the students.

 

edit: The info can't be added all at once though, since the roll is updated weekly. I need to use INSERT though rather than update, otherwise I'd have to create a new table every term, so that the current table is not over-written. I want the table to be searchable in the future.

[Potatis]

Changing to OR gets us somewhere.

 

It calculates correctly that Bugs Bunny has had 4 lessons, even though Bugs Bunny's lessons are recorded on different lines. WooHoo!

 

But it does so 4 times:

 

Bugs Bunny: 4

 

Bugs Bunny: 4

 

Bugs Bunny: 4

 

Bugs Bunny: 4

 

And there is no mention of the other students.

[litebearer]

just as a minor assist - relative to not being able to post your screen shot...

 

1. dl a free pdf creator.

2. do a screen capture

3. paste screen catpure into word

4. print as pdf file and post it

 

 

[Potatis]

Thanks for the tip, litebearer! I don't have Word on my computer either, I am running a fairly barebones laptop at the moment, thanks to a recent Windows update that crippled my laptop and forced a drive wipe and reinstall. I have got Photoshop at home ready to reinstall now that the license issue has been sorted out after the other one was lost in the crash. I'll be home again tomorrow, I'll set up some software then.

[litebearer]

LOLOL Ok, second aside (yes I know the potential dangers involved).

 

Frequently problems occur due to our eyes seeing one thing and our minds THINKING we are seeing something different. It may be appropriate to do an 'sql dump' (HEY! I am old fart and may not know the porper term). Post the 'dump' so that a 'different' pair of eyes can have the EXACT data you are using.

 

Just a thought

 

[ignace]

But it does so 4 times

 

what code goes behind this? what rolls them out?

[Potatis]

This is the full page as it stands:

 

<?php include('includes/admin_session.php'); ?>
<?php require_once("includes/connection.php");

// Set variables from search query

/*$tutor_name = $_POST['tutor_name'];
$school_name = $_POST['school'];
$term = $_POST['term'];
$year = $_POST['year'];*/

$tutor_name = "Krismack";
$school_name = "MPS";
$term = "1";
$year = "2010";

// Query database with the above variables to get all stored data belonging to the tutor, at X school during X term and X year

$result = mysqli_query($connection,"SELECT * FROM student_roll WHERE tutor_name='$tutor_name' AND school='$school_name' AND term='$term' AND year='$year' ORDER BY last_name ASC")
or die(mysqli_error($connection));

while($row = mysqli_fetch_array($result)) {

    //get first and last name of all students belonging to tutor at X school, term and year

         $student_first_name= $row['first_name'];
         $student_last_name= $row['last_name'];

    // Use student names to loop through table the names of all students who had a lesson on week 1 and display 1
    // as the number of lessons they had.

$result = mysqli_query($connection,"SELECT first_name, last_name, count(*) as lessons FROM student_roll
WHERE week1 = 'y' OR week2 = 'y' OR week3 = 'y' OR week4 = 'y' OR week5 = 'y' OR week6 = 'y' OR week7 = 'y' OR week8 = 'y' OR week9 = 'y' OR week10 = 'y' GROUP BY first_name, last_name ")
or die(mysqli_error($connection));

while($row = mysqli_fetch_array($result)) {


$num_rows = mysqli_num_rows($result);

echo $student_first_name . " " . $student_last_name . ": ";
echo $num_rows;
echo "<br />";
echo "<br />";
}
}
?>

 

I could understand why it might roll out 4 times with "1", but it isn't. It is adding the total lessons and doing that 4 times, and not going through the other 5 students. I currently have 6 students in my students_roll table.

[ignace]

I found your problem (finally ) First you query:

 

Query database with the above variables to get all stored data belonging to the tutor, at X school during X term and X year

 

Perform this query it should give you 4 rows as a result

 

Then you perform:

 

Use student names to loop through table the names of all students who had a lesson on week 1 and display 1 as the number of lessons they had.

 

Which loops for 4 times thus displaying one record 4 times.

 

Execute these 2 queries separatly in PHPMyAdmin for confirmation.

[ignace]

Anyway this should be your final query, you can't count the number of weeks a student attended the school (unless you change your db scheme) but this should do the trick:

 

SELECT first_name, last_name
FROM student_roll
WHERE (tutor_name = '$tutor_name' AND school = '$school_name' AND term = '$term' AND year = '$year') AND
      (week1 = \'y\' AND week2 = \'y\' AND week3 = \'y\' AND week4 = \'y\' AND week5 = \'y\' AND
       week6 = \'y\' AND week7 = \'y\' AND week8 = \'y\' AND week9 = \'y\' AND week10 = \'y\')

 

This will list all students who have attended all weeks

[Potatis]

Ok, the first query:

 

SELECT * FROM student_roll WHERE tutor_name='Krismack' AND school='MPS' AND term='1' AND year='2010' ORDER BY last_name ASC

 

Gives me the names of all students of tutor "Krismack, and their associated data, but where the name is duplicated, because of the different weeks with "y", their names appear multiple times. In this table that I am working with, Bugs Bunny appears in the list twice because there are two different entries.

 

This query:

 

SELECT first_name, last_name, count(*) as lessons FROM student_roll

WHERE week1 = 'y' AND week2 = 'y' AND week3 = 'y' AND week4 = 'y' AND week5 = 'y' AND week6 = 'y' AND week7 = 'y' AND week8 = 'y' AND week9 = 'y' AND week10 = 'y' GROUP BY first_name, last_name

 

Gives me one instance of each user, and how many times their name appears in the table (as the code instructs). Bugs Bunny 2, the rest 1.

[Potatis]

Ah ok, yes I see how the code shows student who have had all weeks.

 

I need to have it show how many weeks as a progressive thing too. An admin should be able to look at week 3, or even week 1 - Lessons so far.

 

What changes need to be made to my database scheme?

[Potatis]

What if my column was called "y" instead of all the weeks, and I inserted "week 1" into it? 

 

firstname, lastname,y

Fred, Flintstone,week3

 

Why didn't anyone think of this before? It's so easy.

[Potatis]

Same old problem, it won't loop through the students. I'm not going to bother with this anymore.

 

Thanks very much to ignace, jcbones and litebearer for your help, and anyone else who had some input! I'll have to do this another way, just search one student at a time instead of a full list.

 

Thanks again!

[jcbones]

Don't give up yet, I just seen your Db structure.

 

Try this:

<?php include('includes/admin_session.php'); ?>
<?php require_once("includes/connection.php");

// Set variables from search query

/*$tutor_name = $_POST['tutor_name'];
$school_name = $_POST['school'];
$term = $_POST['term'];
$year = $_POST['year'];*/

$tutor_name = "Krismack";
$school_name = "MPS";
$term = "1";
$year = "2010";

// Query database with the above variables to get all stored data belonging to the tutor, at X school during X term and X year
$sql = "SELECT * FROM student_roll WHERE (tutor_name='$tutor_name' AND school='$school_name' AND term='$term' AND year='$year') ORDER BY last_name ASC";
$result = mysqli_query($connection,$sql);	

if(mysqli_num_rows($result)) {
	while($row = mysqli_fetch_assoc($result)) {
	$student_first_name = $row['first_name'];
	$student_last_name = $row['last_name'];
	//set students in an array, so we increment a count to keep total rows.
		@$students[$student_first_name][$student_last_name] += 0;
		if($row['week1'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week2'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week3'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week4'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week5'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week6'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week7'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week8'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week9'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }
		if($row['week10'] == 'y') {	$students[$student_first_name][$student_last_name] += 1; }			
	}
}


if(is_array($students)) {
foreach($students as $fname => $dem2) {
	foreach($dem2 as $lname => $count) {
		echo $fname . " " . $lname . ": ";
		echo $count;
		echo "<br />";
		echo "<br />";
	}
}
}
else {
echo ucwords(strtolower($tutor_name)) . ' does not have any students to display!';
}

?>

[Potatis]

Don't give up yet, I just seen your Db structure.

 

 

Incredible! It worked first time!

 

Donald Duck: 6

 

Rolf Harris: 5

 

Mickey Mouse: 5

 

Homer Simpson: 6

 

Super Ted: 4

 

Thanks so much jcbones, this is exactly what I wanted! I really appreciate the time you spent on this, and I am very happy that this is working! You're a legend! Now I will take the time to study the code and learn what you did.  :D

 

Another thanks again also to ignace for his time, and all other replies.