Potatis

Hi Nerd99, did you solve this? If not, I spotted this: Try changing <li><a href="tutor_submit_report" target="_self">Submit Reports</a></li> </ul> </li> <li> to <li><a href="tutor_submit_report" target="_self">Submit Reports</a></li> </ul> in tutor_links.php

If you don't find a host by Thursday, I am happy to host your trial, with a different domain name.

Hi nerd99 Don't change your code, change your host. I thought every host supported mysqli by now. You can't just change the word "mysqli" to "mysql" and hope it will work. Mysqli has a different syntax, you have to change whole lines, and there is way too much code to change in your project for it to be worth the time. (I know, I have seen most of it... very very fine mysqli code. ). I'm not surprised if you are seeing thousands of errors. You need a host that will support the .com.au domain. If dreamhost did that (which they don't), I'd recommend them. Doug (the mad violinist)

That doesn't look like a login form to me.

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.

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!

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.

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?

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.

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.

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.

while($row = mysql_fetch_array($query)) { echo $row['title']; echo $row['description']; } Is what you need.

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.

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.

No, I am working with a different database table (just for student_roll) because I am not at home, and didn't have that table on my laptop so I created a new one. I can't do a print screen and post because I don't have anything like photoshop installed, but my new database does have two students who have had 10 lessons, and the only one that has had 10 lessons with "y" is Fred Flintstone. I could do a table dump of that one table if you want, but since you have the table without data, you could experiment if you wanted to. I got bugs bunny:1 because it found my first student which was bugs bunny who had 4 lessons.

Do you mean this? $result = mysqli_query($connection,"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 ") or die(mysqli_error($connection)); I get ony 1 user again, and one of their entries: "Bugs Bunny: 1" My table is "student_roll". If I typed "students_roll" I know I'll get a worse error (though easier to fix)

Using this query: $result = mysqli_query($connection,"SELECT first_name, last_name, count(*) as lessons FROM student_roll GROUP BY first_name, last_name HAVING 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'") or die(mysqli_error($connection)); I get: Unknown column 'week1' in 'having clause' I think it needs glasses. Maybe it's time to stop trying to write PHP code in the hotel bathtub.

"students" is a table with all of the students contact info. "students_roll" is a table which logs whether the student has had a lesson or not. This script I am having trouble with is the result of an admin searching for students of a tutor during the period of X term in X year. It uses the search words "tutor name" "school" "term" and "year" to get the names of all students belonging to that tutor, at X school at whatever term, in whichever year. After getting the student names, it looks in the "students_roll" table to see how many times the student's first names and last names appear in the table with "y" in the week column. My database structure is this: -- phpMyAdmin SQL Dump -- version 3.2.4 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Apr 03, 2010 at 08:39 PM -- Server version: 5.1.41 -- PHP Version: 5.3.1 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `krism_db` -- -- -------------------------------------------------------- -- -- Table structure for table `admin` -- CREATE TABLE IF NOT EXISTS `admin` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -------------------------------------------------------- -- -- Table structure for table `log` -- CREATE TABLE IF NOT EXISTS `log` ( `id` mediumint(11) NOT NULL AUTO_INCREMENT, `ip` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `page` varchar(255) NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=127 ; -- -------------------------------------------------------- -- -- Table structure for table `schools` -- CREATE TABLE IF NOT EXISTS `schools` ( `id` int(11) NOT NULL AUTO_INCREMENT, `school_name` varchar(255) NOT NULL, `type` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `state` varchar(255) NOT NULL, `postcode` varchar(255) NOT NULL, `school_phone` varchar(255) NOT NULL, `school_email` varchar(255) NOT NULL, `contact_name` varchar(255) NOT NULL, `position` varchar(255) NOT NULL, `contact_phone` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Table structure for table `students` -- CREATE TABLE IF NOT EXISTS `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `contact` varchar(255) NOT NULL, `phone_h` varchar(255) NOT NULL, `phone_m` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `state` varchar(255) NOT NULL, `postcode` varchar(255) NOT NULL, `instrument_1` varchar(255) NOT NULL, `tutor_1` varchar(255) NOT NULL, `instrument_2` varchar(255) NOT NULL, `tutor_2` varchar(255) NOT NULL, `school` varchar(255) NOT NULL, `school_year` varchar(255) NOT NULL, `class` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `profile` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -------------------------------------------------------- -- -- Table structure for table `student_roll` -- CREATE TABLE IF NOT EXISTS `student_roll` ( `id` int(11) NOT NULL AUTO_INCREMENT, `term` varchar(1) NOT NULL, `year` varchar(4) NOT NULL, `tutor_name` varchar(50) NOT NULL, `school` varchar(255) NOT NULL, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `week1` varchar(3) NOT NULL, `week2` varchar(3) NOT NULL, `week3` varchar(3) NOT NULL, `week4` varchar(3) NOT NULL, `week5` varchar(3) NOT NULL, `week6` varchar(3) NOT NULL, `week7` varchar(3) NOT NULL, `week8` varchar(3) NOT NULL, `week9` varchar(3) NOT NULL, `week10` varchar(3) NOT NULL, `notes` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -------------------------------------------------------- -- -- Table structure for table `tutors` -- CREATE TABLE IF NOT EXISTS `tutors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `phone_h` varchar(255) NOT NULL, `phone_m` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `instrument` varchar(255) NOT NULL, `school` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `profile` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; I'll try your query now

It seems like Walt Disney has already been successful lol! I'ts the same student each time! This isn't the "students" table. Btw the database table is as you see it in roll_table.jpg

From roll_table.jpg I should get the total lessons for each user. Donald Duck: 6 Rolf Harris: 5 Mickey Mouse: 5 And so on. The idea was to calculate how many times each first name, last name and "y" appeared together in the database. I thought it would be simple, but it isn't

I am away from home for Easter, so I had to set up a different database to try out this code. This is what I have discovered: With my initial attempt, and jcbones attempt, it only shows the first user in the database. If all of the values are in one row, the correct number of lessons is calculated. If the usernames are inserted into the database at different times, the script is reading the first user, once only and the count of "y" in the week column is therefore always "1". I have decided that I my database design must be bad. It works fine for counting the number of lessons for one student though.

Well, I love and understand dark humour.

Why is that? For real I mean I am really interested in hearing your opinions (you can even PM me if you don't want to humiliate me in public ) Haha, well there's certainly no need to humiliate you. I often learn a lot from the help you give others, so I respect your replies, and am always interested to read your suggestions to other people's PHP problems. I have to say though that your suggestion.. that "SELECT first_name,last_name" just produced the first user in the table, like my try attempt did, and jcbones' attempt did. Oh well. I might have to try a different method soon.

Hi jcbones, your script works without any errors for returning the first user in my database, but doesn't loop through the rest. Any ideas of what is stopping the script from looping through the other students? My initial attempt wouldn't loop through the students either.

How do you know for sure? Just deny the student from college/university? Hi ignace, I always like reading your posts. I know for sure because I am only doing testing, and I am typing my own fake names. Eventually this will be a site for music tuition at a few schools. There will be more checks done other than first name and last name, that is the least of my problems right now. I just want a count of lessons for each student to come from the database where the week#="y". Once that works, I can do the rest of the code, including taking away hardcoded variables and other things I know how to do. I don't want to spend time on those extra things now if my script (the bits that I don't know how to do, such as my original question) is never going to work. Thanks for reading my post.