General Question

klaas4's avatar

MySQL WHERE doesn't work.

Asked by klaas4 (2194points) March 31st, 2008

I have this query:
“SELECT * FROM `Methodes Walter` WHERE ‘Title’ LIKE ‘Trumpet’”

But when I run it in PHPMyAdmin, it gives me 0 rows, but there are some titles with Trumpet in it! The names of the columns are also right.

Why is this? I never had any trouble with the WHERE-clause…

Davey

Observing members: 0 Composing members: 0

6 Answers

sferik's avatar

Try changing the WHERE clause to:
WHERE LOWER(`Title`) LIKE '%trumpet%'
(using LOWER makes the search case-insensitive)

In SQL, % is a wildcard that matches any number of characters (even zero).

Also, it's a little strange to have a space in a table name. I'd recommend a migration from `Methodes Walter` to `Methodes_Walter`.

klaas4's avatar

The %s were enough, many thanks!

B.T.W. Does that space really matter, or is it just strange to a real programmers’ eye? (=you! ;-) )

sferik's avatar

It depends what you’re using the database for, but in the "real world", it's simply not done. One advantage of using underscores instead of spaces is that you don’t need to use backticks or quotes around the table name.

It might not be worth changing in this application. Just something to keep in mind if you’re creating schemas in the future.

klaas4's avatar

OK. Thanks for the lesson. :-)

Breefield's avatar

When you get into heavy database designs you’ll want to move to a plural single word table name naming schema.

So like, if you were building a fourm some of the tables you’d have would be…

users
moderators
forums
topics
posts

Also, back to your WHERE troubles, always remember there are the additives of NOT to BETWEEN and LIKE. So, for example I wanted to select all the users from my fourm who’s names begin with a non-alphabet character, the query would look like this.

SELECT * FROM users WHERE username NOT BETWEEN ‘a’ AND ‘z’

I realize this isn’t very applicable to what you’re doing right now, but the NOT, LIKE, and BETWEEN operator thingys always got lost in the ruff for me.

sferik's avatar

@Breefield the pluralization of tables is a hotly contested issue.

The trend seems to be going toward pluralization (for instance, Rails automatically pluralizes tables by default), but this goes against decades of good schema design.

Also, it is typically a good practice to prefix table names, so that they don’t conflict with SQL reserved words (including words that may be reserved in future versions of SQL).

Django automatically derives the name of the database table from the name of the application and the model name. For example, if you have an bookstore application and a Book class defined in your model, it will automatically create a table called bookstore_book. I’d say this is a good way to name tables.
This automatic behavior can be overwritten by using the db_table parameter in the Meta class.

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther