[SOLVED] Submit multiple rows to MySQL from filtered data.

[dcreeves88]

Hello  all,

 

I was wondering if anyone could help me out with a problem here. I'm a relatively inexperienced with PHP and MySQL, but I've managed to hold my own thus far. Here's my problem...

 

What I'm developing is a job costing and time tracking system for a construction department. Basically, a user enters his hours spent on a job, and the results are stored in a DB. Those hours are then to be compared to and multiplied by the user's hourly rate (time.Hours * employee.Rate).

To do so, I have a page called laborimport which allows the user to define a date range and submit. This is then processed by lprocess and displays all the appropriate data in a table.

 

All this is working flawlessly, but now what I need to do is take that filtered data and insert ONLY those rows into a separate table "txn". The issue however, is this.

 

The filtered data displayed is:

time.EmployeeName

time.Date

time.JobNumber

time.Hours

time.Notes

 

I need to submit that filtered data into the new table (txn), but instead of hours I need to submit time.Hours * employee.Rate. On the display page, I display the records by using the "do" function for the time array. (Thus adding new rows to the table for every row in the array). In the table, I have queried the employee table for the Rate and multiplied it with the existing time.Hours and stored it as a variable called $actual. Naturally, this variable only works while inside that particular repeat.

 

So basically, what it comes down to, how can I submit the data from the time array into the new table, while multiplying the Hours column by the appropriate employee.Rate column?

 

I hope this makes sense. If I can provide any more information please let me know.

 

Thank you!

 

-Doug

[ignace]

I don't know how your code works nor how your database looks like except for the time table and one field from employee so this is only what I can suggest at the moment:

 

INSERT INTO txn SELECT time.id, time.hours * employee.rate FROM time JOIN employee ON time.employee_id = employee.id

[dcreeves88]

Thanks for the quick reply Ignace. The query makes a lot of sense actually (I've never seen that JOIN ON command before, great to know). The only thing is, it doesn't take into account the filtered data. Unless I'm missing something of course.

It also doesn't submit multiple rows either, once again unless I'm mistaken.

 

If it would help, I can show my schema as well as the coding. Would that make anything easier?

 

[ignace]

The schema would suffice I suppose unless my INSERT INTO doesn't work like I would like

[dcreeves88]

Schema is as follows:

 

employee

recordID

Name

MFDefault

JobDefault

Rate

IsActive

IsPM

Note

joblist

recordID

Number

Name

Address

IsActive

Note

txn

recordID

Type

Refrence

Date

Job

MFCode

Actual

time

recordID

Employee

Date

Job

MFCode

Hours

Task

laborimport.php

<?php require_once('Connections/db.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_db, $db);
$query_jobs = "SELECT * FROM joblist WHERE IsActive = 'Active'";
$jobs = mysql_query($query_jobs, $db) or die(mysql_error());
$row_jobs = mysql_fetch_assoc($jobs);
$totalRows_jobs = mysql_num_rows($jobs);mysql_select_db($database_db, $db);
$query_jobs = "SELECT * FROM joblist WHERE IsActive = 'Active'";
$jobs = mysql_query($query_jobs, $db) or die(mysql_error());
$row_jobs = mysql_fetch_assoc($jobs);
$totalRows_jobs = mysql_num_rows($jobs);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Labor Import</title>
<!-- Date Picker -->
	<script type="text/javascript" src="js/jquery-1.3.1.min.js"></script>
	<script type="text/javascript" src="js/jquery-ui-1.7.1.custom.min.js"></script>
	<script type="text/javascript" src="js/daterangepicker.jQuery.js"></script>
	<link rel="stylesheet" href="css/ui.daterangepicker.css" type="text/css" />
	<link rel="stylesheet" href="css/redmond/jquery-ui-1.7.1.custom.css" type="text/css" title="ui-theme" />
	<script type="text/javascript">	
		$(function(){
			  $('#start').daterangepicker({arrows:false}); 
		 });
	</script>

	<!-- from here down, demo-related styles and scripts -->
	<style type="text/css">
		body { font-size: 62.5%; }
		input {width: 196px; height: 1.1em; display:block;}
	#content {
width: 450px;
background-color: #A9D9F5;
margin: auto;
}
        #content form #submit a {
font-size: large;
padding: 5px;
}
        </style>

</head>

<body>

<div id="content" align="center">
<form name="limport" action="lprocess.php" method="post">
<table border="0" cellpadding="5">
  <tr>
    <th scope="row">Select Job:</th>
    <td> </td>
    <td><select name="job">
<option value=""%"" selected="selected">All</option>
      <?php
