help? – Custom SQL Query. How to ORDER BY two fields?

Hi, I’m not sure what I changed but I have it working. I got to thinking though, what I’d really like to be able to do is modify the custom field restrictions on the fly instead of hard coding them into the page template.

Here’s what I have right now:

<?php
	 $restriction1 = 'geographic_area';
	 $restriction2 = 'species';
	 $restriction3 = 'donor';

	 $value1 = 'India';
	 $value2 = 'Calisaya';
	 $value3 = 'India Museum';

	 $querystr = "
		SELECT * FROM $wpdb->posts
		LEFT JOIN $wpdb->postmeta AS $restriction1 ON(
		$wpdb->posts.ID = $restriction1.post_id
		AND $restriction1.meta_key = '$restriction1'
		)
		LEFT JOIN $wpdb->postmeta AS $restriction2 ON(
		$wpdb->posts.ID = $restriction2.post_id
		AND $restriction2.meta_key = '$restriction2'
		)
		LEFT JOIN $wpdb->postmeta AS $restriction3 ON(
		$wpdb->posts.ID = $restriction3.post_id
		AND $restriction3.meta_key = '$restriction3'
		)
		WHERE $wpdb->posts.post_status = 'publish'
		AND $restriction1.meta_value = '$value1'
		AND $restriction2.meta_value = '$value2'
		AND $restriction3.meta_value = '$value3'
		ORDER BY species.meta_value ASC
	 	";

	 $pageposts = $wpdb->get_results($querystr, OBJECT);

	?>

Here’s what I was thinking. You create a page, linked to a page template with a standard loop and then following that, the querystr following Dan’s example above. Instead of coding the restrictions into the template the template gets those values from the metadata associated with the newly created page.

So each page would have 6 custom fields (restriction_key_1, restriction_value_1, etc) that would link into the following:

$restriction1 = 'restriction_key_1';
	 $restriction2 = 'restriction_key_2';
	 $restriction3 = 'restriction_key_2';

	 $value1 = 'restriction_value_1';
	 $value2 = 'restriction_value_2';
	 $value3 = 'restriction_value_3';

The only problem is I’m not sure how to call those metadata fields from the post and insert them into place.

Does anyone have an idea?

artsaround, I’m glad you found the code helpful–but you’ve quickly moved beyond my knowledge!

I will add, since others might like to know, that I displayed the results of the query using a table, like this; this loop follows immediately after the query:

$pageposts = $wpdb->get_results($querystr, OBJECT);

?>
 <?php if ($pageposts): ?>

<table id="tablestripe" width="100%" border="0" cellspacing="0" cellpadding="0">
		<tr>
			<th width="12%">Number</th>
			<th width="45%">Title</th>
			<th width="15%">Faculty</th>
			<th width="28%">Day/Time</th>
		</tr>
  <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>
		<tr>
			<td class="coursenumber" valign="top" width="12%">
                                <?php $key="course"; echo get_post_meta($post->ID, $key, true); ?>
                        </td>
			<td class="coursetitle" valign="top" width="45%"><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></td>

			<td class="coursefaculty" valign="top" width="15%"><?php $key="instructor"; echo get_post_meta($post->ID, $key, true); ?></td>
			<td class="coursetime" valign="top" width="28%"><?php $key="time"; echo get_post_meta($post->ID, $key, true); ?></td>
		</tr>
  <?php endforeach; ?>
  <?php else : ?>
<p><?php _e('Sorry, no posts matched your criteria.'); ?></p>

<?php endif; ?>
	</table>

You can see the page in action here.