create database outdoor;
grant all on outdoor.* to "outd"@"localhost" identified by "out321";

use outdoor;

CREATE TABLE tbl_user (
  uid int(11) NOT NULL auto_increment,
  username varchar(30) default NULL,
  pass varchar(120) default NULL,
  userfullname varchar(60) default NULL,
  utype tinyint(4) default '2',
  dt_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  active tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (uid)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


INSERT INTO tbl_user VALUES (1, 'jj', '*7D47B55DCB9BC4DCB439CA5EDB8F0ED5D952AA11', 'Jayant Jose', 1, '2011-12-16 10:34:34', 1);
INSERT INTO tbl_user VALUES (2, 'santhosh', '*C60378F04E47CF13AD4566BEA56FF94DFA677F87', 'Santhosh Kumar', 2, '2011-12-16 11:24:59', 1);

    
create table tbl_outdoor(
 id  int auto_increment Primary Key,
 loc_id int,
 description  varchar(250),
 lat  float(10,6),
 lng  float(10,6),
 img_name  varchar(50),
 catg_id int,
 brand_id int,
 ad_date  date,
 date_of_update  timestamp,
 ad_type  int,
 emirate  int,
 usr_updt int,
 index(loc_id),
 index(img_name),
 index(catg_id),
 index(brand_id),
 index(ad_date),
 index(ad_type),
 index(emirate),
 index(date_of_update),
 index(usr_updt)
 );

create table tbl_location(
 loc_id int auto_increment Primary Key,
 loc_name varchar(50),
 lat  float(10,6),
 lng  float(10,6),
 index(loc_name),
 index(lat),
 index(lng)
);

create table tbl_category(
 catg_id int auto_increment Primary Key,
 catg_name varchar(50),
 index(catg_name)
);

create table tbl_brand(
 brand_id int auto_increment Primary Key,
 brand_name varchar(50),
 catg_id int,
 index(brand_name),
 index(catg_id)
);

create table tbl_adtype(
 ad_type int auto_increment Primary Key,
 ad_desc varchar(50),
 index(ad_desc)
);

create table tbl_emirates(
 emirate_id int,
 emirate_name varchar(50),
 index(emirate)
);



    
select tbl_outdoor.id, 
	tbl_outdoor.loc_id,tbl_location.loc_name,
	tbl_outdoor.description, tbl_outdoor.lat, tbl_outdoor.lng, tbl_outdoor.img_name, 
	tbl_outdoor.catg_id, tbl_category.catg_name,
	tbl_outdoor.brand_id, tbl_brand.brand_name,
	tbl_outdoor.ad_type, tbl_adtype.ad_desc,
	tbl_outdoor.ad_date, 
	tbl_outdoor.emirate,tbl_emirates.emirate_name
from tbl_outdoor
left join tbl_location on tbl_outdoor.loc_id = tbl_location.loc_id
left join tbl_category on tbl_outdoor.catg_id = tbl_category.catg_id
left join tbl_brand on (tbl_outdoor.catg_id = tbl_brand.catg_id and tbl_outdoor.brand_id = tbl_brand.brand_id)
left join tbl_adtype on tbl_outdoor.ad_type = tbl_adtype.ad_type
left join tbl_emirates on tbl_outdoor.emirate = tbl_emirates.emirate_id