SQL - using WHERE / AND

[adammc]

Hi guys,

I havent really had to do this yet whilst I have been learning PHP.
Can anyone possibly help me format this query?

[php]

// make the 1st query to get the sellers email alert info
$query = "SELECT alert_id, email, categories, condition, state, vehicle_make, vehicle_model FROM email_alerts WHERE sellers_id=$_SESSION[sellers_id]";

// run the query
$result = @mysql_query ($query);

// get the number of rows
$num = mysql_num_rows($result);

while($row=mysql_fetch_array($result))
{



// make the 2nd query to get the postings info for matching requests
$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 posting_id='$row[posting_id]' AND category='%$row[categories]%' AND condition='%$row[condition]%' AND state='%$row[state]%' AND vehicle_type='$row[vehicle_make]' AND vehicle_make='$row[vehicle_model]' ";

// run the query
$result2 = @mysql_query ($query2);

while($row2=mysql_fetch_array($result2))
{

[/php]

As you can see from above, I need to run $query2 to extract the data using more than one criteria (AND....)

How do i correctly format this?

Also I took a guess with the instances of '%'.
I'm not sure if it will work?

Example:
The 'categories' column in the table (email_alerts) holds values like (Brakes, Clutches, Exhausts)

I want to search the table (postings) for entries Where the posting ID='$row[posting_id]' AND the category

the 'category' column in the table (postings) holds values like (Brakes)

Any help would be greatly appreciated :)

[adammc]

Ooops, small typo there.
The email alerts table doesnt contain a column called posting_id.

I took another guess and came up with 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 category LIKE %'$row[categories]'% ";

It didnt work either :(
Am I on the right track?

[jatar_k]

given the two examples seem to be fairly different, you have me confused, maybe other members too since no one answered ;)

so you want to

get info from the email_alerts table
use that info to query the postings table

then my questions would be

1. what info (columns) can you use from the email_alerts select
2. what columns do you need from the postings table
3. what criteria and/or data do you have/need for the query on the postings table

[adammc]

Hiya JatarK :)

I have kind of achieved it by using this:

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

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?

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

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 table
ID ¦ sellers_id ¦ email
5 ¦ 5 ¦ [e-mail]
6 ¦ 6 ¦ [e-mail]

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? I havent done a table join query before :(

[jatar_k]

multiple AND's with multiple LIKE's add a JOIN and you could have the slowest query of all time

you shouldn't need all of those LIKEs

categories should be static and you could use =
condition should be static and you could use =
state should be static and you could use =

I like the email alerts as a single table, makes more sense that way

this looks like some kind of automotive classifieds script

so, it looks like you should be using more dropdowns for entry of listings and less free form text fields, this would allow all of these to skip LIKE and just use =

from the look of your queries above it looks you have quoting issues, if that is the actual php code

here are some debugging tips (you may already know them but that's fine)

1. build queries seperately from the mysql_query function call.
2. concatenate all variables when building your query, do not try to let them resolve inside double quotes
3. echo your constructed query to the browser to see if what you created is what you wanted to create
4. always use an or die statement on your mysql_query function call to trap errors returned from mysql

so, given those rules, I will demonstrate how you can see your problem

you already followed rule 1, perfect

ah, but you're not following rule 2 so I want you to skip to rule 3 and add

echo '<p>',$query2;

after where you are building your query and take a look at what you are trying to send to mysql

then you probably need to go back to rule 2 ;)

[adammc]

Thank you so much for the informative reply JatarK :)

Yes, it is an automotive classifieds site.

Most of the data entries for this part of the script need to allow for multiple selections (radio boxes) so I really cant use less free form fields.

The condition field for example lets the user select which condition of the part he wants to be notified about. He might select to be notified about posts added into the 'New Parts & Used Parts, but not the Reconditioned)

3. echo your constructed query to the browser to see if what you created is what you wanted to create
Do you mean for debugging purposes?

You said that I would leave the DB design the way it is... So it is ok to store muliple data in a column (Brakes, Clutches)?

[jatar_k]

when I say free form fields I mean textboxes, if you are using radio buttons then the set of values for this column will be fixed, therefore, no need to use LIKE.

yes you can have multiple values in a single column, no worries there

ever looked at ENUM [dev.mysql.com]?

did you try echo'ing your query to see what looked wrong with it?