errors setting up mysql select based on field like

[sketchgal]

Can anyone tell me how I can set up the following please?

I need to set up a script that will check to see if a field in a mysql db contains one of a list of words and another field is blank.

For example, if field1 contains red, blue, green or yellow and field4 is blank say yes.

How would I set up the script to show this?

Thanks for any help in advance?

[guido2004]

From the database, extract only those rows that have red, blue, green or yellow in field1, and blank in field4.
Loop through the result set, and do whatever you want with the results.

[sketchgal]

Thanks for the reply but I don't know how to code what you're telling me to do. Can you give me an example please?

Thanks

[guido2004]

Do you already have any code?

[StarLion]

What you need is not a PHP script but a better mySQL query.

[sketchgal]

I'm afraid I can do nothing about how the data is entered into the database.
Yeah its the sql query part that I'm struggling with.
The code I was trying to get to work is below:

<?php $usedquery = mysql_query("SELECT DISTINCT Vehicle_Id, Full_Registration FROM Used_Stock WHERE Picture_Refs='' AND Colour LIKE '%red%' OR '%blue%' OR '%green%' ORDER BY Full_Registration ASC");
while($row_used = mysql_fetch_array($usedquery)){
echo ?>
<option value="<?php echo $row_used['Full_Registration']; ?>"><?php echo $row_used['Full_Registration']; ?></option>
<?php }; ?>

[guido2004]

SELECT DISTINCT 
    Vehicle_Id
  , Full_Registration 
FROM Used_Stock 
WHERE Picture_Refs='' AND 
      (Colour LIKE '%red%' OR 
       Colour LIKE '%blue%' OR 
       Colour LIKE '%green%'
      )
ORDER BY Full_Registration ASC

[StarLion]

It may be helpful to see why your code didnt work... here's how mySQL saw your query. I've added comments on the end of the lines to show the 'thought' process of the server

SELECT DISTINCT Vehicle_Id, Full_Registration //Okay, those two fields.
FROM Used_Stock //Single table. No problem.
WHERE Picture_Refs='' //Null. Okay. Could be IS NULL as well.
AND Colour LIKE '%red%'  //Sure.
OR '%blue%'  //Huh? Thats not a logical test.
OR '%green%'  //Huh? Thats not a logical test either.
ORDER BY Full_Registration ASC //Okay.

[r937]

and unless two different vehicles can be registered with the same vehicle_id, you don't need DISTINCT

[sketchgal]

Ok thanks for the comments I've now worked out the query so it does what I wanted it to but now have another issue. Thanks for the help.