SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast adamschroeder's Avatar
    Join Date
    Mar 2011
    Location
    World
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple to one row mysql

    Hi guys,
    I have a multiple select box I've succeeded to insert into my database, but when I insert the data, rows are obviously created for each value inserted. I looked up serialization to try and rearrange the array in a string so only one row is created, but can't get it.
    What am I doing wrong.

    code:
    HTML Code:
    <select name="inputfield[]" size=5 multiple="yes">
    <option value="tea">tea</option>
    <option value="coffee">coffee</option>
    <option value="water">water</option>
    PHP Code:
    foreach ($_POST['inputfield'] as $value)
                                            {
                                              
    $denserialize($value);
                                              
    $data $den;

     
    mysql_query ("INSERT INTO mainweb (`id`) VALUES('"$data ."')";


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by adamschroeder View Post
    What am I doing wrong.
    well, for starters, thinking that you should put multiple values in a string so only one row is created

    better to create multiple rows, that way searching on any given value will be an efficient, indexed query rather than forcing a scan of your entire table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast adamschroeder's Avatar
    Join Date
    Mar 2011
    Location
    World
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I put multiple values in a string when using a select box with multiple values?

    I know how to use the foreach function, creating an array but I'm having a hard time converting it into a string. I don't need multiple rows in my database, not for this field at least.

  4. #4
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I agree with Rudy, but if you need the items as a string in the DB, this is an idea:

    PHP Code:
    <?php

    if( isset($_POST['inputfield']) ) {
        
    $array = array();
        foreach( 
    $_POST['inputfield'] as $key => $value ) {
            
    $array[] = $value;
        }
        
    $array json_encode($array);


    // Example insert
        
    echo "INSERT INTO mainweb (`id`) VALUES('"$array ."') LIMIT 1";
    // Example decode    
        
    print_r(json_decode($array));
    }
    ?>
    <form action="test.php" method="post">
        <select name="inputfield[]" size=5 multiple="yes">
            <option value="tea">tea</option>
            <option value="coffee">coffee</option>
            <option value="water">water</option>
        </select>
        <input type="submit" value="submit" />
    </form>
    So the insert could look like:
    INSERT INTO mainweb (`id`) VALUES('["coffee","water"]') LIMIT 1

    And the decode would be:
    Array ( [0] => coffee [1] => water )

  5. #5
    SitePoint Enthusiast adamschroeder's Avatar
    Join Date
    Mar 2011
    Location
    World
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Centered effect, it worked. I hate bothering you again but if or when you have time could you please explain to me what these three lines of your code mean. Because I just copy-pasted it without learning anything.
    They did the job, but what does $array = array(); mean or $key => $value????

    And how would I echo it out as a normal string. Just plain text that will show beer, tea (assuming I chose those two)?

    PHP Code:
    if( isset($_POST['inputfield']) ) {

        
    $array = array();

        foreach( 
    $_POST['inputfield'] as $key => $value ) {

            
    $array[] = $value;

        }
     
    $array json_encode($array);

    // Example decode    

        
    print_r(json_decode($array)); 

  6. #6
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry, here is an easier code using implode and explode:
    Implode would collapse the array into a string delimited by what you choose. explode will do the opposite

    PHP: implode - Manual
    PHP: explode - Manual

    PHP Code:
    <?php

    print_r
    ($_POST);

    if( isset(
    $_POST['inputfield']) ) {
        
        
    $str implode(','$_POST['inputfield']);

    // Example insert
        
    echo "INSERT INTO mainweb (`id`) VALUES('"$str ."') LIMIT 1";
    // Example explode    
        
    print_r(explode(','$str));
    }
    ?>
    <form action="test.php" method="post">
        <select name="inputfield[]" size=3 multiple="yes">
            <option value="tea">tea</option>
            <option value="coffee">coffee</option>
            <option value="water">water</option>
        </select>
        <input type="submit" value="submit" />
    </form>
    You can echo it out by echoing $str like what is in the Value part of the SQL statement


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
  •