SQL LIMIT Conflict in Dreamweaver MX ??

[grobermatica]

Hi,

I'm using Dreamweaver to create a list from a MySQL table, however I want to limit the list using the SQL syntax "1,999" (so that I can start at the second record and go from there.)

However when I load the page on the server I get an error: 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 'LIMIT 0, 5' at line 1

I'm assuming that this must mean that there is some sort of conflict and that Dreamweaver is setting its own LIMIT on the recordset.

Question is how cna I influence the LIMIT that DMX puts on the recordset? It works fine when I take out my LIMIT clause form the query.

Any Help Appreciated

Craig

 

[r937]

show the entire query, please

sometimes the error is caused by whatever comes just before the quoted part

r937.com | rudy.ca

 

[grobermatica]

Hi,

the query is a simple one:

SELECT *
FROM tblnewsitems
ORDER BY dteAdded DESC
LIMIT 1,999

I'm pretty sure that the LIMIT part is the cause because when I take it out all is fine... except that I get the top record in with the rest... which is what I'm trying to avoid as it is displayed elswhere on the page in full.

Cheers

Craig

 

[r937]

well, your query is fine

very troubling that it's an actual mysql error message, though

try running the query directly in mysql, and i bet it'll work

maybe dreamweaver isn't passing the query correctly...

r937.com | rudy.ca

 

[grobermatica]

The wierd thing is that the error reports the syntax LIMIT 0,5 which is not what I have entered... this makes me think that Dreamweaver is passing this LIMIT. There are the following lines in the code generated by dreamweaver:

mysql_select_db($database_stdConnx, $stdConnx);
$query_rstNewsList = "SELECT * FROM tblnewsitems ORDER BY dteAdded DESC";
[b]$query_limit_rstNewsList = sprintf("%s LIMIT %d, %d", $query_rstNewsList, $startRow_rstNewsList, $maxRows_rstNewsList);[/b]
$rstNewsList = mysql_query($query_limit_rstNewsList, $stdConnx) or die(mysql_error());
$row_rstNewsList = mysql_fetch_assoc($rstNewsList);

I've highlighted the row I think is the culprit... however the limit clause seems to be populated by some variables but I can't find out where these variables are set.

Any ideas?

Cheers

Craig

 

[TonyGroves]

I don't know Dreamweaver, but it would be simple to tweak the PHP code to get rid of the limit.

 

[grobermatica]

I thought so too but if I tweak it in this way to remove the LIMIT part... I get more errors... its like dreamweaver expects it to be there and if I remove it or change the "%d" to my own values dreamweaver ain't happy.

Can't post the error at the moment as I'm an a different machine but I'll put the error in a seperate post.

Any further ideas?

Craig

 

[vacunita]

Can you just change the values of the variables that its expecting before the part where they are expected something like:

mysql_select_db($database_stdConnx, $stdConnx);
$query_rstNewsList = "SELECT * FROM tblnewsitems ORDER BY dteAdded DESC";
[red]
$startRow_rstNewsList="[blue]1[/blue]";
$maxRows_rstNewsList="[blue]999[/blue]"
[/red]
$query_limit_rstNewsList = sprintf("%s LIMIT %d, %d", $query_rstNewsList, [red]$startRow_rstNewsList, $maxRows_rstNewsList[/red]);
$rstNewsList = mysql_query($query_limit_rstNewsList, $stdConnx) or die(mysql_error());
$row_rstNewsList = mysql_fetch_assoc($rstNewsList);

This will not solve the root issue but will let you control the values for the limit clause. You should still try to find where they are coming from. Where the $startRow_rstNewsList and
$maxRows_rstNewsList are being set in dreamweaver's code. As it is those variables that appear to control the limit clause parameters.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[grobermatica]

Thanks vacunita... I'll give it a try :0)

Craig