Extracting and displaying data from two tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gerrymac
Forum Newbie
Posts: 15
Joined: Sat Mar 13, 2004 6:19 am

Extracting and displaying data from two tables

Post by gerrymac »

Hi

I have a formn which insets customers data and inserts fifferent part of the input into two different tables. name and address etc go into customer and th4e custorder table contains the data about the actual order. i wish ti have an option that allows staff members to login a nd view customers details. They will enter the name of the customer whose record they wish to view on a form and and when they click submit they are brought to a page where all the details are listed. I know how to get data out of 1 table and display it but how do you do it for two tables. My tables look something like this:


CUSTOMER
custid
name
address1
address2
email



CUSTORDER
custid
startdate
enddate
housename


cust id links the two tables together and it is inserted into the custorder table using the mysql_insert() function. Could someone please help with this problem?


Thx
Gerry



phone
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

I would use two separate selects to do this, as you will probably return only one customer record and possibly multiple orders, right? If you do it in one select then you are going to return all the customer information over and over again for each order which would be a waste!
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Perhaps;

Code: Select all

select distinct
    customer.*
    custorder.*
from 
    customer
    inner join custorder on customer.custid = custorder.custid
where
    customer.custid = 1
But as posted earlier, depending on the size of the table, amount of customers etc... 2 different select clauses might be better. But MySQL can handle quite abit of data before slowing down...
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Its not mySQL, I'd worry about! The result array would be nearly twice as large as it needs to be, with the same customer data appended to each row unecesarily.. after a while that's a good bit of memory... especially on a busy server
gerrymac
Forum Newbie
Posts: 15
Joined: Sat Mar 13, 2004 6:19 am

Post by gerrymac »

I wouldnt worry bout a busy server. This project is for my final year thesis. Got a booking system and a search algoritm working so this is only really tidying up making it look professional so im not too concerned bout a busy server...

thanks for the advice i may be back....

Gerry
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

magicrobotmonkey wrote:Its not mySQL, I'd worry about! The result array would be nearly twice as large as it needs to be, with the same customer data appended to each row unecesarily.. after a while that's a good bit of memory... especially on a busy server
True.

But you need to take the aspect of that you actually are using important recources to actually open up a second thread/connection, just to send the second mysq_query() from the webserver to the database & back again.

Thus creating 2 times the traffic. Yes, the pure amount of data being sendt/recieved would be much less as you can limit the queries better. But again, the resources are put on the trafic instead. And that might give you the opposite effect of what you just stated, as a mysql_query() might be using more resources than the result from the query itself...

And even so, the benchmarks I've seen makes a difference. But when the difference is 1-2 seconds on 100k rows of data, I wouldn't care less, but look for other bottlenecks.
But that might be my personal opinion only...
gerrymac
Forum Newbie
Posts: 15
Joined: Sat Mar 13, 2004 6:19 am

Post by gerrymac »

Is there not a standard way of doing this this? i mean surely its a common enough request to be able to display data to customers or employees on one page which comes from two tables such as customer and order. I did it before in SQL and access and cant remember having any real problems....
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Yea i think either way would be fine - i was just nitpicking!
Post Reply