Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question SQL - searching help

    Hi guys,

    I have used the following code during a MYSQL INSERT

    PHP Code:
    //  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]' ";

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    without using % as a wildcard in the comparison, LIKE is effectively '='.

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

  • #3
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks for the reply..

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

    How should i construct it?

  • #4
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have kind of achieved it by using this:

    PHP Code:
    $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?

    PHP Code:
    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.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #6
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?


  •  

    Posting Permissions

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