hunna03

Ok, this is somewhat confusing and I'm not sure how to go about it.

Whenever I get a new product it is entered into the database. The database creates two new tables based on the date of the product. One table contains general information about the table, such as whether or not the table has been closed (no longer used) and the other table maintains records, such as when somebody checks the product out or in.

So right now I have about 20 tables in my database.

I want to create a master inventory list. Basically I want this list to echo all the tables that are not 'closed' and then I want it to list out all of the records that are in each table. This is going to be many pages long but I need to be able to do this.

I would imagine a where statement would be involved but I'm not exactly sure how to do this. I'm confused. :-|

__________________
~CJA~
72.29.87.117
"Constantly lost in the world of PHP" is my personal understatement.

fury

I don't know a -whole- lot about MySQL, but how are you marking the tables as closed?

Is there a record in the table with the status of open or closed? If so,

Or something like that. If you do a Google search, you can find quite a few examples and modify it to fit your needs.

__________________
fury™ - not helping the situation since 1987
robmonroe.net | | bullsux.com

hunna03

Yea, I know what you are saying. But that's just pulling information from one table.

I would need something more like this:

One table in the database is a log of all the tables. It contains the table name and whether or not it is closed. Then I need it to open the tables that are open, extract all the data and echo it, and then continue to do that for each table that is open based off of product type.

:-| Maybe I need to post images to illustrate my point?

__________________
~CJA~
72.29.87.117
"Constantly lost in the world of PHP" is my personal understatement.

fury

No, I get it now. My question is, why do you need a separate table for each product? Can't they all go into one table and just have a field to say whether it's closed or not? Even if you have different fields in each one, some could just be blank if they don't apply.

Anyways,

That would give you the number of products, and their names. Do you need to display ALL the data from the individual tables too?

__________________
fury™ - not helping the situation since 1987
robmonroe.net | | bullsux.com

hunna03

Because each table created keeps track of inventory. The masterlist just keeps track of the product name, when it was added, and whether or not it is still active 'closed or open'. The other table keeps track of records.

For instance, one table might be pencils.

Someone comes and checks out 10 pencils today.
Tomorrow they return 4.
The next day someone checks out 1.
Etc. etc. etc.

Yea, I need to see all the data in the individual tables too. It's going to be one long masterlist that somebody can print out.

__________________
~CJA~
72.29.87.117
"Constantly lost in the world of PHP" is my personal understatement.

fury

Sorry, I really don't know enough about mySQL. Have you tried posting in a forum that specializes in this stuff?

I'm sure there's someone else around that could help..

Wish I could do more!

__________________
fury™ - not helping the situation since 1987
robmonroe.net | | bullsux.com

hunna03

Yea, I posted on phpfreaks.com too but surprisingly nobody has answered. :-(

__________________
~CJA~
72.29.87.117
"Constantly lost in the world of PHP" is my personal understatement.

H

It's hard to provide an answer due to the complexity. I've read over the problem 3 times and I'm not even sure I'm understanding it -- but that might be that the attempt to solve it was done differently than I'd do it.

As far as I can tell, you have an inventory system and you're trying to keep track of what's currently checked out and what's "in stock"... but also have the ability to turn items on or off.

What I would do is have a table that lists each item on your inventory list. Something like

id:uint
name:String
active:Boolean

Then I'd have a table with each actual item. So if you had 10 pencils in the inventory, you'd have 10 entries. 4 LCD monitors would be 4 entries. You get the picture... It could look like this:

id:uint
inventory_id:uint
available:Boolean

Now you can do queries that selects only active items, with the ability to return counts of available or unavailable records. I'm sure there's better ways to do it, but it's pretty flexible. It'll require a nice join in some cases, but those are always a good practice to write.

patrickb

fury and H are right that you do not need separate tables for each product. Tables should only be used to group similar data structures together. The way I understand it now, you have one table that is Masterlist or something like that. It contains the date it was added, and maybe general information about the item? Then you create another table for the item itself, so pens have a table, pencils, paperclips, etc? If that is the case, you would have to do individual queries on each table as you sort of outlined above using a general query, then iterating through a loop and building a query to do each individual table and get the records you need. This is a bit messy and very inefficient. Any decent database software (such as MySql) is going to be designed to pull data out of the database very quickly and efficiently, much more so than any script could. That is their sole purpose, to store large amounts of data and retrieve select information from that data quickly. It takes more overhead to build the query, open the database, and submit the query than it does for the actual query itself usually. So, with that in mind, we would want to be able to do one query that returns all of the information we need versus doing 20 individual queries for that same information. The other thing about that is that as you add more products, you would increase the number of queries needed to get the same amount of data linearly.

Instead take a look at your database design itself first, sort of like what H pointed out. You should have a masterlist as you do now, then a table to store the items themselves in (with all their info) and maybe even a third table to act as a log entry for each checkin/checkout that occurs. This may sound a bit complicated at first, but once you set it up and get it working, you will see how much easier it is to maintain and grow in size than the setup you have now. You will be able to get all the data you need from the three tables with one single query that will be fast and efficient.

H's idea of giving each individual item its own entry into the inventory table is a good idea, but may not be needed for your case. That is something you should think about. I doubt that you have serial numbers on the pencils, so tracking the individual pencils currently onhand wouldn't be necessary, but will you have items in the future that you need to track individually? If so, H's idea would be the best way to go right now since that functionality would be in place already for when you need it. However, if you know that you will never need to track individual items specifically, you could just make one record in the inventory table for pencils and keep track of how many of them you have total, and how many are available inside of the table. But say for instance you did track the individual pencils involved, you may wish to keep track of how much use they had and how much of the pencil is left, then having a separate entry for each one would be a good idea. Just some food for thought.

Now, the table layout I would use from what I am assuming right now would be something like this.

Table MasterList:
id: uint
name: String
description: String (This would be a very general description if you needed one here, very handy for categorizing and grouping 'like' items)
active: Boolean

Table InventoryList:
id: uint (this is going to be the same as the correlating record in MasterList. That is critical for when you do your join queries later)
description_secondary: String (in case you need a more specific description on a per item basis)
quantity_total: uint (total number of these items you have accountability for)
quantity_available: uint (This would be how many you have left and available for checkout)

Table CheckOutLog:
id: uint (again, this should match the same id for the actual item in MasterList and InventoryList both)
date_checkout: Timestamp
date_return: Timestamp (the date the item was returned, if you need to track this field)
checkout_person: String (optionally you could make this an uint and make another table to track the information on the person themselves if you needed that ability. This uint would need to make the number of the persons record in that other table then)

Now, that would be a basic and general layout to get started with. To get all the information you are looking for, we build a query similar to this:

SELECT * FROM MasterList m, InventoryList i, CheckOutLog c WHERE m.id = i.id AND m.id = c.id AND m.active != 'FALSE'

Now, its been a few years since I did any coding, but that above query should work like this:

For each entry in MasterList, it will check whether it is active or not. If it is active, an individual record for each CheckOutLog entry will be pulled using the id field to match up the tables. The ID field is the key between all three tables to keep things in sync. Now, the return result for each entry would look something like:

id: 01
name: Pencil
description: Writing pencil
active: 1
description_secondary: Wood 2H pencil for writing
quantity_total: 10
quantity_available: 6
date_checkout: 2008-07-01 00:00:00
date_return: 0000-00-00 00:00:00 (or equivalent of NULL meaning it hasn't been marked as being returned for this checkout entry)
checkout_person: Bob

I am sure there are plenty of flaws in the above system, and that isn't the most efficient way to deal with it, but it should get you thinking conceptually on the design and execution of your database/script relations. The beauty of this basic layout is that you can use MYSQL queries to pull the data in any format, sorting, order or any other combinations of ways you need it. Keep in mind that mysql is the data management workhorse and your scripts should focus mainly on the display and entry of the data. Let mysql do the sorting and updates for you. Each record in the above example will have a lot of duplicate information because of the design, but with practice and research, you can learn to use JOIN statements much more effectively and prune that data further using the tools that MYSQL provides. Example, if you want to see what Susan has checked out and never returned, a query like:

SELECT * FROM MasterList m, InventoryList i, CheckOutLog c, WHERE c.checkout_person = 'Susan' AND m.id = c.id AND m.id = i.id AND m.active != 'FALSE' AND c.date_return = 'NULL'

That would provide records of everything Susan has checked out, but never returned! Again, I probably missed something in the actual syntax of the query, but you should get the idea.

One other thing to keep in mind, make sure your data fields in each table have unique names (except the ID field). The WHERE statement is a type of JOIN statement and any fields between the tables that have duplicate names will be overwritten with data from the other tables in the returned results. There are ways to control the overwriting priority using different types of joins, but unless that field in the table is identical to the same field in the other tables, give it a unique name. It isn't a bad idea to prepend the field names with an initial or alias for the table they are in, IE: MasterList name field would be m_name.

And I am sorry for the long drawn out post, just trying to get ya thinking a little differently about how you are looking at your project. Take a bit and look at your current design versus where you think it might be one year from now. There is nothing worse than having a live database with half a million entries and realizing that the current design won't support easy expansion or feature upgrades. That means downtime and reorganizing tables, converting data, potential data loss etc.... Been there and done that before and it ain't fun.

__________________
Patrick

Warnings: The program(s) might crash unexpectedly or behave otherwise strangely. (But of course, so do many commercial programs on Windows.) --www.gimp.org