I don't understand why this query errors out...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kevinwinters
Forum Newbie
Posts: 20
Joined: Wed Jan 06, 2010 8:31 pm

I don't understand why this query errors out...

Post by kevinwinters »

Here is the query that produces an error

Code: Select all

SELECT inventory.*, categories.name AS furname
 FROM furniture.inventory INNER JOIN furniture.categories ON (inventory.perm_category_id = categories.id)
  WHERE inventory.sales_status !='sold' AND inventory.date_last_modified  <= DATE_SUB(CURDATE(),INTERVAL 5 DAY) AND inventory.category_id =20 AND inventory.perm_category_id !=20
in SQLYog
The error returned is:
Query : SELECT inventory.*, categories.name as furname FROM furniture.inventory INNER JOIN furniture.categories ON (inventory.perm_cat...
Error Code : 1142
SELECT command denied to user: 'xxxxxxxxxx' for table 'inventory'


But I can execute this query with no problem (it is happily using the select command on the inventory table)

Code: Select all

SELECT *
	 FROM inventory
	  WHERE sales_status ='sold' 
	  AND date_last_modified  <= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
	  AND date_sold  <= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
I can only conclude that there is something wrong with the first query that gives me that error and not anything to do with permissions
Any help with this would be appreciated, thank you in advance
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: I don't understand why this query errors out...

Post by requinix »

kevinwinters wrote:and not anything to do with permissions
I'll accept that as a possibility if you can give me an interpretation of "command denied to user" that does not involve permissions.

Spoiler: it does have to do with permissions.
kevinwinters wrote:But I can execute this query with no problem
Ah, but from where? And as what user?
kevinwinters
Forum Newbie
Posts: 20
Joined: Wed Jan 06, 2010 8:31 pm

Re: I don't understand why this query errors out...

Post by kevinwinters »

thank you for that quick reply, yah, it didn't have anything to do with permissions, just a sloppy query with (I think, incorrect quotes at the wrong places, sorry I did not post the PHP part but after re-arranging the query like this: (this time with the PHP part)

Code: Select all

$oCat = $db->setQuery(
            sprintf("SELECT
    inventory.*
    , categories.name as furname
FROM
    inventory
    INNER JOIN categories 
        ON (inventory.perm_category_id = categories.id)
        WHERE inventory.sales_status !='sold' AND inventory.date_last_modified  <= DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND inventory.category_id =20 AND inventory.perm_category_id !=20"
 )
);
It works fine
phu
Forum Commoner
Posts: 61
Joined: Tue Mar 30, 2010 6:18 pm

Re: I don't understand why this query errors out...

Post by phu »

Without attempting to reproduce your environment, the very trivial fact is that tasairis is right: mySQL says it's a permissions issue.

It seems pretty clear that you have access issues. Run...
grant all on 'dbname'.* to 'dbuser'@'localhost' identified by 'dbpassword';

... and if you still have problems, they should at least be new ones.
Post Reply