Displaying priorty records and normal records in the one dataset.

[silencer01]

Hi all,

 

I have a page which displays the records from the database and uses paging to display the records in groupes of 10 per page.

 

I now want to be able to show results depending on the database field (display) in the database (can be set to either "full" or "limited"). If the record and the field display is equal to "full" i want to then display this record at the top of the record set and display all the fields. If the record is equal to "limited" i want to display different results and they must show after all the records which are equal to "full". I can't output two different recordsets as i use paging and only want to show 10 records per page.

 

//the class file which incorporates the paging
require('classes/pagedresults.php'); 

$cnx = @mysql_connect('host','user','password'); 
mysql_select_db('db',$cnx); 
$rs = new MySQLPagedResultSet("select * from table WHERE display = 'full'
                             10,$cnx); 

 

Now the HTML page which displays the results:

 

<?php while ($row = $rs->fetchArray()): ?>

		<h4><?=$row['company']?></h4>						
		<strong>Details:</strong> <?=$row['description']?>... 
		<br /><br />						
		<strong>Address:</strong> <?=$row['address']?>, <?=$row['state']?>, <?=$row['zip']?>, <?=$row['country']?>
		<br />
		<strong>Phone:</strong> <?=$row['phone']?> | <strong>Fax:</strong> <?=$row['fax']?>
		<br />
		<strong>Email:</strong> <a href="mailto:<?=$row['email']?>"><?=$row['email']?></a>
		<br />
		<strong>Website:</strong> <a href="http://<?=$row['website']?>" target="_blank"><?=$row['website']?></a>			
		<br /><br />
                        <hr />
		<br />
		<?php endwhile; ?> 

 

This works but only shows the records where display is equal to "full", i want to also show the records that are equal to "limited" which only the "company" and "website" details displayed:

 

<?php while ($row = $rs->fetchArray()): ?>

		<h4><?=$row['company']?></h4>
		<strong>Website:</strong> <a href="http://<?=$row['website']?>" target="_blank"><?=$row['website']?></a>			
		<br /><br />
                        <hr />
		<br />
		<?php endwhile; ?> 

 

Also not that these records need to be displayed after all the "full" records (kinda like priority results). Please note that im using MySql.

 

Thanks in advance!

[GingerRobot]

You just need to order your query by the display field:

 

 

"SELECT * FROM table ORDER BY display ASC"

 

You will then need to add an if statement inside your while loop to only show the particular results:

 

<?php
if($row['display'] == 'full'){
//show full results
}else{
//show limited results
}
?>

[Barand]

or you can use a union

 

SELECT company, phone, email, website FROM table WHERE display = 'full'

UNION

SELECT company, '', '', website FROM table WHERE display <> 'full'