I have two tables; 'client' and 'sys_notes' in the same DB.
(SELECT client.*, sys_notes.* FROM client LEFT JOIN sys_notes ON sys_notes.client_id = client.id ORDER BY create_date DESC
)
I need to combine the two tables echo in the same <div>content</div>
, like this:
echo all from client = first.name + last.name (e.g. Scott)
echo all from client = role
echo all from client = whatever else
echo all from sys_notes sent by Robert Scott = note1, note2, note3, note(etc.)...
echo all from client = whatever else
echo all from client = whatever else
example output:
<div class="content">
Robert Scott
Supervisor
Robert works at the order office at the firm.
Robert´s notes:
- today: (Note from sys_notes table)
- yesterday: (Note from sys_notes table)
- 19.oct 2013: (Note from sys_notes table)
Robert´s Phone: 000 000 000
Robert´s E-mail: etc.
Other information about Robert: ............
</div>
<div class="content">
Lisa Johansson
Supervisor
Lisa works at the marketing dep.
Lisas´s notes:
- today: (Note from sys_notes table)
- 12.may 2013: (Note from sys_notes table)
Lisa´s Phone: 000 000 000
Lisa´s E-mail: etc.
Other information about Lisa: ............
</div>
When i echo results today Robert is being "echoed" as many times he has notes,
with one note in each <div class="content">
. (In example over Robert has 3 notes & 3 different Robert-div is echoed.)
Please help.
--- UPDATE ---
-- Table structure for table `sys_notes`
CREATE TABLE IF NOT EXISTS `sys_notes` (
`note_id` int(11) NOT NULL AUTO_INCREMENT,
`client_id` int(11) NOT NULL,
`note_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`owning_user` varchar(50) NOT NULL,
`note` varchar(500) NOT NULL,
PRIMARY KEY (`note_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;
-- Table structure for table `client`
CREATE TABLE IF NOT EXISTS `client` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_date` datetime NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`phone` varchar(20) NOT NULL,
`email` varchar(50) NOT NULL,
`issue` varchar(50) NOT NULL,
`other` longtext NOT NULL,
`owning_user` int(11) NOT NULL,
`status` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1081 ;