Member Avatar for 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!

Edited by mike_2000_17 because: Fixed formatting
  • 2 Contributors
  • 6 Replies
  • 124 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by LONGWAY

Dani AI

Generated

A clearer, normalized layout will make reporting and inserts much easier. Keep four logical tables but make their roles explicit: authors(id, first_name, last_name, ...), publishers(id, name, ...), books(id, title, publisher_id, year, type, ...) and a junction table book_authors(book_id, author_id, author_order) for the many-to-many relation between books and authors. If your existing info table is acting as the junction, rename it so its purpose is obvious. Use InnoDB and declare foreign keys to enforce integrity and avoid duplicated author/publisher records.

Do not expect MySQL to copy values automatically. Insert the canonical author/publisher row once, then insert the key(s) where they belong (book row or junction row). Typical insert flow (pseudo): insert or find publisher, insert the book with the publisher_id, then insert one book_authors row per author that belongs to that book. Use transactions and prepared statements (PDO or mysqli) so a failure in any step rolls back cleanly.

For reports, a single JOIN query can produce a flat result set ordered for grouping; or use aggregation to return one row per book with a comma list of authors. Examples (MySQL):

SELECT a.id AS author_id, CONCAT(a.first_name,' ',a.last_name) AS author_name,
       b.id AS book_id, b.title
FROM authors a
JOIN book_authors ba ON a.id=ba.author_id
JOIN books b ON ba.book_id=b.id
ORDER BY a.last_name, b.title;
SELECT p.name AS publisher, b.title,
       GROUP_CONCAT(CONCAT(a.first_name,' ',a.last_name) SEPARATOR ', ') AS authors
FROM publishers p
JOIN books b ON b.publisher_id=p.id
JOIN book_authors ba ON b.id=ba.book_id
JOIN authors a ON a.id=ba.author_id
GROUP BY b.id
ORDER BY p.name, b.title;

To render grouped lists in PHP, fetch ordered rows and emit a header when the grouping key changes (example below uses PDO and protects output with htmlspecialchars):

$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$current = null;
foreach ($rows as $r) {
  if ($r['author_id'] !== $current) {
    if ($current !== null) echo "</ul>";
    echo "<h3>" . htmlspecialchars($r['author_name']) . "</h3><ul>";
    $current = $r['author_id'];
  }
  echo "<li>" . htmlspecialchars($r['title']) . "</li>";
}
if ($current !== null) echo "</ul>";

As suggested, you can reset a result pointer, but a more robust approach today is to fetch results into an array (or run a separate query per report) so the same data can be rendered multiple ways without re-querying the DB repeatedly. Add indexes on foreign keys and use unique constraints only where the domain guarantees uniqueness (publisher name may not be unique across publishers in real data).

Recommended Answers

All 6 Replies

Member Avatar for 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
Member Avatar for LONGWAY

Thanks a lot,
and for the inserting data into the columns that are related and share same data (example: = info.author_id shares the same data and the question is should I insert the data only in 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

Member Avatar for 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'
Member Avatar for 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.

Edited by mike_2000_17 because: Fixed formatting
Member Avatar for 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

Member Avatar for LONGWAY

thanks a lot

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.