query question

wittanthony

Can you have more than one veriable after where?

For example:

SELECT * FROM test WHERE test = 1, testing = 1

Is that possible? Can somebody give a example?

Thanks for your help in advance.

leatherback

Yes you can!

something like

SELECT * FROM test WHERE test = 1 and testing = 1

should give you the right results

wittanthony

Thanks, but I'm still having problems. Can any body tell me whats wrong with this statement?

$result = mysql_query("SELECT count(*) FROM messages WHERE to = '$username' OR to = 'Everybody' AND type = 'New' ",$db);

leatherback

Hi,

Just from the top of my head: Do you need the quotes around a variable in a query?

It is always nice when you have such a problem to echo the query, and parse it directly into mysql, to see what errors that gives. It also gives you an idea of the question you give MYSQL to solve..

Not fully sure about the answer... Try it and see. (It is getting towards bedtime here ... 😉

wittanthony

Yes, you do need the quotes around a veriable or it will not work. I just can't seem to figure out why it's not working. I know the problem is after the WHERE, but I haven't figured out why its not working. I keep getting an error.

Warning: Supplied argument is not a valid MySQL result resource

There is data in the database.

leatherback

Hi,

I checked on my pages: You do not need the quotes around $username.
Also, I have no experience with $db (I presume a database handler). i connect only to one database, and don't identify the database afterwards.

So I would use:

$result = mysql_query("SELECT count(*) FROM messages WHERE (to = $username) OR (to = 'Everybody') AND (type = 'New'));

The last option is or. I cannot acces the Mysql website, so I cannot check wether that has an error. (Combining dsata from 1 row twice)

wittanthony

I tried that but that didn't work. It still say it has to do with the AND and OR, but I don't know why they would cause an error.

This statement works fine.

$result = mysql_query("SELECT * FROM members WHERE name = '$username' ",$db);

This statement doesn't work.

$result = mysql_query("SELECT count(*) FROM messages WHERE (to = $username) OR (to = 'Everybody') AND (type = 'New'),$db);

I got the same error with that sql query.

wittanthony

I think I might have figured out the problem. It wasn't liking the to for the field veriable. Once I change the field veriable from to to messageto, it stated to work some what right.

philipolson

It's time to learn some basic SQL:

All the basic SQL commands you'll ever need live in the above links, you should bookmark them.

wittanthony

I know sql from using oracle. PHP is the one I don't know much of yet. I'll refer to those links if I need help before posting another question on here.

philipolson

All mysql_query does is run a mysql query so this isn't a PHP question. I didn't mean to sound harsh but this is a SQL question and you are in the newbie forum so it's a good idea to learn basic SQL syntax.

Btw, the above links do mention the sql command IN which is what you want. WHERE blah IN('foo','bar')

Gary

Just for clarity, I would keep my OR and AND conditionals separated in their own set of parenthesis.

Instead of:

$result = mysql_query("SELECT count(*) FROM messages WHERE (to = $username) OR (to = 'Everybody') AND (type = 'New'),$db);

I would try:

$result = mysql_query("SELECT count(*) FROM messages WHERE ((to = $username) OR (to = 'Everybody')) AND (type = 'New'),$db);

Just to keep the OR conditional separate from the AND conditional.

Lars_Berg

You shouldn't have a column named TO. It is a reserved word in SQL.

zalath

MySQL doesn't mind reserved words (if you get errors, try to backticks the word like so: to)

philipolson

Using reserved words can only lead to problems, IMHO nobody should ever use them.

http://www.mysql.com/doc/en/Reserved_words.html

zalath

I'm aware that it's a bad practice 🙂 I was just pointing out that in my experience most of the reserved words will do when backticked.