php reports from mysql tables "URGENT"

[LONGWAY]

Hi, i have four tables:

books                  info               autor             publisher
----------          -------------     ---------------     --------------  
id                    id                    id                  id
bookname              type                  name               publisher name     
autor_id              year                  second name        quantity
publisher_id                                autor year
                                            country

I tried with this relations but it did not work (is this normalization ok)
relations: autor.id = books.autor_id AND publisher.id = books.publisher_id AND info.id = books.id
I need to create relations and to create several reports (lets say name of the autor and his books, name of the publisher and his books)
The question is what is the best way to do that, I tried but the queries didnt work, and id column from autor should have the same data with autor_id column from books table so should i enter the data only in one place or in bouth AND how to use queries many times for displaying data from tables

for one table is SELECT * FROM table

whilewhile ($row = mysqli_fetch_array($result)){

echo"row[]";

but what should I do to get result from lets say 2 tables and from several queries?
It must be in php and mysql
THANKS!

[hielo]

If I understand you correctly, this is what you currently have:

--------
   |Author|  1---------------+
   --------                  |
   id                        |
   name                      *
   second name            --------             --------
   year                   | Book | 1---------1 | Info |
                          --------             --------  
                             * id               id
                             | bookname         type
-----------                  | Author_id        year
|Publisher|  1---------------+ Publisher_id     author
-----------
id
publisher name
quantity
country

Now consider a book that has multiple authors!

Try this instead:

--------
   |Author|  1---------------+
   --------                  |
   id                        |
   firstName                 *
   lastName               --------              --------
                          | Info | *----------1 | Book |
                          --------              --------  
                             * Author_id         id
                             | Publisher_id      title
-----------                  | Book_id           year
|Publisher|  1---------------+                   quantity
-----------                                      type
id
name
country

[LONGWAY]

Thanks a lot,
and for the inserting data into the columns that are related and share same data (example: author.id = info.author_id shares the same data and the question is should I insert the data only in author.id and dhe data will be automaticly inserted into the column info.author_id or I have to insert the data in bouth columns twice.
question two:how can I get two or three reports
-should I do that with separate while ($row = mysqli_fetch_array($result)){ and separate submit buttons or there is better way

[hielo]

...and dhe data will be automaticly inserted into the column info.author_id

NO - No "automatic" insertion will take place. YOU have to insert it in BOTH tables explicitly. With the design I gave you (if you enforce referential integrity), you may at any time provide an entry in Author and NOT enter it Info, BUT you may NEVER enter a value in Info UNLESS it first exists in Author. Thus, you must FIRST enter the value in Author and THEN you may enter it in Info.

question two:how can I get two or three reports

Considering that you asked a very vague question, here's the best answer I can furnish:
First You get report One
Then report Two
THEN report Three :)

Seriously, you need to be very explicit. You should be able to retrieve whatever data you need from the design above in a single query, BUT if you need to populate three different sections of a web page, you just need to do so from the result set. Meaning, you don't have to forcibly execute three separate queries.

To Select ALL the data:

SELECT a.firstName,a.lastName,p.name,p.country,b.title,b.year,b.quantity,b.type,i.*
FROM (((Author a 
	INNER JOIN Info i ON a.id=i.Author_id)
	INNER JOIN Book b ON b.id=i.Book_id)
	INNER JOIN Publisher p ON p.id=i.Publisher_id)

To select info about a specific Author (based on his/her id):

SELECT a.firstName,a.lastName,p.name,p.country,b.title,b.year,b.quantity,b.type,i.*
FROM (((Author a 
	INNER JOIN Info i ON a.id=i.Author_id)
	INNER JOIN Book b ON b.id=i.Book_id)
	INNER JOIN Publisher p ON p.id=i.Publisher_id)
WHERE a.id=3

Or based on lastName:

SELECT a.firstName,a.lastName,p.name,p.country,b.title,b.year,b.quantity,b.type,i.*
FROM (((Author a 
	INNER JOIN Info i ON a.id=i.Author_id)
	INNER JOIN Book b ON b.id=i.Book_id)
	INNER JOIN Publisher p ON p.id=i.Publisher_id)
WHERE a.lastName='Smith'

[LONGWAY]

Thanks, I used the primary key option and hope that I will not get the repeating data or should I use unique key for my reports
I want to use a list field and text field so if I'll use the first query for selecting all,could I get diferent reports with one query and displaying the reports something like this

while ($row = mysqli_fetch_array($result)){
 echo [ name from author table]
        while ($row = mysqli_fetch_array($result)){
           echo [book_name]

with a single submit button or I have to use several submit buttons one for each report.

[hielo]

If you query for ALL the fields, you can use a while to iterate through all the records. Then if you need to generate another "report" later on in the page you can use mysql_data_seek($result, 0); to "reset" the $result so it points back to the initial/first row WITHOUT having to requery the db

[LONGWAY]

thanks a lot