Page 2 of 2 First 12
  • Jump to page:
    #16
  1. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,700
    Rep Power
    9646
    Don't need the full SQL to solve the problem: stop storing comma-separated values and normalize the column into a separate table.

    Bad structure:
    Code:
    id | values
    ---+-------
     1 | 1,2,3
     2 | 4,5,6
    Good structure:
    Code:
    id
    --
     1
     2
    
    (new table)
    id from other table | value
    --------------------+------
                      1 |     1
                      1 |     2
                      1 |     3
                      2 |     4
                      2 |     5
                      2 |     6
    Any questions?

    Once it is into that form, querying is a matter of taking whatever you have now, JOINing in the new table, and then looking in it for the matching value. Naturally, adding values is no longer appending to a text column but adding a new row to the table. Yes, you will have a table with a lot of rows, but databases are totally designed for that sort of thing.

    Comments on this post

    • pwpx2 agrees
    Last edited by requinix; December 1st, 2017 at 02:06 PM.
  2. #17
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2017
    Location
    Worldwide
    Posts
    45
    Rep Power
    44
    Originally Posted by requinix
    Don't need the full SQL to solve the problem
    True. Reason I wanted to see the full SQL is because it is highly likely there are other problems with the DB.
  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    95
    Rep Power
    2
    Thank you requinix.
    @benanamen how can it be problems with the DB if everything works ?
  6. #19
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    95
    Rep Power
    2
    The idea is that i already have an entire new table for such thing where each row is designed for a userid, article id and username. And if article id from that table doesnt match the userid and username their access is denied.
    I just wanted to be able to insert this directly from the article edit page and hence thought of a single field into the articles table.
    For the moment this is done from the member's edit page where i select on which articles to have access but i wanted to change it for various reasons.
  8. #20
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2017
    Location
    Worldwide
    Posts
    45
    Rep Power
    44
    Well @pwpx2, as you learn more about programming you will find there are many things that "work" but are not correct. You could pound in a nail with a rock and it will "work" but it is obviously not the way to hammer nails. Depending on what is not right it could lead to forever having to code "hacks" or "workarounds" which require more time and money to deal with. The database is the foundation of all the code you will ever write to interact with it. If the DB is not right, then the code you write for it will not be optimum.

    For example, this thread about putting multiple data values in the same field could be made to "work" but is incorrect. If you really want an answer about your DB you can post the whole dump of your DB structure and we will be happy to review it for you.

    My experience with OP's on the various forums makes it quite clear that they are not database engineers so the probability that their DB design has problems is very high. Many of the experts prefer to just answer the specific question posted. I prefer go a bit further and look beyond the exact issue posted about. For example, the place I have the oil changed in my car "could" just change the oil and filter, after all, that's why I went there, but they go beyond that and check the other fluids, check the air filter, vacuum the floor, clean my windows, etc, etc.
  10. #21
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2017
    Location
    Worldwide
    Posts
    45
    Rep Power
    44
    FYI: I was answering post #18. We cross posted each other. I cant edit on this forum.
  12. #22
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,653
    Rep Power
    1822
    benanamen is right. It is best to learn how to do it right as early as you can, it will save a ton of grief at a later stage. The 'sad' thing is that the right way often looks to be harder, more hassle, involving more work. That is true. But later down the line you will be very glad if you did it right.
    And don't bother looking at my post count - I don't do php, dabble lightly in mySQL, but have 30+ years in IT from code-monkey to SysProg on mainframes/Unix boxes.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  14. #23
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    95
    Rep Power
    2
    ...
  16. #24
  17. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,700
    Rep Power
    9646
    Just because it works does not mean it is right or good. Sorry you aren't getting the replies you wanted to hear.

    You need to split the values into a new table. That is the right and good thing to do. It has many benefits that a comma-separated approach does not have. It sounds easy to have just the one column for multiple values but it will cause problems - such as the one you have right now where you're trying to search that column for one value contained inside it. Other database systems could do it but MySQL cannot.

    Move it into a table. It's a little more work but it's definitely worth it.
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo