SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    377
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question errors setting up mysql select based on field like

    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?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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.

  3. #3
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    377
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Do you already have any code?

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    What you need is not a PHP script but a better mySQL query.

  6. #6
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    377
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    <?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 }; ?>

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    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
    Code MySQL:
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    and unless two different vehicles can be registered with the same vehicle_id, you don't need DISTINCT
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    377
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •