Editing my PHP mySQL DB Code

[eric1328]

I was wondering if someone can help me edit this PHP script that searches my mySQL DB. Right now I have a script that shows the most recent 6 entries to a table in my DB. It is here:
http://www.wireless-nets.com/syndicated_news.htm

The entries (or titles) shown are long sentences. I would like to add code so I could specify a number of characters or words that would be shown and then after that I would add something like " . . ." to each entry (or title) shown.

Here is my php script, any help would be appreciated. If possible please reply with edited code that would work.

Thanks!
- Eric Geier

<?php

 $link = mysql_connect("---------------", "-----------", "-------------") or die("Impossible to connect : " . mysql_error());
 mysql_select_db("guide") or die(mysql_error());
 $query = "SELECT
                                       Date,
                                       Title
                                 FROM      

                                       news";
  // ************* search *****************/
 if(!empty($_GET['search']))
 {
    // WHERE found ?
    (!stristr($query,"WHERE "))?$query .= " WHERE ":$query .= " OR ";

// add field
 $query .= " Date LIKE '%{$_GET['search']}%' OR";    
 $query .= " Title LIKE '%{$_GET['search']}%'";    
 }
 // ************* end of search *****************/  

// add ORDER BY CLAUSE
$query .= " ORDER BY Date DESC";


 $result = mysql_query($query) or die(mysql_error());
 $numrows = mysql_num_rows($result); // result of count query

// ************** pager **************************
 $limit = 6; // Default results per-page.
 if(empty($_GET['page']))$_GET['page'] = 0; // Default page value

 $pages = intval($numrows/$limit); // Number of results pages.

 if($numrows%$limit) $pages++; // has remainder so add one page
 $current = ($_GET['page']/$limit) + 1; // Current page number.

 if(($pages < 1) || ($pages == 0)) 
      $total = 1; // If $pages is less than one or equal to 0, total pages is 1.
 else 
     $total = $pages; // Else total pages is $pages value.

 $first = $_GET['page'] + 1; // The first result.

 if(((($_GET['page'] + $limit) / $limit) >= $pages) && $pages != 1) 
         $last = $_GET['page'] + $limit; //If not last results page, last result equals $page plus limit. 
else
    $last = $numrows; // If last results page, last result equals total number of results.
// ************** end of pager **************************

 if($numrows == 0)
 {
     echo "<b>No record found</b>
         <br>
         <br>
         <a href=\"javascript:history.back();\">[<< back to previous page]</a>\n";
 }
 else
 {
     $query = "SELECT
                                       Date,
                                       Title
                                 FROM      

                                       news";

 // ************* search *****************/
 if(!empty($_GET['search'])) 
 {
    // WHERE found ?
    (!stristr($query,"WHERE ")) ? $query .= " WHERE ":$query .= " OR ";

// add field
 $query .= " Date LIKE '%{$_GET['search']}%' OR";
 $query .= " Title LIKE '%{$_GET['search']}%'";
 }
 // ************* end of search *****************/  

$query .= " ORDER BY Date DESC";

 $query .= " LIMIT ".$_GET['page'].", $limit"; // add query LIMIT
 $result = mysql_query($query) or die(mysql_error());
 $numrows = mysql_num_rows($result);

 //echo our table
 echo "<table class=\"Mtable\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\n";     
     echo "<th>".ucfirst("")."</th>\n";
     echo "<th>".ucfirst("")."</th>\n";

 $i = 0;

 while ($row = mysql_fetch_assoc($result)) 
 {         
    // alternate color
    if($i%2 == 0)  
           echo "<tr class=\"TRalter\">\n";
    else
    echo "<tr>\n";

     echo "<td nowrap>".$row["Title"]."</td>\n";

    echo "</tr>\n";
    $i++;    
 }   
 echo "</table>\n";

 mysql_free_result($result);
 }

