Dynamic SQL statement problem ?

[limey]

I have a strange problem with what I think is life span of variable value or php.ini setting or mysql setting or something.

The following code goes into a script that provides multiple pages for the user to browse through. If I hard code the 'where' clause values into the sql statement everything works fine - page after pages of the correct database data. However, I want to make the SQL query dynamic by allowing the user to choose town, county and postcode. The first script works fine (hardcoded where values). The second script (dynamic values) only shows results on the first web page.

"SELECT * FROM customers
where cust_new = 'Y'
and cust_town = 'testTown'
and cust_county = 'testCounty'
and cust_postcode = 'testPostcode'
ORDER BY cust_reg_date ASC
LIMIT $page, $limit" // these are numeric variables set up for pages

"SELECT * FROM customers
where cust_new = 'Y'
and cust_town = '".$town."'
and cust_county = '".$county."'
and cust_postcode = '".$postcode."'
ORDER BY cust_reg_date ASC
LIMIT $page, $limit" // these are numeric variables set up for pages

Anybody got any ideas on what's causing this problem ?

[tekky]

could be all the escaping you are messing up the string somewhere... easy way to check that... is to actually use the PHP tags here...

$var = "SELECT * FROM customers 
where cust_new = 'Y' 
and cust_town = '$town' 
and cust_county = '$county' 
and cust_postcode = '$postcode' 
ORDER BY cust_reg_date ASC 
LIMIT $page, $limit" // these are numeric variables set up for pages

looking at my preview... it appears fine.... Since you are using double " to enclose your string you could simplifiy this by using what I did above....

and to troubleshoot...

$result = mysql_query($var) OR die('error: ' .mysql_error());

[crimaniak]

you don't transfer your variables ($town and so on) to second page..
you can:

1. use POST method to transfer it (Form with buttons FF, BACK,... and hidden fields)

2. better: use GET method to transfer it (add urlencoded values to links
   $href="list.php?page=$pagenum&town=".urlencode($town) ...

3. better: use session to store it
   $_SESSION['town']=$town

4. best: use subsession class to store it (not now)

[tekky]

I would recommend POST over GET.... SESSION over POST...

but what he said is what my troubleshooting would likely reveal... that not all your variables were set properly...