...

View Full Version : Database Data Display



user55
07-19-2007, 02:05 AM
Hi,

I need some help setting this database up correctly. Any help would be appreciated. I currently have 2 tables with the following fields in each:

======================
Table Name: Master |
======================
ID (Primary ID) ----->
City ------------>
Section ------------>
Year ------------>
Address ------------>
City ------------>
State ------------>
Zip ------------>
Phone ------------>
Quota ------------>
*******************************
=========================
Table Name: Orders |
=========================
Order_ID (Primary ID)
ID
City
Section
Year
Address
State
Zip
Phone
Quota
<------------ Qty
<------------ Goal
<------------ Last_Order

I would like to display the fields from the table called "MASTER": ID, City, Section, Year, Address, City, State, Zip, Phone, and Quota in the Table called "ORDERS".Because that information would be coming from the "MASTER" Table.

The "Orders" table has 3 fields that will display on the "MASTER" table, which are: Qty, Goal, and Last_Order.

And everything is Set by "ID" which is the primary key in the "MASTER" table. So the fields would have to display according to what "ID" they belong to. The "ORDERS_ID" from the "ORDERS" table is the primary key since the "ID" in the "ORDERS" table will have multiple orders.

I have a master.php and orders.php file.

How would I set something like this up to work correctly? Hope all this makes sense.

__________________________

Thanks, Sara

Fumigator
07-19-2007, 03:52 PM
I was confused by your table definitions. Does the ORDERS table really contain city, section, year, address, etc? Or are you just showing the data that you want to display from the MASTER table?

Assuming the latter, you need some way of connecting the ORDERS table to the MASTER table. If any given row in the ORDERS table will belong to one and only one row in the MASTER table, then you can store the MASTER_ID in the ORDERS table to establish that connection. (You may or may not have already done this depending on the answer to my question above.) Then it's a simple join query:



SELECT
City
,Section
,Year
,Address
,City
,State
,Zip
,Phone
,Quota
,Qty
,Goal
,Last_order
FROM MASTER
JOIN ORDERS
ON MASTER.ID = ORDERS.MASTER_ID
WHERE MASTER.ID = $var

user55
07-19-2007, 05:49 PM
Hi,

Thanks for the reply. Yes, the "ORDERS" table also contains the fields city, section, year, address, etc.

Sara

Fumigator
07-19-2007, 06:13 PM
In that case you should normalize your tables and store that stuff in only one place, then store the MASTER_ID in the ORDERS table.

user55
07-19-2007, 06:46 PM
Hi,

This is the PHP code that I have in orders php file. How would this be setup to work like in your example?



$sql .= " Select\n";
$sql .= " orders.`Order_ID`,\n";
$sql .= " orders.`ID`,\n";
$sql .= " orders.`City`,\n";
$sql .= " orders.`Section`,\n";
$sql .= " orders.`Year`,\n";
$sql .= " orders.`Address`,\n";
$sql .= " orders.`State`,\n";
$sql .= " orders.`Zip`,\n";
$sql .= " orders.`Phone`,\n";
$sql .= " orders.`Quota`,\n";
$sql .= " orders.`Qty`,\n";
$sql .= " orders.`Goal`,\n";
$sql .= " orders.`Last_Order`,\n";
$sql .= " From\n";
$sql .= " orders orders\n";

if (strpos(strtoupper($sql), " WHERE ")) {
$sqltemp = $sql . " AND (1=0) ";
}else{
$sqltemp = $sql . " Where (1=0) ";
}
$result = mysql_query($sqltemp . " " . $sql_ext . " limit 0,1")
or die("Invalid query");


______________________________________________________
Would I need to delete the fields city, section, year, address, etc; that are already in the "MASTER" table?

Thanks


Sara

Fumigator
07-19-2007, 07:43 PM
Actually my thought was keep the "master" data (address, etc) in the MASTER table, and store only the "order" data (qty, etc) in the ORDERS table. Connect the two tables by storing the MASTER_ID in the ORDERS table.

user55
07-19-2007, 08:23 PM
Hi,

Thanks for getting back to me. So, would I delete the fields that are the same like this:



$sql .= " Select\n";
$sql .= " orders.`Order_ID`,\n";
$sql .= " orders.`Quota`,\n";
$sql .= " orders.`Qty`,\n";
$sql .= " orders.`Goal`,\n";
$sql .= " orders.`Last_Order`,\n";
$sql .= " From\n";
$sql .= " orders orders\n";

if (strpos(strtoupper($sql), " WHERE ")) {
$sqltemp = $sql . " AND (1=0) ";
}else{
$sqltemp = $sql . " Where (1=0) ";
}
$result = mysql_query($sqltemp . " " . $sql_ext . " limit 0,1")
or die("Invalid query");


How would I get the other fields to appear here in PHP?

Sara

Fumigator
07-19-2007, 10:12 PM
You would use a join query, like I posted in #2 of this thread.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum