MySQL + PHP + Aspirin

[IDemo]

I haven't set foot in the coding waters for years. My last experience was in VB6 and Excel VBA, and nothing a professional would point to on a resume. A couple of us have been tasked with creating an inventory database for lab supplies, with a strong preference by the boss toward MySQL. He actually used the phrase "you can learn it in five minutes". That, plus "anything but Access".

Both of us are more familiar with a soldering iron than a schema, lol.

The total volume of the database would be quite small, probably close to what a mom and pop bookstore might need. I'm quite familiar with SQL syntax, but creating a MySQL database and implementing it on the LAN via PHP is stretching my capabilities.

I have installed MySQL and have begun playing with table design in MySQL Workbench, which looks like a pretty friendly tool. The importation of data from tables in our present MS Access would follow. There appears to be more than one way of doing that.

The where to store the db on the LAN, using what kind of connections, security, etc., and how to present things to the casual user via PHP are more likely to be a greater challenge. Quite a few freeware and low cost tools exist for the PHP-webpage creation, so that may help. But I'm not even a bit familiar with the networking and administration side of this, and no help is available from our IT department (this is at a very large hi-tech company, but oh well).

Is there an easier set of tools for doing this (SQLite, maybe?), and an easy to follow roadmap for setting things up on an intranet?

[ChromeFoundry]

1. A few options...

SQLite is a file manager like Access. They both have a high potential for corruption especially in a multi-user configuration since the updates are not purely transactional. Only one connection may write to the database at a time. Direct from the Sqlite.org site: "A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem."

MySQL is a much better option, but I understand your hesitations since most of the documentation expects that you already have familiarity with an RDBMS. I'm not sure if you've looked into MS SQL Server.. It has, by far the most "Google-support" available of all the mainstream RDB options. And, SQL Server does have a free version, MS SQL Server Express Edition which only has a limitation of 10 GB max data size (transaction logs are not counted in this limit). It doesn't sound like you would come very close to that limit in a long, long, time.
https://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

To manage the SQL Database, Microsoft also offers a free version of SQL Server Management Studio
SQL Server 2008 Management Studio Express - http://www.microsoft.com/en-us/download/details.aspx?id=7593

Here is a pretty good article that explains how to connect your PHP app to the SQL DB and perform your CRUD operations:
http://technet.microsoft.com/en-us/library/cc793139%28v=sql.90%29.aspx

Hope this helps.

[Jesus Malverde]

2. You need some more administration tools.

I'd recommend http://www.phpmyadmin.net/home_page/index.php as a free solution and Navicat as a paid solution and use both.

phpmyadmin will generate the php code for the web pages.

You need to extend your knowledge to LAMP or MAMP for your deployment

Linux/Mac Apache PHP MySQL

Google will be your go to for source code examples.

learn user admin/security for your db's, and make sure everything is being backed up and you know how to restore it.

It's a blast, your lucky to get paid to work on a project like this..

Have fun

[IDemo]

3. Thanks for the help, guys

I did speak with a couple of others today with quite a bit more experience than me; one a programming instructor, the other an engineer with several MySQL web apps under his belt. I also found that our present Access db can likely be published directly to SharePoint but whether the current tables, forms and queries will all play together nicely afterwards remains to be seen.

After scouring the intranet for clues about all this, it seems that the company's preference is for smaller workgroups such as ours to use a tool called "PowerPivot" for Excel for things like inventory tracking. Odd, since that requires later versions of Office than we and almost the entire company currently use.

So much to learn, or forget, or avoid learning in the first place..

[Drew Richards]

4. Check out mysql and wiki...you can have a secure web based wiki interface up in minutes

Just run mysql on back end and name tables.