CODEWITHSUNDEEP
mysqldatabase
Manish Kumar
Manish Kumar

Reputation: 208

Found difficult to make sql query

I checked the data is coming in the $posts_not_to_select as simple array and everything other is fine but it is showing me error shown below :

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE entity_id NOT IN (SELECT post_id FROM mst_blog_store_post WHERE NOT stor' at line 1, query was: SELECT * FROM mst_blog_post_entity ORDER BY created_at DESC LIMIT 1 WHERE entity_id NOT IN (SELECT post_id FROM mst_blog_store_post WHERE NOT store_id = 1)

$posts_not_to_selectssql1 = "SELECT post_id FROM mst_blog_store_post WHERE NOT store_id = $storeID";
    $posts_not_to_select12 = $connection->fetchAll($posts_not_to_selectssql1);
    $posts_not_to_select = array();

    foreach ($posts_not_to_select12 as $key => $value){
        $posts_not_to_select[$key] = $value['post_id'];
    }

    "SELECT * FROM `mst_blog_post_entity` ORDER BY `created_at` ASC LIMIT 1 WHERE `entity_id` NOT IN ( '" . implode( "', '" , $posts_not_to_select ) . "' )"

Upvotes: 2

Views: 46

Answers (2)

Rohit
Rohit

Reputation: 33

SELECT * FROM `mst_blog_post_entity` WHERE `entity_id` NOT IN ( '" . implode( "', '" , $posts_not_to_select ) . "' )" ORDER BY `created_at` ASC LIMIT 1;

Just run this query in SQL section of phpmyadmin and everything will work just fine.

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

ORDER BY comes after WHERE clause.

SELECT * FROM `mst_blog_post_entity` 
WHERE `entity_id` NOT IN ( '" . implode( "', '" , $posts_not_to_select ) . "' )"  
ORDER BY `created_at` ASC LIMIT 1

Complete sequence would be:

  • SELECT
  • FROM
  • JOIN [ON]
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY [LIMIT]

Upvotes: 0

Related Questions