Jump to content


Photo

Using LIMIT or TOP in MSSQL

query limit top php

  • Please log in to reply
4 replies to this topic

#1 SarahB12

SarahB12

    Member

  • Members
  • PipPip
  • 27 posts

Posted 20 March 2014 - 03:32 PM

Hey guys, I'm trying to limit my query to 5 results, but TOP is not working and LIMIT obviously does not work (I usually work with MySQL).

 

Here is my query:

$query = "SELECT PERSON.PERSON_ID, PERSON.LAST_NAME, PERSON.FIRST_NAME FROM PERSON where PERSON.LAST_NAME like '%$q%' or PERSON.FIRST_NAME like '%$q%' order by PERSON.LAST_NAME";

Can someone please point me in the right direction for limiting the query to 5 results?

 

Thank you!

~ Sarah



#2 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,709 posts
  • LocationBonita, FL

Posted 20 March 2014 - 04:48 PM

$query = "SELECT TOP 5 PERSON.PERSON_ID, PERSON.LAST_NAME, PERSON.FIRST_NAME FROM PERSON where PERSON.LAST_NAME like '%$q%' or PERSON.FIRST_NAME like '%$q%' order by PERSON.LAST_NAME";
That should be all you need to do.
Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#3 SarahB12

SarahB12

    Member

  • Members
  • PipPip
  • 27 posts

Posted 20 March 2014 - 06:25 PM

$query = "SELECT TOP 5 PERSON.PERSON_ID, PERSON.LAST_NAME, PERSON.FIRST_NAME FROM PERSON where PERSON.LAST_NAME like '%$q%' or PERSON.FIRST_NAME like '%$q%' order by PERSON.LAST_NAME";
That should be all you need to do.

 

I have tried both "SELECT TOP 5" and "SELECT TOP (5)". Here is the error that is generated:

Fatal Error:  Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft OLE DB Provider for SQL Server<br/><b>Description:</b> Line 1: Incorrect syntax near '5'.' in C:\xampp\apps\wordpress\htdocs\autocomplete-search\search.php:9
Stack trace:
#0 C:\xampp\apps\wordpress\htdocs\autocomplete-search\search.php(9): com->execute('SELECT TOP 5 PE...')
#1 {main}
  thrown in


#4 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,709 posts
  • LocationBonita, FL

Posted 20 March 2014 - 07:14 PM

What database software are you using, and what version? Also what functions are you using to connect to the database from PHP?

SELECT TOP 5 ... works fine for me. TOP has been supported for as long as I have been using SQL Server (since version 2000).
Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#5 SarahB12

SarahB12

    Member

  • Members
  • PipPip
  • 27 posts

Posted 20 March 2014 - 09:03 PM

SQL Server 2000.

 

Here is my connection string: 

$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");

//define connection string, specify database driver
$connStr = "Provider=sqloledb;Data Source=SQLDB;Initial Catalog=PERSONDB;User Id=*****;Password=*****";

$conn->open($connStr); //Open the connection to the database

$query = "SELECT TOP (5) PERSON.PERSON_ID, PERSON.LAST_NAME, PERSON.FIRST_NAME FROM PERSON where PERSON.LAST_NAME like '%$q%' or PERSON.FIRST_NAME like '%$q%' order by PERSON.LAST_NAME";

$rs = $conn->execute($query);

Thank you.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com