Gossamer Forum
Home : General : Databases and SQL :

Mysql - query - sorting by field, but I want only certain values in that field

Quote Reply
Mysql - query - sorting by field, but I want only certain values in that field
Hi All

I would like to create an SQL Dump that displays all the records having a certain field value.

Right now, my query string shows all the records, sorted by the field.

Quote:
SELECT * FROM csup_tickets ORDER BY subject LIMIT 0, 2000

What I want is to be able to display all of the records that have a value of "fred" in the subject field.

subject "fred"

How can I modify the above query string to pull out only the subject "fred" records?

Many thanks Smile

------------------------------------------

Last edited by:

DogTags: Apr 11, 2002, 5:30 AM
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
SELECT * FROM csup_tickets WHERE subject LIKE '%fred%' ORDER BY subject LIMIT 0, 2000
Quote Reply
Re: [Paul] Mysql - query - sorting by field, but I want only certain values in that field In reply to
Thanks so much, Paul !!!!

This is getting "funner" by the minute......yeah, right....Cool

------------------------------------------
Quote Reply
Re: [Paul] Mysql - query - sorting by field, but I want only certain values in that field In reply to
If I wanted to sort by the values of 2 fields, how can that be done?

Quote:
SELECT * FROM csup_tickets WHERE subject LIKE '%fred%' name LIKE '%ethel%' ORDER BY subject LIMIT 0, 2000

Selecting by a combination of values for subject and name:

subject 'fred'
name 'ethel'

Super thanks! Smile

------------------------------------------
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
Just some notes for you on the above code.

Code:
%fred% will match adzfredadz - both sides of fred.
%fred will match adzfred - anything then fred.
fred% will match fredadz - fred then anything.
fred will match fred - just fred.

- wil
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
This should sort (order) the results by subject then by name. Is this what you want?

Code:
SELECT * FROM csup_tickets WHERE subject LIKE '%fred%' AND name LIKE '%ethel%'
ORDER BY subject, name LIMIT 0, 2000

- wil

Last edited by:

Wil: Apr 11, 2002, 5:37 AM
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
You'd need a bool in the middle, either OR or AND:

....subject LIKE '%fred%' OR name LIKE '%ethel%'
Quote Reply
Re: [Wil] Mysql - query - sorting by field, but I want only certain values in that field In reply to
Thank you, Paul and Wil, soooooooooo much!!

This is really great Cool Cool

I feel like my life is almost complete.....now, I gotta get a dog......Smile

------------------------------------------

Last edited by:

DogTags: Apr 11, 2002, 5:48 AM
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
No problem!

What breed of dog you looking for?

- wil
Quote Reply
Re: [Wil] Mysql - query - sorting by field, but I want only certain values in that field In reply to
Sort of a border collie/golden retriever mix.

Our last dog was our dog-of-a-lifetime, and we still miss her tons. We got her from the pound.

I've been so busy, and will continue to be for the next few months, that I haven't been able to justify looking for another pooch, but we're always scouting...

There's nothin' like a dog.........
SmileSmileSmile

------------------------------------------
Quote Reply
Re: [Wil] Mysql - query - sorting by field, but I want only certain values in that field In reply to
Okay, I've got another one...

What if I wanted to sort on 2 values for subject and 2 values for name:

subject 'fred'
subject 'dog'

name 'ethel'
name 'bone'

Would it be something like:

SELECT * FROM tickets WHERE subject LIKE '%fred%, %dog%' AND name LIKE '%ethel%, %bone%' ORDER BY subject, name LIMIT 0, 2000

Many thanks !

------------------------------------------
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
SELECT * FROM tickets
WHERE (subject LIKE '%fred%' OR subject LIKE '%dog%')
AND (name LIKE '%ethel%' OR name LIKE '%bone%')
ORDER BY subject, name
LIMIT 2000

I think thats right :)

Last edited by:

Paul: Apr 11, 2002, 6:48 AM
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
Code:
SELECT * FROM tickets WHERE (subject LIKE '%fred% OR subject LIKE %dog%') AND (name LIKE '%ethel% OR name LIKE %bone%')
ORDER BY subject, name LIMIT 0, 2000

- wil

Last edited by:

Wil: Apr 11, 2002, 6:49 AM
Quote Reply
Re: [Wil] Mysql - query - sorting by field, but I want only certain values in that field In reply to
Thank you, both! Mysql is actually becoming fun...sort of...

Okay, I've got another one...

Let's say that I've entered a query (using mysqlman sql monitor...) by selecting out only certain records, and I get a multi-page result.

Is there a way to save that result to the screen or to a file?

It's important for me to be able to snag just the records that I need, do some updates or whatever, and then plug those records back in with some sort of an UPDATE command.

Super thanks, again, for your help! Smile

------------------------------------------
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
After you do a query with mysqlman, click "Export" in the top menu...that should do what you require.
Quote Reply
Re: [Paul] Mysql - query - sorting by field, but I want only certain values in that field In reply to
I'll give 'er a go, Paul. Much appreciated! Smile

------------------------------------------
Quote Reply
Re: [Paul] Mysql - query - sorting by field, but I want only certain values in that field In reply to
Hi All

Okay, two things:

1. when I click to export the results to either the screen or to an external file, I get a "denied" error message. Can I do something about this, or is this a server admin thing and I'm stuck?

Quote:
Permission to perform action denied!

MySQL Said: Access denied for user: 'fred@localhost' (Using password: YES).

2. How could I get a range of results? For instance, how could I get the range of orders placed from May 01 to May 31?

SELECT * FROM transactions WHERE date ??? ORDER BY date LIMIT 0, 2000

Many thanks. I appreciate your help muchly Smile

------------------------------------------
Quote Reply
Re: [DogTags] Mysql - query - sorting by field, but I want only certain values in that field In reply to
1) Your admin can limit the type of queries you can do... for example, select, update, export, import, etc. and that may be what's going on.

2) If your date field was a timestamp you can use BETWEEN or you could just do a greater than/less than comparison. You might be able to do it with text fields too but I've never tried.

--Philip
Links 2.0 moderator