How to truncate multiple query results....

[noisenet]

Heya,

I'm new here, as well as to PHP. I know the basics of what PHP can do, even methods by which to implement a lot of what I want, however my coding skills aren't yet up to par. Being impatient, I've turned to GUI based coding, such as Dreamweaver, to faster do what I want.

 

Here's my issue: I have a news section on my website, where the article(s) are stored in a MySQL database. Titles in one column, article body in another. On the actual page, I have a limited space to which to print the content, and need to truncate it so it does not overlap, or stretch the content div and blow the design out of whack.

 

When only returning one article from the db, I can do this easily, however, the problem is that not all articles are/will be lengthy enough to fill the div. In such cases, I want to return the next article, up to the remaining div space, then truncate it.

 

For example, right now, the article that's being displayed fills up more than the allotted space, so it's truncated. But the next three articles I post may be short, where I'd need all three plus a segment of a fourth to fill the space. Using Dreamweaver MX2k4 I can do this by "repeating that region", but limiting the number of characters is only applicable to each query, not to the overall div. As such, it won't work in the way I need.

 

Below is the code I'm using for this purpose...

 

<?php
$maxRows_ssh_news = 1;
$pageNum_ssh_news = 0;
if (isset($_GET['pageNum_ssh_news'])) {
  $pageNum_ssh_news = $_GET['pageNum_ssh_news'];
}
$startRow_ssh_news = $pageNum_ssh_news * $maxRows_ssh_news;

mysql_select_db($database_ssh_db, $ssh_db);
$query_ssh_news = "SELECT * FROM newsContent ORDER BY id DESC";
$query_limit_ssh_news = sprintf("%s LIMIT %d, %d", $query_ssh_news, $startRow_ssh_news, $maxRows_ssh_news);
$ssh_news = mysql_query($query_limit_ssh_news, $ssh_db) or die(mysql_error());
$row_ssh_news = mysql_fetch_assoc($ssh_news);

if (isset($_GET['totalRows_ssh_news'])) {
  $totalRows_ssh_news = $_GET['totalRows_ssh_news'];
} else {
  $all_ssh_news = mysql_query($query_ssh_news);
  $totalRows_ssh_news = mysql_num_rows($all_ssh_news);
}
$totalPages_ssh_news = ceil($totalRows_ssh_news/$maxRows_ssh_news)-1;

?>

 

 

And in the page body:

 

 

<?php do { ?>
      <span class="header"><?php echo $row_ssh_news['title']; ?></span><br>
      <span class="whitetext2"><?php 
  if(strlen($row_ssh_news['content'])>2000){
echo substr($row_ssh_news['content'], 0, 2000) . '<span class="footnote">...(continued)</span>';
}
else {
echo $row_ssh_news['content'];
}
?>
</span><br><br>
  <?php } while ($row_ssh_news = mysql_fetch_assoc($ssh_news)); ?>
    <div align="right"><span class="footnote">
  <?php if ($pageNum_ssh_news < $totalPages_ssh_news) { // Show if not last page ?>
<a href="javascript:void" onClick="MM_openBrWindow('news_full.php','newsFull','scrollbars=yes,width=500,height=400')">View All <?php echo $totalRows_ssh_news ?> News Items... </a>
<?php } // Show if not last page ?>

 

As you can see, this limits the string length by query. If I had it set for a max of 4 items returned by changing the line {$maxRows_ssh_news = 1;}, the character count is reset at the beginning of each returned result.

 

As I'm an uber noob, I don't know if the data is being returned in a string (or strings), or as (an) array(s). I've wondered if I could use the implode function to kind of compress everything into one array, then limit character count, but I need titles and body text to appear in their rightful place, & as imploding would just create one big array of both title and body, that wouldn't work. So I'm stumped.....

 

Any info on how I can achieve this would be greatly appreciated! Thank you for taking the time to read this lengthy post from a total noob.......

 

To look at the page and see what I'm talking about for space limitations, you can go to http://www.superswamp.com The page I'm talking about is the index page.

[Wildbug]

<?php

