Sorry about the long horizotal scroll I did it to preserve formating.
I'm currently having trouble with setting up mysql quarrys to take information from mutiple tables in MySQL. Below is my table layouts.
Parent Table
cmf_customer
| id | name | house | house_number | buisness | tax_exempt | discount | referral | ads | reminder | comment |
+----+------+-------+--------------+----------+------------+----------+----------+-----+----------+---------+
| 1 | Bob | 0 | null | 1 | 0 | 0.00% | 0 | 1 | 0 | null |
| 2 | Foo | 1 | 546 | 0 | 1 | $0.50 | 2 | 1 | 1 | null |
| 3 | Bar | 0 | null | 0 | 0 | 2.00% | 3 | 0 | 1 | null |
+----+------+-------+--------------+----------+------------+----------+----------+-----+----------+---------+
Child Tables
------------------
cmf_customer_address
| customer_id | address | unit | city | state | zip | contact_type |
+-------------+-----------------+------+------------+-------+-----------+--------------+
| 1 | 456 any street | b6 | Utopia | NY | 021564509 | 1 |
| 2 | 321 fake strret | null | springfild | NJ | 45678 | 3 |
| 3 | 420 stoner ave | null | los angels | CA | 902344568 | 2 |
+-------------+-----------------+------+------------+-------+-----------+--------------+
cmf_customer_email
| Customer_id | email | contact_type |
+-------------+------------------+--------------+
| 2 | spamcity@aol.com | 4 |
+-------------+------------------+--------------+
cmf_customer_phone
| customer_id | phone | contact_type |
+-------------+------------+--------------+
| 1 | 9999954206 | 3 |
| 1 | 9969878694 | 2 |
| 2 | 9894568241 | 1 |
| 3 | 9899456215 | 4 |
+-------------+------------+--------------+
cmf_contact_type
| id | type | address | phone | email |
+----+------------------+---------+-------+-------+
| 1 | Home | 1 | 1 | 0 |
| 2 | Work | 1 | 1 | 1 |
| 3 | Cell | 0 | 1 | 0 |
| 4 | Pager/Voice-mail | 0 | 1 | 0 |
| 5 | Personal | 0 | 1 | 1 |
+----+------------------+---------+-------+-------+
What i'm trying to do is something along these line, lets say I choose to search a number "9969878694". When i search that i also want to be able to extract items from the master and child tables such as id, name, phone, address, and email.
So lets say I searched "9969878694", I would like it to return the information like in the table formst below
|id | name | phone | address | email |
+---+------+------------+----------------+-------+
| 1 | Bob | 9969878694 | 456 any street | |
| 1 | Bob | 9999954206 | 456 any street | |
+---+------+------------+----------------+-------+
Basicly what I'm try to do is the id of a customer to retrive all the information needed from all the differnt tables. The other reason I have it in multiple tables is to be able to tereticly store infinate amounts of contact information for each customer.
Here is a dump of my table structure from MySQL if it helps any
# phpMyAdmin MySQL-Dump
# version 2.3.0
# http://phpwizard.net/phpMyAdmin/
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Oct 05, 2002 at 01:50 PM
# Server version: 4.00.03
# PHP Version: 4.2.2
# Database : `cmfcms`
# --------------------------------------------------------
#
# Table structure for table `cmf_contact_type`
#
CREATE TABLE cmf_contact_type (
id tinyint(2) NOT NULL auto_increment,
type varchar(100) NOT NULL default '',
address tinyint(1) NOT NULL default '0',
phone tinyint(1) NOT NULL default '0',
email tinyint(1) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `cmf_customer`
#
CREATE TABLE cmf_customer (
id int(10) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
house tinyint(1) NOT NULL default '0',
house_number int(5) default NULL,
buisness tinyint(1) NOT NULL default '0',
tax_exempt tinyint(1) NOT NULL default '0',
discount int(5) NOT NULL default '0',
referral tinyint(4) NOT NULL default '0',
ads tinyint(1) NOT NULL default '1',
reminder tinyint(1) NOT NULL default '1',
comment text NOT NULL,
PRIMARY KEY (id),
FULLTEXT KEY name (name)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `cmf_customer_address`
#
CREATE TABLE cmf_customer_address (
customer_id int(10) NOT NULL default '0',
address varchar(100) NOT NULL default '',
unit varchar(5) default NULL,
city varchar(50) NOT NULL default '',
state char(2) NOT NULL default '',
zip bigint(10) NOT NULL default '0',
contact_type tinyint(2) NOT NULL default '0',
PRIMARY KEY (customer_id)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `cmf_customer_email`
#
CREATE TABLE cmf_customer_email (
Customer_id int(10) NOT NULL default '0',
email tinytext NOT NULL,
contact_type tinyint(2) NOT NULL default '0',
PRIMARY KEY (Customer_id),
FULLTEXT KEY email (email)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `cmf_customer_phone`
#
CREATE TABLE cmf_customer_phone (
customer_id int(10) NOT NULL default '0',
phone bigint(10) NOT NULL default '0',
contact_type tinyint(2) NOT NULL default '0',
PRIMARY KEY (customer_id)
) TYPE=MyISAM;
