Thank you so much for your prompt guidance. I update everything what you provided me. Although its a little difficult to follow each and every code what you did for me but I really appreciate for your instant help. With a very few changes i got able to connect as didn’t get any connectivity error. but when i insert data i receive following error and it doesn’t insert data in mysql and doesn’t echo as well: Please bear me i know i am very close to finish it. THANKS
Error:
Mandatory field ‘Driver’ is missing
Mandatory field ‘Time Zone’ is missing
select driver is using another table(sorry forgot to tell u)
now selectdriver.php is but its working…
<?php
//selectdriver.php
include 'connectdb.php';
$sqldriver = "SELECT DISTINCT(name) FROM drivers";
$name = array();
$querydriver = $dbc->prepare($sqldriver);
$querydriver->execute();
while($row = $querydriver->fetch(PDO::FETCH_ASSOC)){
$name[] = $row['name'];
}
?>
**now insertdata.php is:**
<?php include ('selectdriver.php'); ?>
<?php
//POST processing
if($_SERVER['REQUEST_METHOD'] == 'POST'):
/*
echo "<pre>";
print_r($_POST);
echo "</pre>";
*/
$erArr = array();
$required = array(
'unit_id' => 'Car Carrier'
, 'type' => 'Car Carrier Type'
, 'fuel_date' => 'Date'
, 'qty' => 'Quantity'
, 'odomtr' => 'Odometer'
, 'rate' => 'Fuel Rate'
, 'driver_name' => 'Driver'
, 'filled_by' => 'Filled By'
, 'fillup_time' => 'Time'
, 'time_zone' => 'Time Zone');
foreach($required as $field => $fieldlabel):
if(!isset($_POST[$field]) || empty($_POST[$field])):
$erArr[] ="Mandatory field '" . $fieldlabel . "' is missing <br/>";
endif;
endforeach;
if(empty($erArr)):
$unitid = $_POST['unit_id'];
$cctype = isset($_POST['type']) ? $_POST['type'] : '';
$fuelqty = $_POST['qty'];
$odometer = $_POST['odomtr'];
$rate = $_POST['rate'];
$name = $_POST['driver_name'];
$filledby = $_POST['filled_by'];
$filltime = $_POST['fillup_time'];
$timezone = isset($_POST['time_zone']) ? $_POST['time_zone'] : '';
$datepicker = $_POST['fuel_date'];
// PDO version
$sql = "INSERT INTO fuel_use_tbl (unit_id, type, fuel_date, qty, odomtr, rate, driver_name,filled_by, fillup_time, time_zone, `Prev.Odomtr`, mileage_diff, fuel_ratio)
SELECT :newunit_id, :type, :fuel_date, :qty, :odomtr, :rate, :driver_name, :filled_by, :fillup_time, :time_zone, odomtr, ABS(:odometernew - odomtr), ABS(ABS(:odometerratio - odomtr) / :qtyratio) FROM fuel_use_tbl
WHERE unit_id = :unit_id
ORDER BY fu_id DESC LIMIT 1";
$query = $dbc->prepare($sql);
$query->bindParam(":newunit_id", $unitid);
$query->bindParam(":unit_id", $unitid);
$query->bindParam(":type", $cctype);
$query->bindParam(":fuel_date", $datepicker);
$query->bindParam(":qty", $fuelqty);
$query->bindParam(":qtyratio", $fuelqty);
$query->bindParam(":odomtr", $odometer);
$query->bindParam(":odometernew", $odometer);
$query->bindParam(":odometerratio", $odometer);
$query->bindParam(":rate", $rate);
$query->bindParam(":driver_name", $name);
$query->bindParam(":filled_by", $filledby);
$query->bindParam(":fillup_time", $filltime);
$query->bindParam(":time_zone", $timezone);
$query->execute();
$num = $query->rowCount();
//Above will fail if unit_id not found.
//We use $num to check and use regular insert
if($num === 0):
//Default values for Prev.Odomtr,mileage_diff,fuel_ratio
$PrevOdomtr = "0";
$mileage_diff = "0";
$fuel_ratio = "0";
$sql2 = "INSERT INTO fuel_use_tbl (unit_id, type, fuel_date, qty, odomtr, rate, driver_name, filled_by, fillup_time, time_zone, `Prev.Odomtr`, mileage_diff, fuel_ratio)
VALUES(:newunit_id, :type, :fuel_date, :qty, :odomtr, :rate, :driver_name, :filled_by, :fillup_time, :time_zone, :PrevOdomtr, :mileage_diff, :fuel_ratio)";
$query2 = $dbc->prepare($sql2);
$query2->bindParam(":newunit_id", $unitid);
$query2->bindParam(":type", $cctype);
$query2->bindParam(":fuel_date", $datepicker);
$query2->bindParam(":qty", $fuelqty);
$query2->bindParam(":odomtr", $odometer);
$query2->bindParam(":rate", $rate);
$query2->bindParam(":driver_name", $name);
$query2->bindParam(":filled_by", $filledby);
$query2->bindParam(":fillup_time", $filltime);
$query2->bindParam(":time_zone", $timezone);
$query2->bindParam(":PrevOdomtr", $PrevOdomtr);
$query2->bindParam(":mileage_diff", $mileage_diff);
$query2->bindParam(":fuel_ratio", $fuel_ratio);
$query2->execute();
endif;
endif;
endif;
//Grab display data
$data = array();
$sqldd="SELECT
fu_id
, unit_id
, type
, fuel_date
, odomtr
, qty
, rate
, driver_name
, filled_by
, fillup_time
, time_zone
, sum(qty * rate ) as cost
, `Prev.Odomtr`
, mileage_diff
, fuel_ratio
FROM fuel_use_tbl
GROUP BY fu_id";
$querydd = $dbc->prepare($sqldd);
$querydd->execute();
while($row = $querydd->fetch(PDO::FETCH_ASSOC)){
$data[] = $row;
}
/*
echo "<pre>";
print_r($data);
echo "</pre>";
*/
//After processing and queries add header
include 'header.html';
?>
<?php
// echo any errors
if(!empty($erArr)):
foreach($erArr as $error):
echo $error;
endforeach;
endif;
?>
<div class="regisform">
<form name="regisform" action="insertdata.php" method="post">
<h3> ADD RECORDS</h3>
<label for="unit_id">Select Car Carrier:</label>
<input type="text" name="unit_id" id="unit_id" value="" />
<label for="type">Select C C Type :</label>
<select name="type" id="type">
<option value="">-</option>
<option value="Long">Long</option>
<option value="Single">Single</option>
<option value="Rigid">Rigid</option>
</select>
<label for="fuel_date">Select Fuel Date:</label>
<input type="date" name="fuel_date" id="fuel_date" value="" />
<label for="qty">Fuel Qty:</label>
<input type="text" name="qty" id="qty" value="" />
<label for="odomtr">Odometer :</label>
<input type="text" name="odomtr" id="odomtr" value="" />
<label for="rate">Fuel Rate :</label>
<input type="number" name="rate" id="rate" value="1.7" />
<label for="name">Select Driver :</label>
<select name="name" id="name">
<?php
foreach ($name as $value):
echo '<option value="' . $value . '">' . $value . '</option>';
endforeach;
?>
</select>
<label for="filled_by">Filled By :</label>
<input type="text" name="filled_by" id="filled_by" value="" />
<label for="fillup_time">Filled up Time :</label>
<input type="text" name="fillup_time" id="fillup_time" value="" />
<label for="timezone">Select TimeZone:</label>
<select name="timezone" id="timezone">
<option value="AM">AM</option>
<option value="PM">PM</option>
</select>
<input type="submit" class="submit" value="Save" name="Save" />
</form>
</div>
<table class="display">
<tr>
<th colspan="15">Mileage Report</th>
</tr>
<tr>
<td class="subheading">Fu_id</td>
<td class="subheading">CC No</td>
<td class="subheading">CC Type</td>
<td class="subheading">Fuel Date</td>
<td class="subheading">Odometer</td>
<td class="subheading">Qty/Liters</td>
<td class="subheading">Rate/RM</td>
<td class="subheading">Cost/RM</td>
<td class="subheading">Driver's Name</td>
<td class="subheading">Filled by</td>
<td class="subheading">Fillup Time</td>
<td class="subheading">Time Zone</td>
<td class="subheading">Prev/Odomtr</td>
<td class="subheading">Mile/Diff</td>
<td class="subheading">MPL</td>
</tr>
<?php
foreach($data as $row):
echo '<tr>
<td>' . $row['fu_id'] . '</td>
<td>' . $row['unit_id'] . '</td>
<td>' . $row['type'] . '</td>
<td>' . $row['fuel_date'] . '</td>
<td>' . $row['odomtr'] . '</td>
<td>' . $row['qty'] . '</td>
<td>' . $row['rate'] . '</td>
<td>' . $row['cost'] . '</td>
<td>' . $row['driver_name'] . '</td>
<td>' . $row['filled_by'] . '</td>
<td>' . $row['fillup_time'] . '</td>
<td>' . $row['time_zone'] . '</td>
<td>' . $row['Prev.Odomtr'] . '</td>
<td>' . $row['mileage_diff'] . '</td>
<td>' . $row['fuel_ratio'] . '</td>
</tr>'."\r";
endforeach;
?>
</table>
EDIT
This post has been reformatted by enclosing the code block in 3 backticks
```
on their own lines.
