Value not getting inserted in mysql database

Question

I've made this small little code to track my daily expenses and earnings from various sources. Anyway, the first query increments the record id + adds the time. But, the second query doesn't seems to be doing anything. Any ideas? Here's the code :-

<?php
error_reporting(E_ALL);
include('dbconnector.php');
if(isset($_REQUEST['action']))
{
    switch($_REQUEST['action'])
    {
        case 'inpute':
        $amt=(isset($_POST['amt'])) ? $_POST['amt'] : '';
        $desc=(isset($_POST['desc'])) ? $_POST['desc'] : '';
        if(!empty($amt) && !empty($desc))
        {
            $query = 'insert into tb1(record_id,record_date) values (NULL,now())';
            $result = mysql_query($query, $db);
            $query2='insert into input(record_input_id,amt_in,in_desc) values (NULL,"' . $amt . '", "' . $desc . '"';
            $result2=mysql_query($query2, $db);
            if($result && $result2)
            {
                header('Location: index.php');
            }
            else
            {
                echo "Error";
            }
        }
        else
        {
            echo "value cannot be null";
            header('Refresh:3;URL = index.php');
        }
        break;
        case 'outpute':
        $amt=(isset($_POST['amt'])) ? $_POST['amt'] : '';
        $desc=(isset($_POST['desc'])) ? $_POST['desc'] : '';
        if(!empty($amt) && !empty($desc))
        {
            $query = 'insert into tb1(record_id,record_date) values (NULL,now())';
            $result = mysql_query($query, $db);
            $query2='insert into output(record_output_id,amt_out,out_desc) values(NULL,$amt, "' . $desc . '"';
            $result2=mysql_query($query, $db);
            if($result && $result2)
            {
                header('Location: index.php');
            }
            else
            {
                echo "Error";
            }
        }
        else
        {
            echo "value cannot be null";
            header('Refresh:3;URL = index.php');
        }
        break;
        default:
        header('Location: index.php');
    }
}
?>

you have million of insert queries , just post your problem code. — echo_Me, Sep 20 '13 at 20:57

score 0 · Answer 1 · edited May 25 '19 at 14:17

You're posting the same query twice!

Use following insert statements: (I've included Victor's answer - thanks!)

$query = 'insert into tb1(record_id,record_date) values (NULL,now())';
$result = mysql_query($query, $db);
$query2='insert into output(record_output_id,amt_out,out_desc) values(NULL,$amt, "' . $desc . '")';
$result2=mysql_query($query2, $db);

For $result2 you originally executed $query as well, but that was already inserted...

And PLEASE consider using prepared statements... As well as mysqli_*-functions or PDO.

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

There seems to be a few (potential) issues with your code.

Firstly, you miss the closing bracket ) on $query2 - both times.

Secondly, you need to place $amt in quotation marks when referencing in the query. Otherwise when it is empty (i.e. $amt == '') the query will fail.

Third, you reference $query in two mysql_query calls in the second case.

Fourth, while not an issue per-se, I'm assuming that all of the record_id and record_*_id fields are set to auto increment? If this is the case you can omit them from the query entirely (as opposed to setting them to NULL)

Your queries should end up looking something like:

$query2="insert into input(amt_in, in_desc) values ('$amt', '$desc')";

$query2="insert into output(amt_out,out_desc) values('$amt', '$desc')";

More info

If the above doesn't work try inserting the following after your problem mysql_query calls:

echo $query, "<br>"; //Use $query or $query2 depending on what one you called!
echo mysql_error(), "<br>";

This will show you any errors that were thrown and also the used SQL statement which can help massively when debugging!

For example, in your queries when $amt is empty your query will look something like:

insert into output(record_output_id,amt_out,out_desc) values(NULL,, "descending"

Which won't work (as stated secondly earlier) see the two commas with no value - where $amt should be?

Value not getting inserted in mysql database

2 Answers2

More info