Using UNION

[ecopetition]

Hello everybody,

 

I'm making a private messaging system which consists of modules (such as inbox, drafts, sentbox etc) stored as rows in a "modules" table (with corresponding PHP files), and custom-made user folders stored in a "folders" table.

 

Here's the structure for the "modules" table:

  `module_id` int(11) NOT NULL AUTO_INCREMENT,
  `module_type` varchar(10) NOT NULL DEFAULT '',
  `module_name` varchar(255) NOT NULL DEFAULT '',
  `module_file` varchar(255) NOT NULL DEFAULT '',
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `left_id` int(11) NOT NULL DEFAULT '0',
  `right_id` int(11) NOT NULL DEFAULT '0',
  `module_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `show_menu` tinyint(1) NOT NULL DEFAULT '0'

 

And here's the structure for the "folders" table:

  `folder_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `folder_name` varchar(255) NOT NULL,
  `folder_quota` int(11) NOT NULL,
  `folder_creation_date` int(11) NOT NULL,
  `show_menu` int(1) NOT NULL DEFAULT '1'

 

If I am currently using the following SQL command to build an array of rows in the "modules" table:

 

$sql = "SELECT *
	FROM modules
	WHERE module_type = 'pm'
	AND module_enabled = 1
	ORDER BY left_id ASC";
$result = $db->query($sql);
while($row = $db->fetch_assoc($result))
{
$system_module_list[] = $row;
}

 

Is it possible to sort of merge the "modules" table and the "folders" table into a single array using the UNION command (or alternative)? Can I convert columns in the "folders" table into columns in the "modules" table for the purpose of the array (for example make the 'folder_name' column into the 'module_name')? For any columns in the "folders" table that don't have a corresponding column in the "modules" table can they be assigned default values, again for the purposes of the array?

 

I know this might sound confusing but thanks a lot for your help,

Eco.

[Bjom]

UNION is for adding a second identical "table" to another, that means adding rows to an existing table.

 

What you most likely want to do is adding columns and you do that by using JOIN.

 

The syntax is like this:

SELECT tblA.fld1, tblB.fld2 FROM tblA JOIN tblB ON tblA.refID = tblB.ID

 

and the trick is to have a field in the one table that references (and thus links) the other table.

 

have a look here at the manual too.

 

hth

 

Bjom