Displaying specific results using links

[SeanPeoples]

Hi, fairly new to PHP over the last couple weeks. Been having a problem with certain queries. I have a database with football results, games, teams etc. I can filter these using drop down and that's all well and good. The problem I'm having is displaying the data via gameweek. I've been asked to display the table like so - Gameweek1 will display week1 teams, results etc. Gameweek2 will display week2... and so on.

I can manage to do this in a drop down. But I've been asked to display this using links like "Previous, 1, 2, 3 Next". I've tried pagination but I couldn't figure it out. Can anyone point me in the right direction? If I need a GET() method, how would I go about coding that so it will be used in a link(s)? Been searching and searching to find an answer but to no avail...

//Database connection etc...

$gameweek = "SELECT * FROM games WHERE gameweek= 1";
    
    //if(isset($_GET['gameweek']))
    //{
    //    $gameweek = $_GET['gameweek'];
    //    
    //}
    //....
    $result=mysqli_query($connection, "select * from games WHERE gameweek= 1");
 //Print table and table headings...
    
    mysqli_close($connection);
?>
<a href="http://weeks.php?gameweek=2">Week 2</a>
<a href="http://weeks.phpgameweek=3">Week 3</a>

</body>
</html>

[mac_gyver]

using pagination, with a 'rows per page' value of one, and ordering the rows in the query by gameweek, would give the result you need, especially for the previous/next links, and would end up working for any number of games stored in the table.

[SeanPeoples]

Thanks for the reply! Just a quick question before I try this out, the gameweek have more than one game per week. For example, gameweek1 has 3 games, gameweek2 has 5 games. Would the $rows_per_page still work with a value of one? Each gameweek has a different number of games.

[CroNiX]

$rows_per_page would just be the maximum results shown per page. If $rows_per_page = 5, and there were 3 results found, it would just be one page with 3 results. If 6 results were found, it would be 2 pages with 5 results on first page and 1 result on 2nd.

[mac_gyver]

the rows per page would apply to how many gameweeks to display on one page. if set to a one, only one gameweek would be displayed per page and the pagination links would become gameweek selectors rather than page selectors.

 

next, do you want to display all information for the games on the selected gameweek or do you want to paginate that information too (assuming there are a large enough number of games in any gameweek to require pagination)?

[SeanPeoples]

Just the gameweek is to be displayed by week with all other information within the tables. So gameweek1 would be displayed along with team names, scores etc. Thanks for the help!

[mac_gyver]

okay, what's your code that attempts to do this?

 

you started the thread with a general fishing question. you got replies mentioning ways you could catch a fish. we are not here to do the fishing for you, especially since the only specific information you have posted is a table name and one column name.

[SeanPeoples]

Apologies, I never noticed the reply. I've been looking at tutorials on pagination and have managed to display the results along with your suggestions and a little changing about. All I need to do now is to implement a table to tidy up the data. Here it is.

    $connection =mysqli_connect("localhost", "root", "");
    mysqli_select_db($connection, "gaa2013");
    

$sql = "SELECT COUNT(gameweek) FROM games";
$query = mysqli_query($connection, $sql);
$row = mysqli_fetch_row($query);

$rows = $row[0];

$page_rows = 10;

$last = ceil($rows/$page_rows);

if($last < 1){
	$last = 1;
}

$pagenum = 1;

if(isset($_GET['pn'])){
	$pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
}

if ($pagenum < 1) { 
    $pagenum = 1; 
} else if ($pagenum > $last) { 
    $pagenum = $last; 
}

$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;
$sql = "SELECT gameweek, team1, team2 FROM games ORDER BY gameweek $limit";
$query = mysqli_query($connection, $sql);

$textline1 = "Users(<b>$rows</b>)";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";

$paginationCtrls = '';

if($last != 1){
	
	if ($pagenum > 1) {
        $previous = $pagenum - 1;
		$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a>     ';
	
		for($i = $pagenum-4; $i < $pagenum; $i++){
			if($i > 0){
		        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a>   ';
			}
	    }
    }
	
	$paginationCtrls .= ''.$pagenum.'   ';
	
	for($i = $pagenum+1; $i <= $last; $i++){
		$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a>   ';
		if($i >= $pagenum+4){
			break;
		}
	}
	
    if ($pagenum != $last) {
        $next = $pagenum + 1;
        $paginationCtrls .= '     <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> ';
    }
}
$list = '';
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
	$gameweek = $row["gameweek"];
	$team1 = $row["team1"];
	$team2 = $row["team2"];
	//$name = $row["name"];
	$list .= $gameweek.' - '.$team1.' VS '.$team2. '';
}

mysqli_close($connection);
?>
//Divs and html style...
//...