SQL - searching help - PHPBuilder Forums

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]' ";

mjax

Although this kind of thing should be modelled with a related/linked table, I think you could modify your query slightly like this:

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

adammc

That did the trick, THANK YOU 🙂

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

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

$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]%'";

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.

adammc

Or....

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_condition table
ID | condition |
5 | NEW |
6 | USED |

How would I then build my query?
I havent done a table join query before 🙁