SQL - searching help

[adammc]

Hi guys,

I have used the following code during a MYSQL INSERT

// Format the categories variable, make them seperated by a comma
if (is_array($categories))
{
$categories_new = implode(", " , $categories);
}

For example: the categories column contains data like this:

Brakes
Brakes, Clutches, Wheels, Windscreens
Windscreens

I am trying to search my table for any entries that for example contain 'Brakes'. This should return 2 results based oin the example above.

Only problem is my code isnt working, I am guessing because its not going through the comma deliminatede list. (getting to the first entry and stopping there.

Do I have to explode it before I can search it?

$query2 = "select * FROM postings WHERE vehicle_make = '$row[vehicle_model]' AND category LIKE '$row[vehicle_model]' ";

[GJay]

without using % as a wildcard in the comparison, LIKE is effectively '='.

Using delimited strings isn't sensible though, take a look at 'normalisation'

[adammc]

thanks for the reply..

so instead of storing my data in this format:
Brakes, Clutches, Wheels, Windscreens

How should i construct it?

[adammc]

I have kind of achieved it by using this:

$query2 = "select posting_id, vehicle_type, vehicle_make, year, buyers_city, buyers_state, condition, category, DATE_FORMAT(expiry_date, '%e-%m-%y, %h:%i %p') as e_date FROM postings WHERE vehicle_type = '$row[vehicle_make]' AND category LIKE '%$row[category]%'";

However, I still have a few more criteria than needs to be compared:

AND condition LIKE '%$row[condition]%'
AND buyers_state LIKE '%$row[state]%'


I have tried using AND multiple times but get no result, how do I correctly format this?

WHERE vehicle_type = '$row[vehicle_make]' AND category LIKE '%$row[category]%' AND conditionLIKE '%$row[condition]%' AND state LIKE '%$row[state]%'";


Example:


First row of Data in postings table
Vehicle_type= Nissan
Category= Brakes
Condition= New
State= QLD


Second row of Data in postings table
Vehicle_type= Toyota
Category= Brakes
Condition= New
State= QLD


Entry in the email alerts table
Vehicle_type= Nissan, Ford
Category= Brakes, Clutches
Condition= New
State= QLD, VIC, TAS

Using this example above...
If I ran my query that I am trying to construct the result should be only the first row in the postings table becausde the second row's vehicle type =Toyota even though it is a match on all other columns.

[guelphdad]

you should not have multiple data stored in a single column. you should look into database normalization. check the mysql forum and the resources thread for an article.

[adammc]

Hi guelphdad,

If I create a seperate tables for the columns that require multiple data:

email_alerts_make
email_alerts_model
email_alerts_state
email_alerts_categories
email_alerts_condition

I assume that when I 'INSERT' a record I make the tables relate using my inital 'email_alert_id' which is a auto inc row in the email_alerts table?

Example:

email_alerts table
ID | date | name | etc....
5 | datehere | JOHN | .....
6 | datehere | BOB | .....

email_alerts_vehicle table
ID | model |
5 | FORD |
6 | HOLDEN |


email_alerts_condition table
ID | condition |
5 | NEW |
6 | USED |

How would I then build my query?