PHP Forum

**MrConn** · 10-25-2008, 08:47 AM

Hi,
I have some problems on my site...
I have a database with 20 entrys (will grow) and I want the entrys out on my webpage.

By now I am doing something like this to do it:

Code:

 
SELECT * FROM table ORDER BY id DESC LIMIT 1

And saving the results for latest entry...
But the way I am doing it I need to write 20 lines of that line, something that seems very unnessecary!

The way I would like to have it is like this:

$title1 - title from DESC LIMIT 1
$story1 - story from DESC LIMIT 1
$title2 - title from DESC LIMIT 2
$story2 - story from DESC LIMIT 2
And so on...

Is there a way to do this?

**chrishirst** · 10-25-2008, 08:58 AM

remove the LIMIT x then ALL the records will be retrieved. You then loop and display the fields.

AND do NOT use "SELECT * FROM" it is very inefficient

**MrConn** · 10-27-2008, 02:08 PM

Let me show you what I mean...
If you check out www.sauda.net/demo you will see how my output is supposed to be... But I dont know how to do that...

**tripy** · 02:48 PM

Code:

select column_list
from table
where clause1=value1
order by field_name desc
limit 10 offset 0

What you want is a called "paging".
The two keywords in your request will be the limit (telling how many records to fetch) and the offset (telling from where it will start).

this can be done easily like this:

PHP Code:


			
<?php
$self=$_SERVER['PHP_SELF'];
$page=(isset($_GET['page']))?$_GET['page']:0;  //if there is a page parameter, use it, otherwise declare that this is the page 0
$maxRecords=10;  //10 records by pages
/*
a little trick, rather than counting the total each time, fetch 1 more item than displayed. 
As long as you have more than $maxRecords rows returned, you have a "next page" link displayed. 
This put less strains on high traffic sites
*/
$realFetchCount=$maxRecords+1;
$offset=$page*$maxRecords;
//we fetch the id (for the link page), the complete title and the 100 first characters of the story
$qMain=<<<sql
select id, title, substring(story, 0, 100)+'...'
from table
order by id DESC
limit $realFetchCount offset $offset
sql;
$rMain=mysql_query($qMain);

$html=""; //The generated html will be placed here
$cntDisplay=0;
$totFetched=mysql_num_rows($rMain);
while($oMain=mysql_fetch_object($rMain) && $cntDisplay<$maxRecords){
  $html.=<<<html
<li>
  <a href="read.php?id={$oMain->id}">{$oMain->title}</a><br/>
  {$oMain->story}
</li>
html;
  $cntDisplay++;  //to exclude the rows we fetched in addition, we only go up to $maxRecords rows
}

/*
We now generate the navigation part
*/
$nav=<<<html
<a href="$self?page=0">First page</a> &nbsp;
html;

if($page>0){
  //we are not on the first page
  $prev=$page-1;
  $nav.=<<<html
<a href="$self?page=$prev">Previous page</a> &nbsp;
html;
}
if($totFetched>$maxRecords){
  //we have more records than those displayed
  $next=$page+1;
  $nav.=<<<html
<a href="$self?page=$next">Next page</a> &nbsp;
html;
}
?>
<html>
  <head>
    <title>blah</title>
  </head>
  <body>
    <?php echo $nav;?>
    <ul>
      <?php
      echo $html;
      ?>
    </ul>
    <?php echo $nav;?>
  </body>
</html>

I did not tested this code, but it should be fonctionnal.

**MrConn** · 10-27-2008, 03:43 PM

Ehh...
How do I connect this to my DB?

**tripy** · 10-27-2008, 04:13 PM

by doing an mysql_connect() and mysql_select_db() first.
http://www.php.net/manual/en/functio...-select-db.php

PHP Code:


			
  <?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}
?>

</span> </span> </div> </div>

**MrConn** · 10-27-2008, 04:50 PM

Ok, then it seems like I acctually did connect it before, I just didnt get any result... :/
I've changed 'title' to 'tittel' and 'story' to 'ingress' so that it would work with my db, but it doesnt show anything...
www.sauda.net/demo/test.php

