MySQL's LIMIT and MSSQL's TOP statements question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hcmweb
Forum Newbie
Posts: 1
Joined: Thu May 16, 2002 9:28 am

MySQL's LIMIT and MSSQL's TOP statements question

Post by hcmweb »

Hi all...
I am trying to port a php program over from using a MySQL database to using MSSQL 7.0 and have run into two nasty snags. They both deal with the LIMIT statement available in MySQL and how to rewrite the PHP and SQL code to accomplish same result using a Microsoft SQL Server 7.0 database.

1) A fairly straightforward change of "SELECT ... LIMIT x" to SELECT TOP x ..." should do the trick for most of the statements, but I have one module that builds the query statement before executing, and since I'm not that good with PHP, it's kicking my butt! I know that the statement should be rewritten to be something like; "SELECT TOP x FROM table ORDER BY field1" but I don't know what I'm doing wrong with that part of the code.

2) This paging class code also contains a variation of the LIMIT statement utilizing a starting position and number of rows to return. This one is the most difficult as I believe it may require a subquery, but am not sure.

The code that I need to change is posted below, I posted the whole class since it's Open Source so that I don't hack off some critical piece of information. If you could also include a sample of how the SQL statement will look after being generated that would be GREAT!! (mainly so I can learn from it and not pester anyone for help :oops: )

******************** START OF CODE SECTION *******************
<?php

class splitPageResults {
function splitPageResults(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows) {
if (empty($current_page_number)) $current_page_number = 1;

$pos_to = strlen($sql_query);
$pos_from = strpos($sql_query, ' from', 0);

$pos_group_by = strpos($sql_query, ' group by', $pos_from);
if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by;

$pos_having = strpos($sql_query, ' having', $pos_from);
if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having;

$pos_order_by = strpos($sql_query, ' order by', $pos_from);
if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by;

// ********************************************************
// THIS SECTION NEEDS TO BE CHANGED TO USE TOP INSTEAD OF LIMIT
$pos_limit = strpos($sql_query, ' limit', $pos_from);
if (($pos_limit < $pos_to) && ($pos_limit != false)) $pos_to = $pos_limit;
// *******************************************************

$pos_procedure = strpos($sql_query, ' procedure', $pos_from);
if (($pos_procedure < $pos_to) && ($pos_procedure != false)) $pos_to = $pos_procedure;

// ********************************************************
// THIS APPEARS TO BE THE OTHER SECTION AS OUTLINED IN SEC 2 ABOVE
$offset = ($max_rows_per_page * ($current_page_number - 1));
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
// ********************************************************

$reviews_count_query = tep_db_query("select count(*) as total " . substr($sql_query, $pos_from, ($pos_to - $pos_from)));
$reviews_count = tep_db_fetch_array($reviews_count_query);
$query_num_rows = $reviews_count['total'];
}

function display_links($query_numrows, $max_rows_per_page, $max_page_links, $current_page_number, $parameters = '', $page_name = 'page') {
global $PHP_SELF;

if ($parameters != '') $parameters .= '&';

// calculate number of pages needing links
$num_pages = intval($query_numrows / $max_rows_per_page);

// $num_pages now contains int of pages needed unless there is a remainder from division
if ($query_numrows % $max_rows_per_page) $num_pages++; // has remainder so add one page

$pages_array = array();
for ($i=1; $i<=$num_pages; $i++) {
$pages_array[] = array('id' => $i, 'text' => $i);
}

if ($num_pages > 1) {
$display_links = tep_draw_form('pages', basename($PHP_SELF), '', 'get');

if ($current_page_number > 1) {
$display_links .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $page_name . '=' . ($current_page_number - 1), 'NONSSL') . '" class="splitPageLink">' . PREVNEXT_BUTTON_PREV . '</a>&nbsp;&nbsp;';
} else {
$display_links .= PREVNEXT_BUTTON_PREV . '&nbsp;&nbsp;';
}

$display_links .= sprintf(TEXT_RESULT_PAGE, tep_draw_pull_down_menu($page_name, $pages_array, '', 'onChange="this.form.submit();"'), $num_pages);

if (($current_page_number < $num_pages) && ($num_pages != 1)) {
$display_links .= '&nbsp;&nbsp;<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $page_name . '=' . ($current_page_number + 1), 'NONSSL') . '" class="splitPageLink">' . PREVNEXT_BUTTON_NEXT . '</a>';
} else {
$display_links .= '&nbsp;&nbsp;' . PREVNEXT_BUTTON_NEXT;
}

if ($parameters != '') {
if (substr($parameters, -1) == '&') $parameters = substr($parameters, 0, -1);
$pairs = explode('&', $parameters);
while (list(, $pair) = each($pairs)) {
list($key,$value) = explode('=', $pair);
$display_links .= tep_draw_hidden_field(rawurldecode($key), rawurldecode($value));
}
}

if (SID) $display_links .= tep_draw_hidden_field(tep_session_name(), tep_session_id());

$display_links .= '</form>';
} else {
$display_links = sprintf(TEXT_RESULT_PAGE, $num_pages, $num_pages);
}

return $display_links;
}

function display_count($query_numrows, $max_rows_per_page, $current_page_number, $text_output) {
$to_num = ($max_rows_per_page * $current_page_number);
if ($to_num > $query_numrows) $to_num = $query_numrows;
$from_num = ($max_rows_per_page * ($current_page_number - 1));
if ($to_num == 0) {
$from_num = 0;
} else {
$from_num++;
}

return sprintf($text_output, $from_num, $to_num, $query_numrows);
}
}
?>

********************** END OF CODE SECTION *******************

I have been beating my head over this for weeks and can find no reference that has been able to help me solve this one. Any help would be greatly appreciated!!!!! :)

Best Regards,
Steve M.
User avatar
cwcollins
Forum Commoner
Posts: 79
Joined: Thu May 16, 2002 3:51 pm
Location: Milwaukee, WI, USA

TOP and LIMIT are different

Post by cwcollins »

Hey,
i did some checking, and i found this thread at PHPBuilder.com. check it out... if you follow this thread to the last, it will give you a possible solution...

Relevant thread:http://www.phpbuilder.com/forum/read.ph ... read=13280

Possible Solution:http://www.phpbuilder.com/forum/read.ph ... read=13280

-c.w.collins
Post Reply