sql syntax error on odbc

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
tr3s
Forum Newbie
Posts: 17
Joined: Mon May 19, 2003 10:29 am
Location: Philippines
Contact:

sql syntax error on odbc

Post by tr3s »

hi!

does anybody had a problem using LIMIT on odbc? here's my code, if there's any error in it:

Code: Select all

<?php
$dsn = odbc_connect('emp', '', '');
$sql = "SELECT * FROM items LIMIT 0 , 5";
$result= @odbc_do($dsn, $sql);
while (odbc_fetch_row($result)) {
   $id = odbc_result($result, 1);
   $name = odbc_result($result, 2);
   echo $name;
}
?>
i always got a syntax error!

any idea pls!



?>
User avatar
award
Forum Newbie
Posts: 13
Joined: Tue Jul 15, 2003 10:45 am
Location: Wakefield, UK
Contact:

Post by award »

Take the @ out of the code, this will give you a more exact error and then post it here also.

Instead of "SELECT * FROM items LIMIT 0 , 5" try "SELECT Top 5 * from items" instead. Also what sort of DB are you using?
tr3s
Forum Newbie
Posts: 17
Joined: Mon May 19, 2003 10:29 am
Location: Philippines
Contact:

Post by tr3s »

thanks man!

actually, I’m using pear db. using mysql works fine but in odbc (ms access), the error points to the LIMIT expression in the sql. looks weird huh…

i tried it using the native odbc functions then just got the odbc_fetch_row error! by the way, without the LIMIT exp, then it’s perfect.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

LIMIT is a mysql specific thing, if you want your application to be usable with multiple DB's don't use it..

btw, Access/Jet has a limited SQL functionality..
tr3s
Forum Newbie
Posts: 17
Joined: Mon May 19, 2003 10:29 am
Location: Philippines
Contact:

Post by tr3s »

ok.

what is the sql to get the next 5 records in access/jet then? can i still use the TOP syntax?

or will it be easier if i use sql server? since i need to run my site on NT server.

thanks!
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

Using a RDBMS would likely improve performance if you use JOINS and insert and delete records quite often, if you are usually just reading a bunch of data from one table at the time and adding now and again a flatfile system is fine (Access would sort of be one of those although Jet provides an SQL api).

NEVER use SELECT * , ALWAYS use explicit columns..

Because of that MySQL has the LIMIT function, a lot of peopla are abusing this and make a lot of unecessary resource using queries, I am not sure if TOP is widely supported, rarely used or seen that myself.. To me, the proper way of chunking/paginating is to first query for the id's (SELECT id FROM bla bla), all of them, then based on this paginate and fetch the wanted records with a WHERE asking just for the id's you are interrested in, and cache this id list for the next page/fetch so that you dont have to do that query again...
Unless ofcourse you are talking millions of records, then you want to limit on other things like dates or location etc for the initial query
tr3s
Forum Newbie
Posts: 17
Joined: Mon May 19, 2003 10:29 am
Location: Philippines
Contact:

Post by tr3s »

i don’t see any drawbacks using LIMIT in mysql, in fact it lightens the task since i just need a single query. i usually use SELECT * if i need all the fields in the recordset so in one way it’s a help.

what i want to know is if there is an equivalent of mysql SELECT LIMIT in access/jet.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

I dont know Bill gates stuff wekk enough to know that..

It may be good (enough) for your application and envrionment, but from a developers point of view you want to do what you can for versatility, re-usability and effectiveness without extensive cost increase, so using SELECT * and LIMIT is cheap-cheats that may cost later..
Post Reply