Jump to content

Using TINYINT boolean in ORDERBY clause


anderson_catchme
Go to solution Solved by anderson_catchme,

Recommended Posts

I have a table which contains a TINY INT column. If there are images associated with the post, the TINY is 1. If not, 0.

 

I want to order results by images first, and earliest DATETIME first.

 

Basically, like using a boolean but not. Simply ordering by the TINY INT column ASC or DESC isn't working. Not sure how to solve this.

Help appreciated. Thanks!

Link to comment
Share on other sites

 

 

I have a table which contains a TINY INT column. If there are images associated with the post, the TINY is 1. If not, 0.

You shouldn't need to do that. If you are storing the images in a separate table to the posts stored in the posts table then you'd use a  LEFT JOIN to retrieve the images associated to each post. Then you can sort the results accordingly. Example query

SELECT p.id, p.title, p.content, p.created_at,     # get these columns from the posts table
       i.image                                     # get these columns from the images table
FROM posts p
LEFT JOIN images i ON i.post_id = p.id             # join images table
ORDER BY i.image IS NOT NULL DESC, p.created_at DESC    # order the results so post with images are listed first and then sort by the date posted in descending order
Edited by Ch0cu3r
  • Like 2
Link to comment
Share on other sites

       $query = "SELECT *
		FROM posts LEFT JOIN `images` ON `images`.`post_association`=`posts`.`id`
	        WHERE `posts`.`category` IN ('1','2','3','4','5','6')
		GROUP BY `posts`.`id`
		ORDER BY `images`.`post_association` IS NOT NULL DESC, `posts`.`datetime` DESC
		";

Sort of solved the duplicate rows issue w GROUP BY clause, but now getting random bad results. Possibly related to having duplicate 'id' column names. 3 am though might sleep :P

Edited by anderson_catchme
Link to comment
Share on other sites

Each images associted with the post you will get duplicated results. Example result from my example query

+----+--------+-------------------------+---------------------+------------+
| id | title  |         content         |     created_at      |   image    |
+----+--------+-------------------------+---------------------+------------+
| 3  | Post 3 | This is post 3 content  | 2014-09-10 00:00:00 | image3.jpg |
| 1  | Post 1 | Hello world             | 2014-09-05 00:00:00 | image1.jpg |
| 1  | Post 1 | Hello world             | 2014-09-05 00:00:00 | image2.jpg |
| 2  | Post 2 | This is post 2 contewnt | 2014-09-06 00:00:00 | NULL       |
| 4  | Post 4 | Post 4 content          | 2014-09-06 00:00:00 | NULL       |
+----+--------+-------------------------+---------------------+------------+

As you can see the post with the id of 1 has two images associated to it. Its data is duplicated twice (the data from the post table will be duplicated for how many images are associated to it)

 

In PHP you'd process the results grouping the results into an array. Example code

$posts = array();
while($row = mysql_fetch_assoc($result))
{
    if(!isset($posts[$row['id']])
    {
        $posts[$row['id']] = array(
            'title'      => $row['title'],
            'content'    => $row['content'],
            'created_at' => $row['created_at'],
            'images'     => array()
        );
    }

    $posts[$row['id']]['images'][] = $row['image'];
}

And then you'd loop over the $posts array to output the posts. Basic example

<h1>Posts</h1>
<?php foreach($posts as $post_id => $post): ?>
  <h3><?=$post['title']?></h3>
  <p>Posted On: <?=$post['created_at'];</p>
  <p><?=$post['content'];?><p>
  <p>Images:<br />
  <?php foreach($post['images'] as $image): ?>
     <img src="<?=$image?>" />
  <?php endforeach;
endforeach ?>

If you used GROUP BY only the last image associated to the post will be returned.

Link to comment
Share on other sites

  • Solution
$query = "SELECT *
	  FROM posts
	  WHERE `posts`.`Category` IN ('1','2','3','4','5','6') 
          ORDER BY `posts`.`image_or_not` IN (1) DESC, `posts`.`datetime` DESC
          ";

Above is my working solution, without any JOINS. Works with no duplicates. I wanted to get this done 100% in mysql without having to rewrite my php.

Edited by anderson_catchme
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.