Dear All,,
I have a search query for searching 40 fields that are belonging to 5 different tables..
I have used INNER JOIN for this purpose...
I have stored the search conditions into a $string variable and the result is getting correctly..
Here comes the problem....
I want to paginate the result of my search query comprises of JOINing of 5 tables...
I had done it as i know. but it has error... The last page will be displayed correctly and as i click on the FIRST link it will give the below error message...
SELECT * FROM reg_personal inner join reg_english_level inner join reg_services inner join reg_status ON(reg_personal.personal_code=reg_english_level.english_code AND reg_english_level.english_code=reg_services.services_code AND reg_services.services_code=reg_status.status_code)WHERE LIMIT 0,5
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0,5' at line 1
I am giving the complete code with this thread..
Any one please help me..
Thanks in advance.
Jino..
<?php
require "config.php";
session_start();
if(!session_is_registered('code'))
{ header('Location:index.php?msg=Please login by using your username and password');
exit;}
if(session_is_registered('username'))
{ $username=$_SESSION['username'];
if($username=='Adminpanel'){$home='main-menu.php';}else if($username!='Adminpanel'){$home='main-menu1.php';}
}
$name=$_POST['name'];
$agefrom=$_POST['agefrom'];
$ageto=$_POST['ageto'];
$sex=$_POST['sex'];
$marital_status=$_POST['marital_status'];
$mobile=$_POST['mobile'];
$phone=$_POST['phone'];
$email=$_POST['email'];
$s1=$_POST['services1'];
$lc1=split(',',$s1);
$j1=count($lc1);
$service= $lc1[0];
$country=$_POST['Category'];
$location=$_POST['SubCat'];
$level=$_POST['level1'];
$university=$_POST['universities1'];
$training=$_POST['training1'];
$course=$_POST['course1'];
$ref=$_POST['refered_by'];
$ref_lc=split(',',$ref);
$ref=count($ref_lc);
$refered_by=$ref_lc[0];
$eng1=$_POST['english_level'];
$eng1_lc=split(',',$eng1);
$enj1=count($eng1_lc);
$english_level= $eng1_lc[0];
$total_score1=$_POST['total_score1'];
$total_score2=$_POST['total_score2'];
$writing_score1=$_POST['writing_score1'];
$writing_score2=$_POST['writing_score2'];
$speaking_score1=$_POST['speaking_score1'];
$speaking_score2=$_POST['speaking_score2'];
$listening_score1=$_POST['listening_score1'];
$listening_score2=$_POST['listening_score2'];
$reading_score1=$_POST['reading_score1'];
$reading_score2=$_POST['reading_score2'];
$result=$_POST['result'];
$date_from=$_POST['datefrom'];
$dat_com1=split('-',$date_from);
$da_no=count($dat_com1);
$d=$dat_com1[0];
$m=$dat_com1[1];
$y=$dat_com1[2];
$datefrom=date("$y-$m-$d");
$date_to=$_POST['dateto'];
$dat_com2=split('-',$date_to);
$da_no=count($dat_com2);
$d=$dat_com2[0];
$m=$dat_com2[1];
$y=$dat_com2[2];
$dateto=date("$y-$m-$d");
//Query for simply getting the total no. of records...
$query_pagi = " SELECT count(personal_code) FROM reg_personal ";
$result_pagi = mysql_query($query_pagi, $link) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result_pagi);
$numrows = $query_data[0];
$rows_per_page1 = 15;
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
$query_array=array();
if($date_to!='' && $date_from!='')
{ $query_array[]= "reg_personal.personal_date BETWEEN '". $datefrom ."' AND '". $dateto ."'";}
else if($date_from!='' && $date_to==''){
$query_array[]= " reg_personal.personal_date = '". $datefrom ."'";}
if($name!=''){
$query_array[]= " reg_personal.personal_name = '". $name . "'";}
if($sex!=''){
$query_array[]= "reg_personal.personal_sex='".$sex."'";}
if($phone!=''){
$query_array[]= " reg_personal.personal_phone = '". $phone . "'";}
if($mobile!=''){
$query_array[]= " reg_personal.personal_mobile = '".$mobile."'";}
if($email!=''){
$query_array[]= " reg_personal.personal_email = '". $email . "'";}
if($marital_status!=''){
$query_array[]= " reg_personal.personal_matrital_status = '".$marital_status . "'";}
if($service!='0'){
$query_array[]= " reg_services.services_service1 ='". $service ."' OR reg_services.services_service2='". $service ."'";}
if($country!='0'){
$query_array[]= "reg_services.services_country1 ='". $country ."' OR reg_services.services_country2='". $country ."'";}
if($location!='0'){
$query_array[]= " reg_services.services_location1 ='". $location ."' OR reg_services.services_location2 ='". $location ."'";}
if($level!='0'){
$query_array[]= " reg_services.services_level1 ='". $level ."' OR reg_services.services_level2='". $level ."'";}
if($university!='0'){
$query_array[]= " reg_services.services_universities1 ='". $university ."' OR reg_services.services_universities2='". $university ."'";}
if($training!='0'){
$query_array[]= " reg_services.services_training1 ='". $training ."' OR reg_services.services_training2='". $training ."'";}
if($course!='0'){$query_array.= " reg_services.services_course1 ='". $course ."' OR reg_services.services_course2='". $course ."'";}
if($english_level!=0){
$query_array[]= "reg_english_level.english_level1 = '".$english_level. "' OR reg_english_level.english_level2 = '".$english_level. "' OR reg_english_level.english_level3 = '".$english_level. "'";}
if($total_score!=''){
$query_array[]= " reg_english_level.english_total_score1 = '". $total_score . "' OR reg_english_level.english_total_score2 = '". $total_score . "' OR reg_english_level.english_total_score3 = '". $total_score . "'";}
if($writing_score!=''){
$query_array[]= " reg_english_level.english_writing_score1 = '". $writing_score . "' OR reg_english_level.english_writing_score2 = '". $writing_score . "' OR reg_english_level.english_writing_score3 = '". $writing_score . "'";}
if($speaking_score!=''){
$query_array[]= "reg_english_level.english_speaking_score1 = '". $speaking_score . "' OR reg_english_level.english_speaking_score2 = '". $speaking_score . "' OR reg_english_level.english_speaking_score3 = '". $speaking_score . "'";}
if($listining_score!=''){
$query_array[]= " reg_english_level.english_listening_score1 = '". $listining_score . "' OR reg_english_level.english_listening_score2 = '". $listining_score . "' OR reg_english_level.english_listening_score3 = '". $listining_score . "'";}
if($reading_score!=''){
$query_array[]= " reg_english_level.english_reading_score1 = '". $reading_score . "' OR reg_english_level.english_reading_score2 = '". $reading_score . "' OR reg_english_level.english_reading_score3 = '". $reading_score . "'";}
if($result!=0){
$query_array[]= "reg_english_level.english_result1 = '". $result . "' OR reg_english_level.english_result2 = '". $result . "' OR reg_english_level.english_result3 = '". $result . "'";}
if($refered_by!=0){
$query_array[]= "reg_status.status_refered_by ='". $refered_by ."'";
}
$query_string=implode(" AND ",$query_array);
echo "<br><br>query_string=".$query_string;
$query_string1="$query_string";
echo "<br>query_string1_updated=".$query_string1;
if($query_string1!='')
{ $query_data=" SELECT SQL_CALC_FOUND_ROWS * FROM reg_personal inner join reg_english_level inner join reg_services inner join reg_status ON(reg_personal.personal_code=reg_english_level.english_code AND reg_english_level.english_code=reg_services.services_code AND reg_services.services_code=reg_status.status_code) WHERE $query_string1 ";
}else{
$query_data="SELECT SQL_CALC_FOUND_ROWS * FROM reg_personal inner join reg_english_level inner join reg_services inner join reg_status ON(reg_personal.personal_code=reg_english_level.english_code AND reg_english_level.english_code=reg_services.services_code AND reg_services.services_code=reg_status.status_code)";}
echo "<br>First Query".$query_data;
$result_data=mysql_query($query_data,$link) or die(mysql_error());
if($pageno <= $lastpage) {
$prevpage = $pageno-1;}
if ($pageno <= $lastpage){
$nextpage = $pageno+1;}
//Query for getting the count of records satisfying the search conditions...
$query1 = "select FOUND_ROWS()";
$result = mysql_query($query1,$link) or trigger_error("SQL", E_USER_ERROR);
$row_no = mysql_fetch_assoc($result);
$numrows1 = $row_no['FOUND_ROWS()'];
$rows_per_page2 = 5;
$lastpage = ceil($numrows1/$rows_per_page2);
echo '<br>$_get_pageno='.$_GET['pageno'].'<br>';
if (isset($_GET['pageno']))
{ $pageno = $_GET['pageno'];
} else{
$pageno = $lastpage;}
$pageno = (int)$pageno;
if ($pageno < 1) {
$pageno = 1;
} elseif ($pageno > $lastpage){
$pageno = $lastpage;}
$limit1 = 'LIMIT ' .($pageno - 1) * $rows_per_page2 .',' .$rows_per_page2;
echo "<br>Inner_Query_string1_new=".$query_string1;
$query_data1=" SELECT * FROM reg_personal inner join reg_english_level inner join reg_services inner join reg_status ON(reg_personal.personal_code=reg_english_level.english_code AND reg_english_level.english_code=reg_services.services_code AND reg_services.services_code=reg_status.status_code)WHERE $query_string1 $limit1";
print "<br>Second Query=".$query_data1."<br>";
$result_data1=mysql_query($query_data1) or die(mysql_error());
if($pageno <= $lastpage) {
$prevpage = $pageno-1;}
if ($pageno <= $lastpage){
$nextpage = $pageno+1;}
if($numrows=='0')
{ header('Location:registeration-reports.php?regmsg=No Results. Try Again!'); }
function headerdisp()
{ if(session_is_registered('username'))
{ $username=$_SESSION['username'];
if($username=='Adminpanel'){$home='main-menu.php';}else if($username!='Adminpanel'){$home='main-menu1.php';}
}
echo"<html>
<head>
<meta http-equiv='Content-Language' content='en-us'>
<meta name='GENERATOR' content='Microsoft FrontPage 5.0'>
<meta name='ProgId' content='FrontPage.Editor.Document'>
<meta http-equiv='Content-Type' content='text/html; charset=windows-1252'>
<title>International Academy Office Software</title>
<style>
<!--
p.MsoNormal
{mso-style-parent:'';
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:'Times New Roman';
margin-left:0in; margin-right:0in; margin-top:0in}
-->
</style>
</head>
<body bgcolor='#0360A5'>
<p align='center'><img border='0' src='logo.jpg' width='500' height='143'></p>
<form method='POST' action='registeration_form_edit.php'>
<div align='center'>
<center>
<table border='1' cellpadding='7' cellspacing='0' style='border-collapse: collapse' bordercolor='#FFFFFF' width='700' height='50' bgcolor='#CCCCFF'>
<tr>
<td height='19' colspan='2' align='center' bgcolor='#FFFFCC' width='686'>
<table border='0' cellpadding='5' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111' width='100%'>
<tr>
<td width='80%'><span style='font-weight: 700'>
<font face='Verdana' style='font-size: 9pt'><a href='$home'>
Main Menu</a> >>$username>> Payment
Results</font></span></td>
<td width='20%' bgcolor='#FFFFCC'>
<p align='center'><span style='font-weight: 700'>
<font face='Verdana' style='font-size: 9pt' color='#FF0000'>
<a href='logout.php' style='text-decoration: none'>
<font color='#FF0000'>Logout</font></a></font></span></td>
</tr>";
}
echo "<br>Prev_page=".$prevpage;
echo "<br>Query_string1_test=".$query_string1;
function pagination($nextpage,$lastpage,$prevpage,$pageno,$query_string1)
{ echo "<tr>
<td width='100%' colspan='2'><strong><span style='font-weight: 700'><font color=' #800080' face='Arial' size='2'>
<font color='#800080'> </font><a href='{$_SERVER['PHP_SELF']}?pageno=1 &query_string=$query_string1'><<First</a>
<a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'><<Prev</a></font></span></strong><font face='Arial' style='font-size: 11pt'>
</font><font face='Arial' style='font-size: 11pt;'> [</font><strong><span style='font-weight: 700'>
<font face='Arial' size='2'>$pageno - $lastpage</font></span></strong><font face='Arial' style='font-size: 11pt; '>]
</font><strong><span style='font-weight: 700'><font face='Arial' size='2' color='#800080'> <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'> Next>></a>
<a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>Last>></a>
</font></span></strong></td>
</tr></table>
</td>
</tr>";
}
function disp($row)
{ $p_code=$row[personal_code];
$sql1=mysql_query(" SELECT * FROM reg_english_level WHERE english_code='$p_code' ") or die(mysql_error());
while($raw1=mysql_fetch_array($sql1))
{ $eng11=mysql_query(" SELECT course_name FROM englesh_level WHERE englesh_level_code='$raw1[english_level1]' ") or die(mysql_error());
while($eng1=mysql_fetch_array($eng11))
{ $course_name1=$eng1['course_name'];}
$eng2=$raw1['english_level2'];
$eng22=mysql_query(" SELECT course_name FROM englesh_level WHERE englesh_level_code='$raw1[english_level2]' ") or die(mysql_error());
while($eng2=mysql_fetch_array($eng22))
{ $course_name2=$eng2['course_name'];}
$eng3=$raw1['english_level3'];
$eng33=mysql_query(" SELECT course_name FROM englesh_level WHERE englesh_level_code='$raw1[english_level3]' ") or die(mysql_error());
while($eng3=mysql_fetch_array($eng33))
{ $course_name3=$eng3['course_name']; }
$eng_date=$raw1['english_date'];}
$email=$row['personal_email'];
$sql2=mysql_query("SELECT * FROM reg_payment WHERE payment_code='$p_code'") or die(mysql_error());
while($raw2=mysql_fetch_array($sql2))
{ $total_paid=$raw2['payment_amount_paid'];
$payment_date=$raw2['payment_date'];
$total_amount=$raw2['payment_total_amount'];
$balance=$total_amount-$total_paid;}
echo"
<tr>
<td width='503' height='19'><font face='Arial' style='font-size: 9pt'>
$row[personal_name];<b>($row[personal_code])</b>,$row[personal_present_address],Tel: $row[personal_phone], E-mail:
<a href='mailto:$email'><font color='#000000'>
$email</font></a>,<b>Joined $course_name1,$course_name2,$course_name3, at
$eng_date - Total Paid $total_paid/-. Balance $balance/- payment $payment_date.</b></font></td>
<td width='169' height='19'>
<font face='Arial' style='font-size: 10.5pt'><b>
<a href='registration-result-reports.php?id=$p_code'>Continue to Payment >></a></b></font></td>
</tr>";
}
$val='search';
if($val=='search')
{ headerdisp();
echo "<b>Total Search Results = </b>".$numrows1;
pagination($nextpage,$lastpage,$prevpage,$pageno,$query_string1);
while($row=mysql_fetch_array($result_data1))
{ disp($row);
}
} echo"</table>
</center>
</div>
</form>";
?>
