How to display records from a different table when you query an initial table

Report · Mar 31, 2013

Hi all,

Currently I have a problem. I have three tables.

The first table is user with the attributes username (primary key), password, Full name etc.

The 2nd table is books with the attributes ISBN (primary key), Title, author, publisher etc.

The last table is reservation with the attributes reservation number (primary key), username (foreign key in reservation table as it is a primary key from user table), ISBN (foreign key in reservation table as it is a primary key from book table), number of orders, and collection date.

When I query my database by using the username that I have, I can display all the records related to the username from my reservation table.

May I ask how do I also display the Tite and Author beside the results?

Please provide detailed steps as I am quite new in this.

Thanks!

Report · Mar 31, 2013

You do that in SQL using joins. For example

SELECT user.username, title, author FROM user, books, reservation

WHERE

user.username = reservation.username AND

books.ISBN = reservation.ISBN

That's an example of a join in the WHERE clause using pre ANSI92 syntax which I prefer. The newer style uses joins in the FROM clause. Google SQL joins for more details.

Report · Mar 31, 2013

Hi bregent,

Thanks alot! I managed to show results from other tables after u point out to me it is SQL joins.

May you guide me on mysql_insert_id which you have comented on my previous 2 discussions? I searched alot on youtube and google but seem not able to understand.

Thanks!

Report · Mar 31, 2013

Hi,

May I ask how do i get a multiplied result?

For example, I have no.of orders from the reservation table and Price from the books table. How can i find the multiplication of no. of orders and price in mysql. Thanks!

Report · Mar 31, 2013

SELECT user.username, title, author, (qty * price) as total FROM user, books, reservation

WHERE

user.username = reservation.username AND

books.ISBN = reservation.ISBN)

Report · Mar 31, 2013

 $_SESSION['reservationID'] = mysql_insert_id();

Report · Mar 31, 2013

Hi Bregent,

I just realised it returns the results two times. How do i remove duplication?

Report · Mar 31, 2013

>I just realised it returns the results two times. How do i remove duplication?

It should not return more than one row unless there is duplication in the data. Can you post the results.

Report · Mar 31, 2013

Hi bregent,

For the duplication of results,

below is mysql

SELECT reservation.`Reservation No`, books.Title, books.Author, books.Publisher, reservation.`Number of Orders`, reservation.`Collection Date`

FROM users, books, reservation

WHERE reservation.Email = colname AND books.ISBN = reservation.ISBN

The results that i get is

Reservation No	Title	Author	Publisher	Number of Orders	Collection Date
3	Sherlock - The Casebook	Adams, Guy	Ebury Press	1	2013-04-13
4	Sherlock - The Casebook	Adams, Guy	Ebury Press	2	2013-04-13
7	Breaking Dawn	Meyer, Stephenie	Little Brown & Co	1	2013-04-13
11	Memory of Light	Jordan, Robert / Sanderson, Brandon	Little, Brown Book Group	9	2013-04-13
15	How Will You Measure Your Life	Christensen, Clayton M. / Allworth, James / Perez, Karen Dillon	Harperbusiness	15	2013-04-15
3	Sherlock - The Casebook	Adams, Guy	Ebury Press	1	2013-04-13
4	Sherlock - The Casebook	Adams, Guy	Ebury Press	2	2013-04-13
7	Breaking Dawn	Meyer, Stephenie	Little Brown & Co	1	2013-04-13
11	Memory of Light	Jordan, Robert / Sanderson, Brandon	Little, Brown Book Group	9	2013-04-13
15	How Will You Measure Your Life	Christensen, Clayton M. / Allworth, James / Perez, Karen Dillon	Harperbusiness	15	2013-04-15

Thanks!

Report · Apr 01, 2013

Hi bregent,

For

<? php $_SESSION['reservationID'] = mysql_insert_id() ?>

do i put it after <body> of reservation page?

And also, how do i round off the value of the total to 2 decimal places?

Thanks!

Report · Apr 01, 2013

You should put the code immediately after the SQL query has been submitted to the database.

To round a value to a specific number of decimal places, use the number_format() function. For example, if the total is in $total:

$total = number_format($total, 2);

Report · Apr 01, 2013

Hi David_Powers,

Thanks for guiding me. I have successfully rounded off values and also removed duplicate figures by inserting distinct after select.

I understand that I may ask minor questions that may bore experienced programmers. But I have no choice, as I was assigned to an IT project to develop dynamic web pages in only a few weeks. With zero experience of programming, I read, research and ask on forums to find out about local servers, php, mysql, dreamweaver to build up my web pages. Hopefully, I can do decently for my IT project. Then, I can take my time off to slowly read and understand in depth about php and mysql when I am free. I appreciate your time to answer my queries.

Thanks once again!

Report · Apr 01, 2013

It's not a case of boring experienced programmers. We were all beginners at one time, and most of us turned to online forums for help. It's because of the help we received that we try to return the favour by helping newcomers.

The problem is that it sounds as though you have been lumbered with a project that you're not yet qualified to undertake. In some respects, it will probably turn out to be a valuable learning experience. But it could also lead to costly mistakes. The sooner you get the time to read up on the basics of PHP and MySQL, the better. Good luck.

Report · Apr 01, 2013

Hi David_Powers,

My project is a self-guided experential journey with minimal guidance from my tutor. I guess my tutor expects too much from us as he wants us to learn programming from scratch through a project. He thinks that we should be exposed to different disciplines though we do not major in programming.

I really appreciate the help that I received from all the forum users. I will listen to your advice and read up on the fundamentals of PHP and MYSQL during my summer break.

Keep up your passion to help those newcomers with queries! And I believe this is the 'pay it forward' concept that will develop the next generation of experienced programmers to help newcomers in the future. Thanks!

Report · Apr 01, 2013

Ah, I didn't realize you were a student. If your project is purely an exercise, your tutor has set you a tough task, which is no bad thing. You can learn a lot from making mistakes, as long as it doesn't dampen your enthusiasm for continuing.

As I wrote elsewhere, do NOT rely on Dreamweaver server behaviors. They use deprecated code that will be removed from PHP at some future date. Also steer clear of books and online tutorials that use the old mysql_ functions. You should use MySQL Improved (functions beginning with mysqli_) or PDO instead. As a beginner, it's pointless learning to use functions that have already been marked for removal from PHP.

Take good note of bregent's advice. He's extremely knowledgeable about database structure and building SQL queries.

Report · Apr 01, 2013

>But I have no choice, as I was assigned to an IT

>project to develop dynamic web pages in only a few weeks.

Ah, you mentioned this was for a school project so I assumed it was just a homework assignment, not for a real production system. For a real system, the data model I suggested is probably not suitable and you will run into big problems down the road and will have to rework everything. I would need to know more about the workflow to suggest a real working data model.

>Thanks for guiding me. I have successfully rounded off values

>and also removed duplicate figures by inserting distinct after select.

From what I can see, you should not be getting duplicated values - so adding DISTINCT may only be putting a bandaid on other data issues.

Report · Apr 01, 2013

Hi bregent,

Ya, it is for a school project. These webpages will be limited to our classmates and friends in a smaller scale. I guess I will keep my data models as they seem to be working fine for now.

This is intended for a bookstore, or even a library. The workflow is that users can register and then log in to my webpage. Then he can search by title, author, publisher, price or category to get to the result page. He can click on a specific result to see more details. If he likes it, he can place a reservation for it so that he can collect it at a later time. The user can also check his collection date, the reserved book details anytime as these details are captured during reservation. If the user is already at the huge bookstore or a library and wants to find a specific book, he can click 'find it' to have an image displaying the the specific region with the bookshelves number to direct him to it.

I intend to have the ordering system like when users can order the book to be delivered to their address. But I guess that is even more complicated, as it needs to go through credit card payments or paypal. So I decided not to have it.

And I intend to have confirmation email sent to their real time email addresses when the users register new accounts or reserve books. But I am still figuring it out for now.

However, it is good to listen to recommendations from experienced programmers on improving the data model for real world application purposes. And I can learn much from it.

Thanks!

Report · Apr 01, 2013

>For the duplication of results,

>

>below is mysql

>

>SELECT reservation.`Reservation No`, books.Title, books.Author, books.Publisher, reservation.`Number of Orders`, reservation.`Collection Date`

>FROM users, books, reservation

>WHERE reservation.Email = colname AND books.ISBN = reservation.ISBN

OK, I just realized that you did not join all the tables, so you are getting a cartesian product. The actual SQL should be:

SELECT reservation.`Reservation No`, books.Title, books.Author, books.Publisher, reservation.`Number of Orders`, reservation.`Collection Date`

FROM users, books, reservation

WHERE

books.ISBN = reservation.ISBN AND

user.EMAIL = reservation.EMAIL AND

reservation.Email = colname

Again, you should not need to use the DISTINCT keyword for this.

And I'll mention this again: NEVER use spaces or special characters in SQL table or column names.

Use underscores, proper case or camel case.

So 'Number of Orders' should be NumberOfOrders or number_of_orders or numberOfOrders.

And be consistent. Pick a naming convention and stick with it.

Report · Apr 01, 2013

bregent wrote:
So 'Number of Orders' should be NumberOfOrders or number_of_orders or numberOfOrders.

Unless MySQL has changed recently, it's best to stick with lowercase for all table and column names because of the way it converts uppercase identifiers to lowercase on Windows. So, my recommendation would be number_of_orders.

Report · Apr 01, 2013

>because of the way it converts uppercase identifiers to lowercase on Windows

Ah, wasn't aware of that - thanks.

How to display records from a different table when you query an initial table

1 Correct answer