Assistance needed for MySQL syntax issue

Data and Databases
1

I’m encountering a MySQL syntax error and need help resolving it

I’m receiving an error from my PHP script that states:

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 ‘-5,5’ at line 1

After hours of reviewing my code, I still can’t identify the source of the error. Could anyone lend a hand in figuring out what might be wrong?

Here’s my full script:

<?php
require_once("database.php");
require_once("functions.php");
require_once("templates/Header.php");
require_once("pagination.php");

$display_all = false;
if ($_REQUEST['region_id']) {
    $query = "Select region_name From regions where region_id = ".$_REQUEST['region_id'];
    $result = mysql_query($query) or die(mysql_error());
    $has_results = mysql_num_rows($result);
    $region_data = mysql_fetch_array($result);
    $display_text = "<table width='550'><tr><td>Displaying Properties For <font color='FF0000'>$region_data[region_name]</font></td></tr></table>"; 
}
else {
    $display_text = "Displaying All Properties"; 
    $display_all = true;
}

if ($_REQUEST['region_id'])
    $extra_sql = "and property_listings.region_id=".$_REQUEST['region_id'];
else
    $extra_sql ="";
    
$main_query = "select * from property_listings, real_estate_agents, agent_priority where property_listings.agent_id = real_estate_agents.agent_id and real_estate_agents.priority_level = agent_priority.priority_level and real_estate_agents.status = 'active' ".$extra_sql;
$count_result = mysql_query($main_query);
$total_count = mysql_num_rows($count_result);

if($total_count == 0) {
    $property_table = "<table align=center width=500 cellspacing=0>\n";
    $property_table .= "<tr>\n<td>$navigation</td></tr>\n\t";
    $property_table .= "<tr>\n<td>&nbsp;</td></tr>\n\t";
    $property_table .= "</tr>\n</table>\n\n";
    $property_table .= "<br><br><center><Strong>No Properties Found!</Strong></center>";
    require_once("templates/Header.php");
    require_once("templates/Search.php");   
    require_once("templates/Footer.php");
}

$items_per_page = 5;
$page_number = 1;

if ($_REQUEST['page']) {
    $page_number = $_REQUEST['page'];
} 

$pager = new pagination($total_count, $items_per_page, $page_number);
$pager->create();

$final_query = "select * from property_listings, real_estate_agents, agent_priority where property_listings.agent_id = real_estate_agents.agent_id and real_estate_agents.priority_level = agent_priority.priority_level and real_estate_agents.status = 'active' ".$extra_sql." order by view_count desc LIMIT $pager->start_record,$pager->rows_count";
$final_result = mysql_query($final_query) or die(mysql_error());
$property_rows = mysql_num_rows($final_result);

if($property_rows > '0') {
    $property_table .= "<table align=center width=500 cellspacing=0>\n";
    $property_table .= "<tr>\n<td width=75>&nbsp;</td>\n\t";
    $property_table .= "</tr>\n</table>\n\n";
    $property_table .= "<table align=center width=500 border=0 bordercolor=#336699 rules=rows cellspacing=0>\n";

    $start_num = $pager->start_record+1;
    $end_num = $items_per_page*$page_number;
    $total_pages = $pager->getTotalPages();
    
    if ($page_number==$total_pages)
        $end_num = $pager->getTotalCount();
        
    $record_info = "<strong>Showing Properties $start_num - $end_num</strong>";
    $property_table .= "<tr>\n<td colspan=4>$record_info</td></tr>\n\t";

    while($property = mysql_fetch_array($final_result)) {
        $property_table .= "<tr style='border-width:1; border-color:blue' onMouseOver='this.style.background=#FFFFFF; this.style.cursor=hand' onMouseOut='this.style.background=white' onClick='window.open(details.php?id=$property[property_id], _top)'>\n\t";
        $property_table .= "<td height=60>";
        $property_table .= "<table align=center width='100%'>\n";
        $property_table .= "<TR style='background-color:#001592; color:white; font-family:verdana; font-size:11; font-weight:bold; height=20px '>\n<TD colspan=4>\n$property[priority_name] listing</TD>\n</TR>\n";
        $property_table .= "<tr>\n\t<td width=75>";
        
        $formatted_price = number_format($property[listing_price], 2, ".", "'");
        
        if(empty($property[region_id]))
            $region_name = "Not Available";
        else {
            $region_id = $property[region_id];
            $region_query = "Select region_name from regions where region_id=$region_id";
            $region_result = mysql_query($region_query) or die(mysql_error());
            $region_info = mysql_fetch_array($region_result);
            $region_name = $region_info[region_name];
        }

        $property_table .= "<TR bgcolor='D2EEFF'><TD width='20%'><b>Country: </b></TD><TD width='20%'> $property[country_name]</TD>";
        $property_table .= "<TD width='20%'><b>Region: </b></TD><TD width='20%'>$region_name</TD></TR>";
        $property_table .= "<TR bgcolor='FFFFFF'>\n\t<td valign=top width='20%'><B>City: </b></td><td valign=top width='20%'>$property[city_name]</td>";
        $property_table .= "<TD width='20%'><b>Address: </b></TD><TD width='20%'> $property[street_address]</TD></TR>";
        $property_table .= "<TR bgcolor='D2EEFF'><TD width='20%'><b>Price: </b></TD><TD width='20%'> $$formatted_price</TD>";
        $property_table .= "<TD width='20%'><b>Area: </b></TD><TD width='20%'> $property[square_footage] sq.ft.</TD</TR>";
        $property_table .= "</td>\n\t";
        $property_table .= "</tr>\n";
        $property_table .= "<HR>";
        $property_table .= "</table>\n\n</td>\n</tr>\n\n";
    }
    $property_table .= "</table>";
} else {
    $property_table = "<table align=center width=500 cellspacing=0>\n";
    $property_table .= "<tr>\n<td>&nbsp;</td></tr>\n\t";
    $property_table .= "</tr>\n</table>\n\n";
    $display_text = "<table align=center width=550 cellspacing=0><tr><td><br><br><center><Strong>No Properties Found!</Strong></center></td></tr></table>";
}

echo "<BR><b>&nbsp;&nbsp;&nbsp;".$display_text;

require_once("templates/Search.php");
require_once("templates/Footer.php");
?>
2

yep, looks like your $pager->start_record is going negative. that ‘-5,5’ error means it’s using start_record = -5. check the pagination logic, especially when page_number is 0 or less. add a check to keep start_record >= 0 before LIMIT.

3

hmm interesting issue there! what happens if you echo out $pager->start_record right before the LIMIT query? sounds like the pagination class might be returning negative values when theres no page parameter or invalid data. are you validating $_REQUEST['page'] before passing it to the pagination constructor?

4

The negative value in your LIMIT clause suggests your pagination class is calculating start_record incorrectly. This typically occurs when the page number calculation results in a negative offset. I’ve encountered this exact issue before when dealing with edge cases in pagination logic. The problem likely stems from how your pagination class handles the initial page state or when invalid page parameters are passed. Try wrapping your LIMIT variables in a validation check before constructing the final query. Something like ensuring both start_record and rows_count are positive integers before using them in the SQL statement. You might also want to add some debugging output to see what values your pagination object is actually generating, particularly when no page parameter is provided or when it receives unexpected input.