elsewhile equivelant for two sql results?

[veeps]

I'm having a problem figuring out the best way to output results without simply writing two near copies of the same script.

I have a db table, "sale_items" with a list of images to display in a gallery. In this table exists a "feature" column that is either empty or contains #1-9. IF a feature number exists, then the gallery should display features in feature order. Otherwise, I want the gallery to display non-feature images in order by id.

As you can see, this requires two sql queries:

$fsql = "SELECT * FROM sale_items WHERE gallery_name = 'CoolName' AND feature != '' ORDER BY feature ASC LIMIT $startRow,".GALMAX; //GALMAX for page display limit

as well as

$sql = "SELECT * FROM sale_items WHERE gallery_name = 'CoolNAME' AND feature = '' ORDER BY image_id LIMIT $startRow,".GALMAX;

The gallery works, first, by getting the $mainImage to display like this:

//$fresult = mysql_query($fsql) or die('Problems with features');
$result = mysql_query($sql) or die('Problem attaining sale items');
//$frow = mysql_fetch_assoc($fresult);
$row = mysql_fetch_assoc($result);
//get the main image to display large
if (isset($_GET['image'])) {
	$mainImage = $_GET['image'];
}
//elseif (!empty($frow['filename'])) { $mainImage = $frow['filename']; } does that work?
 else {
	$mainImage = $row['filename'];
}
if(!empty($mainImage)) {
$imageSize = getimagesize('../path_to/CoolName/'.$mainImage);
}

Then, the thumbs and mainImage are displayed like this:

<div id="Thumbs">
<table>
<tr>
<?php
//count
$pos = 0;
do { 
?>
<td><a href="<?php echo $_SERVER['PHP_SELF']; ?>?image=<?php echo $row['filename']; ?>&amp;curPage=<?php echo $curPage; ?>"><img src="../CoolName/thumbs/<?php echo $row['filename']; ?>" alt ="<?php echo $row['filename']; ?>"  /></td>
<?php
$row = mysql_fetch_assoc($result);
$pos++;
//if at end of row, insert tags as needed
if ($pos%COLS === 0 && is_array($row)) {
echo '</tr><tr>';		   }
} while ($row);
//new loop for final row
while ($pos%COLS) {
echo '<td><img src="../../images/generic.jpg" alt="end of images image"></td>';
$pos++;
}
?>
</tr>
</table>
</div>

<div class="MainImage">
<img src="../path_to/CoolName/<?php if(!empty($mainImage)) { 
						echo $mainImage;
						} ?>" alt="" <?php if(!empty($mainImage)) {
						echo $imageSize[3];
} ?>/>
</div><!--END <MainImage-->

With each of the above $sql statements as an individual statement, the code works fine. But, as you can see above, it gets tricky when trying to combine a $row['value'] and display $row['filename']. I'd like to know if someone can help me best define $row[] (or display the separate results in one gallery as shown above), since there's no such thing as an elsewhile statement. I've been stumped on this for a couple of days now...THANKS IN ADVANCE!!!

[Derokorian]

Why not ORDER BY feature ASC, image_id DESC ? It will order by feature first, then by image_id

[veeps]

I just tried the following:

$sql = "SELECT * FROM sale_items WHERE gallery_name = 'Air_Heart' ORDER BY feature ASC, image_id DESC LIMIT $startRow,".GALMAX;

The problem with this is that non-featured is equal to 0, which comes before 1-9, so the featured images showed up last
:queasy:
I supposed I could enter a feature value for each, upon upload, that is greater than 9, and only allowing >= 9 if specified...except that my feature type is set(1,...9) in the db.

[Derokorian]

Well first off I would reccomend using tinyint instead of set for the field, second I would allow for NULL values and then if its not a feature it would be NULL. Then you could do:

$sql = "SELECT * FROM sale_items WHERE gallery_name = 'CoolName' ORDER BY IFNULL(feature,10) ASC, image_id DESC LIMIT $startRow,".GALMAX

I just tested it with this table:

CREATE TABLE IF NOT EXISTS `feature` (
  `image_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `feature` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`image_id`),
  KEY `feature` (`feature`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

INSERT INTO `feature` (`image_id`, `feature`) VALUES
(1, NULL),
(2, 1),
(3, NULL),
(4, NULL),
(5, 2),
(6, 4),
(7, 7),
(8, NULL),
(9, NULL),
(10, 9),
(11, 8),
(12, NULL),
(13, 3),
(14, 5),
(15, NULL),
(16, 6);

and this query:

SELECT * FROM feature ORDER BY IFNULL(feature,10) ASC, image_id DESC LIMIT 0,20 

Hope that helps!

PS. I would suggest listing all the columns you expect data from instead of using SELECT * syntax.

[veeps]

Hey man! Thanks a million. Slow reply, because I was busy and also needed to know exactly what your query was doing.

Very helpful!