Help needed (mysql)

osullima

Hi Chaps,

I am in need of some help. My programming skills never really took off after GWbasic!! Anyway, I am looking at creating the following on a website. The website supports php and mysql databases. I need to be able to list 100's of parts numbers in a database with the following information. Part description, price in US, price in EURO and Price in Sterling.

I then want to be able to go into the website, type a prat number in to a box and have the details of that part number come back to me i.e. Part description, price in US, price in EURO and Price in Sterling.

Can somebody help me out on this as to what I need to do (step by step)? Your help would be very much appreciated.

Best regards,
mark.

daymobrew

Your requirements sound simple enough.

You can read the MySQL docs, and a nice basic MySQL tutorial there.
Also look at the PHP MySQL function docs. Users have contributed notes to the site and they can contain useful information and example usage.

To administer your database I'm a big fan of phpMyAdmin. You can create, delete, modify and browse your tables. Much easier than using MySQL at the command line (which may not be available to you anyway). I guess some hosts have phpMyAdmin installed for you.

For database table design, it's not an area I know. I assume that the part number will be a unique number so that can be the primary key in the table. Each part number will have an associated cost. I wonder if you should just have one cost column reflecting the currency that the part is bought with, and the other two currencies would be calculated on the fly as exchange rates change. Others with db experience can advise on this.

For the query page, that should be simple enough - a text box for the part number and a 'Query' or 'Submit' button. The script (written in PHP will presumably just query the database with something like "SELECT * FROM table_name WHERE partnum='entered_partnum'" [this means select all columns [to get part num, description and cost] from the table for the specified part number].

seamus

Yeah, sounds like an easy enough order.

How simple or comprehensive you'd want it, could be up to you, but I have a tendency to build in more information/functionality than I seem to need at the beginning.

For example, you say you just want partnum, description and price, but would it make sense to include manufacturer and a category - e.g. if they were car parts, you would have an engine category, and perhaps further subcategories. This would give you more functionality then just part number searches - you have somewhere to go if you don't know the part number, for e.g. It may be of no use to you as you have a partnum reference elsewhere, but just giving you ideas.

As daymobrew says, you may be better off only storing the euro price in the database, and calculating the other two prices from exchange rates. Afaik, some sites such as xe.com do RSS feeds that allow you to give the price based on up-to-date exchange rates. If however, you wanted to specify your own exchange rate (as many sites do), then you can have a section to set these.
Or you may have set prices. In which case, storing static prices would work best.

Ph3n0m

xe.com do not provide free currency feeds at all

seamus

Well there ya go. I just assumed they did......

GP

there are ways of getting the currencies from XE. It's a bit involved and deals with stripping e-mails and then saving the data you want to a database. It's not "live" but more accurate and less time consuiming than doing it yourself every day.

I guess for the moment your best bet would be to have a currencies table and just updated it manually with the latest currency values, with phpMyAdmin or whatever admin interface you want to use.

daymobrew

Maybe use page 154 on Aertel. Probably easy enough to parse.
Or Yahoo! Currency Convertor.