Page 1 of 1

Extracting and displaying data from two tables

Posted: Wed Apr 21, 2004 12:22 pm
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

Posted: Wed Apr 21, 2004 12:30 pm
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!

Posted: Wed Apr 21, 2004 12:37 pm
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...

Posted: Wed Apr 21, 2004 12:47 pm
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

Posted: Wed Apr 21, 2004 1:06 pm
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

Posted: Wed Apr 21, 2004 3:28 pm
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...

Posted: Thu Apr 22, 2004 3:55 am
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....

Posted: Thu Apr 22, 2004 6:08 am
by magicrobotmonkey
Yea i think either way would be fine - i was just nitpicking!