MySQL question

[EQTitan]

I currently have a simple login script that I run to have technicians login to, but i'd like to add some new features.

Right now I basically have a single database named 180, with a table called users, and users has two fields username & password.

Now I'd like to add a sub field to Users, called inventory

Basically the user logs in and after doing so it will pull up their personal inventory, from there they'll be able to add or remove items from there inventory.

So, my question to you is how do I add this extra field to the data base? I'm using phpMyAdmin for my database work. Also each persons inventory will be blank the first time they login.

Thanks

 

[Nothinman]

I'm not a DBA, but wouldn't it make sense to create an inventory table and then link the contents of that able to the users table. I guess you could use the username as the key, although that's kind of ugly.

 

[EQTitan]

I created the table right after I posted that Now I'm just trying to figure out how to have it so that when each user logs in to their area it pulls up their specific inventory.

 

[tfinch2]

Two tables, users and inventory

users: username, password, inventory id
inventory: inventory id (link), item 1, item 2, item 3, etc

for first time login, inventory id will be null, so empty inventory

 

[mAdMaLuDaWg]

Originally posted by: tfinch2
Two tables, users and inventory

users: username, password, inventory id
inventory: inventory id (link), item 1, item 2, item 3, etc

for first time login, inventory id will be null, so empty inventory

Thats not the right way to go about it because the inventory id will be null for a first time user. Moreover, the inventory a person can have may be more than the number of 'item' fields defined and even if you put in 1000 item fields, you are wasting space and querying the database will be a pain.
Here is the right way to do it:

User: userid (Primary Key), username, password
UserInventoryLink: UserID, InventoryID
inventory: inventory id (PrimaryKey), ItemName

So when you want to add a user, you just have to specify a username and password. When you want to add inventory, you just have to specify the ItemName. And to link the two up, you specify the UserID of the User and the InventoryID of the Inventory in the 'UserInventoryLink' table. That way, you won't have any null values as part of a relationship which will allow you to link tables.

OP, I'd recommend going over a few Database Normalization tutorials on the web, it will help you to understand how to properly design databases.

 

[tfinch2]

Originally posted by: mAdMaLuDaWg

Originally posted by: tfinch2
Two tables, users and inventory

users: username, password, inventory id
inventory: inventory id (link), item 1, item 2, item 3, etc

for first time login, inventory id will be null, so empty inventory

Thats not the right way to go about it because the inventory id will be null for a first time user. Moreover, the inventory a person can have may be more than the number of 'item' fields defined and even if you put in 1000 item fields, you are wasting space and querying the database will be a pain.
Here is the right way to do it:

User: userid (Primary Key), username, password
UserInventoryLink: UserID, InventoryID
inventory: inventory id (PrimaryKey), ItemName

So when you want to add a user, you just have to specify a username and password. When you want to add inventory, you just have to specify the ItemName. And to link the two up, you specify the UserID of the User and the InventoryID of the Inventory in the 'UserInventoryLink' table. That way, you won't have any null values as part of a relationship which will allow you to link tables.

OP, I'd recommend going over a few Database Normalization tutorials on the web, it will help you to understand how to properly design databases.

:thumbsup:

 

[WildHorse]

Originally posted by: mAdMaLuDaWg

Originally posted by: tfinch2
Two tables, users and inventory

users: username, password, inventory id
inventory: inventory id (link), item 1, item 2, item 3, etc

for first time login, inventory id will be null, so empty inventory

Thats not the right way to go about it because the inventory id will be null for a first time user. Moreover, the inventory a person can have may be more than the number of 'item' fields defined and even if you put in 1000 item fields, you are wasting space and querying the database will be a pain.
Here is the right way to do it:

User: userid (Primary Key), username, password
UserInventoryLink: UserID, InventoryID
inventory: inventory id (PrimaryKey), ItemName

So when you want to add a user, you just have to specify a username and password. When you want to add inventory, you just have to specify the ItemName. And to link the two up, you specify the UserID of the User and the InventoryID of the Inventory in the 'UserInventoryLink' table. That way, you won't have any null values as part of a relationship which will allow you to link tables.

OP, I'd recommend going over a few Database Normalization tutorials on the web, it will help you to understand how to properly design databases.

Elegant solution!

 

[Armitage]

Originally posted by: mAdMaLuDaWg

Originally posted by: tfinch2
Two tables, users and inventory

