kool_samule

January 26, 2010

Sorry! OK, I have changed it to:

<input type="checkbox" name="jobadminquote[<?php echo $row_rsInvQuote['jobid'];?>]" id="jobadminquote_<?php echo $row_rsInvQuote['jobid'];?>" value="y" />

But I'm guessing this will force me to change the script in someway?

January 26, 2010

Thanks for the reply,

I have donew that:

<td><input type="checkbox" name="jobadminquote[]" id="jobadminquote_335" value="y" /></td>

Don't think thats the problem though, the problem is when one job doesn't have the checkbox ticked, which results in the jobadminquote array becoming out of sync.

I think I need something to POST a default value of 'n', which will keep the array in check?

January 26, 2010

Hi Chaps,

I have a Query that calculates a Quote for a job/jobs for a given project.

As there can be more than one job for a project, I have to loop through the query and present the data in a table.

Users have the option to 'override' the estimated quote and enter a 'custom quote'.

The ProjectID, JobID and TableInfo are part of my 1st Array, the estimated/custom quote figure and the 'override' option are part of my 2nd Array.

The information is then POSTed to a script file that joins the information together and then updates the relevant Table/JobID based on the figures and override options.

Example of HTML Code:

<form action="CompleteQuoteSingle.php" method="post" enctype="multipart/form-data">
  <table border="0" cellpadding="0" cellspacing="0">
    <caption><input type="submit" id="button" value="Submit" /></caption>
    <tr>
      <th>Project No.</th>
      <th>Project Title</th>
      <th>Job Title</th>
      <th>Type</th>
      <th>Language</th>
      <th>Deadline</th>
      <th>Document Format</th>
      <th>Pages</th>
      <th>Word Count</th>
      <th>Net Total</th>
      <th>EN Proofreading Cost</th>
      <th>Total</th>
      <th>Admin Override</th>
    </tr>
    <script type="text/javascript"> 
	$(function() {
     	var jobquote = $('#jobquote_328');
     	var value = jobquote.val();
     	$('#jobadminquote_328').click(function() {
          if (jobquote.attr('readonly')) {
               jobquote.removeAttr('readonly');
               jobquote.val('');
	} 
	else {
               jobquote.attr('readonly', 'readonly');
               jobquote.val(value);
          		}
     	});
});
</script>
      <tr>
        <td>1111</td>
        <td>QuickTrace - Project Template</td>
        <td>TEST JOBSHEET</td>
        <td>DTP</td>
        <td>EN</td>
        <td>31/12/2010</td>
        <td>MS Word</td>
        <td>20</td>
        <td>280</td>
        <td>£350.40</td>
        <td>£ 8.40</td>
        <td>£<input type='text' name='jobquote[]' id="jobquote_328" value="358.80" readonly="readonly" /></td>
        <td><input type="checkbox" name="jobadminquote[]" id="jobadminquote_328" value="y" /></td>
      </tr><input type="hidden" name="jobinfo[]" value="tbl_jobs:328:1111" />
      <script type="text/javascript"> 
	$(function() {
     	var jobquote = $('#jobquote_335');
     	var value = jobquote.val();
     	$('#jobadminquote_335').click(function() {
          if (jobquote.attr('readonly')) {
               jobquote.removeAttr('readonly');
               jobquote.val('');
          } else {
               jobquote.attr('readonly', 'readonly');
               jobquote.val(value);
          }
     	});
});
</script>
      <tr>
        <td>1111</td>
        <td>QuickTrace - Project Template</td>
        <td>TEST</td>
        <td>DTP</td>
        <td>CZ</td>
        <td>31/12/2010</td>
        <td>InDesign CS4</td>
        <td>654</td>
        <td>280</td>
        <td>£ 50.40</td>
        <td>£ 0.00</div></td>
        <td>£<input type='text' name='jobquote[]' id="jobquote_335"  class='price' value="50.40" readonly="readonly" /></td>
        <td><input type="checkbox" name="jobadminquote[]" id="jobadminquote_335" value="y" /></td>
      </tr><input type="hidden" name="jobinfo[]" value="tbl_jobs:335:1111" />
    </table>

CompleteQuoteSingle.php

$allowed_tables = Array('tbl_jobs','tbl_jobtransline','tbl_jobxml'); // to prevent SQL injection
$i = 0;
foreach($_POST['jobinfo'] as $var) {
    $arr = explode(':', $var);
    if(in_array($arr[0], $allowed_tables)) {
        $table = $arr[0];
        $rowid = $arr[1];
	$projid = $arr[2];
        $setprice = $_POST['jobquote'][$i];
	$adminoverride = $_POST['jobadminquote'][$i];
	$i++;
        if(is_numeric($rowid)){
		if($adminoverride=='y') {
            // run your SQL query here to update $table where row matches $rowid
            $query = sprintf("
		UPDATE $table 
		SET jobquote='$setprice', jobquotecomplete='y', jobadminquote='y'
		WHERE jobid=$rowid");
            //$result = mysql_query($query, $conndb2) or die(mysql_error());
		//$mess = $ref = $_SERVER['HTTP_REFERER']; header( 'refresh: 0; url=../../projects/project_details.php?id='.$projid);
        }
    		else {
		// run your SQL query here to update $table where row matches $rowid
            $query = sprintf("
		UPDATE $table 
		SET jobquote='$setprice', jobquotecomplete='y', jobadminquote='n'
		WHERE jobid=$rowid");
            //$result = mysql_query($query, $conndb2) or die(mysql_error());
		//$mess = $ref = $_SERVER['HTTP_REFERER']; header( 'refresh: 0; url=../../projects/project_details.php?id='.$projid);
		}
	}
}
}

My problem is:

The Override option only gets passed to the Array, if selected. This means that if I have two jobs, and I select the override option for the second job, the array looks like this:

Array
(

[jobquote] => Array

(

[0] => 358.80

[1] => 100

)

[jobinfo] => Array

(

[0] => tbl_jobs:328:1111

[1] => tbl_jobs:335:1111

)

[jobadminquote] => Array

(

[0] => y

)

)

Question:

Is there a way of POSTing a default value of 'n' for the 'jobadminquote' checkbox, so that the above would look like:

[jobadminquote] => Array
(

[0] => n

[1] => y

)

I hope this is clear?!

January 21, 2010

Got a bit further:

<input type='text' name='jobquote' value="<?php echo $price_total; ?>"/>
        <input type='hidden' name='original_jobquote' value="<?php echo $price_total; ?>"/>
        <?php
		if ($_POST['original_jobquote'] != $_POST['jobquote'])
			{ ?>
        		<span id="spryradio1">
        		<input type="radio" name="jobquoteadmin" value="y" id="radio" />Confirm<br />
        		<span class="radioRequiredMsg">Please confirm Admin Override</span></span>
        		<?php };
	?>

Problem 1. The information is '$_POST'ed to a script file, not to the page itself and at the moment, the $_POST takes place before the PHP validation takes place. If I remove the link to the script page, the validation works.

Problem 2. (link to script removed for testing) If I change the value (from the default 'original_quote'), then submit, the page reloads, the 'Confirm' radio button appears, but the value of 'job_quote' has reverted back thte default:

1. Start - job_quote = £350

2. Change - job_quote = £100

3. Submit

4. Page reloads - job_quote = £350, confirm appears

Is there a way around this?

January 20, 2010

Hi, should have been a bit clearer I guess,

$price_total=['calculation']
<input type='text' name='jobquote' id="count" class='price_warning' value="<?php echo number_format($price_total, 1, '.', '').'0'; ?>"/>

So $price_total is the default value of the input, but it can be changed, and if changed, i need the 'admin override' option to appear.. . .

January 20, 2010

Hi Chaps,

Need a bit of guidence with some PHP code.

I have a Query that estimates a quote ($price_total) for a job.

The estimate ($price_total) is the value of an input (jobquote), and the database is updated once the form is submitted (using a seperate script.php page).

What I need, is to validate the entered value of 'jobquote' against the estimated value of $price_total, just incase a 'custom' price has been agreed with a customer.

If the values are different, then I need an 'admin override' radio button (admin_quote enum('y','n')) to appear.

If someone can help or point me in the right direction, I'd be most grateful.

Cheers

January 12, 2010

Cool, thanks for the info . . .I'll try and keep it in the memory bank.

January 12, 2010

Nice shot! Seems to have worked! Bingo, cheers for now dude!

January 12, 2010

Hi Chaps,

I'm having some trouble with a PHP Form page and an Update Script page.

I've tested this on my Apache server and it works fine, but fails on my 'Live' IIS server:

Form Code:

<input type='text' name='jobpriority[]' value="<?php echo $row_rsWorkload_All['jobpriority'];?>"/>
<?php echo $row['jobcount']; ?>
<?php echo $row_rsWorkload_All['jobpriority'];?>
<?php
$table_name = $row_rsWorkload_All['fromtable'];
        $item_id = $row_rsWorkload_All['jobid']; 
?>

<input type="hidden" name="setpriority[]" value="<?php echo $table_name; ?>:<?php echo $item_id; ?>" />

The 'hidden' input did look like this:

<input type="hidden" name="setpriority[]" value="<?php echo $table_name; ?>:<?php echo $item_id; ?>:<?php $_POST ['jobpriority']; ?>" />

But I had to remove the

:<?php $_POST ['jobpriority']

bit to get the page to open on the IIS server.

Since removing the code, the page opens but when I 'click send' to pass the data to the script page, I get this error:

PHP Notice: Undefined offset: 22 in C:\Inetpub\XxxxxXxxxx\NewFiles\Xxxxx\Xxxxx\scripts\script.php on line 64

Script.PHP:

$allowed_tables = Array('tbl_table1','tbl_table2','tbl_table3'); // to prevent SQL injection
$i = 1;
foreach($_POST['setpriority'] as $var) {
    $arr = explode(':', $var);
    if(in_array($arr[0], $allowed_tables)) {
        $table = $arr[0];
        $rowid = $arr[1];
        $priority = $_POST['jobpriority'][$i];
	$i++;
        if(is_numeric($rowid)) {
            // run your SQL query here to update $table where row matches $rowid
            $query = sprintf("
		UPDATE $table 
		SET jobpriority='$priority' 
		WHERE jobid=$rowid");
            $result = mysql_query($query, $conndb2) or die(mysql_error());
		$mess = $ref = $_SERVER['HTTP_REFERER']; header( 'refresh: 0; url='.$ref);
        }
    else {
  $mess = "<p>There was a problem</p>";
}
}
}

I'm a bit stuck as I have around 10 pages that use the same sort of functionality, so if anyone can help with this, I'd be most grateful!

December 21, 2009

http://www.sitepoint.com/forums/showthread.php?t=652564

December 21, 2009

Hi Chaps,

I have a column ['projdue'], which stores the deadline for a project.

I have PHP code and <span class>'s to show projects that are within timescale, due today and overdue:

<?php 
      // get the date parts from the MySQL date
      $dateparts = preg_split('/[-\s]/', $row_rsProjects['projdue']);
      // get the Unix timestamp for midnight at the beginning of the due date
      $projdue = mktime(0,0,0, $dateparts[1], $dateparts[2], $dateparts[0]);
      // get the Unix timestamp for midnight at the beginning of today
      $today = mktime(0,0,0, date('n'), date('j'), date('Y'));
      if ($today == $projdue) { ?>
      <tr class="duetoday">
        <?php } else if ($today > $projdue) { ?>
        <tr class="within">
          <?php } else if ($today < $projdue) { ?>
        <tr class="overdue">
          <?php }?>

What I'm after is a bit of code to flag-up any projects that are due in 7 days

November 30, 2009

Thanks to play_ for the help, a true gent.

November 30, 2009

Hi play_

tried that, got this response:

Array ( [translatorcharge] => 40 [jobinvsent] => Array ( [0] => :: [1] => tbl_jobxml:86: [2] => tbl_jobxml:69: [3] => tbl_jobs:145: [4] => tbl_jobs:144: ) )

Nothing was entered in the database

November 30, 2009

Hi Chaps,

I have a repeat region, displaying rows of data: jobid, fromtable, translatorcharge

In each row there is an input field to enter a cost for each job 'charge'.

//INPUT - TRANSLATOR CHARGE

<input type="text" name="translatorcharge" id="count" class="price" value="<?php echo $row_rsInvPending['jobtranslatorcharge']; ?>"/>

I have a hidden input that collects the information for each row, before sending all the information to a script page:

//HIDDEN INPUT - INFO COLLECTOR

<?php
$table_name = $row_rsInvPending['fromtable'];
$item_id = $row_rsInvPending['jobid'];
?>
<input type="hidden" name="jobinvsent[]" value="<?php echo $table_name; ?>:<?php echo $item_id; ?>:<?php $_POST['translatorcharge'] ?>" />

The script page then updates the translatorcharge column in the relevant table:

//SCRIPT - TO UPDATE DATABASE

$allowed_tables = Array('tbl_jobs','tbl_jobtransline','tbl_jobxml'); // to prevent SQL injection
foreach($_POST['jobinvsent'] as $var) {
    $arr = explode(':', $var);
    if(in_array($arr[0], $allowed_tables)) {
        $table = $arr[0];
        $rowid = $arr[1];
        $transcharge = $_POST['translatorcharge'];
        if(is_numeric($rowid)) {
            // run your SQL query here to update $table where row matches $rowid
            $mess = $ref = $_SERVER['HTTP_REFERER']; header( 'refresh: 0; url='.$ref);
            $query = sprintf("UPDATE $table SET jobtranslatorcharge='$transcharge' WHERE jobid=$rowid");
            $result = mysql_query($query, $conndb2) or die(mysql_error());
        }
    else {
  $mess = "<p>There was a problem</p>";
}
    }
}

The problem is, although I'm entering different values for each row, only the last entered value is being passed to the array and therefore updating all rows with the same value.

My questions is, how can I 'individualise' each input, relating to each row?

November 26, 2009

Hi Chaps,

I have a recordset that pulls data from a database, then presents the data in a table. The data is grouped by 'projid', repeated for every 'projid', with a show/hide control, to show all the 'jobid's' relating to that particular 'projid'

This is what I have so far:

SELECT              
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, DATE_FORMAT(tbl_projects.projdue, '%%d/%%m/%%Y') as projdue_format, 
tbl_projects.projtype,  
tbl_projects.projinvtype, 
tbl_projects.FK_custid, 
tbl_projects.projcompletedate
tbl_languaget.langtname,
tbl_doctype.doctypename,
tbl_jobs.jobid, 
tbl_jobs.FK_projid,               
tbl_jobs.jobname,               
tbl_jobs.FK_langid,               
tbl_jobs.jobpages,               
tbl_jobs.jobshipped, 
tbl_jobs.jobinvsent,    
tbl_jobs.jobtranslatorcharge,     
'tbl_jobs' as fromtable,
tbl_customers.custid,
FROM tbl_projects   
INNER JOIN tbl_jobs               
ON tbl_projects.projid=tbl_jobs.FK_projid   
INNER JOIN tbl_languaget               
ON tbl_languaget.langtid=tbl_jobs.FK_langid   
INNER JOIN tbl_customers               
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_costs
ON tbl_costs.FK_custid=tbl_customers.custid  
INNER JOIN tbl_doctype
ON tbl_doctype.doctypeid=tbl_jobs.FK_doctypeid
WHERE tbl_projects.projstatus='Complete'         
AND tbl_projects.projinvtype='Costing Sheet'
AND langtname!='TH'
AND langtname!='ID'
AND langtname!='KO'
AND langtname!='JP'
AND jobinvsent='y'
AND FK_custid = %s
ORDER BY projid ASC", GetSQLValueString($colname_rsInvPending, "int");
$rsInvPending = mysql_query($query_rsInvPending, $conndb2) or die(mysql_error());
//$row_rsInvPending = mysql_fetch_assoc($rsInvPending);
$totalRows_rsInvPending = mysql_num_rows($rsInvPending);

// REPEAT - FOR EVERY PROJECT
  <?php
  $previousProject = '';
  if ($totalRows_rsInvPending > 0) {
  // Show if recordset not empty
    while ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)) {
      if ($previousProject != $row_rsInvPending['projid']) {
      // for every Project, show the Project ID 
  ?>

// SHOW/HIDE CONTROL
    <tr>
    <td colspan="9" class="highlight"><span class="blueBold"><a href="#" onclick="toggle2('proj1<?php echo $row_rsInvPending['projid'] ?>', this)"><img src="../../Images/plus.gif" border="0" /></a> <?php echo $row_rsInvPending['projid'] ?> - </a></span><span class="blueNOTBold"><em><?php echo $row_rsInvPending['projtitle'] ?></em></span></td>
    </tr>

// SHOW/HIDE 
    <?php $previousProject = $row_rsInvPending['projid']; } ?>
    <tr class="proj1<?php echo $row_rsInvPending['projid'] ?>" style="display:none">
    <td>column 1</td>
    <td>column 2</td>

What I want, is to put in another grouped by stage, where the 'projid's' themselves are in a show/hide region, grouped by 'projcompletedate' (year/month). 'projcompletedate' is in DATE format, but how to I get PHP/SQL to take the month and year and then group them correctly?

November 19, 2009

Hi Chaps,

I have a Query that pulls data from 3 different job tables:

tbl_jobs
- jobid (auto) primary key
- jobinvsent (y,n)
tbl_jobxml
- jobid (auto) primary key
- jobinvsent (y,n)
tbl_jobtrans
- jobid (auto) primary key
- jobinvsent (y,n)

I'm able to present the data in an HTML table, but what I need is the ability to update the values of jobinvsent to 'y' of all the jobid's presented.

I'm guessing I'll need to wrap the table in a form and then post it to a script.php file, but I don't know how to gather all the jobid's in such a way that the scrip file will know which table it came from.

Would really appreciate any help!

November 10, 2009

Thanks for the link . . .I've come across this before but don't have a clue how to incorporate the classes (or know how they work) into my existing web app.....?

November 10, 2009

Hi Chaps,

I have a HTML table, and need to export the contents, with formatting to MS Excel.

Does anyone know of a simple way to do this?

November 9, 2009

Hi Chaps,

I have a SQL Query:

mysql_select_db($database_conndb2, $conndb2);
$query_rsProjects = "SELECT tbl_projects.projid, tbl_projects.projtitle, tbl_customers.custname, tbl_projects.projstart, tbl_projects.projdue, tbl_projects.projstatus, tbl_projects.projstatus, DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format, tbl_projects.projpriority FROM tbl_projects, tbl_customers WHERE tbl_projects.FK_custid=tbl_customers.custid AND tbl_projects.projstatus!='Complete' ORDER BY projid ASC";
$rsProjects = mysql_query($query_rsProjects, $conndb2) or die(mysql_error());
$row_rsProjects = mysql_fetch_assoc($rsProjects);
$totalRows_rsProjects = mysql_num_rows($rsProjects);

The data is presented in a table:

<table border="0" cellpadding="0" cellspacing="0" id="tblrepeat_proj">
      <caption>
        <img src="../Images/project.jpg" border="0" /><a href="project_add.php"><img src="../Images/new.jpg" border="0" /></a><a href="#" onclick="print()"><img src="../Images/print.jpg" border="0"/></a>
      </caption>
      <tr>
        <th scope="col">Order No.</th>
        <th scope="col">Document Name</th>
        <th scope="col">Customer</th>
        <th scope="col">Start Date</th>
        <th scope="col">Due Date</th>
        <th scope="col">Status</th>
        <th scope="col">Edit</th>
        <th scope="col">Remove</th>
      </tr>
      <?php do { ?>
      <td>
      <?php echo $row_rsProjects['projid']; ?></span></td>
          <td><a href="project_details.php?id=<?php echo $row_rsProjects['projid']; ?>"><?php echo $row_rsProjects['projtitle']; ?></a></td>
          <td><?php echo $row_rsProjects['custname']; ?></td>
          <td><?php echo $row_rsProjects['projstart_format']; ?></td>
          <td><?php echo $row_rsProjects['projdue_format']; ?></td>
          <td><?php echo $row_rsProjects['projstatus']; ?></span></td>
          <td><a href="project_edit.php?id=<?php echo $row_rsProjects['projid']; ?>">Edit</a></td>
          <td><a href="project_remove.php?id=<?php echo $row_rsProjects['projid']; ?>" onclick="tmt_confirm('Are%20you%20sure%20you%20want%20to%20delete%20this%20reco rd?');return document.MM_returnValue">Remove</a></td>
        </tr>
        <?php } while ($row_rsProjects = mysql_fetch_assoc($rsProjects)); ?>
    </table>

Is it possible to use the table headings as the control for the ORDER BY in the SQL query?

November 9, 2009

Hi Chaps,

I have an SQL query that calculates a word count multiplied by a fixed figure:

...$pricegross = ($row_rsInvPending['projanalysis_total']*$row_rsInvPending['costbase']);...

Then use a bit of php code to round up the figure to 2 decimal places....

<?php echo number_format($pricegross, 2, '.', ''); ?>

I then total up $pricegross for all records:

.....SELECT SUM(projanalysis_total) as total_pricegross...

If I have two records:

($pricegross) 1. £2.295

($pricegross) 2. £2.295

There are both displayed as £2.30, which is what I want

Then the total ($total_pricegross) shows up as £4.59, but what I want is: £4.60

Is this possible?....Hope this is clear?!

November 2, 2009

This is what I've got so far,

<?php

$today = date("dd/mm/yyyy");

if ($row_rsProjects['projdue_format'] == $today) { ?>

<?php } else if ($row_rsProjects['projdue_format'] < $today) { ?>

<?php } else if ($row_rsProjects['projdue_format'] > $today) { ?>

<?php }?>

But still isn't working correctly, projects with today's date are showing up as 'overdue'

November 2, 2009

Hi Chaps,

I have a bit of PHP code, that at the moment doesn't work.

I'm trying to show all the 'overdue' projects in a different style:

<?php if ($row_rsProjects['projdue'] == '= DATE(NOW())') { ?>
      <tr class="overdue">
  <?php } else if ($row_rsProjects['projdue'] == '< DATE(NOW())') { ?>
      <tr class="duetoday">
  <?php } else { ?>
      <tr class="within">
  <?php }?>

The

'= DATE(NOW())'

works with MySQL, but I don't know how to get it to work with PHP. Any ideas?

October 12, 2009

Hi Chaps,

I have a query that searches through 3 tables and displays the results for Open Jobs for a specific customer:

$colname_rsOpen_Cust = "-1";
if (isset($_GET['id'])) {
  $colname_rsOpen_Cust = $_GET['id'];
}
mysql_select_db($database_conndb2, $conndb2);
$query_rsOpen_Cust = sprintf("SELECT*
FROM((
SELECT 
tbl_projects.projid, 
tbl_projects.projtype,
tbl_jobs.jobname, 
tbl_jobs.jobid,
tbl_jobs.jobwnet, 
tbl_jobs.jobprepared, 
tbl_jobs.jobtranscomplete, 
tbl_jobs.jobftype, 
tbl_jobs.jobcorrec, 
tbl_jobs.jobcordoc, 
tbl_jobs.jobshipped, 
tbl_customers.custid,
tbl_customers.custname, 
tbl_languaget.langtname 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobs 
ON tbl_projects.projid=tbl_jobs.FK_projid 
INNER JOIN tbl_customers 
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobs.FK_langid 
) 
UNION
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtype,
tbl_jobtransline.jobname, 
tbl_jobtransline.jobid,
tbl_jobtransline.jobwnet, 
tbl_jobtransline.jobaccepted AS jobprepared, 
tbl_jobtransline.jobtranscomplete, 
tbl_jobtransline.jobftype, 
tbl_jobtransline.jobcorrec, 
tbl_jobtransline.jobcordoc, 
tbl_jobtransline.jobshipped, 
tbl_customers.custid,
tbl_customers.custname, 
tbl_languaget.langtname 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobtransline 
ON tbl_projects.projid=tbl_jobtransline.FK_projid 
INNER JOIN tbl_customers 
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobtransline.FK_langid 
)
UNION
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtype, 
tbl_jobxml.jobno AS jobname, 
tbl_jobxml.jobid, 
tbl_jobxml.jobwnet, 
tbl_jobxml.jobdownload AS jobprepared, 
tbl_jobxml.jobtranscomplete, 
tbl_jobxml.jobftype, 
tbl_jobxml.jobcorrec, 
tbl_jobxml.jobcordoc, 
tbl_jobxml.jobshipped,
tbl_customers.custid,  
tbl_customers.custname, 
tbl_languaget.langtname 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobxml
ON tbl_projects.projid=tbl_jobxml.FK_projid 
INNER JOIN tbl_customers 
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobxml.FK_langid 
)) SomeAlias
WHERE 
custid = %s 
AND jobshipped='n'", 

GetSQLValueString($colname_rsOpen_Cust, "int"));

$rsOpen_Cust = mysql_query($query_rsOpen_Cust, $conndb2) or die(mysql_error());
//$row_rsOpen_Cust = mysql_fetch_assoc($rsOpen_Cust);
$totalRows_rsOpen_Cust = mysql_num_rows($rsOpen_Cust);

What I need is a COUNT of all the "jobid".

I've tried a few ways but e.g., if there are 6 open jobs in total, for some reason by COUNT shows 3 ?!

October 9, 2009

Hi Lemin . . .

You say:

I would suggest relating the individual values instead of joining them.

How would I do this if I want the results to appear in the same table, grouped by "projid", then "projdue"?

October 9, 2009

Hi Chaps,

I have this code:

mysql_select_db($database_conndb2, $conndb2);
$query_rsJobs_Translation = "
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, 
tbl_jobs.jobid, 
tbl_languaget.langtname, 
tbl_jobs.jobwnet, 
tbl_jobs.jobtransih, 
tbl_jobs.jobtranscomplete,
tbl_jobs.jobname,
DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, 
DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format
FROM 
tbl_projects 
INNER JOIN tbl_jobs 
ON tbl_projects.projid=tbl_jobs.FK_projid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobs.FK_langid 
WHERE tbl_jobs.jobtransih='y' 
AND tbl_jobs.jobtranscomplete='n'
)
UNION
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, 
tbl_jobtransline.jobid, 
tbl_languaget.langtname, 
tbl_jobtransline.jobwnet, 
tbl_jobtransline.jobtransih, 
tbl_jobtransline.jobtranscomplete, 
tbl_jobtransline.jobname,
DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, 
DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format 
FROM 
tbl_projects 
INNER JOIN tbl_jobtransline 
ON tbl_projects.projid=tbl_jobtransline.FK_projid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobtransline.FK_langid 
WHERE tbl_jobtransline.jobtransih='y' 
AND tbl_jobtransline.jobtranscomplete='n'
)
UNION
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, 
tbl_jobxml.jobid, 
tbl_languaget.langtname, 
tbl_jobxml.jobwnet, 
tbl_jobxml.jobtransih, 
tbl_jobxml.jobtranscomplete, 
tbl_jobxml.jobname,
DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, 
DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format
FROM 
tbl_projects 
INNER JOIN tbl_jobxml 
ON tbl_projects.projid=tbl_jobxml.FK_projid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobxml.FK_langid 
WHERE tbl_jobxml.jobtransih='y' 
AND tbl_jobxml.jobtranscomplete='n'
)
ORDER BY
projdue ASC";

$rsJobs_Translation = mysql_query($query_rsJobs_Translation, $conndb2) or die(mysql_error());
//$row_rsJobs_Translation = mysql_fetch_assoc($rsJobs_Translation);
$totalRows_rsJobs_Translation = mysql_num_rows($rsJobs_Translation);

Which produces results from 3 different tables, and works fine.

I'm presenting the data in a table:

<table border="0" cellpadding="0" cellspacing="0" id="tblrepeat">
<caption><p>Jobs for Translation</p></caption>
  <tr>
    <th scope="col">Due Date</th>
    <th scope="col">Language</th>
    <th scope="col">Title</th>
    <th scope="col">Translated</th>
    <th scope="col">Words - Net</th>
  </tr>
    <?php
  $previousProject = '';
  if ($totalRows_rsJobs_Translation > 0) {
  // Show if recordset not empty
    while ($row_rsJobs_Translation = mysql_fetch_assoc($rsJobs_Translation)) {
      if ($previousProject != $row_rsJobs_Translation['projid']) {
      // for every Project, show the Project ID
?>
  <tr>
    <td colspan="5" class="highlight"><span class="blueBold"><a href="#" onclick="toggle2('proj<?php echo $row_rsJobs_Translation['projid'] ?>', this)"><img src="../Images/plus.gif" border="0" /></a> <?php echo $row_rsJobs_Translation['projid'] ?> - </a></span><span class="blueNOTBold"><em><?php echo $row_rsJobs_Translation['projtitle'] ?></em></span></td>
    </tr>
  <?php $previousProject = $row_rsJobs_Translation['projid']; } ?>
  <tr class="proj<?php echo $row_rsJobs_Translation['projid'] ?>" style="display:none">
  			<td><?php echo $row_rsJobs_Translation['projdue_format']; ?></td>
            <td><?php echo $row_rsJobs_Translation['langtname']; ?></td>
            <td>
    <?php if ($row_rsJobs_Translation['jobname'] == 'Transline') { ?>
          <a href="jobsheet_trans_details.php?id=<?php echo $row_rsJobs_Translation['jobid']; ?>&proj=<?php echo $row_rsJobs_Translation['projid']; ?>"><?php echo $row_rsJobs_Translation['jobname']; ?></a>
          <?php } else if ($row_rsJobs_Translation['jobname'] == 'XML'){ ?>
         <a href="jobsheet_xml_details.php?id=<?php echo $row_rsJobs_Translation['jobid']; ?>&proj=<?php echo $row_rsJobs_Translation['projid']; ?>"><?php echo $row_rsJobs_Translation['jobname']; ?></a>
          <?php } else { ?>
         <a href="jobsheet_details.php?id=<?php echo $row_rsJobs_Translation['jobid']; ?>&proj=<?php echo $row_rsJobs_Translation['projid']; ?>"><?php echo $row_rsJobs_Translation['jobname']; ?></a>
          <?php }?>           
    </td>
            <td><?php if ($row_rsJobs_Translation['jobtranscomplete'] == 'y') { ?>
                <span class="greenBold">Yes</span>
                <?php } else if ($row_rsJobs_Translation['jobtranscomplete'] == 'n') { ?>
                <span class="redBold">No</span>
                <?php } ?>            </td>
            <td><?php echo $row_rsJobs_Translation['jobwnet']; ?></td>
          </tr>
          <?php } while ($row_rsJobs_Translation = mysql_fetch_assoc($rsJobs_Translation)); ?>
      <?php } // Show if recordset not empty ?>
</table>

Which should group the results by "projid", with collapsible rows for the job/jobtransline/jobxml results.

The problem is, the grouping doesn't seem to work, i.e., there are two rows for the same "projid", one for each "job"...

How can I solve this?

Cheers

Sign In

kool_samule

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by kool_samule

POST to Array trouble

POST to Array trouble

POST to Array trouble

Validate input value against calculated value

Validate input value against calculated value

Validate input value against calculated value

PHP Notice: Undefined offset ???

PHP Notice: Undefined offset ???

PHP Notice: Undefined offset ???

PHP time (deadline within one week)

PHP time (deadline within one week)

POST into Array problem

POST into Array problem

POST into Array problem

Group by month/year from DATE FORMAT column

Update values from repeated table

PHP/HTML - Export to Excel

PHP/HTML - Export to Excel

ORDER BY - Table headings

PHP Rounding Up Issue

PHP filter by Date

PHP filter by Date

[SOLVED] COUNT through 3 tables?

PHP - Presenting data problem .....?

PHP - Presenting data problem .....?

Browse

Activity

Important Information