// whatever your query is:
$result = mysql_query('SELECT * FROM newsContent ORDER BY date DESC LIMIT 10') or die(mysql_error());

if ($result && mysql_num_rows($result)) {

$total_length = 0;
$max_length = 2000;

while ($row = mysql_fetch_assoc($result) or $total_length < $max_length) {
	$total_length += strlen($row['content']);
	echo "html blah";
	echo $total_length > $max_length ? substr($row['content'],0,$total_length-$max_length > 100 ? 100 : $total_length-$max_length).'...more' : $row['content'];
	echo "more html blah";
}
}

?>

 

Cumulatively add the content length during the while loop.  The loop will end if the maximum size is exceeded or mysql_fetch* runs out of results, whichever comes first.  Maximum size will also be exceeded on the last execution of the loop since the size is added within the loop.  That's okay; you'll truncate the excess.  I arbitrarily picked 100 chars -- if the content exceeds the max by 100 chars, just print 100 chars, if less, print whatever remains.  (That way you have some meaningful content, regardless -- not just a truncated two character stump if the $total_length is 2002.)

 

It doesn't really matter that you get more results than you'll use, because you'll exit the loop before printing the excess.  The result memory is released at the end of the script, or you can explicitly do it via mysql_free_result($result).  I put a "LIMIT 10" because you almost definitely will never need 10 articles to fill the space, but it's still small enough that you won't get big results.

 

Is that what you're looking for?

[noisenet]

Heya Wildbug,

Thanks a million for your VERY informative response. Honestly, I think I understand the majority of it, however, I'm not quite sure how to implement it into the page. Should I kill the php that's in there already, and just put in what you've posted, or is what you posted in addition to the DW code that's in there?

 

Also, I'm don't know how to insert the titles of each newsContent item. The DB table for this content has three columns - 'id', 'title' and 'content'. Using the method you gave above, will it be possible to call and echo the title then content in order?

 

Again, thank you for your help! I know these dumb noob questions can be a pain.......

[Wildbug]

One correction to my posted code:  change the "or" in the while loop condition to an "and"; otherwise, it will only end when both are false -- you want it to end when either is false.

while ($row = mysql_fetch_assoc($result) and $total_length < $max_length) {

 

Should I kill the php that's in there already, and just put in what you've posted, or is what you posted in addition to the DW code that's in there?

 

Well, not quite either.  I would replace the code you currently have, but my code wasn't a verbatim plug-in; it's more along the lines of a conceptual example in that it should give you the idea of what steps to take, but you'll need to change some things to fit your exact situation.  Let me know if you need help adapting it.

 

Also, I'm don't know how to insert the titles of each newsContent item. The DB table for this content has three columns - 'id', 'title' and 'content'. Using the method you gave above, will it be possible to call and echo the title then content in order?

 

Using mysql_fetch_assoc() will return an associative array containing a row's worth of data each time it is called until it runs out of returned rows.  Using "SELECT *...." means that all of the columns are selected.  If you want to limit your query to certain rows, you can change "*" to "id,title,content".  Either way, you can access the data via the associative array:  $row['id'], $row['content'], etc.

[noisenet]

Heya Wildbug,

Again, thank you. It's really cool that you're helping me so much.

 

I fooled around a bit last night with the code, plugged in your examples. I was able to get it to do some looping, but not how I'd wanted. For starters, I also added in an echo for the 'title' before the 'content', but the title was never printed out. Secondly, while I was able to get a loop going, it seems that it would truncate EACH story at 100 or so characters (I'm thinking it's the 100's you entered in there), while the 2000 character limit, it seems, didn't have any bearing. I futzed around with a few things, then this morning put things to (what I thought) was how it was last night.

 

There are, at the moment, 4 items in the db to draw from. As it is now, it's displaying the first three in their entirety (they're short entries), and then a small fragment of the fourth. No titles are displaying though, so it's kinda hard to differentiate between items. Also, I tried upping the 2000 limit to 3000, and it had no effect. below is the code as it stands now.....

 

	  
  <?php

// whatever your query is:
$result = mysql_query('SELECT * FROM newsContent ORDER BY date DESC LIMIT 10') or die(mysql_error());

