SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Enthusiast iwiz_seo's Avatar
    Join Date
    Sep 2009
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How can split and display data retrieved from datavase containing commas

    Hi all,

    Below are the data that I have to split and display in select box.

    cs,it,ece,eee,others
    cs,se,ae,em,pe,vlsi

    I want to split the above two rows retrieved from the database and display in a select box. Could anybody help on this.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    This should get you started.
    PHP Code:
    <?php
    echo '<select name="foo">';
    foreach(
    explode(',''cs,it,ece,eee,others') as $value)
    {
        
    printf(
            
    '<option value="%1$s">
                %1$s
            </option>'
    ,
            
    $value
        
    );
    }
    echo 
    '</select>';
    /*
        <select name="foo">
            <option value="cs">
                cs
            </option>
            <option value="it">
                it
            </option>
            <option value="ece">
                ece
            </option>
            <option value="eee">
                eee
            </option>
            <option value="others">
                others
            </option>
        </select>
    */
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Enthusiast iwiz_seo's Avatar
    Join Date
    Sep 2009
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as you can see in my original post two rows having the same name(cs,cs). How can remove the repeated item.

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ah.
    PHP Code:
    <?php
    $values 
    array_unique(
        
    array_merge(
            
    explode(',''cs,it,ece,eee,others'),
            
    explode(',''cs,se,ae,em,pe,vlsi')
        )
    );
    echo 
    '<select name="foo">';
    foreach(
    $values as $value)
    {
        
    printf(
            
    '<option value="%1$s">
                %1$s
            </option>'
    ,
            
    $value
        
    );
    }
    echo 
    '</select>';
    /*
        <select name="foo">
            <option value="cs">
                cs
            </option>
            <option value="it">
                it
            </option>
            <option value="ece">
                ece
            </option>
            <option value="eee">
                eee
            </option>
            <option value="others">
                others
            </option>
            <option value="se">
                se
            </option>
            <option value="ae">
                ae
            </option>
            <option value="em">
                em
            </option>
            <option value="pe">
                pe
            </option>
            <option value="vlsi">
                vlsi
            </option>
        </select>
    */
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What you should do, is normalise your database. Comma separated values in a column is not a good thing. It'll create problems when you want to select certain values, or, in this case, when you want to get unique values. Of course, you can do it in PHP, but that partially defeats the advantage of using a relational database.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SilverBulletUK,

    I don't think that your code is alright.

    The explode(',', 'cs,it,ece,eee,others') in foreach explodes the string into an array during each cycle of foreach, it should be like this:

    PHP Code:
    $myArr explode(',''cs,it,ece,eee,others');
    foreach(
    $myArr as $value){
    ...

    Moreover I don't think, that it is the best to use foreach in this case. Foreach creates a copy of an array and it can lower performance of system in case of large arrays.

    It is better to use:
    PHP Code:
    $myArr explode(',''cs,it,ece,eee,others');
    $i 0;
    $myArrLength count($myArrLength);
    for (
    $i$i myArrLength$i++) { ... } 
    Jake

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by jake.polak View Post
    The explode(',', 'cs,it,ece,eee,others') in foreach
    There is no explode in the foreach in silverbullet's code.
    Moreover I don't think, that it is the best to use foreach in this case. Foreach creates a copy of an array and it can lower performance of system in case of large arrays
    5 values is hardly a large array.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    guido2004,

    read first SilverBulletUK's comment and you will see that there is explode in foreach.

    Because iwiz_seo is retrieving data from database it is possible that he will want to add more and more data there, that's why I suggested to use for cycle instead of foreach. An array which contains 100 values is big enough to consider using for cycle instead of foreach cycle.

    Jake

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by jake.polak View Post
    guido2004,

    read first SilverBulletUK's comment and you will see that there is explode in foreach.
    In his first comment, there's an explode in the 'function' part (I don't know how it's called ) of the foreach. It'll execute it only once, and then loop through the resulting array.
    Because iwiz_seo is retrieving data from database it is possible that he will want to add more and more data there, that's why I suggested to use for cycle instead of foreach. An array which contains 100 values is big enough to consider using for cycle instead of foreach cycle.

    Jake
    That is possible. In that case, you might be right. I didn't know foreach has this drawback. Of course, any way it would be much better if he normalized his database.

  10. #10
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it is as you are saying that it executes only once, than I am sorry for my mistake, but I would still prefer to put it before foreach call, because of improved readability of code.

    Jake

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    "How can split and display data retrieved from datavase containing commas"

    I'm still giggling about why you'd want to keep commas in a vase.

    And no, I have not touched a drop (going teetotal for the next month).

  12. #12
    SitePoint Enthusiast iwiz_seo's Avatar
    Join Date
    Sep 2009
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Data already stored in database with commas and now I cannot modify the database. Please tell me how to retrieve the same data stored in many rows with commas and how to remove the redundant data.

    Example:

    column1 column2
    Degree Department

    be cs,it,ece,eee,others
    me cs,it,ece,civil,vlsi

    I want to retrieve data in department column and display in select box. Also I want to display 'cs' twice as it repeats in both rows.
    Last edited by iwiz_seo; Oct 20, 2009 at 22:15. Reason: update

  13. #13
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by iwiz_seo
    I want to display 'cs' twice as it repeats in both rows.
    Quote Originally Posted by iwiz_seo
    as you can see in my original post two rows having the same name(cs,cs). How can remove the repeated item.
    Er, so which one is it?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  14. #14
    SitePoint Enthusiast iwiz_seo's Avatar
    Join Date
    Sep 2009
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I made a mistake. I said "I want to display 'cs' twice as it repeats in both rows" instead of I don't want to display 'cs' twice as it repeats in both rows

    "Originally Posted by iwiz_seo
    I want to display 'cs' twice as it repeats in both rows. "

    Please help me.

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code PHP:
    function createArray ($array, $string) {
      $values = array_unique(
        array_merge(
          $array,
          explode(',', $string)
        )
      );
      return $values;
    }
     
    // elaborate mysql result set and create array with unique values
    $values = array();
    while ($row = mysql_fetch_array($result) {
       $values = createArray($values, $row['Department']);  
    }
     
    // create drop down
    echo '<select name="foo">';
    foreach($values as $value)
    {
        printf(
            '<option value="%1$s">
                %1$s
            </option>',
            $value
        );
    }
    But you really should normalize your database. Why can't you do that? If you think it's a waste of time redesigning it, and migrate the data to the new structure, then please reconsider. In the end, it'll save you a lot of time and future development problems.

  16. #16
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi I'm just curious, if there are any situations that using comma separated values in a field might be considered.

    For instance, I have a user database with such field, holding a list of favorite games that user picked.

    Column Name: favGames
    Value: game1,game2,game3...


    value may also contain additional data for each game as:

    game1:visitcount:score:lastplayed,game2:.....,game3:.....

    I do this because I find it easier to get all user data (like userName, lastLogin, totalScore...favGames...) I need from a single table with a single query. Plus being able to update the field with simple string manipulation (within the query or by PHP).

    I accept it looks against using a rel. database at all. What migth be a better structure?

    Any ideas appreciated.
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter

  17. #17
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your solution is not very wise. You should have a separate table of users, a separate table of games and a table which would be a connection between users and games with 2 columns - id_user, id_fav_game. It is possible to select all you need with 1 select. It is a bit more complex, but not impossible.

  18. #18
    Floridiot joebert's Avatar
    Join Date
    Mar 2004
    Location
    Kenneth City, FL
    Posts
    823
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $key '';
    $options = array();
    for(
    $i 0$toi strlen($str); $i $toi$i++)
    {
       if(
    $str[$i] == ',')
       {
          
    $options[$key] = "<option>$key</option>";
          
    $key '';
          continue;
       }
       
    $key .= $str[$i];
    }
    $options implode($options); 

  19. #19
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, you should use implode and explode function to carry out. just check SilverBullet, i think it is right, i have not tested it.

  20. #20
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by glenngould View Post
    Hi I'm just curious, if there are any situations that using comma separated values in a field might be considered.
    I don't know. Probably there are some extreme, rare cases, but that wouldn't mean anything for the other 99,99%.
    For instance, I have a user database with such field, holding a list of favorite games that user picked.

    Column Name: favGames
    Value: game1,game2,game3...


    value may also contain additional data for each game as:

    game1:visitcount:score:lastplayed,game2:.....,game3:.....

    I do this because I find it easier to get all user data (like userName, lastLogin, totalScore...favGames...) I need from a single table with a single query.
    After normalization, you'd still be able to get all the info with a single query. All you'd have to do is join the tables. That isn't hard to do. It isn't hard to learn either.
    Plus being able to update the field with simple string manipulation (within the query or by PHP).
    A string manipulation surely isn't easier than a simple UPDATE of a column value?

    You might also save all your data in a single text file. No need for a database. All you have to do is read the file, do some simple string manipulations, rewrite the file, and you're done

    Of course, if you'd want to know what game is favorite with the most users, things might get a bit complicated. Or if you want to know the average score of a certain game, or...

    You ask for a better structure? jake.polak has given you a good idea. And you might want to study normalization a bit.

  21. #21
    SitePoint Guru glenngould's Avatar
    Join Date
    Nov 2005
    Posts
    661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I agree with all guido2004. It's actually no different than using a text file instead of a database and harder to modify things when you need to retrieve different kind of data in the future.
    Anyway I generally use a mixed structure; tables for the most part, comma separated values for some rare cases.
    Tweep List adds an avatar menu to Twitter (open source)
    Word Stats shows your most used words on Twitter


Tags for this Thread

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
  •