SQL query challenge...

[Johan Beijar]

Hi,

 

After some extensive trouble-shooting I came to my senses and understood that I need help. 

 

I'm trying to execute the SQL-question below and I get the errormessage above it and I do not understand why. I run the query in MySQL Admin and it works like a charm. There is no column "test1" but it should select the row where the username is "test1". It seams to be some issue with `plogger_collections`.`username` = test1 but that's all that I know.

 

test1 is actually a varchar variable.

 

Does anyone have a clue why it is not working and what I should do?

 

Thank you,

 

/Johan

 

Unknown column 'test1' in 'where clause'

 

SELECT name, description, path, id, thumbnail_id, username FROM `plogger_collections` WHERE `plogger_collections`.`username` = test1 and `id` IN (-1,2,4) ORDER BY id DESC LIMIT 0, 20

[steelaz]

You should surround your value fields in single quotes.

 

SELECT `name`, `description`, `path`, `id`, `thumbnail_id`, `username` FROM `plogger_collections` 
WHERE `plogger_collections`.`username` = 'test1' and `id` IN (-1,2,4) 
ORDER BY `id` DESC 
LIMIT 0, 20

 

[Maq]

This should work, you had two sets of backticks on username, you also used plogger_collections.username when you only had to use username, and like steelaz said, any value that's not an integer should be surrounded with single quotes.  See if this works.

 

$sql = "SELECT name, description, path, id, thumbnail_id, username FROM `plogger_collections` WHERE `username` = 'test1' AND `id` IN (-1,2,4) ORDER BY id DESC LIMIT 0, 20";

 

NOTE: You really only need backticks for column names that are reserved words.

[Johan Beijar]

Thank you Maq! That did it, i'm a happy man and will get a beuty-sleep tonight. Thanx!

 

/Johan

[Maq]

Thank you Maq! That did it, i'm a happy man and will get a beuty-sleep tonight. Thanx!

 

/Johan

 

Sure, you only had a few minor errors.  The main thing was that when you select FROM a single table you don't need to specify that table before the column name.  SQL thought you were referring to 2 tables because you had 2 sets of backticks. 

 

Mark as [sOLVED] please.