**tripy** · 10-27-2008, 05:19 PM

Right now, it says

Quote:

Can't use foo : Access denied for user 'sauda_sauda'@'%' to database 'sauda_saud'

which means that the DB refused the connection.
Either the username or the password are wrong, or you mispeeled the DB name.
Take care of the case, a db named "SauDa" is not the same as "Sauda".

**lothop** · 05:52 PM

Just a guess

Its trying to get the data from the table foo.
$db_selected = mysql_select_db('foo', $link);

Change the 'foo' to the name of your database table. and enter it between the ' '

**tripy** · 10-27-2008, 08:10 PM

Quote:

Change the 'foo' to the name of your database table. and enter it between the ' '

No.
A database is like a book, where the server is the library.
You connect to a server, select a database, and run queries against tables to fetch columns

But it's true that this have to be adapted to the database you want to work on.

**MrConn** · 10-27-2008, 08:22 PM

Ohh... Was just a test to see if it acctually did "anything"... Sry...
Its up now...

**tripy** · 04:49 AM

Ok, so I did put this in my ide, and there was a small mistake that rpevented the datas from being fetched actually.
The complete and up to date script is:

PHP Code:


			
<?php
$link = mysql_connect('localhost', 'user', 'pwd');
$db_selected = mysql_select_db('test', $link);
$self=$_SERVER['PHP_SELF'];
$page=(isset($_GET['page']))?$_GET['page']:0;  //if there is a page parameter, use it, otherwise declare that this is the page 0
$maxRecords=5;  //10 records by pages
/*
a little trick, rather than counting the total each time, fetch 1 more item than displayed.
As long as you have more than $maxRecords rows returned, you have a "next page" link displayed.
This put less strains on high traffic sites
*/
$realFetchCount=$maxRecords+1;
$offset=$page*$maxRecords;
//we fetch the id (for the link page), the complete title and the 100 first characters of the story
$qMain=<<<sql
select id, title, story
from demoPaging
order by id desc
limit $realFetchCount offset $offset
sql;
$rMain=mysql_query($qMain);
$html=""; //The generated html will be placed here
$cntDisplay=0;
$totFetched=mysql_num_rows($rMain);
while( ($oMain=mysql_fetch_object($rMain)) && ($cntDisplay<$maxRecords) ){
  $html.=<<<html
<li>
  <a href="read.php?id={$oMain->id}">{$oMain->title}</a><br/>
  {$oMain->story}
</li>
html;
  $cntDisplay++;  //to exclude the rows we fetched in addition, we only go up to $maxRecords rows
}

/*
We now generate the navigation part
*/
$nav=<<<html
<a href="$self?page=0">First page</a> &nbsp;
html;

if($page>0){
  //we are not on the first page
  $prev=$page-1;
  $nav.=<<<html
<a href="$self?page=$prev">Previous page</a> &nbsp;
html;
}
if($totFetched>$maxRecords){
  //we have more records than those displayed
  $next=$page+1;
  $nav.=<<<html
<a href="$self?page=$next">Next page</a> &nbsp;
html;
}
?>

<html>
  <head>
    <title>blah</title>
  </head>
  <body>
    <?php echo $nav;?>
    <ul>
      <?php
      echo $html;
      ?>
    </ul>
    <?php echo $nav;?>
  </body>
</html>

The result is:

HTML Code:

<html>
  <head>
    <title>blah</title>
  </head>
  <body>
    <a href="1.php?page=0">First page</a> &nbsp;<a href="1.php?page=1">Next page</a> &nbsp;    <ul>
      <li>
  <a href="read.php?id=7">title 7</a><br/>
  story 7
</li><li>
  <a href="read.php?id=6">title 6</a><br/>
  story 6
</li><li>
  <a href="read.php?id=5">title 5</a><br/>
  story 5
</li><li>
  <a href="read.php?id=4">title 4</a><br/>
  story 4
</li><li>
  <a href="read.php?id=3">title 3</a><br/>
  story 3
