Print all from Database table in PHP

drumdogg

drumdogg

drumdogg

Associate
Joined
6 Mar 2009
Posts
495
I have a search function which will allow users to search from different fields of each table in my database. For example "Date" and "Time". I would like then for the rest of the fields relating to the search to be printed out into a php table. I can do it by hard coding the fields i want printed out but each table has a different amount of table heading or fields so is there a way of printing all. Is there a way of using the '*' to print all the table heading and data from the search??

Thanks
 
Unless your tables are of the same structure or have a relationship (corresponding id's) it won't make much sense.

I'm not sure if I'm misunderstanding your needs but look into JOIN:

Code: 
SELECT * FROM table_one
JOIN table_two ON table_one.some_id=table_two.some_id
WHERE table_one.date = '07/07/2012'
 
I haven't any relationships between my tables. I have different tables for different products which are tested every so often and recorded into the database. Some products have a different amount of test in them, some may have 6, some may have 10. So if i search for a test that was taken yesterday i would like to print all the test values on that product.
 
Hmm, it sounds like you're making this harder work than it needs to be. Surely there's one products table? Should you need to separate the products into categories for example, then you can assign the product with a category_id. Another table lists the id and the category name, not more products.

Example:
Code: 
Products table:
----------------------------------------------------------
- product_id    |   product_name   |   product_category_id -
----------------------------------------------------------
- 1                | Nike Blaze         |  1          -
- 2                | Reebok Sleek       |  2          -
----------------------------------------------------------

Category table:
-----------------------------------
- category_id    |   category_name -
-----------------------------------
- 1                | Trainers    -
- 2                | Bags        -
-----------------------------------

Taking the above example as a relationship between tables, you can expand further on this.

Products will have product_id, product_name, product_description, product_option_id, product_category_id
Categories will have category_id, category_name
Options will have option_id, option_name, option_product_id
Prices will have price_id, price, price_option_id

To access all the data across the tables you'd query it like:
Code: 
SELECT * FROM products
JOIN categories ON product_category_id = category_id
JOIN options ON option_product_id = product_id
JOIN prices ON price_option_id = option_id
WHERE product_name = "nike"
ORDER BY price ASC

Can you see the way the database related from one table to another? I'm still not quite clear how you've structured your tables so it might be best to post some screen shots to let us know what's going on and then type the output as you'd like to see it on the screen. That way we can determine whether it's a PHP, SQL or table structure issue that needs to be addressed.
 
Sir SpankalotUK said:
just use a while loop, mysql query will return the header info

loop through header, add table header.

while results exist {
while record { add column }
}
Click to expand...

While there are undoubted ways to pull data from tables and dump them straight to the screen, I think at this stage of the learning process it's important to get some fundamental principles of architecture understood because without it, learning to program will be twice the job it needs to be.

@drumdogg: I'd be inclined to learn about database normalisation before moving on to extracting the data from the database. You'll see with a solid database it's far easier to query and return what you need from it.
 
Here is a brief design of how each table looks.

Code: 
Product Table: Product 1
---------------------------------------------------------------------------
-Date | Start Time | Test1 | Test 2 | Test 3 | Test 5 | Test 6 | Pass/Fail

So i have one of those for every product and would like to print it out with basically the same layout in table form. I understand where your coming from in regards to setting up relationships etc. I sort of started into this head first without thinking it through.
 
In SQL if I want to display records from more than one table that basically record a similar thing, with the same fields, I will use a UNION command. For example:

SELECT * FROM TableA
UNION
SELECT * FROM TableB

As long as the columns are the same, it will return both sets of data in one resultset.
 
Eulogy said:
In SQL if I want to display records from more than one table that basically record a similar thing, with the same fields, I will use a UNION command. For example:

SELECT * FROM TableA
UNION
SELECT * FROM TableB

As long as the columns are the same, it will return both sets of data in one resultset.
Click to expand...

This can be done if the tables match but in all my years and of all the databases I've worked with, I've rarely used this. When I have it's down to poor normalisation of the database which I've had no control over and having to output what I'm asked for. I'll concede there are conditions where UNION is required but in this case, my instincts are telling me it's the database that needs to be better structured, not fudging the SQL to make it work :)
 
Ed said:
This can be done if the tables match but in all my years and of all the databases I've worked with, I've rarely used this. When I have it's down to poor normalisation of the database which I've had no control over and having to output what I'm asked for. I'll concede there are conditions where UNION is required but in this case, my instincts are telling me it's the database that needs to be better structured, not fudging the SQL to make it work :)
Click to expand...

Perhaps.

I am no expert in SQL but I do have a database I manage/run which is not system critical, but stores some useful info. In this I separate a couple of different environments logically by tables, so rather than having user info from both environments all lumped together I can update/query them individually.

UNION is occasionally useful to me as a result. However not often used.

For something like products then yes it's a much better idea to have a single table that contains all of the products, which you can just query based on a product ID or name field or similar to limit the results returned.
 
I guys, sorry for the late reply.

Ed, yes at the minute each product has its own table.
I will try to sort my tables in some sort of normal form. Just find it hard to think of how it would work for my database. I am trying to create an electronic method which users will be able to input test results taken on each product when they are made. At the minute it is all done by paper form and would like to eliminate this.

May be able to have a table of all the products to be tested and at table for which part of the factory they are made in.

Will see how it goes.

Thanks guys
 
Ok i have come up with this so far below. Tell me if I am on the right tracks please.

Code: 
products
--------------------------------------------------
product_id | product_name| circuit_id
-1         | ..........  | 1
-2	   | ..........  | 2
-3         | ..........  | 3

Circuit
---------------------------------------------------
circuit_id | circuit_name | 
-1         | ............           
-2         | ............
-3         | ............

Testing
-------------------------------------------------
testing_id | testing_name | product-id
-1         | ............ | 3
-2	   | ............ | 6
-3         | ............ | 1

Each product has its own id, each product has its own test and circuit is where the product is being made. Although for what i am doing I think it would be better for each product to have its own table its it test results in it.

Any advice or tips would be great:)
 
"each product has its own test and circuit is where the product is being made"

At the Products table there is no need to add the product_name there, because as you said each product has its own test and circuit. In other words circuit_name = product_name.

OR you can add it in the circuit table describing what each product is produced.
 
Each product has its own separate test but there are 8 different locations or (Circuits) where they are made.

So by doing the above how do i link testing_id to its own table?? Is it just whatever you called the table in the database you call the testing_name?? So once again i am going to have a table for each product!
 
You must log in or register to reply here.
Back
Top Bottom