echo multiple table rows & joining certain WHERE clause together.

[jacko_162]

Ok so i have a table which lists upto 200 different rows, some of these rows need to be somehow grouped together so here is my fields in the table (attached)

 

i list my rows like the following:

 

<?php
// Define and perform the SQL query
$results = mysql_query('SELECT * FROM `ecmt_memberlist` ORDER BY `name` ASC');
$results_array = array();
while ($row = mysql_fetch_array($results)) {
$results_array[$row['characterID']] = $row;
}
?>


<?php foreach ($results_array as $id => $record) { ?>

<td><?php echo $record['name'];?></td>

<?php } ?>

 

 

and it lists my results perfectly fine, i want it to list them dependent on a few exceptions.

 

This is how it currently lists them;

Name Loaction Category Main Toon

 

Name_1 Home Main Toon -

Name_2 Home Main Toon -

Name_3 Home Alt Toon Name_1

Name_4 Home Alt Toon Name_1

Name_5 Home Alt Toon Name_1

Name_6 Home Alt Toon Name_1

Name_7 Home Main Toon -

Name_8 Home Alt Toon Name_1

Name_9 Home Main Toon -

Name_10 Home Main Toon -

 

i want it to show the MAIN Toon Category then under each Main Toon its LINKED alt toon (if any, can be seen where the field 'mainToon' shows the same name as 'name')

 

for example;

Name Loaction Category Main Toon

 

Name_1 Home Main Toon -

Name_3 Home Alt Toon Name_1

Name_4 Home Alt Toon Name_1

Name_5 Home Alt Toon Name_1

Name_6 Home Alt Toon Name_1

Name_8 Home Alt Toon Name_1

Name_2 Home Main Toon -

Name_7 Home Main Toon -

Name_9 Home Main Toon -

Name_10 Home Main Toon -

 

 

Difficult to explain but hopefully you understand..

[jacko_162]

to make this a little easier to understand i made the following 2 pages:

 

http://www.skulldogs.com/dev/testview.php <- if i echo all the rows like so;

 

<?php
// Define and perform the SQL query
$results = mysql_query('SELECT * FROM `ecmt_memberlist` ORDER BY `name` ASC LIMIT 10');
$results_array = array();
echo "<table border='1'><tr><th>Character Name</th><th>Catagory</th><th>Linked Character</th></tr>";
while ($row = mysql_fetch_array($results)) {
// TIME TO ECHO OUR RESULTS
echo "<tr><th>";
echo $row['name'];
if ($row['toonCategory'] == 'Main Toon') {
 echo "</th><th bgcolor='#00FF00'>"; }
else {
 echo "</th><th bgcolor='#FFFF00'>"; }
echo $row['toonCategory'];
echo "</th><th>";
echo $row['mainToon'];
echo "</th>";
 }
?>

 

i need it to echo like the following;

 

http://www.skulldogs.com/dev/mockup.php

 

anyone point me in the right direction?

[Jessica]

I wrote a tutorial covering this.

http://thewebmason.com/tutorial-parent-child-lists/

 

You'll need to order them by the parent id, then use a variable in PHP to track if it's a new one yet or not. 

 

Although you may just need to fix your ordering. 

[jacko_162]

your tutorial seems nice and easy to follow but to join data from 2 tables where mine uses a single table (that gets updated via a xml file!)

 

i am confused on how i would do this, each character does have a characterID i could use? you can see the table structure in the first post (attachment) can i use the INNER JOIN on what i already have?

[Jessica]

I think you just need to order them then. You may need to change your query a bit...

 

If you do a left join to the same table you can very easily order them in the query.

 

Can you do a dump of the structure and some data?

[jacko_162]

ok here goes;

 

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
--
-- Host: ******.db.1and1.com
-- Generation Time: Feb 25, 2013 at 12:43 AM
-- Server version: 5.1.67
-- PHP Version: 5.3.3-7+squeeze14
--
-- Database: `******`
--


-- --------------------------------------------------------


--
-- Table structure for table `ecmt_memberlist`
--


