Inventory Management Etc.

[Velandil]

Currently working for a company who uses quick books to manage all their inventory and I frankly think it sucks . We have to enter in serial numbers for everything sold and shipped etc. which isn't very friendly in quick books.

Basically, what I want to do is the following:

Have a publically viewable sort of "shopping cart" that shows things by model number and how many we have in stock, with the ability to "order" some products and have an order form sent to us

Have a back where I can enter in new product types

Have another back end where I can pick a product type from a list, enter in it's serial number, where it's being stored, if there are any problems with it, etc.

Have a page not publically viewable where we can add, remove, manage and what not current database items

And, finally, be able to save and print a PDF or the like with an order form created by us and after the products have been ordered to have them removed from stock (example, if 10 are in stock and I print off an order for 2, it changes the stock to 8, and removes the serial numbers of the 2 I put on order from the list).

I also want it to be secure (doesn't have to be military-base secure, just so people can't get in and goof around).

Is this possible? Are there any resources for similar things? Thanks, Velandil (former comp sci student, now evolved to shipping / recieving at a company that sells networking equipment)

[rdrake]

Yes, this can be done.

Most of what you want can easily be accomplished using a PHP/MySQL setup. Use PHP to modify the MySQL database which contains all product information. If you don't want to use MySQL, other databases can be used as well.

As for security, you could build a login script which only allows certain people in to modify information. Also don't forget to use session variables which will ensure nobody tries accessing one of the other pages without logging in first. This information can still be viewed by another person though. To prevent this, you'll need a SSL certificate which will send only encrypted information to the person's browser.

The only difficult thing to create for your project would be the PDF exporting feature. There are libraries available to create PDF files, but it's still not as easy as a simple command. Check out SourceForge to find a nice one.

You might even be able to find a pre-existing project which'll do what you want. Just search around for product management systems.

[Velandil]

Awesome, thanks for the help. The PDF thing really is I guess the last thing I'm worried about.

I think one of the things I need most help with is organizing the MySQL database. I don't have a whole ton of experiance with either to start, but I'm still much more comfortable with PHP than MySQL. I'm also a quick learner and I'm very set on getting something like this done. I poked around, and all the current IMS (Inventory Management Systems) seem to work okay, but they all also didn't have a good handful of the features I want. The only one that was easily customizable ran about 6 grand US for a 10 user license, which didn't interest me at all (looking to make something that makes my job easier, not cost me a fortune at the same time).

This is more specifics of what I want:

code:

1 Main Public Page  > User Viewable Page (organized list of brand {I.E. Cisco, 3web, etc.} type of hardware {I.E. Switches, Routers, Etc.}, then by model {I.E. under Switches, have a 2950T, 2950G, 2950-48, etc.}, then after clicking on that specific model, display an image of the item, a quick summery, and how many we have in stock via method listed below). It should calculate stock by number of serial numbers in the database. Several Main Backend Pages, viewable only by registered users.Guess I'll have to use SSL or something for that, will have to research it. For Products:  > One form that allows users to enter in Manufacturers (Cisco, Bay, Etc.)  > One form that allows users to enter in Items (Routers, Switches, Etc.)  > One form that allows users to enter in Model Numbers (2950T, 2950G Etc.)  > One form that allows users to enter in Company Names and info (For who we buy from)  > And one form that allows users to enter in a company name and info for customers  > One form that allows users to enter in several things after. I want this form to have drop down menu's of Manufacturer, Item, and model number. After you pick those on the form, it should have a text box to enter in the serial number, a check box stating whether or not the device is working, a text box allowing user to enter in either the problem with the device if it's not working or any extra comments if it is working (I.E. Item doesn't power on if check box is for not working, or Item has a small scratch on the top left if box is checked working), and then a drop down menu of where it is located (which would be really cool if it could default to a section when the manufacturer, item and model is chosen, and I would probably just set this manually, as once we set sections of shelves, like A1, B3, etc., they won't change). I would also like there to be an Add button, so I can either enter in one item, or if we happen to get say 20 that day, then I can just press the Add button to add another row of the serial number, working, and all the stuff above, kind of like a multiple product at a time using the same manufacturer, item and model number (example, I want to add 5 Cisco 2950T Switches, and one at a time would take forever, where as if I could just pick Cisco, Switch, and 2950T, then add 4 more rows to the original first row to enter in a total of 5 serial numbers and what not, it would save a ton of time). And that's pretty much it for raw products. From there, I would like 3 more main back end pages. The most important being for ordering.  > This is where I'd want the PDF bit. I'd essentially like it to look like this (hxxp://www.pcwaterloo.com/makequote.php), copy and paste, change xx to tt, it allows you to add products one by one, sums up price, and I'd also want it to allow us to chose tax (we don't have to charge anyone but those in ontario PST, and we don't have to charge anyone outside of Canada GST) and have it automatically calculate. After saving that order to the database, it throws it into a PDF file that already contains our letterhead, policies, date, etc. so that if we want to later we can pull up an order number and view that order made and change it, re print it, whatever. I also want the actual order form to remove items from the database. For example, if I want to ship 10 Cisco 2950T Switches, I want to be able to just enter in 10 serial numbers, have it search the database, tally it up (quality 10 of whatever switch or whatever), and then add the price etc. and list all the serial numbers after that. After saving the order form, it should remove those 10 serial numbers from the database and essentially remove 10 items from stock. The second one would be for RMA. I'd like to be able to give an RMA number to a customer and enter in the serial numbers for the RMA and the problem and company and what not, store it for record keeping etc. and have a page to view it. The third one would be for searching, quite basic, I want to type in a serial number, have it search the database and result to me what it is. I want it to show all the information, manufacturer, item, model, serial number, if there was a problem, all the fun stuff. Just want the ability to see where it came from and if there was a problem really so we can better track end user problems.

That's essentially everything I want so far. I would love to expand it but I want the basics (although that's alot of typing up there) for now.

Thanks a ton for any help here, I really aprecieate it.

[rdrake]

If you prefer an easier way to set up the database initially, I'd highly recommend phpMyAdmin. You'll probably need a few tables, one being employees and another being inventory. I would create the following columns for the employees table:

• Employee Number
  
• Last Name
  
• First Name
  
• Permission Level
  

The first few are pretty standard ones, you could even add things like DOB or whatever other information you need. The permission level is something like either disabled (0), readonly (1), readwrite(2). This way you can ensure that employees only do what they're suppost to and nothing to damage the system.

For the inventory table, you'd probably want the following columns:

• SKU
  
• Name
  
• Price
  
• Quantity
  

You can easily add/remove columns as desired. That should just about do it for the database.

You'll probably need several pages in order to achieve this and keep things organized. Something like the following should do:

• View inventory (view.php)
  
• Login page (login.php)
  
• Index (index.php)
  
• CRUD actions (create, read, update, delete) (modify.php)
  

The index page would have a link to the inventory page if the user is still logged in, or would redirect the user to the login page if not. Once logged in, a person with the permission level of 1 could view the inventory items. A person with level 2 access could both view the items and add/modify/delete the items as well.

I think that's about it for the planning. Just take what I've said and modify it to fit your needs, and you shouldn't have problems. If you have any more questions either about the design or coding, don't hesitate to ask.

[Velandil]

Blah I was typing this big long thing then power outage >.<. Apartment wiring is really messed up.

Anyhow, I accidentally submitted my previous post early and had to edit it so I don't think you had the chance to read it all.

I am currently using phpMyAdmin as yeah it does seem alot easier. I will try throwing together a database and then doing some light coding. I'll have to poke around the forums and what not to see how I can do this securely, as once I'm done testing and ready to put it on our work server, it won't be safe anymore . I'll post back here with my problems. As for tonight, gotta catch some sleep for a long day tomorrow . Thanks again.

[rdrake]

Just remember, never fully trust input from the user. It could contain harmful bits of information which will allow your site to be subject to attack. Remember to verify each bit of inputted information from the user before using it.

[Velandil]

How do ya do that?

[rdrake]

Make sure certain things aren't in the input, such as ";". That would allow the person to potentially execute another query on your database.

Be sure to create a user with a large and unique password that only has select, alter, and create privilidges. Doing so will only allow the users to do certain things, even if they make it around your security. They cannot drop tables and you will not lose important data.

Just do other simple things like making sure certain fields have only what they're suppost to in them. For example, make sure the product id field only has numbers in it and just the right amount too.

Something like the following should make sure the length is the right one. Change "10" to whatever it should be plus one.

code:

if((strlen($prod_id) < 10) and (is_numeric($prod_id))) {     echo "Product ID field is valid"; } else {     echo "Product ID field is too long and/or not numerical and therefore invalid"; }

Make sure you validate all input which you will process, even something as simple as trimming off the spaces on the ends will help make this application more secure.

[Velandil]

Alrighty. Question then, if I do that code you entered in there, what exactly will happen? Will it reload the page, keep all current fields that are filled with the data that was in them before and post that error? Or what? Not much experience with forms and the such .

[rdrake]

How about I write a quick tutorial on handling forms in PHP?

Here it is.

[Velandil]

Another quick question, drop down menu's.

As I mentioned before, I wanted to have certain sections when entering in a product to the inventory. A drop down menu would be best for this. For example, under the manufacturer company I'd want 2 options. Cisco and 3com. Now, I've never done a dropdown menu using php and mysql. I'd imagine the actual menu would be coded into the PHP, and then submit the result chosen into the mysql database, but how? .

Thanks again for all the help.

[rdrake]

It's pretty easy to do that.

Here's what you'd have for the HTML code.

code:

<form action="process.php" method="post">   <select name="provider">     <option value="Cisco">Cisco</option>     <option value="3Com">3Com</option>   </select> </form>

Now, you can access the selected value with the PHP script which the form action indicates. The following code will show you the result of that selection.

code:

<?   echo $_POST['provider']; ?>

If Cisco was selected, then the script would output "Cisco", if it were 3Com, then it would output "3Com".

[Velandil]

Heh I was actually thinking (I think about this all day >.<), is it possible to manage that drop down list with the database? Like say I wanted to add another manufacturer to that list, could I just add it to the database or is that not possible (and therefor I would have to add it manually to the code)?

Also, is it hard to make the form keep the results if say the form is submitted then the back button is pressed (or say the user hits submit, something goes wrong and he gets an error page, goes back, and everything is still there if ya know what I mean)?

I really appreciate all the help .

[rdrake]

Velandil wrote:

is it possible to manage that drop down list with the database?

It sure is. You just need some database code in order to pull them out.

code:

<?        // Variables used to store database information, change as needed        $host = "localhost";     $user = "";     $pass = "";     $dbse = "";         // Create the connection     $conn = mysql_connect($host, $user, $pass) or die("Could not connect to server!");         // Select the database     mysql_select_db($dbse, $conn) or die("Could not select database!");     // Build the query and execute it.  This will change.     $q = "SELECT * FROM dropdownlist WHERE list=\"name\"";     $r = mysql_query($q);     // Makes sure something was found to avoid errors     if($r == 0) die("<p><strong>0</strong> results found.</p>");     // Fetches the results of your query and makes a dropdown list out of them     while ($row = mysql_fetch_assoc($r)) {         echo "<option>".$row."</option>";     } ?>

Just put that between your select tags and it should fill it with the values from the database. You'll just need to change the query to match your database and also the variables so you can connect to your database.

[Velandil]

Okay so I made a table called "inventory" and in it:

code:

`inventory_company` varchar(32) collate latin1_general_ci NOT NULL default '',   `inventory_product` varchar(32) collate latin1_general_ci NOT NULL default '',   `inventory_model` varchar(32) collate latin1_general_ci NOT NULL default '',   `inventory_serial` varchar(32) collate latin1_general_ci NOT NULL default '',   `inventory_working` varchar(32) collate latin1_general_ci NOT NULL default '',   `inventory_comment` varchar(32) collate latin1_general_ci NOT NULL default '',   `inventory_supplier` varchar(32) collate latin1_general_ci NOT NULL default ''

I want company, product, model and supplier to be drop down menu's. I tried putting your code in and changed what I thought was right. For example, I did:

code:

<?   $q = "SELECT * FROM inventory WHERE list=\"name\"";     $r = mysql_query($q);     if($r == 0) die("<p><strong>0</strong> results found.</p>");     while ($row = mysql_fetch_assoc($r)) {         echo "<option>".$row."</option>";     } ?>

but I keep getting the 0 results found. I imagine I need to tell it what field it should pull the info? And if so, where does that go?