It appears you have not yet registered with DEVPPL. To register please click here... (it's fast, easy and free!)

Forum

Log In Sponsors
Board index Programming PHP and MySQL Forum

Grabbing from a database

Moderator: Malcolm

Postby webmaster on Wed May 11, 2005 6:40 pm

Here's the script I use for the news-page here on DEVPPL:
Code: Select all
<?php

$result = @mysql_query("SELECT count(*) as count FROM news")
     or die("Error fetching number in DB<br>".mysql_error());



$limit = 10;
$result = @mysql_query("SELECT count(*) as count FROM news")
  or die("Error fetching number in DB<br>".mysql_error());
$row = @mysql_fetch_array($result);
$numrows = $row['count'];


if (!isset($_GET['start']) || $_GET['start'] == "")
  $start = 0;
else
  $start = $_GET['start'];

$pages = intval($numrows/$limit);
if ($numrows%$limit)
  $pages++;

if ($start > 0) {
  $numlink = '<a href="/news/0/">««</a> ';
  $numlink .= '<a href="/news/'.($start - $limit).'/">«</a> ';
} else {
  $numlink = '«« ';
  $numlink .= '« ';
}

for ($i = 1; $i <= $pages; $i++) {
  $newoffset = $limit*($i-1);
  if ($start == $newoffset)
    $numlink .= '['.$i.'] ';
  else
    $numlink .= '<a href="/news/'.$newoffset.'/">'.$i.'</a> ';
}


if ($numrows > ($start + $limit))
  $numlink .= '<a href="/news/='.($start + $limit).'/">»</a> ';
else
  $numlink .= '» ';


if ($start != $newoffset)
  $numlink .= '<a href="/news/='.$newoffset.'/">»»</a> ';
else
  $numlink .= '»»';



   $result = @mysql_query("SELECT * FROM news ORDER BY added DESC LIMIT ".$start.",".$limit."")
   or die("Error fetching data<br>".mysql_error());




while ($data = @mysql_fetch_array($result)) {


   $username = $data[username];

   echo $username;


}


?>
Make sure to check out our TNX Review and Link Vault Review
User avatar
webmaster
Site Admin
 
Posts: 2695
Joined: Tue Aug 17, 2004 1:07 pm
Location: Sweden

Postby reed682 on Thu May 12, 2005 1:53 pm

Ok, here is the entire code... could you guys help me incorporate these ideas into what I already have? I mean, obviously I can't just insert you code at teh bottom and hope it works. Is there anything that I can do to use what i already have only modified?

Code: Select all
<?
$db_name = "database";
$table_name = "table name";
$connection = @mysql_connect("localhost", "username", "*****") or die("Could not connect.");
$db = @mysql_select_db($db_name, $connection) or die("Could not select database.");
$sql = "SELECT id, name, msg, email, url, timestamp FROM $table_name ORDER by id DESC LIMIT 10";
$result = @mysql_query($sql,$connection) or die("Could not execute query.");

while ($row = mysql_fetch_array($result)) {
$timestamp = $row['timestamp'];
$name = $row['name'];
$msg = $row['msg'];
$email = $row['email'];
$url = $row['url'];

// Regular Emoticons
$msg = str_replace(":D","<img src='images/grin.gif'>", $msg);
$msg = str_replace(":)","<img src='images/smile.gif'>", $msg);
$msg = str_replace(":(","<img src='images/sad.gif'>", $msg);
$msg = str_replace("X(","<img src='images/mad.gif'>", $msg);
$msg = str_replace(":-L","<img src='images/frustrated.gif'>", $msg);
$msg = str_replace(":X","<img src='images/love.gif'>", $msg);
$msg = str_replace(":&","<img src='images/sick.gif'>", $msg);
$msg = str_replace(":alien:","<img src='images/alien.gif'>", $msg);
// Character Emoticons
$msg = str_replace(":moped:","<img src='images/moped.gif'>", $msg);
$msg = str_replace(":bob:","<img src='images/bob.gif'>", $msg);
$msg = str_replace(":tiki:","<img src='images/tiki.gif'>", $msg);
$msg = str_replace(":sluggo:","<img src='images/sluggo_smile.gif'>", $msg);
$msg = str_replace(":skittle:","<img src='images/skittle.gif'>", $msg);
stripslashes("$msg");

if ($email !== "") {
$name_link = "
<p><strong><a href='mailto:$email'>$name:</a></strong>
</p>
";
} else {
$name_link = "
<p><strong>$name:</p></strong>
";
}
if ($url !== "") {
$if_url = "
<p><strong><a href='$url'target='_blank'>LINK</a></strong></p>
";
} else if($url == "") {
$if_url = "";
}
$tag_messages .= "
   <table width= '100%' border='0' cellspacing='2' cellpadding='0'>
            <tr>
            <td align='left' valign='top'><span class='body'>$name_link</span>
               <div align='left'>
                  <span class='body'></span></div>
            </td>
            <td align='right' valign='top'><span class='body'>$if_url</span></td>
         </tr>
         <tr>
            <td colspan='2' align='left' valign='top'><span class='body'>$msg</span></td>
         </tr>
         <tr>
            <td colspan='2' align='left' valign='top'>
               <hr>
            </td>
         </tr>
      </table>
";
}
?>

<html>

   <head>
      <meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
      <meta name="generator" content="Adobe GoLive">
      <title>Untitled Page</title>
      <link href="../normal.css" type="text/css" rel="stylesheet" media="all">
      <csactions>
         <csaction name="BEA13ED814" class="Open Window" type="onevent" val0="emoticons.php" val1="" val2="250" val3="700" val4="false" val5="true" val6="false" val7="false" val8="false" val9="false" val10="false" urlparams="1"></csaction>
      </csactions>
      <csscriptdict>
         <script type="text/javascript"><!--
function CSClickReturn () {
   var bAgent = window.navigator.userAgent;
   var bAppName = window.navigator.appName;
   if ((bAppName.indexOf("Explorer") >= 0) && (bAgent.indexOf("Mozilla/3") >= 0) && (bAgent.indexOf("Mac") >= 0))
      return true; /* dont follow link */
   else return false; /* dont follow link */
}
CSStopExecution=false;
function CSAction(array) {return CSAction2(CSAct, array);}
function CSAction2(fct, array) {
   var result;
   for (var i=0;i<array.length;i++) {
      if(CSStopExecution) return false;
      var aa = fct[array[i]];
      if (aa == null) return false;
      var ta = new Array;
      for(var j=1;j<aa.length;j++) {
         if((aa[j]!=null)&&(typeof(aa[j])=="object")&&(aa[j].length==2)){
            if(aa[j][0]=="VAR"){ta[j]=CSStateArray[aa[j][1]];}
            else{if(aa[j][0]=="ACT"){ta[j]=CSAction(new Array(new String(aa[j][1])));}
            else ta[j]=aa[j];}
         } else ta[j]=aa[j];
      }         
      result=aa[0](ta);
   }
   return result;
}
CSAct = new Object;
function CSOpenWindow(action) {
   var wf = "";   
   wf = wf + "width=" + action[3];
   wf = wf + ",height=" + action[4];
   wf = wf + ",resizable=" + (action[5] ? "yes" : "no");
   wf = wf + ",scrollbars=" + (action[6] ? "yes" : "no");
   wf = wf + ",menubar=" + (action[7] ? "yes" : "no");
   wf = wf + ",toolbar=" + (action[8] ? "yes" : "no");
   wf = wf + ",directories=" + (action[9] ? "yes" : "no");
   wf = wf + ",location=" + (action[10] ? "yes" : "no");
   wf = wf + ",status=" + (action[11] ? "yes" : "no");      
   window.open(action[1],action[2],wf);
}

// --></script>
      </csscriptdict>
      <csactiondict>
         <script type="text/javascript"><!--
CSAct[/*CMP*/ 'BEA13ED814'] = new Array(CSOpenWindow,/*URL*/ 'emoticons.php','',250,700,false,true,false,false,false,false,false);

// --></script>
      </csactiondict>
   </head>

   <body bgcolor="#ffffff" link="#b80000" vlink="#999999" leftmargin="0" marginheight="0" marginwidth="0" topmargin="0">
      <div align="center">
         <table width="64" border="0" cellspacing="0" cellpadding="0">
            <tr>
               <td>
                  <form action="send_tag.php" method="post">
                     <div align="center">
                        <table border="0" cellspacing="8" cellpadding="0">
                           <tr>
                              <td align="right" valign="top" width="50">
                                 <div align="right">
                                    <span class="body"><font color="#cc0000">Name:</font></span></div>
                              </td>
                              <td align="left" valign="top"><span class="body"><input type="text" name="name" size="50" maxlength="250"></span></td>
                           </tr>
                           <tr height="50">
                              <td align="right" valign="top" width="50" height="50">
                                 <div align="right">
                                    <font color="#cc0000"><span class="body">Message:</span></font></div>
                              </td>
                              <td align="left" valign="top" height="50"><span class="body"><textarea name="msg" rows="4" cols="50"></textarea></span></td>
                           </tr>
                           <tr>
                              <td align="right" valign="top" width="50">
                                 <div align="right">
                                    <font color="#cc0000"><span class="body">E-Mail:</span></font></div>
                              </td>
                              <td align="left" valign="top"><span class="body"><input type="text" name="email" size="50" maxlength="250"></span></td>
                           </tr>
                           <tr>
                              <td align="right" valign="top" width="50">
                                 <div align="right">
                                    <font color="#cc0000"><span class="body">URL:</span></font></div>
                              </td>
                              <td align="left" valign="top"><input type="text" name="url" size="50" maxlength="250"></td>
                           </tr>
                        </table>
                        <input id="submit2" type="image" name="submit2" src="images/submit.gif" alt=""><a onclick="CSAction(new Array(/*CMP*/'BEA13ED814'));return CSClickReturn()" href="#" csclick="BEA13ED814"><img src="images/emoticons.gif" alt="" border="0"></a></div>
                  </form>
               </td>
            </tr>
            <tr height="30">
               <td height="30"></td>
            </tr>
         </table>
         <hr>
         <table width="100%" border="0" cellspacing="8" cellpadding="0">
            <tr>
               <td><? echo "$tag_messages"; ?></td>
            </tr>
         </table>
         <br>
         
         
         
         <?
if(isset($_GET['page'])){
  $page = $_GET['page'];
}else{
  $page = 1;
}

if(isset($_GET['offset'])){
  $offset = $_GET['offset'];
}else{
  $offset = 10;
}

$current = $page * $offset;

$sql = mysql_query("SELECT id, name, msg, email, url, timestamp FROM $table_name ORDER by id DESC LIMIT" . $current . ", " . $offset);
while($result = mysql_fetch_array($sql)){
  echo "<hr /><pre>";
  print_r($result);
  echo "</pre>";
}

$total_IDs = mysql_result(mysql_query("SELECT COUNT(ID) from $table_name"), 0);

$total_pages = ceil($total_IDs / $offset );

for($x=1;$x < $total_pages; $x++){
  if($x == $page){
    echo "<b>" . $x . "</b> ";
  }else{
    echo " <a href=\"?page=$x&offset=$offset\">$x</a> ";
  }
}
?>
      </div>
   </body>

</html>
User avatar
reed682
 
Posts: 16
Joined: Mon Dec 13, 2004 2:43 pm
Location: Florida

Postby reed682 on Fri May 13, 2005 6:11 pm

Ok, I got it working. What I did was to replace the number 10 in the section where I connected to the database and set the LIMIT and replaced it with a variable LIMIT $limit_var, 10"

Then later on I declared two more variables

$limit_prev
and
$limit_next

and made them equal the number of $limit_var with an adjustment of either -10 or +10

Then when I had my links (both previous and next) I simply called the appropriate variable and attached it to the url I called.

echo "<a href='tag_board.php?"$limit_prev",10'>Previous</a>";

And the same for the Next link. So now every time I click Previous it increases the limit number by ten and of course the reverse for next. Thank you so much for your help.
User avatar
reed682
 
Posts: 16
Joined: Mon Dec 13, 2004 2:43 pm
Location: Florida

Previous

Who is online

Users browsing this forum: No registered users and 0 guests