Using Dropdown to LIMIT rows

[MargateSteve]

I am trying to include a dropdown menu on a page so that a user can choose how many records will be shown and return that data back to the same page. what I have so far is....

 

The Form

<form action="PHP_SELF" method="post" name="records_per_page" target="_self">
     Number of Records per page
     <select name="records_per_page" id="records_per_page">
       <option value="5">5</option>
       <option value="10">10</option>
       <option value="15">15</option>
       <option value="20">20</option>
       <option value="25">25</option>
       <option value="30">30</option>
       <option value="40">40</option>
       <option value="50">50</option>
   </select>
   </form>

 

The Query

// How many rows to show per page
$rowsPerPage = 25;

// Retrieve all the data from the seasons table
$result = mysql_query
("SELECT *, 
DATE_FORMAT(`season_start`,' %D %M %Y') AS startdate, 
DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate 
FROM seasons 
LIMIT ";
if ($_POST['records_per_page'] = '')
	$result .= "'$rowsPerPage' ");
else
$result .= "'$records_per_page' ");

or die(mysql_error());  

 

The error I get is

Parse error: syntax error, unexpected ';' in /homepages/46/d98455693/htdocs/content/test/seasonslist2.php on line 28

which refers to the line

LIMIT ";

 

Having posted the code into Dreamweaver it also shows errors for the two options in the if statement

$result .= "'$rowsPerPage' ");

$result .= "'$records_per_page' ");

so it is clearly that I have set the code up incorrectly, probably with misplacement of ;'s.

 

What I am trying to do is when the page loads normally, the records should be limited to $rowsPerPage but if a user has specified a number of rows in the dropdown it should show that number. Once I have that sorted I will also be trying to implement a'Start from Record Number' option.

 

As always, any advice would be immensely appreciated!

 

Thanks in advance

Steve

[Pikachu2000]

You should really do yourself a favor and build your query string in a variable, then use the variable in the query execution. Anyhow, I didn't test this, but it should work for you.

 

// How many rows to show per page
$rowsPerPage = 25;

// Retrieve all the data from the seasons table
$query = "SELECT *,
DATE_FORMAT(`season_start`,' %D %M %Y') AS startdate,
DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate
FROM seasons";
$query .= ( isset($_POST['records_per_page']) && intval($_POST['records_per_page']) > 0 ) ? 'LIMIT ' . (int) $_POST['records_per_page'] : 'LIMIT ' . $rowsPerPage;

$result = mysql_query( $query ) or die('Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>');

[MargateSteve]

Magnificent answer, as always.

 

Needed a slight tweak (putting a space between 'FROM seasons' and the ' " ' and changed the form action to "<?php echo $PHP_SELF;?>" but other than that it was seamless!

 

There are a few bits in the code that I have not come across before so will have a read up to understand how it works before moving on to the next bit.

 

Thanks again

Steve

[Pikachu2000]

Don't use $_SERVER['PHP_SELF'] as a form action. It presents a known XSS vulnerability. Either use action="" or name the script explicitly in the action attribute.

[MargateSteve]

I have changed it to action="". I did not realise that would work just as well!

Thanks

Steve