Using TINYINT boolean in ORDERBY clause

[anderson_catchme]

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!

[Ch0cu3r]

 

 

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 September 10, 2014 by Ch0cu3r

[anderson_catchme]

Not sure why left, and not right, but no matter.   Works great. Thanks.

[kicken]

Not sure why left, and not right, but no matter.

When is a Left table 'Left' and a right table 'Right'

 

Read that for some information on what they different join types are and when they are appropriate.

[anderson_catchme]

One issue, I am getting some extra results.

My 'images' table contains multiple images per post. (one to many relationship?) These are getting filtered back to my main table, returning extra rows. Working on the issue now, but if anybody has any ideas let me know.

Edited September 11, 2014 by anderson_catchme

[anderson_catchme]

       $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

Edited September 11, 2014 by anderson_catchme

[Ch0cu3r]

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.

[anderson_catchme]

$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 September 12, 2014 by anderson_catchme