kool_samule

January 15, 2013

fantasic.

thanks for the help

January 14, 2013

Thanks for the reply, is there anything out there that could jump to a column in a line?

January 11, 2013

Hi Chaps,

I have been given an invalid XML file, which when run through 3rd party software, gives me the line & column numbers of the error.

Instead of opening the file and manually searching for the line/column numbers (which can take a whie, as the file is vast!), is there a way to parse the XML file and locate the error, using the line/column numbers? And maybe display/highlight the error in a browser?

e.g.:

1: open bad-file.xml

2: read bad-file.xml

3. go-to line 50

4. go-to column 3620

5. open in browser

6. highlight error

Cheers

S

July 20, 2012

OK my bad, thanks for the heads up.

Cheers!

July 20, 2012

Yeah, is this a bug, in issue with 5.2.10? Any idea of how to work around this?

It seems to be a problem when the DAY you are querying against happens to be the first day of the given MONTH.

I've just tried it with "October 2012 fourth Monday" and it returned: 2012-10-29, when it should be 2012-10-22..

July 20, 2012

Hi Chaps,

I have a problem trying to find out the fourth Thursday of a Month of a Year, for example November.

date_default_timezone_set('Europe/London');

echo strtotime("November 2012 fourth thursday")."</br>"; // returns 1354147200 SHOULD BE 1353542400
echo date('Y-m-d', 1354147200)."</br>"; // returns 2012-11-29
echo date('Y-m-d', 1353542400)."</br>"; // returns 2012-11-22

So the problem is with the strotime calculation, I'm using PHP Version 5.2.10 on Windows/Apache.

Anyone know how to solve this or alternatives?

July 20, 2012

Thanks guys,

Advice is noted..I'll crack on and hopefully get it sorted.

Cheers for the help.

July 19, 2012

Hi Chaps,

Apologies if this topic should be in MySQL...I wasn't sure.

I'm trying to create a MS Outlook-style Appointment application with Recurrence for a PHP calendar.

I've got all the same options that Outlook gives, e.g.:

Daily - every DAY, or every WEEKDAY

Weekly every X WEEK(S) on X DAYS

Monthly - on DAY of every X month or Nth DAY of every X MONTH(s)

Yearly - recur every X YEAR(S) on DAY of MONTH or Nth DAY of every X MONTH(s)

My question is what is the best way to store the recursive appointment information in MySQL Database (Columns, etc), so I can then use a PHP algorithm to calculate whether an appointment is within a calendar view, or if reminders need to be sent.

Any advice will be a great help.

July 4, 2012

Yeah, clicked around to trigger an error, but nothing happened..got me baffled.

July 4, 2012

Hi,

Yeah, I've already done that, should have mentioned.

FireBug > Console > Debug Info / FireBug > Script > Breakpoints / = Nothing

....?

July 4, 2012

Hi Chaps,

Scratching my head with this one, hopefully someone can tell me where I'm going wrong...my Javascript/jQuery skills are low.

Overview:

I have multiple jQuery 'connectedWith' sortable lists in a form, when groupItems are moved/connected, an updated SUM of two amounts is displayed, using the Net/Gross Calculation function.

The order of each group is added to the group array, using the groupItemOrder# functions.

