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
Extracting and displaying data from two tables
Moderator: General Moderators
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
Perhaps;
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...
Code: Select all
select distinct
customer.*
custorder.*
from
customer
inner join custorder on customer.custid = custorder.custid
where
customer.custid = 1-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
True.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
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...
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA