sorting mysql returns

planetphp

Hi,

I am writing a script for part of a homepage where the 5 latest features are listed.

What I want to do is this:

query database with limit 5.

I want to ignore the first entry returned as this is already covered elsewhere on the page.

With the second return I want to list the title, image and first 100 words of the content.

With the third return I want to list title and first 100 words of content.

With 4th and 5th I just wan tto return the title.

Heres my attempt (at the moment it just returns a parse error but i don' think the code is right anyway...)

<?
$link_id = db_connect("b34t");
$query = "select * from features order by id desc limit 5";
$result = mysql_query($query);
while($home = mysql_fetch_array($result)){
$title = $home[1];
$red_content = (substr("$home[3]", 0, 100));
for ($i = 0; $i < count($home[0]); $i++)
{
if($i == 0){};
elseif ($i == 1){echo "$title[$i], $red_content "};
elseif ($i == 2){echo "$title[$i], $red_content "};
elseif ($i == 3){echo "$title[$i] "};
elseif ($i == 4){echo "$title[$i] "};
}
}
?>

Please help...

waygood

<?php
$query = "select * from features order by id desc limit 1,4"; 
$result = mysql_query($query); 

$element=1;
while($home = mysql_fetch_array($result))
{ 
  $title = $home[1]; 
  if($element<=2)
  {
    $red_content = "," . (substr($home[3], 0, 100)); 
  }
  else
  {
    $red_content = "";
  }
  echo "<font size='1'>$title $red_content<br>"; 
  $element++;
} 
?>

Changed query to 1,4 so you don't get the first element anyway.
Added the comma to the $red_content, so it doesn't appear in the area where the content is blank.
This way you could alter the code to deal with any number of results and change breaking point for displaying content very easily.

Hope this helps, don't know about your db connection though as I use a different method.

Let me know how it works out

jmcneese

$query = "select * from features order by id desc limit 5"; 
$result = mysql_query($query); 

while($home = mysql_fetch_array($result)){ 
	$red_content = substr($home[3], 0, 100); 
	if($home[0] = 0) {
		break;
	} elseif ($home[0] = 1) {
		echo "<font size=\"1\">$home[1], $red_content<br>";
	} elseif ($home[0] = 2) {
		echo "<font size=\"1\">$home[1], $red_content<br>";
	} elseif ($home[0] = 3) { 
		echo "<font size=\"1\">$home[1]<br>";
	} elseif ($home[0] = 4) { 
		echo "<font size=\"1\">$home[1]<br>"; 
	} 
}

planetphp

cheers for that but neither works... the first reply just gives a parse error and the second reply lists the first entry and content for all 5 entries returned...

jmcneese

oops, i was thinking in PEAR:😃B there by not mentioning a foreach()... throw a foreach (or for) loop to show each item in the resultset.

or start using PEAR:😃B 😉

waygood

oops as well, a few errors so here is the full code including my databse connection

<?php
$query = "select * from features order by id desc limit 1,4"; 

// change these to the settings for your database
$location = "localhost";
$user = "root";
$password = "";
$db_name = "dbname";   

// Create database connection
$connection = mysql_connect($location ,$user ,$password ) or die ("Couldn't connect to database");

// Select the database
$db = mysql_select_db($db_name, $connection) or die ("Couldn't select database");

// Execute SQL query and get result
$result = mysql_query($query,$connection) or die ("Couldn't execute query ".$query); 

$element=1;
while($home = mysql_fetch_array($result))
{ 
  $title = $home[1]; 
  if($element<=2)
  {
    $red_content = "," . (substr($home[3], 0, 100)); 
  }
  else
  {
    $red_content = "";
  }
  echo "<font size='1'>$title $red_content<br>"; 
  $element++;
} 
?>

planetphp

still no joy im afraid.... 🙁

planetphp

P.s. My connection code is in an include file the db_connect function initialzes the db for me!!

waygood

very strange?? as this was tested on my machine.

To refine the code a bit more what are the names of the fields you are returning?

A dump of the table structure would be useful as well.

piersk

Just out of question phpplanet,

Why in your first post did you use db_connect and then after that use mysql functions? Could this have something to do with it?

waygood

P.s. My connection code is in an include file the db_connect function initialzes the db for me!!

Duh!!

planetphp

cheers waygood your a diamond.. I just clocked that you had added the php after the <? but there was a space inbetween so it was causing a parse error!!! It all works now!!!

waygood

Cheers

Also check to see if you have the value 100 instead of 10 in the substr line. I have edited the post, but just check if you grabbed it before I changed it.

Used 10 when testing on a small database.

You may find the following function useful. It cuts text into words rather than characters.

$output_text = shortText($source_text, 20, $too_short)
$short_text is set to TRUE if the $source_text is less than 20 words long, in this example.
$output_text will contain the shortened text OR the full text if the word count is less than the specified amount.

function shortText($source_text, $word_count, &$too_short)
{
	$word_count++;
	$too_short = FALSE;
	if ((trim($source_text) != "") && ($word_count > 0))
	{
		$split_text = explode(" ", $source_text, $word_count);
		if (sizeof($split_text) < $word_count)
		{
			$too_short = TRUE;
			$joined_text = $source_text;
		}
		else
		{
			array_pop($split_text);
			$joined_text = implode(" ", $split_text);
		}
	}
	else
	{
		$joined_text = $source_text;
	}
	return $joined_text;
}

piersk

oops, sorry :o