Upon submitting the form, the correct group/groupItemOrder is displayed (for this demo, I'll add the MySQL scripts afterwards).

I have a problem the two functions, Net/Gross Calculation & groupItemOrder#, won't work on the same page.

They both work if the other is not 'active', so to speak.

Function: Net/Gross Calculation function:

/* 
START OF Net/Gross Calculation function
- used to calculate the Net/Gross of each group
- won't work if the groupItemOrder# on-the-fly array updater are active
*/
$(document).ready(
function () {
    $('ul.connectedSortable').sortable({
       update: function (ser) {
            $(".connectedSortable").each(function (i) {
                var total_net = 0;
			var total_gross = 0;
                $(this).find(".net").each(function () {
                    total_net += parseInt($(this).text());
                });
			$(this).find(".gross").each(function () {
                    total_gross += parseInt($(this).text());
                });
				$("div#total_net" + i).remove();
                if(total_net > 0){
				$(this).append($("<div id='total_net" + i + "'></div>").text('Total Net: ' + total_net));
			}
		        $("div#total_gross" + i).remove();
			if(total_gross > 0){
        			$(this).append($("<div id='total_gross" + i + "'></div>").text('Total Gross: ' + total_gross));
			}
            });
        },
    });
});
/* 
END of Net/Gross Calculation function
*/

Function: groupItemOrder#

/* 
START of groupItemOrder# functions
- used to update each group array on-the-fly, to obtain the correct order of each group item, on submit of form
- won't work if Net/Gross Calculation function is active
*/
var groupItemOrder1 = '';
$(function() {
  $("#sortable1").sortable({
update: function(event, ui) {
  groupItemOrder1 = $("#sortable1").sortable('toArray').toString();
}
  });
  $("#sortable1").disableSelection();
});

scriptAr1 = new Array();
scriptAr1[0] = "one";
scriptAr1[1] = "two";
scriptAr1[2] = "three";
function setValue1()
{
var arv1 = groupItemOrder1.toString();
document.form.arv1.value=arv1;
}

var groupItemOrder2 = '';
$(function() {
  $("#sortable2").sortable({
update: function(event, ui) {
  groupItemOrder2 = $("#sortable2").sortable('toArray').toString();
}
  });
  $("#sortable2").disableSelection();
});

scriptAr2 = new Array();
scriptAr2[0] = "one";
scriptAr2[1] = "two";
scriptAr2[2] = "three";
function setValue2()
{
var arv2 = groupItemOrder2.toString();
document.form.arv2.value=arv2;
}

var groupItemOrder3 = '';
$(function() {
  $("#sortable3").sortable({
update: function(event, ui) {
  groupItemOrder3 = $("#sortable3").sortable('toArray').toString();
}
  });
  $("#sortable3").disableSelection();
});

scriptAr3 = new Array();
scriptAr3[0] = "one";
scriptAr3[1] = "two";
scriptAr3[2] = "three";
function setValue3()
{
var arv3 = groupItemOrder3.toString();
document.form.arv3.value=arv3;
}
/* 
END of groupItemOrder# functions
*/

FULL HTML:

<?php 
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "sort-group")) {
$original_groups = $_POST['original_groups']; // returns 3 for example
$i = 1;
do{
	$ss = $_POST['arv'.$i];
	$tok = explode(',',$ss);
	$order = 1;
	foreach($tok as $var) {
		$id = $var;
		if(is_numeric($id)){
			echo "(GROUP: $i) Item ID: $id (Order: $order)</br>";
			$order++;
		}
	}
$i++;
}while($i-1 < $original_groups);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>jQuery UI Sortable - Connect lists</title>
<link class="jsbin" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"></link>
<script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/jquery-ui.min.js"></script>
<script>
/* 
START OF Net/Gross Calculation function
- used to calculate the Net/Gross of each group
- won't work if the groupItemOrder# on-the-fly array updater are active
*/
$(document).ready(
function () {
    $('ul.connectedSortable').sortable({
       update: function (ser) {
            $(".connectedSortable").each(function (i) {
                var total_net = 0;
			var total_gross = 0;
                $(this).find(".net").each(function () {
                    total_net += parseInt($(this).text());
                });
			$(this).find(".gross").each(function () {
                    total_gross += parseInt($(this).text());
                });
				$("div#total_net" + i).remove();
                if(total_net > 0){
				$(this).append($("<div id='total_net" + i + "'></div>").text('Total Net: ' + total_net));
			}
		        $("div#total_gross" + i).remove();
			if(total_gross > 0){
        			$(this).append($("<div id='total_gross" + i + "'></div>").text('Total Gross: ' + total_gross));
			}
            });
        },
    });
});
/* 
END of Net/Gross Calculation function
*/

/* 
START of connectWith function
- works with both functions
*/
$(function() {
$( "#sortable1, #sortable2, #sortable3" ).sortable({
connectWith: ".connectedSortable"
}).disableSelection();
});
/* 
END of connectWith function
*/

/* 
START of groupItemOrder# functions
- used to update each group array on-the-fly, to obtain the correct order of each group item, on submit of form
- won't work if Net/Gross Calculation function is active
*/
var groupItemOrder1 = '';
$(function() {
  $("#sortable1").sortable({
update: function(event, ui) {
  groupItemOrder1 = $("#sortable1").sortable('toArray').toString();
}
  });
  $("#sortable1").disableSelection();
});

scriptAr1 = new Array();
scriptAr1[0] = "one";
scriptAr1[1] = "two";
scriptAr1[2] = "three";
function setValue1()
{
var arv1 = groupItemOrder1.toString();
document.form.arv1.value=arv1;
}

var groupItemOrder2 = '';
$(function() {
  $("#sortable2").sortable({
update: function(event, ui) {
  groupItemOrder2 = $("#sortable2").sortable('toArray').toString();
}
  });
  $("#sortable2").disableSelection();
});

scriptAr2 = new Array();
scriptAr2[0] = "one";
scriptAr2[1] = "two";
scriptAr2[2] = "three";
function setValue2()
{
var arv2 = groupItemOrder2.toString();
document.form.arv2.value=arv2;
}

var groupItemOrder3 = '';
$(function() {
  $("#sortable3").sortable({
update: function(event, ui) {
  groupItemOrder3 = $("#sortable3").sortable('toArray').toString();
}
  });
  $("#sortable3").disableSelection();
});

scriptAr3 = new Array();
scriptAr3[0] = "one";
scriptAr3[1] = "two";
scriptAr3[2] = "three";
function setValue3()
{
var arv3 = groupItemOrder3.toString();
document.form.arv3.value=arv3;
}
/* 
END of groupItemOrder# functions
*/
</script>
<style>
#sortable1, #sortable2, #sortable3 {
list-style-type: none;
padding: 5px;
margin-right: 10px;
width: 300px;
margin-top: 0;
margin-bottom: 0;
margin-left: 0;
display:block;
}
#sortable1 li, #sortable2 li, #sortable3 li{
padding: 5px;
font-size: 12pt;
width: 200px;
margin: 5px;
cursor: pointer;
}
#sortable1 .net , #sortable1 .gross, #sortable2 .net , #sortable2 .gross, #sortable3 .net , #sortable3 .gross{
float: right;
}
</style>
</head>
<body>
<form action="<?php echo $editFormAction; ?>" method="post" name="form" id="form" onSubmit="setValue1(), setValue2(), setValue3()">
<input type="submit" id="button" value="Sort Mulitple Lists" />
<ul id="sortable1" class="connectedSortable">
    	<li class="ui-state-default" id="6411">
	6411
        <div class="net">100</div> - <div class="gross">250</div>
    	</li>
    </ul>
    <input name="arv1" type="hidden">
    <ul id="sortable2" class="connectedSortable">
    	<li class="ui-state-default" id="6230">
	6230
        <div class="net">100</div> - <div class="gross">250</div>
    	</li>
    </ul>
    <input name="arv2" type="hidden">
    <ul id="sortable3" class="connectedSortable">
    	<li class="ui-state-default" id="6434">
	6434
        <div class="net">100</div> - <div class="gross">250</div>
    	</li>
    </ul>
    <input name="arv3" type="hidden">
<input type="hidden" name="original_groups" value="3" />
<input type="hidden" name="MM_insert" value="sort-group" />
</form>

</body>
</html>

If anyone can help, it'll be hi-5-tastic.

Thanks in advance.

April 30, 2012

Thanks, managed to sort it using file_put_contents.

April 27, 2012

Hi Chaps,

I'm working on a PHP script to mysqldump database to file.

Using this as a base: http://www.edmondscommerce.co.uk/mysql/php-mysql-dump-script/, I've managed to strip it down to this:

set_time_limit(0);

$mysql_host='localhost';
$mysql_database='database';
$mysql_username='username';
$mysql_password='pasword';

_mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password);
$print_form = 0;

header('Content-type: text/plain');
header('Content-Disposition: attachment; filename="'.$mysql_database."_".date('Y-m-d').'.sql"');

$filename = $mysql_database."_".date('Y-m-d').'.sql';
_mysqldump($mysql_database);

function _mysqldump($mysql_database)
{
$sql="show tables;";
$result= mysql_query($sql);
if( $result)
{
	while( $row= mysql_fetch_row($result))
	{
		_mysqldump_table_structure($row[0]);
			_mysqldump_table_data($row[0]);
	}
}
else
{
	echo "/* no tables in $mysql_database */\n";
}
mysql_free_result($result);
}
function _mysqldump_table_structure($table)
{
echo "/* Table structure for table `$table` */\n";
	echo "DROP TABLE IF EXISTS `$table`;\n\n";
	$sql="show create table `$table`; ";
	$result=mysql_query($sql);
	if( $result)
	{
		if($row= mysql_fetch_assoc($result))
		{
			echo $row['Create Table'].";\n\n";
		}
	}
	mysql_free_result($result);
}
function _mysqldump_table_data($table)
{
$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
	$num_rows= mysql_num_rows($result);
	$num_fields= mysql_num_fields($result);
	if( $num_rows> 0)
	{
		echo "/* dumping data for table `$table` */\n";
		$field_type=array();
		$i=0;
		while( $i <$num_fields)
		{
			$meta= mysql_fetch_field($result, $i);
			array_push($field_type, $meta->type);
			$i++;
		}
		echo "insert into `$table` values\n";
		$index=0;
		while( $row= mysql_fetch_row($result))
		{
			echo "(";
			for( $i=0; $i <$num_fields; $i++)
			{
				if( is_null( $row[$i]))
					echo "null";
				else
				{
					switch( $field_type[$i])
					{
						case 'int':
							echo $row[$i];
							break;
						case 'string':
						case 'blob' :
						default:
							echo "'".mysql_real_escape_string($row[$i])."'";
					}
				}
				if( $i <$num_fields-1)
					echo ",";
			}
			echo ")";
			if( $index <$num_rows-1)
				echo ",";
			else
				echo ";";
			echo "\n";
			$index++;
		}
	}
}
mysql_free_result($result);
echo "\n";
}
function _mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password)
{
global $output_messages;
$link = mysql_connect($mysql_host, $mysql_username, $mysql_password);
if (!$link)
{
   array_push($output_messages, 'Could not connect: ' . mysql_error());
}
else
{
	array_push ($output_messages,"Connected with MySQL server:$mysql_username@$mysql_host successfully");
	$db_selected = mysql_select_db($mysql_database, $link);
	if (!$db_selected)
	{
		array_push ($output_messages,'Can\'t use $mysql_database : ' . mysql_error());
	}
	else
		array_push ($output_messages,"Connected with MySQL database:$mysql_database successfully");
}
}

The problem is, I want the file to save to a specific network location, rather than the output being sent to headers.

I've tried to play around with:

ob_start();....
$page = ob_get_contents();
ob_end_flush();
$fp = fopen("output.html","w");
fwrite($fp,$page);
fclose($fp);

But this outputs to the browser, then saves the file...I would like to load the page, and have the script create and save the file where I want it (without the output in the browser, or prompt to save/download file).

Note: I need something like this as I'm running PHP/MySQL on IIS, so can use shell/system/etc.

Any help will be hi-5-tastic!

April 26, 2012

Bang on, good work

Thanks

April 26, 2012

Hi Chaps,

Hopefully an easy one for you, having a bit of trouble with an array within array.

I'm parsing an XML document, and attempting to assign variables as I go along. I'm hitting some trouble with one particular child node, as it's in an array.

PHP:

print_r($parent->child);

Array:

Array ( [0] => XMLTag Object ( [tagAttrs] => Array ( ) [tagName] => child [tagData] => Something in tagData [tagChildren] => Array ( ) [tagParents] => 3 ) )

Im basically after the value of child [tagData] , so I'll be able to assign a variable:

$childData = 'Something in tagData'

If anyone can help, it'll help me no end

Cheers

February 8, 2012

Perfect, thanks a lot...would never have got there without your help.

Much appreciated...

Memory bank: updated.

February 7, 2012

OK, I'm stuck...I've tried to alter the SELECT query, with no joy...

I don't know how Divide would help here...

and I can't figure out a way to differentiate between jobs that have a Typesetting task and those that don't...without getting the above results.

Would a SubQuery do the trick?

February 3, 2012

Because Job 1 has a Typesetting Task, I dont want it included in Non-Typesetting.

The current query isnt doing it, but that's the result I'm after..

February 3, 2012

OK, getting really close now...

a) Relationship is 1:1

b)

+-----------+---------+-------------+-----------------+
| FK_job_id | task_id | task_title  | TaskItem_Status |
+-----------+---------+-------------+-----------------+
|         1 |       1 | Preparation | Complete        |
|         1 |       2 | Typesetting | Complete        |
|         1 |       3 | Correction  | Complete        |
|         1 |       4 | Evaluate    | Complete        |
|         2 |       1 | Preparation | Complete        |
|         2 |       3 | Correction  | Complete        |
|         2 |       4 | Evaluate    | Complete        |
+-----------+---------+-------------+-----------------+
7 rows in set (0.07 sec)

c)

+-----+---------+-------------+----------+---------------+-------------+----------+
| Job | Jobdate | JobType     | JobPages | AnalysisGross | AnalysisNet | JobPrice |
+-----+---------+-------------+----------+---------------+-------------+----------+
|   1 |  201201 | Typesetting |        1 |           100 |          50 | 250      |
|   1 |  201201 | Non-Typest  |        1 |           100 |          50 | 250      |
|   2 |  201201 | Non-Typest  |        2 |           500 |         250 | 250      |
+-----+---------+-------------+----------+---------------+-------------+----------+
3 rows in set (0.21 sec)

c) is the best one so far...just need to remove:

|   1 |  201201 | Non-Typest  |        1 |           100 |          50 | 250      |

February 3, 2012

OK, getting close...

QUERY:

SELECT 		EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN 'Typeset'
	ELSE 'Non-Typeset'
	END AS 'JobType',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(job_pages)
	ELSE job_pages
	END AS 'JobPages',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(analysis_gross)
	ELSE analysis_gross
	END AS 'AnalysisGross',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(analysis_net)
	ELSE analysis_net
	END AS 'AnalysisNet',
CASE WHEN 	task_title='Typesetting' AND ti1.status='Complete'
	THEN SUM(invoice_item_price)
	ELSE invoice_item_price
	END AS JobPrice
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	JobDate, JobType
ORDER BY	JobDate, JobType DESC;

OUTPUT:

JobDate   - JobType   - JobPages - AnalysisGross - AnalysisNet - JobPrice
201201   - Typeset    - 1        - 100           - 50          - 250
201201   - Non-Typest - 1        - 100           - 50          - 250

EXPECTED OUTPUT:

JobDate   - JobType   - JobPages - AnalysisGross - AnalysisNet - JobPrice
201201   - Typeset    - 1        - 100           - 50          - 250
201201   - Non-Typest - 2        - 500           - 250         - 250

Thank you for the help!

February 2, 2012

Sorry, same result when using:

SELECT 			EXTRACT(YEAR_MONTH FROM proj_end_date) as job_date,
		SUM(invoice_item_price) as total, 
CASE		       	WHEN task_title='Typesetting'
		AND ti1.status='Complete'
		THEN 'Typeset'
		ELSE 'Non-Typeset'
		END AS 'JobType'
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		job_date, JobType 
ORDER BY		job_date, JobType DESC

I still feel the invoice_item_price / job / tasks are the issue though...

February 2, 2012

Hi again, thanks for the response, I tried:

SELECT 		EXTRACT(YEAR_MONTH FROM proj_end_date),
		SUM(invoice_item_price) as total, 
CASE		WHEN task_title='Typesetting'
		AND ti1.status='Complete'
		THEN 'Typeset'
		ELSE 'Non-Typeset'
		END AS 'JobType'
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		JobType 
ORDER BY		JobType DESC

and this gives me:

EXTRACT(YEAR_MONTH FROM proj_end_date) - total - JobType
201201                                 - 250   - Typeset
201201                                 - 1500  - Non-Typeset

But the 'total' figures are not what I'm after...as mentioned in my previous posts, I want to class all jobs that have had a 'Typesetting' task as a Typeset job, and those that didn't as 'Non-Typeset'.

What is happening with the query at the moment is it is multiplying each task by the job invoice_item_price, rather than each job once.

February 1, 2012

Thanks for the reply, however I'm getting an error: YEARMONTH does not exist.

The main problem I'm having is with the task_items / jobs, rather than the year/month/dates.

Hopefully this might explain the problem a bit better:

Job 1 has an invoice price of 250.

Job 1 has 4 tasks, 1 of which is Typesetting.

Job 2 has an invoice price of 250.

Job 2 has 3 tasks, 0 of which are Typesetting.

I want the query to produce a result of:

Typeset Job - 250 (Job 1 - has Typesetting Task)

Non Typeset Job - 250 (Job 2 - has no Typesetting Task)

Whats happening is:

Typeset Job - 250 (Job 1 - has 1 Typesetting Task)

Non Typeset Job - 750 (Job 1 - has 3 non-Typesetting Tasks)

Non Typeset Job - 750 (Job 2 - has 3 non-Typesetting Tasks)

Any ideas on this?

February 1, 2012

MySQL Design is broken down into seperate tables, for a pretty big database...what i've posted is a stripped down version, purely for this problem.

The SELECT query doesn't work as I want it to...it's just an example of what i've got so far...but the INNER JOINS obviously are required.

The JobType is determined by the task_title 'Typesetting', i.e. if the job has a Typesetting task, then the job as a whole is classed as a 'Typeset Job'.

The result I'm after is something to show the SUM of job_pages, analysis_gross, analysis_net and invoice_item_price per job.

The results need to be grouped by year/month/jobtype (Typeset/Non-Typeset)/langname

Is this possible?

February 1, 2012

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

Sign In

kool_samule

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by kool_samule

PHP Search XML by Line/Column No.

PHP Search XML by Line/Column No.

PHP Search XML by Line/Column No.

Incorrect strtotime() value ?

Incorrect strtotime() value ?

Incorrect strtotime() value ?

PHP / MySQL for Recursive Calendar Appointments

PHP / MySQL for Recursive Calendar Appointments

Conflict with two functions

Conflict with two functions

Conflict with two functions

PHP Save to file, not send to Headers

PHP Save to file, not send to Headers

Array within an Array

Array within an Array

MySQL Query Help

MySQL Query Help

MySQL Query Help

MySQL Query Help

MySQL Query Help

MySQL Query Help

MySQL Query Help

MySQL Query Help

MySQL Query Help

MySQL Query Help

Browse

Activity

Important Information