Searching Database table

cody44

I have set up a few tables in PHP and feel confident in this, still a newbie to it, however, I am looking at setting up a table in PHP where it can be searched from a form. IE, say the table contained a cost for the height and width for an item, the table would have all the heights available and all the widths available and the data would be the prices. Can php work with EXCEL for example or is there an easier way to build this table in a php database.

I am at a loss on how the best way to tackle it. Can I import a database from Excel and if so How would I get the form to read the table?

Hope that makes sense !!!

Regards

Mark

Drakla

Well there's not really such a thing as a 'php database' but php lets you connect with lots of different database systems. You can connect with Excel using the [man]ODBC[/man] functions, but because of the slack format of Excel it's really more aggravation than it's worth.

If you can, export the data as a CSV file, then you can import that into a real database. Most people use MySQL, which you can download free from MySQL.com, but there's also many others like postgres, which is also free.

cody44

Thanks Drakla,

I already use MySQL and have a number of databases set up. My query would be setting a form up with inputs of say two input fields for calculating a price, say height and width of a product. How would the form query the database.

Once I have my head round it I will be okay.

Dr_John

Give the form input fields names, say height and width, and run a basic select query with the variables $height and $width in the WHERE part.

Select price
from tablename
where height=$height
and width=$width;

This is done by having a script run when submit is pressed. This automatically takes the form values and makes them into variables, adding the $ for you to the input box's name.

Oh, and it's not a calculation, it's a query. Unless of course there is a standard formula to calculate price given height and width, in which case you do NOT need a database, as a database should not hold calculated values. Instead you'd just use the formula and the variables and php to do the calculation. Is this what your Excel spreadsheet does with height and width - calculate the price?

PS don't confuse setting up databases with setting up tables in the database - you can have hundreds of tables in the one database, acting independantly or in relations, and probably only need one database.

cody44

that sounds like what I want to do. I should be able to set up the form query but what im confused with is how to set up a table in MySQL and to be able to cross reference height and width values.

what I want would look like a 'train timetable' showing values of the times of each train, in this case it would be price values of a product of different height and values.

So if I wanted a product h1200mm x w 800mm I would cross reference it and find the price on paper.

How would I set up a Mysql table to have the above available?

Would I need two tables, one being 'height' and one being 'width' ?

How do I display different values in a database ?

Mark

Dr_John

You create a MySQL table in the normal way, with three attributes - height, width and price, and insert the values as required. The Select query given will return the value of price which matches your input height and width, assuming these values already exist in the table of course.

But if you use a formula to calculate the price
eg height x width x 1.25 = price in pounds, you don't need a database at all, just do the calculation in a script.

Dare I suggest reading a book on databases in general, and then one on using php with mysql, to get a better idea of what is involved. You'll also have to learn some sql commands as well.

Learning databases is not a five minute, or five day or five month job, unfortunately. It takes time.

A simple introductory text such as Relational Databases by Colin Ritchie would help. I also used PHP Fast & Easy by Julie Meloni as a basic intro in simple linking of php to mysql databases.

cody44

Thanks Dr John,

That has given me some thought. Have built a few databases for searches etc and have done one for products. That was okay and straight forward, you enter id as primary with auto increment and then description, price etc etc.

This table will be built differently............have bought numerous books on PHP and MySQL and as you say it takes time.

I am thinking that I may be able to apply a formula to calculate the inputs !! But for now it is a question of locking myself in my room for a few days until its sorted.

regards

Mark