Hello,

I already have the following query working successfully. I am looking to add a search capability to only pull the rows where the string in the "state" column of my database is part of a large amount of text. In other words, Users will specify that they only want to see rows where the state column is Michigan, Ohio, Texas, California, Florida, etc... My two questions are:

1) How should I be storing the text in MySQL for the states they want to see? Separated by commas (Michigan, Texas, Ohio...) or just bunched together with no spaces (MichiganTexasOhio...) or a different way? I could store it in any way. Is there a certain format I should store it in, Text, Blob, etc to make it searchable?

2) What would I need to add to the query below to make this search and do it fast? Basically, a search with this logic "only pull the rows where the value in the state column is included in a paragraph of text"

$query = "SELECT v.organization, v.city, v.region, v.country, DATE_FORMAT(v.dateTime, '%m/%d/%Y %l:%i %p'), v.visitorID, v.searchWords,
 COUNT(h.hitID) as sumHits FROM visitors as v, hits as h WHERE v.customerID='$customerID_ck' AND cast(v.dateTime as date)='$sGMTMySqlDate' 
AND h.visitorID=v.visitorID GROUP BY h.visitorID ORDER BY $sort LIMIT $start, $displayToday";

    What you probably should be doing is creating one or more separate tables to set up relationships that you can query via a join. For instance, you might have a states table with an auto-increment integer primary key and columns for the state name and the state abbreviation. Then you would have a relation table where each row would be 2 columns, one the primary key value from the visitors table and the other the primary key value from the states table (so there would be one row in this relationship table fore each state for a given visitor).

    You could then populate a select element in your from from the states table, have the value for each option be the primary key. You would then use the selected value in your query to be something like:

    $sql = "SELECT . . . FROM visitors 
INNER JOIN visitor_to_state ON visitors.ID = visitor_to_state.visitors_ID
WHERE visitor_to_state.state_id = '". (int)$_POST['state'] . "'";

      nogdog,

      Thanks for the reply. The query I have in the post works great so I dont think there's a need to modify it. Let me ask another way. I will have a form on a webpage that says, select the states you want to see records for. Right now, I have that as an options pick list. I figured I would store all the values as a large text in MySQL and separate it with commas, like this: "Michigan, Ohio, Texas". This is a way for the viewer to filter out the records they only want to see.

      In the display query, I only want to show records from a "visitors" table that have Michigan OR Ohio OR Texas in the State column.

      I am trying to find out how to do that.

      Ryan

        I know exactly what you are trying to do. What I was trying to suggest was a more "normalized" method of structuring your database to accomplish the same thing, with the advantage that your normalized data design would then be more flexible and easily adaptable to other needs.

        If, however, you prefer to use your non-normalized method of populating a field with multiple data elements, then you can use any of a number of techniques, such as regular expressions, though any of these will me more processing intensive as each potential record will have to be read and parsed.

        $sql = "SELECT blah blah blah WHERE state REGEXP '[[:<:]]" .
mysql_real_escape_string($state) . "[[:>:]]' AND blah blah blah";
          Write a Reply...