CODEWITHSUNDEEP
phpsql
pjmil
pjmil

Reputation: 2097

SQL query retrieve fields from current date

This is my database SQL code;

CREATE TABLE ProductType
(
    ptID int not null auto_increment,
    pType varchar(30),
    PRIMARY KEY(ptID)
)ENGINE=InnoDB;

CREATE TABLE Service
(
    sID int not null auto_increment,
    description varchar(30) not null,
    revenue int,
    stID int not null,
    PRIMARY KEY(sID),
    FOREIGN KEY(stID) references ServiceType(stID)
)ENGINE=InnoDB;

CREATE TABLE Product
(
    pID int not null auto_increment,
    model varchar(30) not null,
    cogs int,
    ptID int not null,
    PRIMARY KEY(pID),
    FOREIGN KEY(ptID) references ProductType(ptID)
)ENGINE=InnoDB;

CREATE TABLE Sale
(
    saleID int not null auto_increment,
    assetID int not null,
    eID int not null,
    sID int not null,
    pID int,  
    saDate date not null,
    PRIMARY KEY(saleID),
    FOREIGN KEY(eID) references Employee(eID),
    FOREIGN KEY(sID) references Service(sID),
    FOREIGN KEY(pID) references Product(pID)
)ENGINE=InnoDB;

CREATE TABLE Employee
(
    eID int not null auto_increment,
    firstName varchar(30) not null,
    lastName varchar(30) not null,
    phone varchar(30),
    email varchar(30),
    PRIMARY KEY(eID)
)

This is my attempt but I don't know how to access the 'revenue' field from 'Service' when I am querying the 'Sale' table.

<?php
// Make a MySQL Connection
mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("pnl") or die(mysql_error());

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM Sale WHERE saDate = CURDATE()")
or die(mysql_error());  

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 

echo "revenue: ".$row['revenue'];

?>

Can anyone give me an example of how I would write a php script to retrieve the revenue of all sales for the current date?

Also this was my first attempt at SQL and relational database models, so if you notice any critical mistakes I've made or things I can improve please let me know!

Upvotes: 0

Views: 184

Answers (2)

Sk MiRaj
Sk MiRaj

Reputation: 59

If sID on sales and Service both are same and unique to every sales then you can use query like this:

$result = mysql_query(SELECT Sale.saleID, Sale.assetID, Sale.eID, Sale.sID, Sale.pID, Sale.saDate, Service.revenue "."FROM Sale, Service "."WHERE Sale.sID = Service.sID AND Sale.saDate = CURDATE()) or die(mysql_error());

Or if Service.sID = Sale.saleID then change acordingly.

This way you can control which column exactly you would like to retrive from each table.

Upvotes: 0

AlSher
AlSher

Reputation: 137

Try this way. Name the SUM field and access it by given name:

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT SUM(Service.revenue) sum FROM Service JOIN Sale ON Sale.sID = Service.sID WHERE Sale.saDate = $some_date") or die(mysql_error());  
// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 
echo "revenue: ".$row['sum'];

Upvotes: 1

Related Questions