Re: [PHP-DB] Modify Query, or sift through results?
My Last post was incorrect - sorry. My correct sql statement would be rlike "(^|;)Midwest(;|$)". Someone mentioned using Distinct before, which wouldn't work because Distinct acts on a field, to my understanding, so "Northwest;East" would be different than "Northwest;South" so it wouldn't really give me the results I'm looking for. Someone else mentioned FIND_IN_STR, which I had previously tried. That will find 'North' in 'Northeast' because 'North' is in the string. Doing a Reg Exp allows me to find exactly what I'm looking for, but with a little flexability to account for the semicolons and what not. Hopefully someone else has learned something too :) Joe Jason Stechschulte wrote: > > On Wed, Jun 27, 2001 at 08:54:04AM -0500, Joseph Koenig wrote: > > I've got a script that searches a db every night and sends out e-mail if > > it finds something a person would be interested in, based on criteria > > they gave us. The problem is that I have one field that stores regions > > of the country in a very unpleasant way. It stores them as: > > > > Northeast;West;South > > > > So all of the regions for one record go into one field. The problem is > > that when searching that, if someone has a preference of 'North', I > > dont want to pull records that have 'Northeast', as would happen with > > the above example. Is there a way to modify the MySQL query so as to > > find only 'North', keeping in mind that 'North' may be the 3rd in a list > > of semi-colon separated regions? Or do I need to sift through the > > results in PHP to clean them up? Thanks, > > You can certainly have MySQL do the work. I can't modify your query, > because you didn't give it to us, but I can give you an example. > > $sql = select * from your_table where your_column rlike "North(;|$)"; > ?> > > This will do a regular expression match. If either North with a semicolon > directly after it or North and the end of the string is found, the > row will be returned. > > -- > Jason Stechschulte > [EMAIL PROTECTED] > -- > If you're going to define a shortcut, then make it the base [sic] darn > shortcut you can. > -- Larry Wall in <[EMAIL PROTECTED]> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Modify Query, or sift through results?
Ah ha..now it looks like we're on to something. I had to modify it a bit, but it worked...for the most part. I had to make it rlike "North[;|$]". But here's the next problem...I have a region 'Midwest' and 'Upper Midwest'. When I do this search for Midwest, I get the 'Upper Midwest' results also. So I tried rlike "[^|;]Midwest[;|$]" which returned no results. If I'm thinking correctly, that should be saying: Find the beginning of a string, or a semicolon, then "Midwest", then a semicolon, or the end of a string. Is that correct? Thanks, Joe Jason Stechschulte wrote: > > On Wed, Jun 27, 2001 at 08:54:04AM -0500, Joseph Koenig wrote: > > I've got a script that searches a db every night and sends out e-mail if > > it finds something a person would be interested in, based on criteria > > they gave us. The problem is that I have one field that stores regions > > of the country in a very unpleasant way. It stores them as: > > > > Northeast;West;South > > > > So all of the regions for one record go into one field. The problem is > > that when searching that, if someone has a preference of 'North', I > > dont want to pull records that have 'Northeast', as would happen with > > the above example. Is there a way to modify the MySQL query so as to > > find only 'North', keeping in mind that 'North' may be the 3rd in a list > > of semi-colon separated regions? Or do I need to sift through the > > results in PHP to clean them up? Thanks, > > You can certainly have MySQL do the work. I can't modify your query, > because you didn't give it to us, but I can give you an example. > > $sql = select * from your_table where your_column rlike "North(;|$)"; > ?> > > This will do a regular expression match. If either North with a semicolon > directly after it or North and the end of the string is found, the > row will be returned. > > -- > Jason Stechschulte > [EMAIL PROTECTED] > -- > If you're going to define a shortcut, then make it the base [sic] darn > shortcut you can. > -- Larry Wall in <[EMAIL PROTECTED]> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Modify Query, or sift through results?
On Wed, Jun 27, 2001 at 08:54:04AM -0500, Joseph Koenig wrote: > I've got a script that searches a db every night and sends out e-mail if > it finds something a person would be interested in, based on criteria > they gave us. The problem is that I have one field that stores regions > of the country in a very unpleasant way. It stores them as: > > Northeast;West;South > > So all of the regions for one record go into one field. The problem is > that when searching that, if someone has a preference of 'North', I > dont want to pull records that have 'Northeast', as would happen with > the above example. Is there a way to modify the MySQL query so as to > find only 'North', keeping in mind that 'North' may be the 3rd in a list > of semi-colon separated regions? Or do I need to sift through the > results in PHP to clean them up? Thanks, You can certainly have MySQL do the work. I can't modify your query, because you didn't give it to us, but I can give you an example. This will do a regular expression match. If either North with a semicolon directly after it or North and the end of the string is found, the row will be returned. -- Jason Stechschulte [EMAIL PROTECTED] -- If you're going to define a shortcut, then make it the base [sic] darn shortcut you can. -- Larry Wall in <[EMAIL PROTECTED]> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Modify Query, or sift through results?
Hi,
you could use the FIND_IN_SET(str,strlist) function which returns a value 1
to N if the string str is in the list strlist consisting of N substrings.
Returns 0 if str is not in strlist or if strlist is the empty string.
Returns NULL if either argument is NULL.
If you want to find all the records that have 'North' you query will look
something like this
SELECT * FROM table_name WHERE FIND_IN_SET('North',regions_field)>0
Dobromir Velev
-Original Message-
From: Joseph Koenig <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Thursday, June 28, 2001 4:50 AM
Subject: [PHP-DB] Modify Query, or sift through results?
>I've got a script that searches a db every night and sends out e-mail if
>it finds something a person would be interested in, based on criteria
>they gave us. The problem is that I have one field that stores regions
>of the country in a very unpleasant way. It stores them as:
>
>Northeast;West;South
>
>So all of the regions for one record go into one field. The problem is
>that when searching that, if someone has a preference of 'North', I
>dont want to pull records that have 'Northeast', as would happen with
>the above example. Is there a way to modify the MySQL query so as to
>find only 'North', keeping in mind that 'North' may be the 3rd in a list
>of semi-colon separated regions? Or do I need to sift through the
>results in PHP to clean them up? Thanks,
>
>Joe
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Modify Query, or sift through results?
Couldn't you use"SELECT DISTINCT" for this? http://www.mysql.com/doc/S/E/SELECT.html - Original Message - From: "Joseph Koenig" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 27, 2001 8:54 AM Subject: [PHP-DB] Modify Query, or sift through results? > I've got a script that searches a db every night and sends out e-mail if > it finds something a person would be interested in, based on criteria > they gave us. The problem is that I have one field that stores regions > of the country in a very unpleasant way. It stores them as: > > Northeast;West;South > > So all of the regions for one record go into one field. The problem is > that when searching that, if someone has a preference of 'North', I > dont want to pull records that have 'Northeast', as would happen with > the above example. Is there a way to modify the MySQL query so as to > find only 'North', keeping in mind that 'North' may be the 3rd in a list > of semi-colon separated regions? Or do I need to sift through the > results in PHP to clean them up? Thanks, > > Joe > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