CREATE TABLE `ecmt_memberlist` (
`characterID` int(11) NOT NULL,
`name` text COLLATE latin1_general_ci NOT NULL,
`startDateTime` datetime NOT NULL,
`baseID` text COLLATE latin1_general_ci NOT NULL,
`base` text COLLATE latin1_general_ci NOT NULL,
`title` text COLLATE latin1_general_ci NOT NULL,
`logonDateTime` datetime NOT NULL,
`logoffDateTime` datetime NOT NULL,
`locationID` text COLLATE latin1_general_ci NOT NULL,
`location` text COLLATE latin1_general_ci NOT NULL,
`shipTypeID` text COLLATE latin1_general_ci NOT NULL,
`shipType` text COLLATE latin1_general_ci NOT NULL,
`roles` text COLLATE latin1_general_ci NOT NULL,
`grantableRoles` text COLLATE latin1_general_ci NOT NULL,
`last_modified` datetime NOT NULL,
`role` text COLLATE latin1_general_ci NOT NULL,
`vouchedBy` text COLLATE latin1_general_ci NOT NULL,
`positionHeld` text COLLATE latin1_general_ci NOT NULL,
`remarks` text COLLATE latin1_general_ci NOT NULL,
`afkNotice` text COLLATE latin1_general_ci NOT NULL,
`toonCategory` text COLLATE latin1_general_ci NOT NULL,
`mainToon` text COLLATE latin1_general_ci NOT NULL,
`watch` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`characterID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


--
-- Dumping data for table `ecmt_memberlist`
--


INSERT INTO `ecmt_memberlist` VALUES (90158470, 'SOCK ZERO', '2012-03-16 23:41:00', '0', '', '', '2013-02-19 22:16:35', '2013-02-23 07:46:18', '60012160', 'Camal IX - Ammatar Fleet Testing Facilities', '-1', '', '0', '0', '2013-02-25 12:02:47', '', '', '', '', '', '', '', 0);
INSERT INTO `ecmt_memberlist` VALUES (2101043486, 'riazall', '2013-01-30 00:07:00', '0', '', '', '2013-02-13 16:39:48', '2013-02-13 16:42:50', '60010705', 'Rokofur VIII - Moon 11 - The Scope Development Studio', '-1', '', '0', '0', '2013-02-25 12:02:47', '', '', '', '', '', '', '', 0);
INSERT INTO `ecmt_memberlist` VALUES (2101306329, 'CMDR MayheM', '2013-02-06 02:11:00', '0', '', '', '2013-02-06 15:07:05', '2013-02-06 16:59:20', '60011656', 'Odixie VII - Moon 1 - Federal Administration Bureau Offices', '-1', '', '0', '0', '2013-02-25 12:02:47', '', '', '', '', '', '', '', 0);
INSERT INTO `ecmt_memberlist` VALUES (2101376851, 'Fire Wasp', '2013-02-10 15:03:00', '0', '', '', '2013-02-18 23:29:30', '2013-02-19 00:39:05', '60011725', 'Adacyne IV - Moon 14 - Federal Administration Bureau Offices', '-1', '', '0', '0', '2013-02-25 12:02:47', '', '', '', '', '', '', '', 0);
INSERT INTO `ecmt_memberlist` VALUES (1102323281, 'Ningsy', '2011-07-04 09:42:00', '0', '', '', '2013-02-19 07:25:36', '2013-02-24 07:42:48', '60014912', 'C-J6MT IV - Moon 1 - RA Prime', '-1', '', '432347771840757760', '0', '2013-02-25 12:02:47', '', '', '', '', '', '', '', 0);
INSERT INTO `ecmt_memberlist` VALUES (961995360, 'lord mercury', '2011-05-22 15:22:00', '0', '', 'Curtain Twitcher', '2013-02-20 15:25:27', '2013-02-24 03:05:38', '61000680', 'MOCW-2 III - The Designer''s Tomb', '-1', '', '9223372036854775807', '0', '2013-02-25 12:02:47', 'DIRECTOR', 'Bad Kharma', '', '', ' ', 'Main Toon', '', 0);
INSERT INTO `ecmt_memberlist` VALUES (1613702826, 'Valeside', '2012-10-26 19:27:00', '0', '', '', '2013-02-20 20:55:45', '2013-02-24 19:18:21', '61000255', 'EUU-4N XII - RA EUU', '-1', '', '0', '0', '2013-02-25 12:02:47', '', '', '', '', '', '', '', 0);

 

some data not filled in yet. but "mainToon" will contain the name of the main character its linked to.

Edited February 24, 2013 by jacko_162