jd22

Dear juddster Finally i did it. I want to thank you thousands of times. Finally query worked. INNER JOIN was solved eerything. Thank you very much again. @Fenway : Thank you too. And i am sorry for late reply.

@Fenway I am terrible sorry for late reply because first day of week and office was too busy. @juddster I changed left join into inner join nothing was happened but i found some point that kidsStudent table holds only kids student's data so, inner join kidsStudent on Student.Studentid = kidsStudent.Student.id inner join Enrol...... will fail because there is no data in enrol table about kids. i changed query something like this But this time mysql (phpmyadmin and search.php) seems takes infinite. New query is $QUERY = " SELECT FirstName, LastName, CourseName, SchLessonStart, Weekday, Email, mMail, Student.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student, KidsStudent, Enrol, FEnrolment, SchCourse, Course, Address WHERE( "; if($_POST['adult'] == "kids") $QUERY .= "Student.StudentID = KidsStudent.StudentID AND "; if($_POST['Floating'] == 1) $QUERY .= "FEnrolment.StudentID = Student.StudentID AND "; if($_POST['StudentName'] != "") { if($_POST['inc'] == 1) $QUERY .= "FirstNameENG LIKE '%" .$_POST['StudentName']. "%' AND "; else if($_POST['inc'] == 2) $QUERY .= "FirstNameENG NOT LIKE '%" .$_POST['StudentName']. "%' AND "; } if($_POST['CourseName'] != "") $QUERY .= "SchCourse.CourseID = '".$_POST['CourseName']. "' AND "; if($_POST['WeekDays'] != "") $QUERY .= "Weekday = '" .$_POST['WeekDays']. "' AND "; if(strlen($_POST['Point']) > 0) $QUERY .= "20101points >= '" .$_POST['Point']. "' AND "; if($_POST['email'] != "") $QUERY .= "Email = '" .$_POST['email']. "' AND "; if($_POST['mMail'] != "") $QUERY .= "mMail = '" .$_POST['mMail']. "' AND "; if($_POST['HomeTel'] != "") $QUERY .= "HomeTel = '" .$_POST['HomeTel']. "' AND "; if($_POST['mTel'] != "") $QUERY .= "mTel = '" .$_POST['mTel']. "' AND "; if($_POST['Fax'] != "") $QUERY .= "Fax = '" .$_POST['Fax']. "' AND "; if($_POST['BirthMonth'] != "") $QUERY .= " MONTH(DateOfBirth) = '" .$_POST['BirthMonth']. "' AND "; if($_POST['pref'] != "") $QUERY .= "Prefecture = '" .$_POST['pref']. "' AND "; if($_POST['city'] != "") $QUERY .= "City = '" .$_POST['city']. "' AND "; if($_POST['town'] != "") $QUERY .= "Town = '" .$_POST['town']. "' AND "; if($_POST['is_private'] == 0) $QUERY .= "Course.CourseName = 'Private' AND "; else if($_POST['is_private'] == 1) $QUERY .= "Course.CourseName = 'DirectorPrivate' AND "; else if($_POST['is_private'] == 2) $QUERY .= "Course.CourseName = 'Kids Private' AND "; if(($_POST['Age_From'] != "") || ($_POST['Age_To'] != "")) { $YEAR = date("Y"); if($_POST['Age_From'] != "") $AGE_FROM = ($YEAR - $_POST['Age_From']) ."-01-01"; else $AGE_FROM = ""; if($_POST['Age_To'] != "") $AGE_TO = ($YEAR - $_POST['Age_To']) ."-12-31"; else $AGE_TO = ""; $QUERY .= " DateOfBirth = '0000-00-00' AND (DateOfBirth BETWEEN " .$AGE_FROM. " AND " .$AGE_TO. " AND )"; } $QUERY2 = substr($QUERY, 0, (strlen($QUERY) - 5)); $SUBMIT_QUERY = $QUERY2 .") GROUP BY FirstNameJSE ORDER BY FirstNameENG ASC"; mysql_query($SUBMIT_QUERY);

Value of $SUBMIT_QUERY is SELECT FirstName, FirstName, CourseName, SchLessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID INNER JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID INNER JOIN Course c ON sc.CourseID = c.CourseID INNER JOIN FEnrolment fe ON c.CourseID = fe.CourseID INNER JOIN Address a ON s.Postcode = a.Postcode INNER JOIN KidsStudent ks ON s.StudentID = ks.StudentID WHERE s.School = 'adults' GROUP BY FirstName ORDER BY LastName ASC"; This is what i have in my screen after hit Submit button. Of course i am testing my queries in phpmyadmin for to get better information insead getting clueless php error. Sorry here is 04:30am and i am unable to judge well.

Submitting query return 0 result. phpmyadmin says "MySQL returned an empty result set (i.e. zero rows). (Query took 0.0006 sec)" By the way, Server has Mysql 5.0.5a1 version does it makes problem? Mysql Client version 4.2.22