</li>    </ul>
    <a href="1.php?page=0">First page</a> &nbsp;<a href="1.php?page=1">Next page</a> &nbsp;  </body>
</html>

I had to change

PHP Code:


			
while($oMain=mysql_fetch_object($rMain)  && $cntDisplay<$maxRecords)

to

PHP Code:


			
while( ($oMain=mysql_fetch_object($rMain)) && ($cntDisplay<$maxRecords) )

**MrConn** · 10-28-2008, 05:26 PM

Now I've finally managed to get the data out of the DB the proper way!

The next step is to implant it in a paging-system, wich I have NO clue how to do!
Here is the code as it is now. I have included this to the mainpage, so it looks good! Check here: http://www.sauda.net/demo/t2.php

But my question now, as you may understand, is how to implent a paging-system here?

PHP Code:


			
<?PHP 
include 'config.php';
// this will disaply the latest news in the main column 
$main = mysql_fetch_array(mysql_query("SELECT * FROM nyheter ORDER BY id DESC")); 
echo ' 
<div id="column_main"> 
<div id="content_main"> 
<div id="content_main_img"><img src="images/'. $main['big_thumb'] .'.jpg" alt="'.  $main['big_thumb'] .'" class="main"/></div> 
  <h2>'. $main['tittel'] .'</h2> 
  <p>'. $main['ingress'] .' <a href="'. $main['id'] .'">Read more</a></p> 
</div> 
</div> 
'; 
?> 
<?PHP 
// This will display column 1 
$Q1 = mysql_query("SELECT * FROM nyheter ORDER BY id DESC LIMIT 1,1");
while($r1 = mysql_fetch_array($Q1)) {  
echo '
<div id="column1"> 
<div id="content_1"> 
<div id="content_art_img"><img src="images/'. $r1['small_thumb'] .'.jpg" alt="'. $r1['small_thumb'] .'" class="art"/></div> 
<h2>'. $r1['tittel'] .'</h2> 
<p>'. $r1['ingress'] .' <a href="'. $r1['id'] .'">Read more</a></p> 
</div>';  
}  
?> 
<?PHP 
// This will display column 2 
$Q2 = mysql_query("SELECT * FROM nyheter ORDER BY id DESC LIMIT 2,1");
while($r2 = mysql_fetch_array($Q2)) {  
echo '
<div id="content_2"> 
<div id="content_art_img"><img src="images/'. $r2['small_thumb'] .'.jpg" alt="'. $r2['small_thumb'] .'" class="art"/></div> 
<h2>'. $r2['tittel'] .'</h2> 
<p>'. $r2['ingress'] .' <a href="'. $r2['id'] .'">Read more</a></p> 
</div>
</div>';  
}  
?> 
<?PHP 
// This will display column 3 
$Q2 = mysql_query("SELECT * FROM nyheter ORDER BY id DESC LIMIT 3,1");
while($r2 = mysql_fetch_array($Q2)) {  
echo '
<div id="column2"> 
<div id="content_left2"> 
<div id="content_art_img2"><img src="images/'. $r2['medium_thumb'] .'.jpg" alt="'. $r2['medium_thumb'] .'" class="art2"/></div> 
<h2>'. $r2['tittel'] .'</h2> 
<p>'. $r2['ingress'] .' <a href="'. $r2['id'] .'">Read more</a></p> 
</div>';  
}  
?>
<?PHP 
// This will display column 4
$Q3 = mysql_query("SELECT * FROM nyheter ORDER BY id DESC LIMIT 4,1");
while($r3 = mysql_fetch_array($Q3)) {  
echo '
<div id="content_right2"> 
<div id="content_art_img2"><img src="images/'. $r3['medium_thumb'] .'.jpg" alt="'. $r3['medium_thumb'] .'" class="art2"/></div> 
<h2>'. $r3['tittel'] .'</h2> 
<p>'. $r3['ingress'] .' <a href="'. $r3['id'] .'">Read more</a></p> 
</div>
</div>';  
}  
?>
<?PHP 
// This will display column 5 
$Q4 = mysql_query("SELECT * FROM nyheter ORDER BY id DESC LIMIT 5,1");
while($r4 = mysql_fetch_array($Q4)) {  
echo '
<div id="column1"> 
<div id="content_1"> 
<div id="content_art_img"><img src="images/'. $r4['small_thumb'] .'.jpg" alt="'. $r4['small_thumb'] .'" class="art"/></div> 
<h2>'. $r4['tittel'] .'</h2> 
<p>'. $r4['ingress'] .' <a href="'. $r4['id'] .'">Read more</a></p> 
</div>';  
}  
?> 
<?PHP 
// This will display column 6 
$Q5 = mysql_query("SELECT * FROM nyheter ORDER BY id DESC LIMIT 6,1");
while($r5 = mysql_fetch_array($Q5)) {  
echo '
<div id="content_2"> 
<div id="content_art_img"><img src="images/'. $r5['small_thumb'] .'.jpg" alt="'. $r5['small_thumb'] .'" class="art"/></div> 
<h2>'. $r5['tittel'] .'</h2> 
<p>'. $r5['ingress'] .' <a href="'. $r5['id'] .'">Read more</a></p> 
</div>
</div>';  
}  
?>
<?PHP
$Q6 = mysql_query("SELECT * FROM nyheter ORDER BY id DESC LIMIT 7,1"); 
while($r6 = mysql_fetch_array($Q6)) {
echo ' 
<div id="column_main"> 
<div id="content_big"> 
<div id="content_main_img"><img src="images/'. $r6['big_thumb'] .'.jpg" alt="'.  $r6['big_thumb'] .'" class="main"/></div> 
  <h2>'. $r6['tittel'] .'</h2> 
  <p>'. $r6['ingress'] .' <a href="'. $r6['id'] .'">Read more</a></p> 
</div> 
</div>';
}
?>

