Jump to content

PHP & MySQL Help

kool_samule

By kool_samule
in PHP Coding Help

 Share

Recommended Posts

kool_samule Regular Member

kool_samule

Posted
Posted

Hi Chaps,

 

I've got something of a mixed bag, in which I need some help with a MySQL query and PHP to display the results correctly.

 

I'm basically after the following:

 

Query to produce results of; invoice_item_price of all jobs that include a Typesetting task and all of those that don't.

 

The problem I'm having is that I can't figure out how to get the correct SUMtotal of the invoice_item_price, when searching through the tbl_task_item, to see if a Typesetting task has been completed. In trying to do this, it multiplies the invoice_item_price by the amount of tasks within a job.

 

Database:

 

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbtemp
-- ------------------------------------------------------
-- Server version	5.0.83-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tbl_analysis`
--

DROP TABLE IF EXISTS `tbl_analysis`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_analysis` (
  `analysis_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `analysis_gross` int(10) default NULL,
  `analysis_net` int(10) default NULL,
  PRIMARY KEY  (`analysis_id`),
  KEY `FK_job_id` (`FK_job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_analysis`
--

LOCK TABLES `tbl_analysis` WRITE;
/*!40000 ALTER TABLE `tbl_analysis` DISABLE KEYS */;
INSERT INTO `tbl_analysis` VALUES (1,1,100,50),(2,2,500,250);
/*!40000 ALTER TABLE `tbl_analysis` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_customer`
--

DROP TABLE IF EXISTS `tbl_customer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_customer` (
  `cust_id` int(20) unsigned NOT NULL auto_increment,
  `cust_name` varchar(255) default NULL,
  PRIMARY KEY  (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_customer`
--

LOCK TABLES `tbl_customer` WRITE;
/*!40000 ALTER TABLE `tbl_customer` DISABLE KEYS */;
INSERT INTO `tbl_customer` VALUES (1,'Customer A');
/*!40000 ALTER TABLE `tbl_customer` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_invoice`
--

DROP TABLE IF EXISTS `tbl_invoice`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_invoice` (
  `invoice_id` int(20) unsigned NOT NULL auto_increment,
  `FK_cust_id` int(20) unsigned default NULL,
  `FK_proj_id` int(20) unsigned default NULL,
  `invoice_date` date default NULL,
  `invoice_total` varchar(30) default NULL,
  `invoice_status` enum('Pending','Sent') default NULL,
  PRIMARY KEY  (`invoice_id`),
  KEY `FK_cust_id` (`FK_cust_id`),
  KEY `FK_proj_id` (`FK_proj_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_invoice`
--

LOCK TABLES `tbl_invoice` WRITE;
/*!40000 ALTER TABLE `tbl_invoice` DISABLE KEYS */;
INSERT INTO `tbl_invoice` VALUES (1,1,1,'2010-01-30','500','Sent');
/*!40000 ALTER TABLE `tbl_invoice` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_invoice_item`
--

DROP TABLE IF EXISTS `tbl_invoice_item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_invoice_item` (
  `invoice_item_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `FK_invoice_id` int(20) unsigned default NULL,
  `invoice_item_price` varchar(30) default NULL,
  `invoice_status` enum('Pending','Sent') default 'Pending',
  PRIMARY KEY  (`invoice_item_id`),
  KEY `FK_job_id` (`FK_job_id`),
  KEY `FK_invoice_id` (`FK_invoice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_invoice_item`
--

LOCK TABLES `tbl_invoice_item` WRITE;
/*!40000 ALTER TABLE `tbl_invoice_item` DISABLE KEYS */;
INSERT INTO `tbl_invoice_item` VALUES (1,1,1,'250','Sent'),(2,2,1,'250','Sent');
/*!40000 ALTER TABLE `tbl_invoice_item` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_job`
--

DROP TABLE IF EXISTS `tbl_job`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_job` (
  `job_id` int(20) unsigned NOT NULL auto_increment,
  `FK_proj_id` int(20) unsigned default NULL,
  `FK_langt_id` int(20) unsigned default NULL,
  `job_title` varchar(255) default NULL,
  `job_pages` int(10) default '1',
  `job_complete` enum('Yes','No') default 'No',
  `job_complete_date` timestamp NULL default NULL,
  PRIMARY KEY  (`job_id`),
  KEY `FK_proj_id` (`FK_proj_id`),
  KEY `FK_langt_id` (`FK_langt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_job`
--

LOCK TABLES `tbl_job` WRITE;
/*!40000 ALTER TABLE `tbl_job` DISABLE KEYS */;
INSERT INTO `tbl_job` VALUES (1,1,1,'Job 1',1,'Yes','2012-30-01 17:30:00'), (2,1,3,'Job 2',2,'Yes','2012-30-01 17:30:00');
/*!40000 ALTER TABLE `tbl_job` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_language`
--

DROP TABLE IF EXISTS `tbl_language`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_language` (
  `lang_id` int(20) unsigned NOT NULL auto_increment,
  `langname` varchar(255) default NULL,
  PRIMARY KEY  (`lang_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_language`
--

LOCK TABLES `tbl_language` WRITE;
/*!40000 ALTER TABLE `tbl_language` DISABLE KEYS */;
INSERT INTO `tbl_language` VALUES (1,'EN'),(2,'DE'),(3,'FR');
/*!40000 ALTER TABLE `tbl_language` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_project`
--

DROP TABLE IF EXISTS `tbl_project`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_project` (
  `proj_id` int(20) unsigned NOT NULL auto_increment,
  `proj_title` varchar(255) default NULL,
  `FK_cust_id` int(20) unsigned default NULL,
  `FK_langsid` int(20) unsigned default NULL,
  `proj_start_date` timestamp NULL default NULL,
  `proj_end_date` timestamp NULL default NULL,
  `proj_status` enum('Open','Hold','Enquiry','Redundant','Complete') default NULL,
  PRIMARY KEY  (`proj_id`),
  KEY `FK_cust_id` (`FK_cust_id`),
  KEY `FK_langsid` (`FK_langsid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_project`
--

LOCK TABLES `tbl_project` WRITE;
/*!40000 ALTER TABLE `tbl_project` DISABLE KEYS */;
INSERT INTO `tbl_project` VALUES (1,'Project A', 1,2,'2012-01-30 09:00:00','2012-01-30 17:30:00','Complete');
/*!40000 ALTER TABLE `tbl_project` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_task`
--

DROP TABLE IF EXISTS `tbl_task`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_task` (
  `task_id` int(20) unsigned NOT NULL auto_increment,
  `task_title` varchar(50) default NULL,
  PRIMARY KEY  (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_task`
--

LOCK TABLES `tbl_task` WRITE;
/*!40000 ALTER TABLE `tbl_task` DISABLE KEYS */;
INSERT INTO `tbl_task` VALUES (1,'Preparation'),(2,'Typesetting'),(3,'Correction'),(4,'Evaluate');
/*!40000 ALTER TABLE `tbl_task` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tbl_task_item`
--

DROP TABLE IF EXISTS `tbl_task_item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_task_item` (
  `task_item_id` int(20) unsigned NOT NULL auto_increment,
  `FK_job_id` int(20) unsigned default NULL,
  `FK_task_id` int(20) unsigned default NULL,
  `status` enum('Pending','Ready','Sent','Active','Complete') default 'Pending',
  `task_complete_date` timestamp NULL default NULL,
  `task_order` int(20) unsigned default NULL,
  PRIMARY KEY  (`task_item_id`),
  KEY `FK_task_id` (`FK_task_id`),
  KEY `FK_job_id` (`FK_job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tbl_task_item`
--

LOCK TABLES `tbl_task_item` WRITE;
/*!40000 ALTER TABLE `tbl_task_item` DISABLE KEYS */;
INSERT INTO `tbl_task_item` VALUES (1,1,1,'Complete','2012-01-30 10:00:00',1),(2,1,2,'Complete','2012-01-30 11:00:00',2),(3,1,3,'Complete','2012-01-30 12:00:00',3),(4,1,4,'Complete','2012-01-30 17:30:00',4),(5,2,1,'Complete','2012-01-30 10:00:00',1),(6,2,3,'Complete','2012-01-30 12:00:00',3),(7,2,4,'Complete','2012-01-30 17:30:00',4);
/*!40000 ALTER TABLE `tbl_task_item` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-01-30 18:59:47

 

QUERY:

SELECT 			DATE_FORMAT(proj_end_date, '%m') as month_number,
  				MONTHNAME(proj_end_date) AS the_month,
			YEAR(proj_end_date) AS the_year,
			SUM(analysis_net) as analysis_net,
			SUM(analysis_gross) as analysis_gross,
			SUM(job_pages) as job_pages,
			SUM(invoice_item_price) as total, 
CASE			        WHEN task_title='Typesetting'
			AND ti1.status='Complete'
			THEN 'Typeset'
			ELSE 'Non-Typeset'
			END AS 'JobType',
			langname
FROM 			tbl_job as j
INNER JOIN 			tbl_task_item as ti1
			ON ti1.FK_job_id = j.job_id
INNER JOIN 			tbl_task as t
			ON t.task_id = ti1.FK_task_id
INNER JOIN 			tbl_invoice_item as ii
			ON ii.FK_job_id = j.job_id
INNER JOIN 			tbl_invoice as i
			ON i.invoice_id=ii.FK_invoice_id
INNER JOIN 			tbl_analysis as a
			ON a.FK_job_id = j.job_id
INNER JOIN			tbl_language as l
			ON l.lang_id = j.FK_langt_id
INNER JOIN			tbl_project as p 				 				
			ON p.proj_id = j.FK_proj_id  
WHERE 			p.FK_cust_id = 1 				
			AND proj_status='Complete' 
GROUP BY			the_year, 				
			month_number, 				
			JobType, 				
			langname 
ORDER BY			the_year DESC, 				
			month_number DESC, 				
			JobType DESC, 				
			langname ASC

 

The second problem I'm having is in presenting the results in the correct way. From my example below, you can see what I'm trying to achieve. The 'commented-out' PHP code, should 'GROUP' the JobType results, just like how the 'the_month' is working. But again, becoming a bit stuck in getting this to work too.

 

<table border="3" cellpadding="3" cellspacing="3">
  <tr>
    <td>Month</td>
    <td>Year</td>
    <td>JobType</td>
    <td>Analysis Net</td>
    <td>Analysis Gross</td>
    <td>Pages</td>
    <td>Invoice Total</td>
    <td>Language</td>
  </tr>
  <?php  
  $previousMonth = '';
  $previousType = '';
  do {	  
  if ($previousMonth != $row_rsProjType['the_month']) {
  //if ($previousType != $row_rsProjType['JobType']) {?>
    <tr>
      <td><?php echo $row_rsProjType['the_month']; ?></td>
      <td><?php echo $row_rsProjType['the_year']; ?></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
    </tr>
    <?php $previousMonth = $row_rsProjType['the_month'];} ?>
    <tr>
      <td></td>
      <td></td>
      <td><?php echo $row_rsProjType['JobType']; ?></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
    </tr>
    <?php //$previousType = $row_rsProjType['JobType'];} ?>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td><?php echo $row_rsProjType['analysis_net']; ?></td>
      <td><?php echo $row_rsProjType['analysis_gross']; ?></td>
      <td><?php echo $row_rsProjType['job_pages']; ?></td>
      <td><?php echo $row_rsProjType['total']; ?></td>
      <td><?php echo $row_rsProjType['langname']; ?></td>
    </tr>
    <?php } while ($row_rsProjType = mysql_fetch_assoc($rsProjType)); ?>
</table>

 

If anyone can help me out in getting this to display correctly, it'll be highly appreciated.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.