I am running all queries through phpmyadmin Here is entire search page php code <?php $QUERY = " SELECT FirstName, FirstName, CourseName, SchLessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID INNER JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID INNER JOIN Course c ON sc.CourseID = c.CourseID INNER JOIN FEnrolment fe ON c.CourseID = fe.CourseID INNER JOIN Address a ON s.Postcode = a.Postcode INNER JOIN KidsStudent ks ON s.StudentID = ks.StudentID WHERE "; if($_POST['StudentName'] != "") { if($_POST['inc'] == 1) $QUERY .= "FirstName LIKE '%" .$_POST['StudentName']. "%' AND "; else if($_POST['inc'] == 2) $QUERY .= "FirstName NOT LIKE '%" .$_POST['StudentName']. "%' AND "; } if($_POST['adult'] != "") $QUERY .= "s.School = '" .$_POST['adult']. "' AND "; // Enum('Kids', 'Adult') if($_POST['CourseName'] != "") $QUERY .= "sc.CourseID = '" .$_POST['CourseName']. "' AND "; if($_POST['WeekDays'] != "") $QUERY .= "Weekday = '" .$_POST['WeekDays']. "' AND "; if(strlen($_POST['Point']) > 0) $QUERY .= "20101points >= '" .$_POST['Point']. "' AND "; if($_POST['email'] != "") $QUERY .= "Email = '" .$_POST['email']. "' AND "; if($_POST['mMail'] != "") $QUERY .= "mMail = '" .$_POST['mMail']. "' AND "; if($_POST['HomeTel'] != "") $QUERY .= "HomeTel = '" .$_POST['HomeTel']. "' AND "; if($_POST['mTel'] != "") $QUERY .= "mTel = '" .$_POST['mTel']. "' AND "; if($_POST['Fax'] != "") $QUERY .= "Fax = '" .$_POST['Fax']. "' AND "; if(($_POST['Age_From'] != "") || ($_POST['Age_To'] != "")) { $YEAR = date("Y"); if($_POST['Age_From'] != "") $AGE_FROM = ($YEAR - $_POST['Age_From']) ."-01-01"; else $AGE_FROM = ""; if($_POST['Age_To'] != "") $AGE_TO = ($YEAR - $_POST['Age_To']) ."-12-31"; else $AGE_TO = ""; $QUERY .= " DateOfBirth = '0000-00-00' OR DateOfBirth BETWEEN " .$AGE_TO. " AND " .$AGE_FROM. " AND "; } // IGNORE START if($_POST['BirthMonth'] != "") { $B_DAY = "-". $_POST['BirthMonth'] ."-"; $QUERY .= "DateOfBirth LIKE '%" .$B_DAY. "' AND "; } // IGNORE END if(strlen($_POST['Address']) > 4) { $add = explode("+", $_POST['Address']); if($add[0] != "") $QUERY .= "Prefecture = '" .$add[0]. "' AND "; if($add[1] != "") $QUERY .= "City = '" .$add[1]. "' AND "; if($add[2] != "") $QUERY .= "Town = '" .$add[2]. "' AND "; } $QUERY = substr($QUERY, 0, (strlen($QUERY) - 5)); $SUBMIT_QUERY = $QUERY ." GROUP BY FirstName ORDER BY LastName ASC"; echo $SUBMIT_QUERY; ?>

When i add KidsStudent then query return no result. I tried die(mysql_error()) for test purpose that i want to see if is there any problem with query and i got white blank page . But i know there 192 kid students in KidsStudent which is linked to Student table.

Your query returned 192 records which is correct data. But why it is not return any records in my query. Is there any limit to joining tables? In my case i am trying to merge 7 tables. I am testing many different possibilities and query works if i remove any of table (joining 6 tables) but adding one more (joining 7 tables) fail. So far i found that kidsstudent table doesn't cause this mess but number of tables. Is there any limitation for INNER JOIN ??

Dear Juddster The problem that is not grabbing data even without WHERE clause. But somehow query return data with SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s INNER JOIN KidsStudent k ON s.StudentID = k.StudentID GROUP BY FirstName ORDER BY FirstName ASC

0000-00-00 is default value. if there is no data insert then record has 0000-00-00

Yes, I have about 20 records in KidsStudent column. Matter of fact that KidsStudent Column just hold student's birthday, emergency contact and parent's name and linked to Student table with StudentID primary key Query Works SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC Query Works SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s INNER JOIN KidsStudent k ON s.StudentID = k.StudentID GROUP BY FirstName ORDER BY FirstName ASC Query doesn't Works SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC INNER JOIN KidsStudent k ON s.StudentID = k.StudentID GROUP BY FirstName ORDER BY FirstName ASC Query doesn't Works SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC INNER JOIN KidsStudent k ON s.StudentID = k.StudentID WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31) GROUP BY FirstName ORDER BY FirstName ASC

