Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Concatinate insert into MYSQL DB

    Goal: Have a dynamic list to check boxes which will be inserted into one field in the DB coma separates!

    For example; I have check boxes for red(1), green(2), blue(3) and yellow(4). I want the client to select as many as they wish and insert the numbers into one field in the DB. So the field colors might have a value of 1,3,4.

    Any ideas

    Nick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    ...which will be inserted into one field in the DB coma separates!
    that's a bad idea -- it will put your query performance into a coma

    Quote Originally Posted by oldnickj View Post
    ... and insert the numbers into one field in the DB.
    Code:
    INSERT
      INTO daTable
         ( somekey
         , colors )
    VALUES
         ( 'somevalue'
         , '1,3,4' )
    it's still a bad idea, but that's how you do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124
    This is going to be a large table of image file names and I want to include attributes, like colors, but the list is quite long and rather then have a wide table, which I have to constantly alter, I thought I would store the attributes in one simple field then when I want images with several attributes I query on LIKE IN().

    What's bad?
    Hi by the way.

    Nick

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    What's bad?
    the SQL to find all red images will be forced to use a table scan

    the SQL to display all colour names for each image will be practically impossible to write -- try it yourself first before you commit to the comma-delimited design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    124
    The list is finite, from a lookup (label) table which is populated by a client form. I would like to store just the key from 'label'.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Create three tables, pictures, attributes, and picture_attributes. In the pictures table, include all of the information about a picture (size, format, URL or UNC, etc). In the attributes table, have one row for each attribute (red, large, pastel, landscape, etc). In the picture_attributes table, have one row for each attribute that a picture has: Picture one might have rows for landscape and JPEG and picture 937 might have rows for pastel and GIF.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2009
    Posts
    124

    Implode

    the solution which gave me the desired result was to rename the check boxes and customize the insert value.

    The field name had to be renamed to: from theme to theme[] to create the array.

    the insert had to be customied form:
    <?php echo((isset($_POST["theme_img[]"]))?$_POST["theme_img[]"]:"") ?>

    to
    <?php echo(isset($_POST['theme_img[]'])?implode(", ", $_POST['theme_img[]']):""); ?>

    the next task is to figure out how to populate the correct check boxes on the update page!

    I'll be back.

    Nick

Posting Permissions

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