Two Queries and a variable from one to the other

[Aero77]

I need two queries cause I don't know how to put them into one. Problem here is I need to know the field Liters from the database in the first query to subtract it from the second query to be able to calculate the average fuel consumption of the chosen range.

 

This is my code, hope you can help

function getContent() {
	include 'connection.php';

$dx = date('m', strtotime('today - 30 days'));	

	if (!isset($_POST["month_select"])) {
		$month = $dx;
		$year = "2014";
	}
	else {
		$month = $_POST["month_select"];
		$year = $_POST["year_select"];
	}

//last fuel fill value 
		$query2 = "SELECT d.liter as FirstFill 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 ORDER BY d.id ASC LIMIT 1";

		$sql2=$oDB->prepare($query2);
		$sql2->execute();
		$row2 = $sql2->fetchAll();
    return $row2;
	
    $FirstFill = $row2['FirstFill'];
	
		$query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0
GROUP
    BY m.id ORDER BY AvgFuel ASC";
	
		$sql=$oDB->prepare($query);
		$sql->execute();
		$row = $sql->fetchAll();
    return $row;

	}

Edited May 23, 2014 by Aero77

[Aero77]

I'm trying to make a while loop with an odbc sql query, but not sure how to do it. Hopefully someone here can help me

 

I've got this code

$query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id
GROUP
    BY m.id ORDER BY AvgFuel ASC";
	
		$sql=$oDB->prepare($query);
		$sql->execute();
		$row = $sql->fetchAll();
    return $row;


while($row = odbc_fetch_array($sql) ) { 

	$nid = $row['id'];
}

but it just gives me a white page, nothing more.

 

Thanks for reading!

[Jacques1]

The first rule of PHP debugging: When you get The White Screen Of Death, that means you need to check your error reporting.

 

But your code really doesn't make any sense at all:

• You pretend to use a prepared statement, but then you somehow change your mind and just drop all values straight into the query string. The whole purpose of a prepared statement is to not use this extremely insecure practice and instead pass the values through parameters.
• You use PDO(?) to fetch all rows of a query and return them. But then you somehow change your mind and decide to use an entirely different database interface to fetch the rows. It's too late for that, the function is already terminated. And you can't just switch from PDO to the odbc_* functions in the middle of it, anyway.
• And on top of that, you don't even fetch the result set in your odbc_* part (which doesn't get executed). You just keep overwriting the same variable.

It seems to don't really understand what all those methods/functions do and which API they belong to. You seem to have copied and pasted code from two entirely different tutorials and then somehow tried to merge everything it into one application. This does not work.

 

The first thing you need to do is make a definite decision about the database interface. You cannot mix and match. It's either PDO or the odbc_* functions, not both. Then you need to learn the interface you've chosen. If you choose PDO, I recommend this tutorial.

[Aero77]

Im doing a mistake here. Its PDO I wanna use. Here is the complete code that works

<?php
include 'template/header.php';
echo "<div class=\"bluebox\">";

function createMonths($id='month_select', $selected=null)
    {
        /*** array of months ***/
        $months = array(
                1=>'January',
                2=>'February',
                3=>'March',
                4=>'April',
                5=>'May',
                6=>'June',
                7=>'July',
                8=>'August',
                9=>'September',
                10=>'October',
                11=>'November',
                12=>'December');

        /*** current month ***/
        $selected = is_null($selected) ? date('m') : $selected;

        $select = '<select name="'.$id.'" id="'.$id.'">'."\n";
        foreach($months as $key=>$mon)
        {
            $select .= "<option value=\"$key\"";
            $select .= ($key==$selected) ? ' selected="selected"' : '';
            $select .= ">$mon</option>\n";
        }
        $select .= '</select>';
        return $select;
    }

	
//start the blue background and the user bar on top of the page
echo "<div class=\"mtab\"><table width=\"100%\" cellspacing=\"0\" cellpadding=\"5\" border=\"0\">";
	echo "<tbody>";

