FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


[SOLVED] "Dynamic" Pages that sort MySQL data





tchaunt
Today is my first day off from school for Christmas break. My goal was to be able to get the basic article sorting scripts finished. I have managed to get the pages developed I need to:
1)Add new links to the database
2)Display all the links (sorted by date submitted and title)
3)Display links based on category (sorted by date submitted and title)

At this point, I really don't have to worry about it, but in the near future, I will end up having more scripts. I want to go ahead and knock this out of the way so I don't have to work on the needed script during school. What I want to do is create a page that will display the articles in sets of 10, going down the list. Each additional set of 10 should be a new page. I don't mind having to add in the page numbers manually.

I'm sorry if I didn't explain it good enough. Feel free to reply or send a PM if you need more details on what I want.

Here is a page with a basic example of what I am wanting: http://prophit.frih.org/articles/test.php
Just assume that each number is a different article.

The code for that page is:
Code:
<a href="http://prophit.frih.org/articles/test.php?page=1">1</a> | <a href="http://prophit.frih.org/articles/test.php?page=2">2</a> | <a href="http://prophit.frih.org/articles/test.php?page=3">3</a>
<br/>
<br/>
<?
$mult=$_GET[page];
if (!$_GET) {
     $mult=1;
     }
$mult=$mult*10;
$start=$mult-9;
for ( $i = $start; $i<=$mult; $i += 1) {
   echo $i ."<br/>";
}
?>


NOTE: If you help me, and the script works, I will send you 100 Frih$.
tchaunt
I feel like a bit of an idiot, but I finally stumbled across the LIMIT clause. Just in case a user found this under search and needs the help, here is the code I ended up putting together:

Code:
<?
$min=$_GET[page]*10;
$min=$min-10;
if (!$_GET) {
$min=0;
}
$con = mysql_connect("localhost","tchaunt_USER","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("tchaunt_articles", $con);

$result = mysql_query("SELECT * FROM Table ORDER BY Column1, Column2 LIMIT $min, 10") or die(mysql_error());

while($row = mysql_fetch_array($result)) {
 //NEEDED ACTIONS;
  }

mysql_close($con);
?>

If the url of the above page is http://server.com/articles, then to view articles 11-20, you would send the page the needed data by using the following link:
Code:
<a href="http://server.com/articles?page=2"> 11 - 20</a>


The reason that the above code works is because when using limit, your data starts at 0. If you are on the first page, here is what would be executed.
Quote:
$min = 1*10
$min= 10 - 10

So $min now equals 0. The LIMIT clause is added as
Quote:
LIMIT <start>, <number>
where <start> is where you start from, and <number> is how many articles to show.

Since the above code uses "LIMIT $min, 10" (and $min equals 0), you will see all the articles between the first and tenth article.
raver
Great job, you almost learned by yourself how to do a pagination script Laughing
You are missing a part though. You should not add the links manually, but let the script crreate the pagination links.
Here is how:

Code:

//this variable contains the number of articles from the "articles" table
$resultsTotal = mysq_result(mysql_query("SELECT COUNT(*) FROM articles"),0);

//ok, so you want to display 10 articles per page, how many pages do we have?
//we use the ceil() function to round up the number. For example if the result is 10.3 it will return 11
$noOfPages = ceil($resultsTotal/10);

//we are using a "page" variable for the select
if(isset($_GET['page'])) {
   if($_GET['page']>0 && $_GET['page']<=$resultsTotal) {
  $minRes = $_GET['page']*10;
  $maxRes = $minRes+10;

} else {
   $minRes = 0;
   $maxRes = 10;
}

$q = mysql_query("SELECT * FROM articles ORDER BY whatever LIMIT $minRes,$maxRes");

}

//groovy, now let's create the "Next" and "Previous" links

if(!isset($_GET['page'])) {
  echo "<a href='articles.php?page=1'>Next</a>";
} else  if($_GET['page']>=1 && $_GET['page']<$noOfPages) {

 $nextPage = $_GET['page']+1;
 $backPage = $_GET['page']-1;
 echo "<a href='articles.php?page=$nextPage'>Next</a>";
 echo "<a href='articles.php?page=$backPage'>Previous</a>";
} else if($_GET['page']==$noOfPages) {
$backPage = $_GET['page']-1;
 echo "<a href='articles.php?page=$backPage'>Previous</a>";
} else {
   echo "stop messing with the links >:P ";

}


Or something like that Razz
Related topics
PHP Toturail
How to get your dynamic PHP website crawled better by se ?
Which script is good to learn?
Help on Backing up MySQL Databases
Separating contents of MySQL row and put them in variables?
How to design a theme?
Which scripts are the most useful to learn?
MySQL Data Not Inserting - I keep getting an error
Comment System
Looping through multiple mysql results
Is it acceptable to alter a human being?
Server 2 Super High Load and DNS down
What MySQL Data type would you use to store a Webpage
Storing MySQL connection data securely and conveniently
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.