I have an error using mysql BETWEEN statement

habbardone

Hi,

I am trying to continue with a search after the previous one
so I save the last row number to the var $Db_rowno

All the rows inthe table have a sequential rowno column.

Obviously when I start the search the $Db_rowno is 1

Probably I am using the BETWEEN statement incorrectly 😕

This is my code:

$hits = 5;
$lastrow = $Db_rowno+$hits;

$sql = "SELECT rowno,  $Db_base_cd, $Db_lang1_cd FROM phrases WHERE ($Db_base_cd != '' AND $Db_lang1_cd != '')  AND level = '$Db_level' AND catmain = '$Db_catmain' AND confirm = 'y' BETWEEN rowno=$Db_rowno AND rowno=$lastrow LIMIT $hits";

I get the error:

could not FIND phrases 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1 AND rowno=6 LIMIT 5' at line 1

Can you see what I have done wrong ?

PS
Things were working fine before I started using the BETWEEN statement :o

Thanks

Weedpacket

Read up on the syntax of the BETWEEN statement: you're using it wrong.

habbardone

Hmmm

I looked again ...

Maybe this is better ?

$sql = "SELECT rowno,  $Db_base_cd, $Db_lang1_cd FROM phrases WHERE ($Db_base_cd != '' AND $Db_lang1_cd != '')  AND level = '$Db_level' AND catmain = '$Db_catmain' AND confirm = 'y' AND rowno BETWEEN $Db_rowno AND $lastrow LIMIT $hits";

habbardone

Well I have tried this:

$sql = "SELECT rowno, $Db_base_cd, $Db_lang1_cd FROM phrases WHERE ($Db_base_cd != '' AND $Db_lang1_cd != '') AND level = '$Db_level' AND catmain = '$Db_catmain' AND confirm = 'y' AND rowno BETWEEN '$Db_rowno' AND '$lastrow' LIMIT $hits";

This displays as:

Sql: SELECT rowno, engl, span1, fren, turk FROM phrases WHERE (engl != '' AND span1 != '' AND fren != '') AND level = '1' AND catmain = 'verb' AND confirm = 'y' AND rowno BETWEEN '1' AND '6' LIMIT 5

But I am getting errors, I think it is because some of the rows between 1 and 6 have no data so they do not
satisfy the WHERE (engl != '' AND span1 != '' AND fren != '')

Therefore using the LIMIT may be better but how would this work ?

If I put:
$sql = "SELECT rowno, $Db_base_cd, $Db_lang1_cd FROM phrases WHERE ($Db_base_cd != '' AND $Db_lang1_cd != '') AND level = '$Db_level' AND catmain = '$Db_catmain' AND confirm = 'y' LIMIT $Db_rowno, $hits";

Would this take the next 5 rows that satisfy the rest of the requirements ?