users: username, password, inventory id
inventory: inventory id (link), item 1, item 2, item 3, etc

for first time login, inventory id will be null, so empty inventory

Thats not the right way to go about it because the inventory id will be null for a first time user. Moreover, the inventory a person can have may be more than the number of 'item' fields defined and even if you put in 1000 item fields, you are wasting space and querying the database will be a pain.
Here is the right way to do it:

User: userid (Primary Key), username, password
UserInventoryLink: UserID, InventoryID
inventory: inventory id (PrimaryKey), ItemName

So when you want to add a user, you just have to specify a username and password. When you want to add inventory, you just have to specify the ItemName. And to link the two up, you specify the UserID of the User and the InventoryID of the Inventory in the 'UserInventoryLink' table. That way, you won't have any null values as part of a relationship which will allow you to link tables.

OP, I'd recommend going over a few Database Normalization tutorials on the web, it will help you to understand how to properly design databases.

What's the point of the UserInventoryLink table? Unless you intend for an inventory item to be linked to mutiple users? I's just use the userid in the inventory table.

 

[tfinch2]

Originally posted by: Armitage

Originally posted by: mAdMaLuDaWg

Originally posted by: tfinch2
Two tables, users and inventory

users: username, password, inventory id
inventory: inventory id (link), item 1, item 2, item 3, etc

for first time login, inventory id will be null, so empty inventory

Thats not the right way to go about it because the inventory id will be null for a first time user. Moreover, the inventory a person can have may be more than the number of 'item' fields defined and even if you put in 1000 item fields, you are wasting space and querying the database will be a pain.
Here is the right way to do it:

User: userid (Primary Key), username, password
UserInventoryLink: UserID, InventoryID
inventory: inventory id (PrimaryKey), ItemName

So when you want to add a user, you just have to specify a username and password. When you want to add inventory, you just have to specify the ItemName. And to link the two up, you specify the UserID of the User and the InventoryID of the Inventory in the 'UserInventoryLink' table. That way, you won't have any null values as part of a relationship which will allow you to link tables.

OP, I'd recommend going over a few Database Normalization tutorials on the web, it will help you to understand how to properly design databases.

What's the point of the UserInventoryLink table? Unless you intend for an inventory item to be linked to mutiple users? I's just use the userid in the inventory table.

What if he has a lot of the same item?

 

[EQTitan]

Each techs inventory must remain seperate from everyone else. The admin area will be able to login and pull up each individual techs id and inventory so the warehouse manager can gather needed materials, and have them ready to go the next day.

I work as a cable technician, and I'm trying to resolve a complaint between tech's a warehouse management.

BTW, I know this has been asked many, many, many, times over but is there a good source on the internet for someone to start from basically not knowing anything about PHP. I have my own domain where I can upload files to test them but I'd like to be able to setup and code test locally, and good all in one php, mysql setups? aside from WAMP?

 

[mAdMaLuDaWg]

Just to clarify something say the equipment includes Ammeters, Voltmeters, Cables, and Phones and you only have four techs. Is each tech going to just have one piece of Equipment? Or ican the techs have the same equipment (that is can two techs have Ammeters).

If it is the first case, then here is the recommended table structure:
User: userid (Primary Key), username, password
inventory: UserID, inventory id (PrimaryKey), ItemName

If it is the second case, then its the structure I listed in my first post. Simply because when you want to add an item to the inventory and assign it to multiple techs, you won't have to add a row for each tech in the inventory table. You'd just have to add the Inventory once and assign the UserID and InventoryID to the InventoryLink table. A well-designed database gets rid of redundant data as much as possible.

I doubt there is a package on Windows that has everything installed but then again, I'm not too familiar with PHP.

 

[GeekDrew]

Originally posted by: EQTitan
Each techs inventory must remain seperate from everyone else. The admin area will be able to login and pull up each individual techs id and inventory so the warehouse manager can gather needed materials, and have them ready to go the next day.

I work as a cable technician, and I'm trying to resolve a complaint between tech's a warehouse management.

BTW, I know this has been asked many, many, many, times over but is there a good source on the internet for someone to start from basically not knowing anything about PHP. I have my own domain where I can upload files to test them but I'd like to be able to setup and code test locally, and good all in one php, mysql setups? aside from WAMP?

For a windows environment, WAMP is definitely your best choice, unless you're intimately familiar with apache, and have time to spare. Is there some reason you're avoiding WAMP?