How to display multiple rows of comma separated values from mysql db

Question

I need your help with display of some comma separated enteries from my database.

My main table schema looks like this

              |---|----------|-----------|----------------------------|
              |id | tab1     |  tab2     |          tab3              | 
              |---|----------|-----------|----------------------------|
              |1  |  state1  |  A-North  |   constA1,constA2,constA3  |
              |2  |  state2  |  B-South  |   constB1,constB2,constB3  |
              ---------------------------------------------------------

Query I'm trying to make work

$query = mysql_query("SELECT * FROM `main` WHERE `tab1` = '$tab1'") 
         or die(mysql_error());

while($row=mysql_fetch_array($query)){                  
  $tab3 = explode(",", $row['tab3']);
  echo $tab3."<br>";
}

What I want to display from the database

               A-North      B-South
               ---------------------                   
               constA1      constB1
               constA2      constB2
               constA3      constB3

Error I'm getting when I run that query is "Array" . When I run the same code from phpMyAdmin, I get the desired rows (result).

Your database structure is violationg the first normalisation rule. You better redesign your model to avoid problems like that in the future. — juergen d
– juergen d, Commented Jun 22, 2012 at 10:43
(Remember to quote both the query string for the database and the output to the browser, e.g. see usage of htmlentities and mysql_real_escape_string.) — initall
– initall, Commented Jun 22, 2012 at 10:47

SpaceBeers · Accepted Answer · 2012-06-22 10:42:51Z

4

Explode gives you the results in an array so you'll want another loop that runs through the $tab3 array printing the result. See the definition from the PHP manual:

Returns an array of strings, each of which is a substring of string formed by splitting it on boundaries formed by the string delimiter.

For example:

for ($i = 0; $i < sizeof($tab3); $i++) {
    echo $tab3[$i].'<br />';
}

answered Jun 22, 2012 at 10:42

SpaceBeers

14k6 gold badges50 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Miqdad Ali · Accepted Answer · 2012-06-22 10:50:42Z

1

explode will return array using print_r($tab3) you can view the items and access it by

echo $tab[0];
echo $tab[1];
echo $tab[2];

etc.....

edited Jun 22, 2012 at 10:50

answered Jun 22, 2012 at 10:43

Miqdad Ali

6,1577 gold badges34 silver badges50 bronze badges

1 Comment

SpaceBeers Over a year ago

Surely you want to loop through the array? What if there are more than 3 items in there?

DaveyBoy · Accepted Answer · 2012-06-22 10:44:44Z

0

explode ($separator,$string) returns an array. You need to step through the array to create the table columns.

Also, as you want the data in two columns, create these separately as two separate tables and then include those into a single table as two separate cells

Finally, redesign your database table. You'll thank us in the end

answered Jun 22, 2012 at 10:44

DaveyBoy

2,9162 gold badges19 silver badges30 bronze badges

2 Comments

user652792 Over a year ago

Thanks for pointing out such a vital information. I was thinking what in a case whereby entries for tab3 are around 100 or 200, does that mean the use will have to keep typing into different boxes? Please how may I redesign the database structure.? Thanks

DaveyBoy Over a year ago

Move the "tab3" column to a separate table but with each value (i.e., constA1,constA2,constA3 etc) in a separate field with a reference back to the ID value of the main table. Something like

create table tab3 (`id` int(10) unsigned not null auto_increment primary key, `mainID` int(10) unsigned not null default 0, `tab3Value` varchar(100) not null default '');

.

Collectives™ on Stack Overflow

How to display multiple rows of comma separated values from mysql db

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related