if ($result && mysql_num_rows($result)) {

$total_length = 0;
$max_length = 2000;

while ($row = mysql_fetch_assoc($result) and $total_length < $max_length) {
	$total_length += strlen($row['content']);
	echo $result['title'];
	echo $total_length > $max_length ? substr($row['content'],0,$total_length-$max_length > 100 ? 100 : $total_length-$max_length).'...more<br />' : $row['content'];
	echo $result['content'];
}
}

?>

 

The code is placed in the page body.....

[Wildbug]

You should be using $row['title'] instead of $result['title'].  $row is the associative array that is created from the mysql_fetch_assoc() on each execution of the while loop.

 

Hmm, I can't figure out why it would be only print 100 chars when $total_length is less than $max_length.  $row['content'] gets printed in that line, btw.

[noisenet]

Heya Again Wildbug,

From your advice, I've made some headway, both with getting the titles in there, as well as both columns formatted, however, I'm still running into the truncation issue.

 

As of now, the most recent entry into that table is a long one. What the script is doing is it's printing the entirety of that long item, then looping through and printing the others, until the final (first entry) which it's truncating after the set amount of characters (you had it at 100, I moved it to 200 to see what would happen.....), but it's not truncating any of the others.....

 

Anyhow, the modified script is below...

 

  <?php

// whatever your query is:
$result = mysql_query('SELECT * FROM newsContent ORDER BY id DESC LIMIT 10') or die(mysql_error());

if ($result && mysql_num_rows($result)) {

$total_length = 0;
$max_length = 3000;

while ($row = mysql_fetch_assoc($result) and $total_length < $max_length) {
	$total_length += strlen($row['content']);

	?>
	<span class="header">
	<?php
	echo $row['title'];
	?>
	</span><br />
	<span class="whitetext2">
	<?php
	echo $total_length > $max_length ? substr($row['content'],0,$total_length-$max_length > 200 ? 200 : $total_length-$max_length).'...   more<br />' : $row['content'];
	?>
	</span><br>
	<?php
}
}

?>

 

Notice that max-length is now set to 3000. When I have it set to 2000, it skips the other entries (due to the ORDERBY statement in the query), then prints a few words of the longest (and most recent) news item entry, then truncates. So I left it at 3000 so you could see what's happening....

 

As before, you can view the results of this code at http://www.superswamp.com/index2.php

[Wildbug]

Try this, it works nicely for me:

 

<?php
// You will probably have to adjust the first two variables to suit your needs.
$max_content_length = 500; // Maximum length of each piece of "content"
$max_div_length = 3000; // Maximum character limit overall (this isn't absolute and will be broken by up to $max_content_length)
$current_length = 0;

$result = mysql_query("SELECT id,title,IF(LENGTH(content) > $max_content_length,CONCAT(LEFT(content,$max_content_length-10),'...   more'),content) AS trimmed_content FROM newsContent ORDER BY id DESC LIMIT 10") or die(mysql_error());

if ($result && mysql_num_rows($result)) {
while ($current_length < $max_div_length and $row = mysql_fetch_assoc($result)) {
	$current_length += strlen($row['trimmed_content']);
	printf('<span class="header">%s</span><br />\n<span class="whitetext2">%s</span><br>",
		$row['title'],
		$row['trimmed_content']
	);
}
}
?>

[noisenet]

Wow, I can pretty much tell by looking at this that it is going to work. I won't have a chance to try it out for quite some time though. I'm gigging every day and night now thru Saturday, leaving after Sunday's gig to head to Nashville to record, won't be back until the following week :-(

 

But I'll be sure to let you know how this works out. I'm dying to try it out!

 

Thanks for your help! For the record, by reading this, it dawns on me that my problem lies in that I was trying to limit the output during it's display, instead of limiting the length of the returned results through the query. It all makes sense now!

 

Again, thank you!

[Wildbug]

I'm still not sure why the first one wasn't working well.  But limiting the results in the SQL query is smarter anyway, since less information must pass between PHP and MySQL if we limit things early on.