Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syn

[excelmaster]

Hello all,

 

Appreciate if you folks could pls. help me understand (and more importantly resolve) this very weird error:

 

 

Fatal error: Uncaught exception 'PDOException' with message '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 'ASC, purchase_later_flag ASC, shopper1_buy_flag AS' at line 3' in /var/www/index.php:67 Stack trace: #0 /var/www/index.php(67): PDO->query('SELECT shoplist...') #1 {main} thrown in /var/www/index.php on line 67

 

Everything seems to work fine when/if I use the following SQL query (which can also be seen commented out in my code towards the end of this post) :

 

 

$sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name
           FROM shoplist, store_master, item_master
           WHERE     shoplist.store_id = store_master.store_id
                   AND shoplist.item_id = item_master.item_id";

 

However, the moment I change my query to the following, which essentially just includes/adds the ORDER BY clause, I receive the error quoted above:

$sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name 
		FROM shoplist, store_master, item_master 
		ORDER BY purchased_flag ASC, 
				purchase_later_flag ASC, 
				shopper1_buy_flag ASC, 
				shopper2_buy_flag ASC, 
				store_name ASC) 
		WHERE 	shoplist.store_id = store_master.store_id 
				AND shoplist.item_id = item_master.item_id";

In googling for this error I came across posts that suggested using "ORDER BY FIND_IN_SET()" and "ORDER BY FIELD()"...both of which I tried with no success.

 

Here's the portion of my code which seems to have a problem, and line # 67 is the 3rd from bottom (third last) statement in the code below:

 

 

                    <?php
                        /*
                        $sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name
                                FROM shoplist, store_master, item_master
                                WHERE     shoplist.store_id = store_master.store_id
                                        AND shoplist.item_id = item_master.item_id";
                        */
                    
                        $sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name
                                FROM shoplist, store_master, item_master
                                ORDER BY FIND_IN_SET(purchased_flag ASC,
                                                 purchase_later_flag ASC,
                                                 shopper1_buy_flag ASC,
                                                 shopper2_buy_flag ASC,
                                                 store_name ASC)
                                WHERE     shoplist.store_id = store_master.store_id
                                        AND shoplist.item_id = item_master.item_id";
                        $result = $pdo->query($sql);                        
                        
                        // foreach ($pdo->query($sql) as $row) {
                        foreach ($result as $row) {
                            echo '<tr>';
                                print '<td><span class="filler-checkbox"><input type="checkbox" name="IDnumber[]" value="' . $row["idnumber"] . '" /></span></td>';

 

 

Thanks

 

[Jacques1]

So you've tried all kinds of things, but it never occured to you to simply check the MySQL manual for how the syntax of a SELECT statement actually looks like? 

 

The ORDER clause comes after the WHERE clause. You also seem to have some loose parentheses in your ORDER clause.

[excelmaster]

Honestly.....I hadn't a clue....and obviously I didn't think that it would matter as to where the "WHERE" was placed, in relation to the "ORDER BY" clause...but of course, I couldn't have been more wrong!

 

And of course...my bad for not checking the MySQL manual.

 

Thank you Sir...for pointing this out to me, and for providing me the link to the SELECT syntax, which I've now bookmarked.

 

Good day!

[Jacques1]

Thank you Sir...for pointing this out to me, and for providing me the link to the SELECT syntax, which I've now bookmarked.

 

Cool.