Subquery

jimbob

Hi,

I want to query a table, selecting only rows with a particular $type AND $color. The 'select where' mysql query does not seem to work. I have tried writing the following with no success.

SELECT * FROM table WHERE type="large'" AND color="green"

Does anyone have any idea how to do this? I was thinking, maybe a subquery?

Thanks in advance,

Jimbob

LordShryku

That query should work. Expand on what doesn't work. Past the actual code being used, and the error outputted from MySQL.

jimbob

That is the code... I don't get any mysql error, however the page doesn't load. It just stays white.

At this query was the only thing I changed, I assumed it must be this. hmmm 🙁

leatherback

Hey Jim.

1 -> IF that is ALL the code you use to create output.. OFF COURSE it doesn't generate anything. You need to parse it to the database, get results and echo it..

So.. PLEASE
parse the whole code.

2 -> Do you actually have a table called table?

jasonmills58

um, and when you do get it working, you may want to edit out this extra single quote:

SELECT * FROM table WHERE type="large'" AND color="green"

but i don't know whether that would really throw off the whole thing...

leatherback

Hey
good spotting jason!

jimbob

Thanks for your help guys.

The main thing for me is knowing whether the query was correct or not. Having never written one like, I was perplexed when it didn't work. Rather than display my whole code, I am going to test it out some more - without that ' Thanks again!

Sxooter

Actually, you should always quote strings literals like that with single quotes, as the SQL spec doesn't reco'nize double quotes there. I.e. you won't be able to easily move your code to other databases.

select * from "tableName" where "fieldNum1" = '123';

Note that identifiers SHOULD be double quoted if needed, except that breaks most older flavors of MySQL unless you turn on the -ansi switch.

jasonmills58

I understand that Sxooter. Look closer at the quote in my post - there was only one single quote (I bolded it), not a pair. I figured it was just a typo, and it looks like it was. 🙂

cheers!

Sxooter

Originally posted by jasonmills58
I understand that Sxooter. Look closer at the quote in my post - there was only one single quote (I bolded it), not a pair. I figured it was just a typo, and it looks like it was. 🙂

cheers!

Not sure what you mean. I was referring to that fact that this query:

select * from table where field = "hello"

will fail on all other databases than MySQL because they won't accept " for value quoting

jasonmills58

Oh, I see what you mean.. Of course. I was just noticing the lone single quote in

SELECT * FROM table WHERE type="large'" AND color="green"

in the closing quote mark after "large". but of course you're right though. using single quotes all around will make the app more portable. i had forgotten about that.