SELECT the last value from column is always null or false SQL

Question

I'm trying to get the last value of 3 columns in SQL, I tried with many queries but all return null or false,

The database works doing the updates and inserts, but I can not get these three values and match to php variables

    $sub_total_1 = mysql_query('SELECT "total_flag1" FROM balance ORDER BY my_date DESC LIMIT 1');
    $total_1 = $val_1 + $sub_total_1;

    $sub_total_2 = mysql_query('SELECT "total_draw" FROM balance ORDER BY my_date DESC LIMIT 1');
    $total_2 = $val_2 + $sub_total_2;

    $sub_total_3 = mysql_query('SELECT "total_flag2" FROM balance ORDER BY my_date DESC LIMIT 1');
    $total_3 = $val_3 + $sub_total_3;

Here is the complete code for PHP and screen of how I made this database, thanks.

<?php

$porcentaje_ofrecido = 1.7;

$val_1 = floatval(addslashes($_POST['first']));
$val_2 = floatval(addslashes($_POST['second']));
$val_3 = floatval(addslashes($_POST['third']));

include_once('config.php');

$mysqlConnection = mysql_connect($server, $username, $password);
if (!$mysqlConnection) {
    $response = array(
        "codigo" => "nok",
        "mensaje" => "<h3>Hubo un error en la base de datos</h3>
                    <p>Intenta más tarde</p>"
    );
} else {
    mysql_select_db($database, $mysqlConnection);
    $date = date_create();
    $date = date_format($date, 'Y-m-d H:i:s');

    if (is_numeric($val_1) && is_numeric($val_2) && is_numeric($val_3)) {


        $sub_total_1 = mysql_query('SELECT "total_flag1" FROM balance ORDER BY my_date DESC LIMIT 1');
        $total_1 = $val_1 + $sub_total_1;

        $sub_total_2 = mysql_query('SELECT "total_draw" FROM balance ORDER BY my_date DESC LIMIT 1');
        $total_2 = $val_2 + $sub_total_2;

        $sub_total_3 = mysql_query('SELECT "total_flag2" FROM balance ORDER BY my_date DESC LIMIT 1');
        $total_3 = $val_3 + $sub_total_3;

        $sub_total = $sub_total_1 + $sub_total_2 + $sub_total_3;
        $total = $total_1 + $total_2 + $total_3;

        $case1 = ($total_1 * $porcentaje_ofrecido * (-1)) + $total_2 + $total_3;
        $case2 = ($total_2 * $porcentaje_ofrecido * (-1)) + $total_1 + $total_3;
        $case3 = ($total_3 * $porcentaje_ofrecido * (-1)) + $total_1 + $total_2;

        if ($case1 > 0 && $case2 > 0 && $case3 > 0) {
            $sql = "INSERT into balance (id,flag1,draw,flag2,total_flag1,total_draw,total_flag2,total,succes,date) VALUES ('','$val_1','$val_2','$val_3','$total_1','$total_2','$total_3','$total',1,'$date')";
            $res = mysql_query($sql);
            if ($res) {
                $sql2 = "UPDATE totales SET sub_total1= $total_1 ,sub_total2= $total_2 ,sub_total3= $total_3 ,total=$total";
                $res2 = mysql_query($sql2);
                if ($res2) {
                    $response = array(
                        "codigo" => "ok",
                        "mensaje" => "<h2>Hemos guardado tu registro</h2>
                          <p>Muchas gracias.</p>"
                    );
                } else {
                    $response = array(
                        "codigo" => "nok",
                        "mensaje" => "<h2>Hubo un error con el registro</h2>
                <p>Lamentablemente hemos tenido problemas al registrar tus datos</p>
                >"
                    );
                }
            } else {
                $response = array(
                    "codigo" => "nok",
                    "mensaje" => "<h2>Hubo un error con el registro</h2>"
                );
            }
        } else {
            $response = array(
                "codigo" => "nok",
                "mensaje" => array('14', $sub_total_1, $val_1, $total_1, $porcentaje_ofrecido, $total_2, $total_3)
            );
        }
    } else {
        $response = array(
            "codigo" => "nok",
            "mensaje" => "<h2>Hubo un error x.x</h2>"
        );
    }
}
echo json_encode($response);
?>

You should take a look at [http://php.net/manual/en/function.mysql-query.php], and discover two things: 1) "This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used." 2) "Return Values: For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error." — Tom, Jul 03 '14 at 07:29
dont use mysql_* instead use mysqli_* check this http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Sudheer, Jul 03 '14 at 07:30

score 1 · Answer 1 · answered Jul 03 '14 at 07:35

To get «sub_total_N» values You are using mysql_query and this function returns "For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error."

You must fetch resource to get values.

NOTE: Use mysqli_query instead of mysql_query, it's deprecated.

score 0 · Accepted Answer · answered Jul 03 '14 at 07:31

Putting double quotes around a name makes it a literal string, not a column name. Get rid of the quotes. Also, there's no reason to do 3 different queries to get fields of the same row. And you're forgetting to call mysql_fetch_assoc() to actually get the rows from the result.

$result = mysql_query("SELECT total_flag1, total_flag2, total_draw
                       FROM balance 
                       ORDER BY my_date DESC
                       LIMIT 1") or die(mysql_error());
$row = mysql_fetch_assoc($result);
$sub_total_1 = $row['total_flag1'];
$sub_total_2 = $row['total_draw'];
$sub_total_3 = $row['total_flag2'];

SELECT the last value from column is always null or false SQL

2 Answers2