Query alterations

[Canman2005]

hi all

 

i have been battling against a alteration to a query for a couple of days now and im totally stuck, i will try and explain what i have.

 

basically i have a query, it produces a bunch or results on a php page which look like

 

 

news

    article 1

 

event

    article 2

 

profile

    article 3

 

news

    article 4

 

profile

    article 5

 

the bit where is says "profile", "news" or "event", this is the heading and then under the heading it lists the article related to that heading.

 

to get this, i used the following query

 

 

<?php
$querystr = "SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'promo' AND wposts.post_status = 'publish' AND wposts.post_type = 'post' ORDER BY wpostmeta.meta_value ASC";
$pageposts = $wpdb->get_results($querystr, OBJECT);
?>
<?php if ($pageposts): ?>
  <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>


<div class="titlepanel"><?php echo get_post_meta($post->ID, 'promo', true);?></div>

<div class="contentpanel">
<div class="compdetails" id="competition_<?php the_ID(); ?>_details">
<p class="postdate"><?php the_time('F jS, Y') ?> <!-- by <?php the_author() ?> --></p>
<?php the_content('<p class="serif">read</p>'); ?>
</div>
</div>

 

each heading (ie: news, events) is created with the

 

<div class="titlepanel"><?php echo get_post_meta($post->ID, 'promo', true);?></div>

 

part of the code above.

 

What i want to do is bundle the 2 news articles together and the 2 profiles together, so the above results would look like

 

news

    article 1

    article 4

 

event

    article 2

 

profile

    article 3

    article 5

 

if you add to the end of the query

 

GROUP BY wpostmeta.meta_value

 

then the results look like

 

news

    article 1

 

event

    article 2

 

profile

    article 3

 

which is right, but I need to put a new query which returns all the news articles & profile articles under the main heading, so the above results for the "news" would NOT look like

 

news

    article 1

 

but it WOULD look like

 

news

    article 1

    article 4

 

does that make sense?

 

any help would be ace as im slowing killing myself here

 

thanks in advance

 

dave

[sKunKbad]

I have something like this on one of my sites. It displays a categorized list of links, but could easily be modified to list categorized articles:

 

$articleTypes = array('news','profile','event');
foreach ($articleTypes as $articleCat){
$query = "SELECT articleType,articleContent FROM articles WHERE articleType = '$articleCat' ORDER BY articleNum DESC";
$result = mysql_query($query);
$num_rows = mysql_num_rows ($result);
	if ($num_rows != 0){
		echo "	<h3>" . ucfirst($articleCat) . "</h3>";
		while($row = mysql_fetch_array($result)){
			extract($row);
			echo "<p>" . $articleContent . "</p>;
		}
	}
}

[Barand]

The problem with that approach is the hard-coding of the categories. If a new one is added to the database the program fails. Also it requires multiple queries.

 

<?php
$sql = "SELECT c.category, i.item
        FROM categories c INNER JOIN items i ON i.catID = c.ID
        ORDER BY c.category, i.item";
$res = mysql_query($sql);

$prevCat = '';

while (list ($cat, $item) = mysql_fetch_row($res))
{
    // has category changed?
    if ($cat != $prevCat)
    {
        echo '<h3>$cat</h3>';                   // if it has, output new category
        $prevCat = $cat;
    }
    
    // output item
    echo $item, '<br/>';
} 

?>

[sKunKbad]

Barand is right. If there is a chance that you will add categories of articles in the future, you would need to create the array of categories dynamically.