I need help(one to many relation)

justtony19

Hi
I'm new at this, and all I know it’s been self taught.
anyway I have a data base called "for sale" and there are 19 tables in it, I had been creating forms for an individual table to insert data in to them which works great, but I have a separate DB which contains 32 tables in it and that means that doing it the way I know, I will need to create 96 separate forms, 3 for each one of my tables'(“insert page, list and details page)
I know there is an easier way, rather than creating so many forms (“by the way I'm using Dreaweaver CS3")
The table names are something like "car_sale" and there are 13 field in it “name, date, price, description, etc." (car_sale_id) is the primary key, int, auto increment.
I'm using PHPmyadmin and I don’t have the slightest idea of how to create a separate table that will insert (hopefully list) the data in a separate selected table,
I figured I can accomplish this with “one to many". Now I know I have to do this in mysql but I don't know how.
Here is my DB for sale

bab_kid_sale
boatsale
book_sale
busi_sale
carsale
collect_sale
comp_sale
elec_sale
furni_sale
garden_sale
houhold_sale
jewel_sale
motorcicles
other_sale
parts_sale
rbsale
sport_sale
tool_sale
Trucksale

and this is the fields in the car sale table
(al tables have the same fieldsin this D😎

CREATE TABLE IF NOT EXISTS carsale (
car_id int(100) NOT NULL auto_increment,
date datetime NOT NULL default '0000-00-00 00:00:00',
title varchar(60) NOT NULL default '',
price varchar(10) default NULL,
location varchar(40) default NULL,
description longtext NOT NULL,
email_add longtext NOT NULL,
contact mediumtext,
image longblob,
reply_to varchar(20) NOT NULL default '',
permition varchar(20) NOT NULL default '',
username varchar(20) NOT NULL default '',
password varchar(20) NOT NULL default '',
PRIMARY KEY (car_id),
UNIQUE KEY title (title)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

Can any one please help me?
is there a very detail tutorial out there I can follow, keep in mind I'm not to experienced at this.
I'm kind of dumb.
😃

tomhath

From what you describe, you don't need a separate table for each category of product. Other than the table name, what's different in the forms for each table?

You should be able to put all products in one table and customize the insert/list/details pages to display what you want for the different item types.

Start by making a new table with the columns you have, plus one more column for "item_type". Now insert a few rows with item types of "car" and "boat". Remember, php can print out whatever HTML you want, just have it print slight differences based on the item_type.

leatherback

Hi,

Let me expand on tomhath's excellent suggestions.

I checked the table you described. it oat contains information on the user selling the item. So ideally you want to split that information out. (If you are keen to learn more about eh ifs and whys: Run a search on the forum & google for database normalisation). What you will end up with I think is 3 tables:

[USERS]
- U_id (user identifier)
- U_name (users' name)
- U_username (username)
- U-email (users' email)
- U_pass (users' password)
- U_status (e.g., banned, waiting for activation, moderator etc)

[ITEM_TYPES]
- I_id
- I_Label

[SALES]
- S_id (Items for sale identifier)
- S_I_nr (ITEM_TYPES.I_id)
- S_U_nr (USERS.U_id)
- S_title (Title of product)
- S_price (Price of product)
- S_location (Location of product)
- S_picture (Picture of product)

This way you can store a user when he/she registers. Once a user has something for sale, it selects from a drop-down of item types (Generated from the table for types) and fills out the other details. You then only insert a record in SALES.

You need to use the JOIN command in mysql fo retrieving records from multiple tables:

'select * from USERS left join SALES on U_id = S_U_nr left join ITEM_TYPES on S_I_nr = I_id where I_id = 1'

will retrieve all information from all tables for all items that have the label item_type = 1.

leatherback

Hi,

I decided to expand a it on what I wrote above, and placed a little tutorial on my coding site. hope it helps.

Cheers,

Jelle