dongritan5654 2013-04-21 16:31
浏览 92

MySQL / PHP用分页搜索

I am getting a syntax error when I search for something that doesn't exist in my database.

The error is, "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 '-1,1' at line 8"

The Form I use to search the database is:

  <form name="search" method="post" action="page.php">
<table width="450px">
<tr>
 <td valign="top">
  <label for="first_name">First Name:</label>
 </td>
 <td valign="top">
  <input  type="text" name="find" maxlength="50" size="30">
 </td>
</tr>
<tr>
 <td valign="top"">
  <label for="last_name">Last Name:</label>
 </td>
 <td valign="top">
  <input  type="text" name="f" maxlength="50" size="30">
 </td>
</tr>
<tr>
 <td valign="top">
  <label for="comments">Select your State:</label>
 </td>
 <td valign="top">
   <select name="state" id="state">
   <option value=""></option>
<option value="Alabama">Alabama</option>
<option value="Alaska">Alaska</option>
<option value="Arizona">Arizona</option>
<option value="Arkansas">Arkansas</option>
<option value="California">California</option>
<option value="Colorado">Colorado</option>
<option value="Connecticut">Connecticut</option>
<option value="Delaware">Delaware</option>
<option value="Florida">Florida</option>
<option value="Georgia">Georgia</option>
<option value="Hawaii">Hawaii</option>
<option value="Idaho">Idaho</option>
<option value="Illinois">Illinois</option>
<option value="Indiana">Indiana</option>
<option value="Iowa">Iowa</option>
<option value="Kansas">Kansas</option>
<option value="Kentucky">Kentucky</option>
<option value="Louisiana">Louisiana</option>
<option value="Maine">Maine</option>
<option value="Maryland">Maryland</option>
<option value="Massachusetts">Massachusetts</option>
<option value="Michigan">Michigan</option>
<option value="Minnesota">Minnesota</option>
<option value="Mississippi">Mississippi</option>
<option value="Missouri">Missouri</option>
<option value="Montana">Montana</option>
<option value="Nebraska">Nebraska</option>
<option value="Nevada">Nevada</option>
<option value="New Hampshire">New Hampshire</option>
<option value="New Jersey">New Jersey</option>
<option value="New Mexico">New Mexico</option>
<option value="New York">New York</option>
<option value="North Carolina">North Carolina</option>
<option value="North Dakota">North Dakota</option>
<option value="Ohio">Ohio</option>
<option value="Oklahoma">Oklahoma</option>
<option value="Oregon">Oregon</option>
<option value="Pennsylvania">Pennsylvania</option>
<option value="Rhode Island">Rhode Island</option>
<option value="South Carolina">South Carolina</option>
<option value="South Dakota">South Dakota</option>
<option value="Tennessee">Tennessee</option>
<option value="Texas">Texas</option>
<option value="Utah">Utah</option>
<option value="Vermont">Vermont</option>
<option value="Virginia">Virginia</option>
<option value="Washington">Washington</option>
<option value="West Virginia">West Virginia</option>
<option value="Wisconsin">Wisconsin</option>
<option value="Wyoming">Wyoming</option>
</select>
 </td>
</tr>
<tr>
 <td valign="top">
  <label for="comments">Zip Code:</label>
 </td>
 <td valign="top">
   <input  type="text" name="zip" maxlength="30" size="30">
 </td>
</tr>
<tr>
 <td valign="top">
  <label for="email">Email Address:</label>
 </td>
 <td valign="top">
  <input  type="text" name="email" maxlength="80" size="30">
 </td>
</tr>
<tr>
 <td valign="top">
  <label for="telephone">Phone Number:</label>
 </td>
 <td valign="top">
  <input  type="text" name="info" maxlength="30" size="30">
 </td>
</tr>

<tr>
 <td colspan="2" style="text-align:center">
  <input type="hidden" name="searching" value="yes" />
 <input type="submit" name="search" value="Search" />
 </td>
</tr>
</table>


 </form>

The Pagination script I am using is:

<?php 



 // Connects to your Database 

 mysql_connect("xxxxxxx", "xxxxxxx", "xxxxx") or die(mysql_error()); 

 mysql_select_db("xxx") or die(mysql_error()); 