Okay. I got what you mean. It is my fault. But query return no data. Actually there is data in DB. Students tables holds all students(adults and kids) KidsStudents holds only kids. Query works perfect without joining KidsStudent table. However, it is not grabbing data after inner join kidsstudent. I have to use Student.StudentID twice if i want to add kidsstudent. Do you think that makes trouble? SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC INNER JOIN KidsStudent k ON s.StudentID = k.StudentID WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31) GROUP BY FirstName ORDER BY FirstName ASC

Dear AyKay47 Seems have no luck. I don't understand why query cannot grab records after adding one more table? Student.StudentID and KidsStudent.StudentID is related. Oh Lord !! it is 5am

Query doesn't work even without WHERE statement. it was work when it was SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC GROUP BY FirstName ORDER BY FirstName ASC but it is not working now after change(added bold part) into this SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC INNER JOIN KidsStudent k ON s.StudentID = k.StudentID WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31) GROUP BY FirstName ORDER BY FirstName ASC

Adding quotes also has no affect. But same query works fine if remove part below :confused: I am doing some mistakes but could not find where. Almost 3am and still working one query :( LEFT JOIN KidsStudent ks ON s.StudentID = ks.StudentID WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN '2001-01-01' AND '1995-12-31') [/code]

Dear AyKay47 Here is entire code. Ir is not working either in phpmyadmin or in embedded php file. I really appreciate for you trying help. SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC INNER JOIN KidsStudent k ON s.StudentID = k.StudentID WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31) GROUP BY FirstName ORDER BY FirstName ASC

Dear AyKay47 Thanks for your reply. Query doesn't work even without WHERE....... clause. It works perfect for SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC But adding LEFT JOIN KidsStudent ks ON s.StudentID = ks.StudentID and/or adding WHERE DateOfBirth = '0000-00-00' AND DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31 doesn't work

Yes, i did my there is no thing but blank page. No output, no warning.

Hi i have a sql query like: SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s LEFT JOIN Enrol en ON s.StudentID = en.StudentID LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID LEFT JOIN Course c ON sc.CourseID = c.CourseID LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID LEFT JOIN Address a ON s.Postcode = a.Postcode [b]LEFT JOIN KidsStudent ks ON s.StudentID = ks.StudentID << This is where the problem[/b] WHERE DateOfBirth = '0000-00-00' AND DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31 GROUP BY FirstName ORDER BY FirstName ASC If i remove bold part then query works fine as it should, unfortunately if i add that part query return empty result(But there is data) What did i do wrong?

Dear salathe Thank you for your reply. I build a function yesterday it is simple but works fine form. function EnumFileds($Table, $Column) { $DSP_RESULT = ""; $ENUM_NAME = "SHOW COLUMNS FROM " .$Table. " LIKE '" .$Column. "'"; $ENUM_QUERY = mysql_query($ENUM_NAME); $ENUM_ROW = mysql_fetch_array($ENUM_QUERY); $GetCol = preg_match("/['](.*)[']/", $ENUM_ROW['Type'], $matches); $SplitIt = str_replace("'", "", $matches[0]); $Result = explode(",", $SplitIt); for($i=0; $i<count($Result); $i++) $DSP_RESULT .= '<option value="' .$Result[$i]. '">' .$Result[$i]. '</option>'; return $DSP_RESULT; } I hope this code help someone else. Thank you again for your help.

Dear Fenway That is the point. how can i merge those 3 queries into one. and use distinct for any of table in those queries but student table. Regards

i have a value something like enum('adult','kid') (This value is result of "SHOW COLUMNS FROM clases LIKE 'School'" school is where enum values store) how can i parse it those and add into <option value="HERE">HERE</option> Regards

Dear MasterACE14 Thanks for reply I need all fields. However as you can see i am getting students name and then try to find which course they are taking in a loop. I want to do that with a single query. Also another question is: with that query i am taking everything. But some students are taking 3 or 4 classes and that query brings me same student 4 times (with different classes) How can i distinct only one class if a student takes 4 classes. I hope i made myself clear... Regards

Hello I am trying to do something but seems i stuck. I have a mysql query but i guess it need optimization. I really appreciate for any help Regards $QUERY = " SELECT * FROM student s LEFT JOIN address a ON s.Postcode = a.Postcode"; $res = mysql_query($QUERY, MyConn()) or die(mysql_error()); while($row = mysql_fetch_array($res)) { $SQL2 = "SELECT * FROM fenrolment f LEFT JOIN Course c ON f.courseid = c.courseid WHERE f.studentid = '" .$row['StudentID']. "'"; $SQL3 = "SELECT * FROM enroll e LEFT JOIN SCourse s ON e.SCourseID = s.SCourseID WHERE e.studentid = '" .$row['StudentID']. "'"; echo $SQL2 ." < -- >". $SQL3 ."<BR>"; // Debug Purpose only }