// ************** bottom pager  **************************
 echo "<table width=\"100%\" border=\"0\">
                <tr>
                <td align=\"center\">\n";

 if (!empty($_GET['page']) && $_GET['page'] > 0)
 {
   $back_page = $_GET['page'] - $limit;
   $url = $_SERVER["PHP_SELF"]."?page=$back_page";
   if(!empty($_GET['search']))$url .= "&search=".$_GET['search']; // add search     


 }

 // loop through each page and give link to it.
 // no page when only one page
 if($pages > 1)
 {
         for ($i=1; $i <= $pages; $i++) 
         {
           $ppage = $limit*($i - 1);
           if ($ppage == $_GET['page'])
                  echo(" \n");
           else
           {
                  $url = $_SERVER["PHP_SELF"]."?page=$ppage";
                                    if(!empty($_GET['search']))$url .= "&search=".$_GET['search']; // add search 

              echo(" \n");
       }
     }
 }

 if(((($_GET['page']+$limit) / $limit) < $pages) && $pages != 1) 
 {   

   $next_page = $_GET['page'] + $limit;// If last page don't give next link.
   $url = $_SERVER["PHP_SELF"]."?page=$next_page";

   if(!empty($_GET['search']))$url .= "&search=".$_GET['search'];// add search   


 }

echo "</td>
     </tr>
     </table>"; 
// ************** end of bottom pager **************************

 mysql_close($link);  


?>

[bastien]

check the length and then decide how you want to termintate the structure of the restu (ie you want to split on a word or simlply chop off the result at the defined length)

ie

...
$display_len = 60;  //60 chars

$title = $row["Title"];

$short_str = substring($title, 0, $display_len);


echo "<td 
nowrap>$short_str...</td>\n"; 
...

will chop it off at the 60th character

[mourisj]

You can also chop off your string already in your SQL-Statement:

$query = "select date,
substr(title, 1, 60),
etc"

and leeeave the rest unchanged.

JJ Mouris

[eric1328]

I tried to use the code from the first reply but I'm probally not doing it right.

Could you please reply with it inserted into my code I had posted?

Also how would I do what you said, cut it off at the end of a word (ie 10th word) instead of characters?

[bastien]

word count limit

<?
$text = "The quick brown fox jumped over the lazy dog.";
$string = ''; //the output string
$print_string = '';
$text_limit = 5;


$text_array = explode(" ", $text);
for ($x = 0; $x < $text_limit; $x++){

  $string .= " " . $text_array[$x];
  }//end if  


  echo $string."...";


?>

AS for your other question, the echo statement was right from your code, the other stuff needs to be somewhere above it...

[eric1328]

I got it to work. But I would only like the "..." to show when the Title length is longer then what i specify.

Right now if the Title is shorter then the length it still puts the "..." on the end of it.

Thanks

The webpage is:
http://www.wireless-nets.com/syndicated_news.htm

Here is my code now:

<?php

 $link = mysql_connect("--", "--", "--") or die("Impossible to connect : " . mysql_error());
 mysql_select_db("guide") or die(mysql_error());
 $query = "SELECT
                                       Date,
                                       Title
                                 FROM      

                                       news";
  // ************* search *****************/
 if(!empty($_GET['search']))
 {
    // WHERE found ?
    (!stristr($query,"WHERE "))?$query .= " WHERE ":$query .= " OR ";

// add field
 $query .= " Date LIKE '%{$_GET['search']}%' OR";    
 $query .= " Title LIKE '%{$_GET['search']}%'";    
 }
 // ************* end of search *****************/  

// add ORDER BY CLAUSE
$query .= " ORDER BY Date DESC";


 $result = mysql_query($query) or die(mysql_error());
 $numrows = mysql_num_rows($result); // result of count query

// ************** pager **************************
 $limit = 6; // Default results per-page.
 if(empty($_GET['page']))$_GET['page'] = 0; // Default page value

 $pages = intval($numrows/$limit); // Number of results pages.

 if($numrows%$limit) $pages++; // has remainder so add one page
 $current = ($_GET['page']/$limit) + 1; // Current page number.

 if(($pages < 1) || ($pages == 0)) 
      $total = 1; // If $pages is less than one or equal to 0, total pages is 1.
 else 
     $total = $pages; // Else total pages is $pages value.

 $first = $_GET['page'] + 1; // The first result.

 if(((($_GET['page'] + $limit) / $limit) >= $pages) && $pages != 1) 
         $last = $_GET['page'] + $limit; //If not last results page, last result equals $page plus limit. 
