Database question

[morrisproject]

I current have a column in my database called ConveyorNumber, which could be something like CMD1234, CMD1256, CMD1136 etc.
I also have a column called Asset_type which relates to the conveyor number. So depending on the conveyor number the asset type will change, its a description of the conveyor type.

Is there anyway i can use some sort of file or something which will hold all of the conveyor number and asset types, so when a conveyor number is displayed the asset type will display as well.

I have already tried entering these into my database first, which works, so it displays the asset type but i have other columns as well which need to be enetered via my form, so when these are entered via my form it adds a new row to my table, so then i end up with one row with just conveyor number and asset type, and another row with conveyor number, asset type and other fields entered.

Please can someone tell me the best way to do this?

[smantscheff]

Design your database first, then the interface.
If the asset type depends on the conveyor number and there is a limited number of asset types, you have a 1:n relation from asset types to conveyors, which in proper database design is expressed as a table Assets (id, asset_type) and a table Conveyors with a foreign key into the Assets table (id, asset_id, ...).
In your data entry interface you offer all asset types as options, for example in a drop-down box or with radio buttons, and you enter in the Conveyor table the associated asset_id together with the other data for the new row.

[morrisproject]

Im not sure if i have done this correct....

but i have set up a table called assets, which has Asset_ID and Asset Type, the Asset_ID being an index.
Table called conveyors which has Asset_ID and Conveyor Number, the Asset_ID and conveyor number being indexes. The Asset_ID in this table links to asset ID in the assets table and the conveyor number links to conveyor number in conveyors table.
Table called conveyor_number which has conveyornumber, asset type, motor temperature. motor frame and date. Asset type and conveyornumber are both indexes.
conveyor number links to conveyor number in conveyors table.

Does this sound correct? Also what does the 'on delete' 'on update' drop down lists mean?

[smantscheff]

Use the mysql command line client as your mysql interface, otherwise you will not learn mysql. Or use HeidiSQL which shows you which SQL commands the interface program sends to the database.
"On delete" and "on update" are explained in the manual: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
Leave them at their default values.
The table setup sounds o.k., but if it is correct can only be judged by an explicit view on the output of the commands

SHOW CREATE TABLE conveyors;
SHOW CREATE TABLE assets;

[morrisproject]

Im not quite sure how to use the mysql command line client asm im new to this, or how to get to it?

I tested my form and i get errors, on the followoing line now....undefined index:
GetSQLValueString($_POST, "text"),
GetSQLValueString($_POST, "int"),
GetSQLValueString($_POST, "date"),
Column 'conveyornumber' cannot be null

even tho these fields are not blank. i get this error when saving the records, i had this working fine that other day using this code, so must be to do with tables.

Also what do you mean when you write:
1.SHOW CREATE TABLE conveyors;2.SHOW CREATE TABLE assets;

how do i do this?

[smantscheff]

Learn how to use the command line client: http://dev.mysql.com/doc/refman/5.5/en/mysql.html
Enter the "SHOW CREATE TABLE ..." statements in the command client and come back with the results.