#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    6
    Rep Power
    0

    Question Mysql query and arrays


    Hi in my table I have a field that contains an array for example the field is colors and it contains red, green, blue

    what im trying to do is

    select * from table
    WHERE `colors`="blue"

    so I want it to see if the table contains the color blue and if it does I want it to grab all the colors from the database and echo them out

    for example if blue is in the database

    echo "red, green, blue";

    if not then echo "not found";

    or something along them lines

    so basically just need it to search if the information is contained in that field then echo out all the information because at the moment when i do this it would only give the one result which is blue. Thanks for any help you can give hopefully its possible :P
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Code:
    WHERE colors LIKE '%blue%'
    this works, but will get slower and slower as your table grows in size

    do a search for first normal form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    I think Edgar Codd just turned over in his grave.

    Never store "arrays" or lists or whatever in a table. This is pretty much the worst thing you can do. To MySQL, your "arrays" are just arbitrary strings. It cannot access the values, which means you end up with terrible workarounds for scraping the data from the strings. There's also no way to ensure data integrity. Your "array" columns can contain any string. You can insert "%$§$QW&", and MySQL will happily accept it.

    MySQL is a relational database system. This means data is supposed to be stored in the rows of tables. What you want is a "colors" table and a table for linking the colors to your objects (whatever those are).

    This will also make the query a piece of cake -- as opposed to somehow extracting all colors from the strings.



    Originally Posted by r937
    Code:
    WHERE colors LIKE '%blue%'
    And what if you have a color name blueberry?
    Last edited by Jacques1; June 29th, 2013 at 08:11 PM.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    6
    Rep Power
    0
    Hi again lol I dont think i explained this too well initially I was just trying not to confuse everyone because I went to post something different at first but confused myself doing it but here it goes lol :P

    The data in the field is an array but its formed in the query lol i find it hard to try get around but this is my query:

    PHP Code:
    SELECT p.usernamep.startpointp.destination,
    GROUP_CONCAT(w.waypoint ORDER BY w.waypoint ASC) AS waypoints,
    d.mondayd.tuesdayd.wednesdayd.thursdayd.fridayd.fridayu.firstnameu.surnameu.genderu.emailu.contactnumber
    FROM postaride 
    AS p
    JOIN waypoints 
    AS w ON p.id w.waypointid
    JOIN days 
    AS d ON p.id daysid
    JOIN users 
    AS u ON p.username u.username
    WHERE p
    .username '$session->username'
    GROUP BY p.id 
    This works for when im showing the user information they posted. so it would show everything where the username is what ever there username is but tonight I was trying to create a form where they can search for certain things and one of them things happens to be the waypoint mentioned above which has stored in it after the join: Barking,Dovercourt,Gestingthorpe,Herongate etc but before the join the waypoints are in their own table with an separate id for each one. I dont know if this makes sense to you because as I mentioned I have trouble trying to explain it lol but hopefully this sheds some light on it a bit
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by scorpion20056
    I have trouble trying to explain it lol
    you sure do lol

    obviously, if you are searching for a waypoint, you do it in the WHERE clause on the waypoint rows and not on the result of the GROUP_CONCAT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    you sure do lol

    obviously, if you are searching for a waypoint, you do it in the WHERE clause on the waypoint rows and not on the result of the GROUP_CONCAT
    Yeah I always have done but the only thing is I need it to group the results in that way so when I search for a certain waypoint it will pick it up and display all of the waypoints for a user with a certain id lol if that one makes sense lol. The only thing im trying to really do is search the array that is in the database to see if something exist in it then output that result. When I use LIKE %% it only outputs the one result. for example LIKE %test% would only output 'test' and not the rest of the array
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by scorpion20056
    for example LIKE %test% would only output 'test' and not the rest of the array
    no, that's wrong

    perhaps you should test it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    no, that's wrong

    perhaps you should test it
    yeah i did this is my query
    PHP Code:
    $query mysql_query("

    SELECT p.username, p.startpoint, p.destination,
            GROUP_CONCAT(w.waypoint ORDER BY w.waypoint ASC) AS waypoints,
            d.monday, d.tuesday, d.wednesday, d.thursday, d.friday, d.friday
    FROM postaride AS p
    JOIN waypoints AS w ON p.id = w.waypointid
    JOIN days AS d ON p.id = daysid
    WHERE `waypoint` LIKE '%barking%'

    GROUP BY p.id

        "
    ); 
    and the output is

    barking - scorpion

    instead of

    barking, dagenham, etc - scorpion i even tried the query in phpmyadmin and in the waypoint column only the one which is is close to the like would show up for some reason
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by scorpion20056
    ...in the waypoint column only the one which is is close to the like would show up for some reason
    wish i could confirm this problem, but i don't have your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    wish i could confirm this problem, but i don't have your data
    ahh err what would you need? i would be happy to show you it could help get an answer :P ive been driving myself mad for a couple days on this problem lol
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    dump your tables with mysqldump (also, phpmyadmin has an option to export) into a .sql that you can post

    include only as many INSERT statements as necessary to have representative data that can be used to illustrate the issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    Just an idea, it may not be the best approach but i think it should give you the result you are looking for.
    Try with this WHERE: (untested)
    sql Code:
     
    WHERE w.waypointid IN (
    		SELECT DISTINCT waypointid 
    		FROM waypoints 
    		WHERE `waypoint` LIKE '%barking%'
    	)


    If am totally wrong on, please tell
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    6
    Rep Power
    0

    Thumbs up


    Originally Posted by MrFujin
    Just an idea, it may not be the best approach but i think it should give you the result you are looking for.
    Try with this WHERE: (untested)
    sql Code:
     
    WHERE w.waypointid IN (
    		SELECT DISTINCT waypointid 
    		FROM waypoints 
    		WHERE `waypoint` LIKE '%barking%'
    	)


    If am totally wrong on, please tell
    Oh wow! I just actually got to try this and I have got to say that is amazing lol it literally does exactly what i need it to and how I wanted to do it so thanks a lot for that thats something I would have never come up with lol

IMN logo majestic logo threadwatch logo seochat tools logo