40658

Printing out partially unique data after querying a db

Question:

I am having trouble finguring out how to use the table data like I need to... my table consists of a category name column (which has the same value for many subcategories) and the other column contains subcategory names.

For example:

col1 col2 ------------------ apples fruits oranges fruits pears fruits honda cars volvo cars audi cars

I am trying to write a select statement that selects all the data from the database and prints out the category name once ONLY, while printing out all subcategories that, that particular category name includes.

Something like:

<strong>Fruits:</strong>

<ul><li>Apples</li> <li>Oranges</li> <li>Pears</li> </ul>

<strong>Cars:</strong>

<ul><li>Honda</li> <li>Volvo</li> <li>Audi</li> </ul>

Answer1:

SELECT col2, GROUP_CONCAT(col1 SEPARATOR ',') as stuff FROM table1 GROUP BY col2

If you want the '-' in front of the names you can use CONCAT('-',col1) instead of col1.

This php code will transform the result into a 1 dimensional array

$conn = new mysqli('host','user','pass','db_name'); if ($conn->connect_errno) { printf("Connect failed: %s\n", $conn->connect_error); exit(); } if (!($rs = $conn->query(" SELECT col2, GROUP_CONCAT(col1 SEPARATOR ',') as stuff FROM table1 GROUP BY col2 "))) { printf("Error: %s\n", $conn->error); exit(); } $result = array(); while ($row = $rs->fetch_object()){ $result[] = $row->col2; if(!empty($row->stuff)) $result = array_merge($result,explode(',',$row->stuff)); }

Answer2:

What you is it recommended. Moreover, it would make your life with an array very difficult. Consider trying something like this:

$query = mysql_query("SELECT * FROM table"); $array = array(); while($row = mysql_fetch_assoc($query)) { $cat = $row['col2']; $item = $row['col1']; $array[$cat] = $item; }

Now you have an array with categories as keys and items as values for each of those keys.

Good luck!

Recommend

  • R ranef(model, condVar) attributes NULL, but postVar show results + deprecated warning
  • php json_encode doesn't result in real object / make array string into real object / turn php a
  • div tag to show on expand and hide on collapse usi ng xslt
  • PHP - Tricky… array into columns, but in a specific order
  • Why is this code not working? Hangman
  • JQuery UI selectable plugin - Multiple mouse drag selection and unselect option
  • Combine two small queries (that group by different values) into one query
  • Intellisense for C++ in VS2010
  • Refactoring phase of the TDD Traffic Light - how to get this right?
  • Order By Split Column
  • Java ClassLoader Confusion
  • Must EOT be at the bebinning in the line?
  • python struct.pack(): pack multiple datas in a list or a tuple
  • Sequelize mssql: order by primary key and limit
  • SQL Server Like Query not case sensitive
  • WordPress MysqlError: Unknown storage engine 'InnoDB' [duplicate]
  • How to enable Drag a Marker (Android Map Api v2) after a single Touch?
  • Aggregating two data frame columns without any existing pattern logic
  • When querying against a view, a filtering clause in the view's definition is being ignored
  • How to make a UserDetailsManager available as a bean
  • How do I fix “The program issued a command but the command length is incorrect.” error when calling
  • How to find MongoDB field name at arbitrary depth
  • Iterating over a container bidirectionally
  • Insert Statement
  • Error Processing Request: Mage registry key “_singleton/inchoo_notes/feed_updates” already exists
  • Symfony2.1 form date field: Argument 1 passed to … must be an instance of DateTime
  • Escaping single quotes in JDBC with MySql
  • javascript variables, What does var x = a = {} do?
  • Importing Excel files with a large number of columns header into mysql with c#
  • R sqldf renaming a field in a select statement
  • .NET video play library which allows to change the playback rate?
  • How to 'create temp table as select' in Slick?
  • KnockoutObservableArray with typed elements in TypeScript
  • MySQL Order by column = x, column asc?
  • Display Images one by one with next and previous functionality
  • Weird JavaScript statement, what does it mean?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?