error_reporting(0);

 //This checks to see if there is a page number. If not, it will set it to page 1 

 if (!(isset($pagenum))) 

 { 

 $pagenum = 1; 

 } 



 //Here we count the number of results 

 //Edit $data to be your query 

 $data = mysql_query("SELECT * FROM form_again WHERE field_1 
LIKE '%" . mysql_real_escape_string($find)      . "%' AND field_2
LIKE '%" . mysql_real_escape_string($f)         . "%' AND field_3
LIKE '%" . mysql_real_escape_string($info)      . "%' AND field_7
LIKE '%" . mysql_real_escape_string($zip)       . "%' AND field_5
LIKE '%" . mysql_real_escape_string($state)     . "%' AND field_6 
LIKE '%" . mysql_real_escape_string($city)  . "%' AND field_9
LIKE '%" . mysql_real_escape_string($email)     . "%'") or die(mysql_error()); 

 $rows = mysql_num_rows($data); 



 //This is the number of results displayed per page 

 $page_rows = 1; 



 //This tells us the page number of our last page 

 $last = ceil($rows/$page_rows); 



 //this makes sure the page number isn't below one, or more than our maximum pages 

 if ($pagenum < 1) 

 { 

 $pagenum = 1; 

 } 

 elseif ($pagenum > $last) 

 { 

 $pagenum = $last; 

 } 



 //This sets the range to display in our query 

 $max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 

 //This is your query again, the same one... the only difference is we add $max into it

 $data_p = mysql_query("SELECT * FROM form_again WHERE field_1 
LIKE '%" . mysql_real_escape_string($find)      . "%' AND field_2
LIKE '%" . mysql_real_escape_string($f)         . "%' AND field_3
LIKE '%" . mysql_real_escape_string($info)      . "%' AND field_7
LIKE '%" . mysql_real_escape_string($zip)       . "%' AND field_5
LIKE '%" . mysql_real_escape_string($state)     . "%' AND field_6 
LIKE '%" . mysql_real_escape_string($city)  . "%' AND field_9
LIKE '%" . mysql_real_escape_string($email)     . "%' $max") or die(mysql_error()); 


 //This is where you display your query results

 while($info = mysql_fetch_array( $data_p )) 

 { 

echo "<hr><br>First Name:&nbsp;"; 
 echo $info['field_1'];
 echo "<br>Last Name:&nbsp;"; 
 echo $info['field_2']; 
 echo "<br>Home Phone:&nbsp;"; 
 echo $info['field_3']; 
 echo "<br>Cell Phone:&nbsp;"; 
 echo $info['field_4']; 
 echo "<br>City:&nbsp;"; 
 echo $info['field_6']; 
 echo "<br>State:&nbsp;"; 
  echo $info['field_5']; 
 echo "<br>Zip:&nbsp;"; 
 echo $info['field_7']; 
 echo "<br>Email:&nbsp;"; 
echo $info['field_9']; 
 echo "<br><hr>"; 

 } 



 // This shows the user what page they are on, and the total number of pages

 echo " --Page $pagenum of $last-- <p>";


 // First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.

 if ($pagenum == 1) 

 {

 } 

 else 

 {

 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";

 echo " ";

 $previous = $pagenum-1;

 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";

 } 


 //just a spacer

 echo " ---- ";


 //This does the same as above, only checking if we are on the last page, and then generating the Next and Last links

 if ($pagenum == $last) 

 {

 } 

 else {

 $next = $pagenum+1;

 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";

 echo " ";

 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";

 } 

 ?> 
 <?php
  //This counts the number or results - and if there wasn't any it gives them a little message explaining that 
 $anymatches=mysql_num_rows($data_p); 
 if ($anymatches == 0) 
 { 
 echo "Sorry, but we can not find an entry to match your query<br><br>"; 
 } 
 //And we remind them what they searched for 
 echo "<b>Searched For:
        </b> " .$find; 

 ?>
  • 写回答

2条回答 默认 最新

  • douou2026 2013-04-21 16:44
    关注
    $max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 
    

    This is the part where the -1, 1 appears. The syntax error is that mysql here expects a positive number.

    If $rows = mysql_num_rows($data); is zero, $last is zero and $pagenum too. Then when you substract 1 from $pagenum, you have -1 in your query.

    A possible fix is to swap this condition from:

    if ($pagenum < 1) {
        $pagenum = 1;
    } elseif ($pagenum > $last) {
        $pagenum = $last;
    }
    

    to:

    if ($pagenum > $last) {
        $pagenum = $last;
    }
    if ($pagenum < 1) {
        $pagenum = 1;
    }
    
    评论

报告相同问题?

悬赏问题

  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)