else
    $last = $numrows; // If last results page, last result equals total number of results.
// ************** end of pager **************************

 if($numrows == 0)
 {
     echo "<b>No record found</b>
         <br>
         <br>
         <a href=\"javascript:history.back();\">[<< back to previous page]</a>\n";
 }
 else
 {
     $query = "SELECT
                                       Date,
                                       Title
                                 FROM      

                                       news";

 // ************* search *****************/
 if(!empty($_GET['search'])) 
 {
    // WHERE found ?
    (!stristr($query,"WHERE ")) ? $query .= " WHERE ":$query .= " OR ";

// add field
 $query .= " Date LIKE '%{$_GET['search']}%' OR";
 $query .= " Title LIKE '%{$_GET['search']}%'";
 }
 // ************* end of search *****************/  

$query .= " ORDER BY Date DESC";

 $query .= " LIMIT ".$_GET['page'].", $limit"; // add query LIMIT
 $result = mysql_query($query) or die(mysql_error());
 $numrows = mysql_num_rows($result);

 //echo our table
 echo "<table class=\"Mtable\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\n";     
     echo "<th>".ucfirst("")."</th>\n";
     echo "<th>".ucfirst("")."</th>\n";

 $i = 0;

 while ($row = mysql_fetch_assoc($result)) 
 {         
    // alternate color
    if($i%2 == 0)  
           echo "<tr class=\"TRalter\">\n";
    else
    echo "<tr>\n";

////////////////////////////////////////////////////////////////////////////

$text = $row["Title"]; 
$string = ''; //the output string 
$print_string = ''; 
$text_limit = 10; 


$text_array = explode(" ", $text); 
for ($x = 0; $x < $text_limit; $x++){ 

  $string .= " " . $text_array[$x];



   }//end if   

      echo "<td>$string...</td>\n";

////////////////////////////////////////////////////////////////////////////


    echo "</tr>\n";
    $i++;    
 }   
 echo "</table>\n";

 mysql_free_result($result);
 }

// ************** bottom pager  **************************
 echo "<table width=\"100%\" border=\"0\">
                <tr>
                <td align=\"center\">\n";

 if (!empty($_GET['page']) && $_GET['page'] > 0)
 {
   $back_page = $_GET['page'] - $limit;
   $url = $_SERVER["PHP_SELF"]."?page=$back_page";
   if(!empty($_GET['search']))$url .= "&search=".$_GET['search']; // add search     


 }

 // loop through each page and give link to it.
 // no page when only one page
 if($pages > 1)
 {
         for ($i=1; $i <= $pages; $i++) 
         {
           $ppage = $limit*($i - 1);
           if ($ppage == $_GET['page'])
                  echo(" \n");
           else
           {
                  $url = $_SERVER["PHP_SELF"]."?page=$ppage";
                                    if(!empty($_GET['search']))$url .= "&search=".$_GET['search']; // add search 

              echo(" \n");
       }
     }
 }

 if(((($_GET['page']+$limit) / $limit) < $pages) && $pages != 1) 
 {   

   $next_page = $_GET['page'] + $limit;// If last page don't give next link.
   $url = $_SERVER["PHP_SELF"]."?page=$next_page";

   if(!empty($_GET['search']))$url .= "&search=".$_GET['search'];// add search   


 }

echo "</td>
     </tr>
     </table>"; 
// ************** end of bottom pager **************************

 mysql_close($link);  


?>

[bastien]

simple to check, find the length of the array, if its larger than the text limit then show the dots, if not then don't show the dots

    $text_array = explode(" ", $text);  

    $string_length = count($text_array);
    for ($x = 0; $x < $text_limit; $x++){  

  $string .= " " . $text_array[$x]; 



   }//end if    

      echo "<td>$string";
      if ($string_length > $text_limit){
        echo "..."; 
      }//end if
      echo "</td>\n";