**tripy** · 10-28-2008, 06:55 PM

:-/
Look at the script I posted, it does paging...

**MrConn** · 07:27 PM

Yes, yes it does... But with your script I could not figure how to show the results the way I wanted.
How, with your script, can you make the articles display in different ways?

Just to make it simple:

What do I have to do to get this output?

HTML Code:

<html>
  <head>
    <title>blah</title>
  </head>
  <body>
    <a href="1.php?page=0">First page</a> &nbsp;<a href="1.php?page=1">Next page</a> &nbsp;    <ul>
      <li>
  <a href="read.php?id=7"><strong>title 7</strong></a><br/>
  story 7
</li><li>
  <a href="read.php?id=6">title 6</a><br/>
  story 6
</li><li>
  <a href="read.php?id=5">title 5</a><br/>
  story 5
</li><li>
  <a href="read.php?id=4">title 4</a><br/>
  story 4
</li><li>
  <a href="read.php?id=3"><strong>title 3</strong></a><br/>
  story 3
</li>    </ul>
    <a href="1.php?page=0">First page</a> &nbsp;<a href="1.php?page=1">Next page</a> &nbsp;  </body>
</html>

**tripy** · 07:38 PM

Quote:

Just to make it simple:
What do I have to do to get this output?

Hum, you point your browser to the php script on a php enabled server....
How would you do otherwise ?

Quote:

But with your script I could not figure how to show the results the way I wanted.

And for that, you simply modify the HTML that is generated in the while loop

PHP Code:


			
while( ($oMain=mysql_fetch_object($rMain)) && ($cntDisplay<$maxRecords) ){
  $html.=<<<html
<li>
  <a href="read.php?id={$oMain->id}">{$oMain->title}</a><br/>
  {$oMain->story}
</li>
html;

</span></span>

**MrConn** · 10-28-2008, 07:54 PM

The output i posted was not excatly like yours... I changed it a litte...

And by modifying the the html like you suggest the html will be identical for every output...
If you just take a look at http://www.sauda.net/demo/t2.php you will see that that's not a option for me... I need every output to go in seperate <div>'s...

But thanx for your suggestions so far