Get 2 variables in sql and subtract them in PHP

Question

I have multiple rows with data, some with data in mysql table kjoring and some that don't. I am able to echo both $km1 and $km2 correctly, but I am unable to subtract them to find the result and echo $km0

$sqlq = "SELECT kjoring FROM oko WHERE kjoring!='' ORDER BY logdate DESC LIMIT 2";

    if ( array_key_exists( 'field' , $out ) ) {
    $sqlq = $sqlq . " where field = '" . $out['field'] . "'" ;
  }
  $conn = new mysqli($servername, $username, $password, $dbname);

  $result = mysqli_query( $conn , $sqlq );

  if ( $result->num_rows  > 0 ) {
  while($row = mysqli_fetch_array($result)) {

  $km1 = $row[0]; 
  $km2 = $row[1];

$km0 = $km1 - $km2 ;  
  }
}

echo $km0 ;

This will help you I think: https://stackoverflow.com/questions/8003943/sql-select-specific-column-from-specific-row — Jacob H, Jan 23 '18 at 18:03
Actually not. I can display the SQL data but I can't get them to stick as variables. — Bergum, Jan 23 '18 at 18:13

rollstuhlfahrer · Answer 1 · 2018-01-24T13:38:50.430

First things first: Little Bobby says your script is at risk for SQL Injection Attacks. Learn about prepared statements for MySQLi. Even escaping the string is not safe!

// $sqlq = 'SELECT ... FROM ... ORDER BY ... LIMIT'
if ( array_key_exists( 'field' , $out ) ) {
    $sqlq = $sqlq . " where field = '" . $out['field'] . "'" ;
}

When you add your WHERE conditions after the LIMIT there WILL be a syntax error returned by your DB. You need to add the WHERE condition after the FROM and in front of the ORDER BY.

$sqlq = 'SELECT ... FROM ... ';    
if ( array_key_exists( 'field' , $out ) ) {
    $sqlq = $sqlq . " where field = '" . $out['field'] . "'" ;
}
$sqlq .= 'ORDER BY ... LIMIT ...';

Now to the 2 results:

while($row = mysqli_fetch_array($result)) {
    $km1 = $row[0]; 
    $km2 = $row[1];

When printing your row using var_dump() you will notice that there is only 1 column. You selected just one.

Using mysqli_fetch_all($result,MYSQLI_ASSOC); you will get an array containing both rows. Using this you can then do your computations.

PHP: mysqli_fetch_all()

Thank you. But if i get an error by using limit, how can i then limit the results to two? — Bergum, Jan 24 '18 at 13:32
I edited the post: You need to have the correct sequence of those parts. — rollstuhlfahrer, Jan 24 '18 at 13:39

Get 2 variables in sql and subtract them in PHP

1 Answers1