[RESOLVED] DB Data Display

user55

Hi,

I need some help setting this database up correctly. Any help would be appreciated. I currently have the following 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
City
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

TobyRT

I'm assuming there's some kind of relationship here between the tables (like, the orders that belong to each contact in the master table?)

Could you perhaps provide a little mroe information on what it is you're trying to do?

user55

Hi,

I am looking to do something like this:

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

How would the above query work in php? This is the sql in the PHP code that I currently have in the orders php file:

$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");

Thanks,

Sara

TobyRT

have you tested the SQL query on the database itself to make sure it gives you the correct resultset. I'm sure there's a reason for all that code (I'm something of a newb myself) but unless you're trying to build a query from variables passed, why not just put the query as you actually want it?

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

mysql_query($myQuery) or die(mysql_error());

Roger_Ramjet

At a quick glance I'm sure that what you are building will not be a valid sql query string. Echo it out before you run it to see what is actually there. (and take out all those '\n' first - no use to anyone and certainly not to the db engine)