do {  
?>
      <option value="<?php echo $row_jobs['Number']?>"<?php if (!(strcmp($row_jobs['Number'], "All"))) {echo "selected=\"selected\"";} ?>><?php echo $row_jobs['Name']?> - <?php echo $row_jobs['Number']; ?></option>
      <?php
} while ($row_jobs = mysql_fetch_assoc($jobs));
  $rows = mysql_num_rows($jobs);
  if($rows > 0) {
      mysql_data_seek($jobs, 0);
  $row_jobs = mysql_fetch_assoc($jobs);
  }
?>
    </select></td>
  </tr>
  <tr>
    <td align="center">Date Range</td>
    <td> </td>
    <td align="center"><input type="text" name="start" id="start" /></td>
  </tr>
</table>
<div id="submit" align="right"><a href="#" onclick="document.limport.submit()">Submit</a></div>
</form>
</div>

</body>
</html>
<?php
mysql_free_result($jobs);
?>

 

lprocess.php

<?php require_once('Connections/db.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];
$job = $_POST['job'];
$range = $_POST['start'];


mysql_select_db($database_db, $db);
$query_time = "SELECT * FROM `time` WHERE Job LIKE $job AND Date BETWEEN $range";
$time = mysql_query($query_time, $db) or die(mysql_error());
$row_time = mysql_fetch_assoc($time);
$totalRows_time = mysql_num_rows($time);mysql_select_db($database_db, $db);


$queryString_time = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_time") == false && 
        stristr($param, "totalRows_time") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_time = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_time = sprintf("&totalRows_time=%d%s", $totalRows_time, $queryString_time);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Processing Labor Import...</title>
</head>

<body>

<table border="1" align="center">
  <tr>
    <td>Employee</td>
    <td>Date</td>
    <td>Job</td>
    <td>Hours</td>
    <td>Actual</td>
  </tr>
  <?php do { ?>
    <tr>
      <td><?php echo $row_time['Employee']; ?>  </td>
      <td><?php echo $row_time['Date']; ?></td>
      <td><?php echo $row_time['Job']; ?></td>
      <td><?php echo $row_time['Hours']; ?></td>
      <td>      
      <!-- PHP to calculate rate -->
      <?php $employee = $row_time['Employee'];
  		$hours = $row_time['Hours'];


  		mysql_select_db($database_db, $db);
$query_qrate = "SELECT * FROM `employee` WHERE Name = '$employee'";
$qrate = mysql_query($query_qrate, $db) or die(mysql_error());
$row_qrate = mysql_fetch_assoc($qrate);
$totalRows_qrate = mysql_num_rows($qrate);

		$rate =  $row_qrate['Rate'];
		$actual = $hours * $rate;

  echo $actual;
  		 ?></td>
    </tr>
    <?php } while ($row_time = mysql_fetch_assoc($time)); ?>
</table>


<br />

</body>
</html>
<?php
mysql_free_result($time);
?>

[dcreeves88]

Ok, so I'm really close now. I can submit the correct data using the JOIN ON function as you suggested ignace, now I just need to get the data filtered. Here's my query:

 

INSERT INTO txn (recordID, type, reference, date, job, mfcode, actual) SELECT "", "L", Employee, Date, Job, MFCode, time.Hours * employee.Rate FROM time JOIN employee ON time.Employee LIKE Name

 

Now I need to add the following filter:

WHERE Job LIKE $job AND Date BETWEEN $range 

(For what it's worth, $range basically contains "Date1" AND "Date2", I'm sure thats not where the problem lies.

The variables pass fine to the page, but I just can't figure out how/where in that statement it would fit. Any ideas?

[dcreeves88]

Alright, even closer. Here's my page:

<?php require_once('Connections/db.php'); ?>
<?php
$job = $_GET['job'];
$range = $_GET['range'];


mysql_select_db ($database_db, $db);
	$query = 	'INSERT INTO txn (recordID, type, reference, date, job, mfcode, actual) 
				SELECT "", "L", Employee, Date, Job, MFCode, time.Hours * employee.Rate 
				FROM time 
				JOIN employee 
				ON time.Employee LIKE Name
				WHERE Date BETWEEN $range AND Job LIKE $job ';

mysql_query ($query, $db) or die(mysql_error());

?>

 

The filtering works fine with strings, but when I try to use the variable, it interprets it literally and dies with the following error "Unknown column '$range' in 'where clause'"

 

Any idea A. Why it's taking the variable literally. B: How to make it process that variable correctly?

[dcreeves88]

Alright, I figured it out.

 

The $query variable was surrounded with single quotes rather than double quotes because of the usage of doubles in the query.

 

Switched the singles to double and escaped all the doubles in the query and it works!

 

Thanks for your help Ignace, I really appreciate it