function getContent() {
	include 'connection.php';

$dx = date('m', strtotime('today - 30 days'));	

	if (!isset($_POST["month_select"])) {
		$month = $dx;
		$year = "2014";
	}
	else {
		$month = $_POST["month_select"];
		$year = $_POST["year_select"];
	}

//START >> "last fuel fill value"
$dieselinfo = mysqli_query($con,"SELECT d.vehicle_id, d.liter FROM diesel AS d 
	LEFT OUTER 
		JOIN members AS m 
			ON m.id = d.userid 
			WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id 
		GROUP BY d.vehicle_id ORDER BY d.id DESC");

	while($data = mysqli_fetch_array($dieselinfo)) {
		$FirstFill = $data['liter'];
	}
//END >> "last fuel fill value"
	
		$query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id
GROUP
    BY m.id ORDER BY AvgFuel ASC";
	
		$sql=$oDB->prepare($query);
		$sql->execute();
		$row = $sql->fetchAll();
    return $row;

	}		
//	error_reporting(E_ALL); ini_set('display_errors', 1);
		//Row Colors setting
	$color1 = "#E1EEf4";
	$color2 = "#FFFFFF";
	$row_count = 0;
$data = getContent();
foreach($data as $row) {
	$nid = $row['id'];
	$username = $row['username'];	
	$AvgFuel = $row['AvgFuel']; 


	$row_color = ($row_count % 2) ? $color1 : $color2;
	 print "<tr bgcolor=\"$row_color\"><td><a rel=external href=\"user.php?profile=".$nid."\"><div class=\"buttonlink\"><img src=\"icons/user.png\" /> ".$username."</div></a></td><td>".round($AvgFuel, 2)."</td></tr>";
	$row_count++;
	
	 }
?>
</tbody></table>
<br />
<?php 
	echo "<form method=\"post\" action=\"dscore.php\">";
	echo createMonths();
echo "<select style=\"margin-top:5px;\" name=\"year_select\">
  <option value=\"2014\">2014</option>
  <option value=\"2013\">2013</option>
  <option value=\"2012\">2012</option>
</select>";
	echo "<input type=\"submit\" value=\"Send\" /></form>";
?>
</div>

</div>
</body>
</html>

My problem with this code is that I would like to subtract the first amount of fuel for each user on each average calculation to make the average correct. The only way I could think of was to have two queries as you can see. In my first post I was trying to remove the function part, but thats not really what I need help with. Its more about making the queries and calculations right.

Edited June 7, 2014 by Aero77

[Jacques1]

Im doing a mistake here. Its PDO I wanna use.

 

Then why do you use MySQLi? That's yet another database interface.

 

Sorry, but the whole architecture just doesn't make sense. You're working with one database, right? Then why do you try to access it in three different ways at the same time?

 

Before we jump into the calculations details, it's really more important to get the basics right.

[Aero77]

I'm trying to make an sql query to calculate the average fuel usage and make a high score list. To calculate correctly I need to do some calculations. Like this LITERS - "the first tanking" / KM * 10 = L/10km

 

Here is my non-working query. The problem is to find the first tanking for each user and subtract that from the total amount of liters in a given date range. Like all tankings in June - the first tanking.

  Hope you understand what I mean

 

The query

//START
foreach($oDB->query("SELECT d.vehicle_id, d.liter FROM diesel AS d 
	LEFT OUTER 
		JOIN members AS m 
			ON m.id = d.userid 
			WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id GROUP BY d.userid") as $row) {
	 $FirstFill = $row['liter'];
	  global $FirstFill;
}
//END
	
		$query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id
GROUP
    BY m.id ORDER BY AvgFuel ASC";
	
		$sql=$oDB->prepare($query);
		$sql->execute();
		$row = $sql->fetchAll();
    return $row;

This one kind of works, but the $FirstFill variable gets wrong cause it will subtract the same amount of gas on each user which is wrong. I would need some help to get this right, and possible all in one query. I was messing around with UNION without luck

 

Thanks for reading!

Edited June 7, 2014 by Aero77

[mac_gyver]

this is at least the third thread you have started for this problem, which i have now merged together. please stick to one thread for the same problem.