awjudd Posted October 1, 2011 Share Posted October 1, 2011 Post the source for your PHP page you are running this on. Or better yet, run this in phpMyAdmin and then you'll get a better sense of what is going on. ~juddster Quote Link to comment Share on other sites More sharing options...
jd22 Posted October 1, 2011 Author Share Posted October 1, 2011 Post the source for your PHP page you are running this on. Or better yet, run this in phpMyAdmin and then you'll get a better sense of what is going on. ~juddster 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; ?> Quote Link to comment Share on other sites More sharing options...
awjudd Posted October 1, 2011 Share Posted October 1, 2011 And what does $SUBMIT_QUERY become? What does it do when you run it in phpmyadmin? You still have your $AGE_TO and $AGE_FROM swapped in your between clause. ~juddster Quote Link to comment Share on other sites More sharing options...
jd22 Posted October 1, 2011 Author Share Posted October 1, 2011 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 Quote Link to comment Share on other sites More sharing options...
awjudd Posted October 1, 2011 Share Posted October 1, 2011 What is the value of $SUBMIT_QUERY (i.e. what is emitted)? And no that doesn't matter/ ~juddster Quote Link to comment Share on other sites More sharing options...
jd22 Posted October 1, 2011 Author Share Posted October 1, 2011 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. Quote Link to comment Share on other sites More sharing options...
awjudd Posted October 1, 2011 Share Posted October 1, 2011 Why are you doing an INNER JOIN on everything except for the LEFT JOIN on the Enrol table? If there is no match from the LEFT JOIN none of the other rows will be returned because the join condition will not be satisfied. i.e. if a student isn't enrolled in any classes then they will not appear in the ending result set (which is different than how you originally had your query). ~juddster Quote Link to comment Share on other sites More sharing options...
fenway Posted October 3, 2011 Share Posted October 3, 2011 We're at 30+ replies and we're going around in cricles. Quote Link to comment Share on other sites More sharing options...
jd22 Posted October 3, 2011 Author Share Posted October 3, 2011 @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); Quote Link to comment Share on other sites More sharing options...
awjudd Posted October 3, 2011 Share Posted October 3, 2011 This is now because you are CROSS JOINing the results from all of your tables. If you cross join against 3 tables each with 10 rows you are getting 1000 rows in your result set. So given each table is larger than 10 rows that is why it is taking forever to return. The LEFT JOINs all the way down except for against the KidsStudent table is the correct approach however, you just need to make sure your query is correct. ~juddster Quote Link to comment Share on other sites More sharing options...
jd22 Posted October 3, 2011 Author Share Posted October 3, 2011 This is now because you are CROSS JOINing the results from all of your tables. If you cross join against 3 tables each with 10 rows you are getting 1000 rows in your result set. So given each table is larger than 10 rows that is why it is taking forever to return. The LEFT JOINs all the way down except for against the KidsStudent table is the correct approach however, you just need to make sure your query is correct. ~juddster 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.