0

I am having trouble getting an sql query to go through to the database. I am checking this with an if ($sql) {echo "success";} else {echo "error";} and I keep getting "error". The code preceding my query all seems to work. If the query syntax is correct, what could be potential trip-ups that I have overlooked? Here is the code in question:

$sql = mysql_query(
    "INSERT INTO monthly_balances (
        bal_id,
        account_id,
        bal_date,

        bal_month_sales,
        bal_month_returns,
        bal_month_net,
        bal_month_coop,

        bal_YTD_sales,
        bal_YTD_returns,
        bal_YTD_net,

        bal_lastYTD_sales,
        bal_lastYTD_returns,
        bal_lastYTD_net
    ) 
    VALUES (
        DEFAULT,
        '$account_id',
        '$bal_date',

        '$bal_month_sales',
        '$bal_month_returns',
        '$bal_month_net',
        '$bal_month_coop',

        '$bal_YTD_sales',
        '$bal_YTD_returns',
        '$bal_YTD_net',

        '$bal_lastYTD_sales',
        '$bal_lastYTD_returns',
        '$bal_lastYTD_net'
    )
 ");


if($sql) {
    echo 'success';
}
else {
    echo 'error';
}

Thank you

brietsparks
  • 4,776
  • 8
  • 35
  • 69
  • 2
    Replace `error` with `mysql_error()` – AyB Apr 15 '14 at 17:22
  • did you try echoing the error. also please update your code to use mysqli or pdo! – Logan Murphy Apr 15 '14 at 17:22
  • I have echoed the error. It reads: `Cannot add or update a child row: a foreign key constraint fails (`coop`.`monthly_balances`, CONSTRAINT `monthly_balances_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `entries` (`account_id`))` – brietsparks Apr 15 '14 at 17:28

1 Answers1

2

From the docs:

MySQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.

So basically, the value of account_id that you are entering in monthly_balances is not present in account_id of table entries.

When a column is declared as foreign key in reference to some column in another table, the foreign column cannot contain a value before the parent column has it.

Note:

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.

Community
  • 1
  • 1
AyB
  • 11,609
  • 4
  • 32
  • 47
  • What you are saying makes sense to me, however, I have set the foreign key to `account_id` of table `accounts` in mysql, which exists. So how is this possible? – brietsparks Apr 15 '14 at 17:46
  • 1
    @bsapaka From the error it seems to be referring to the `entries` table. You should probably re-check it. – AyB Apr 15 '14 at 17:48
  • I did recheck it. Turns out I have dislexia Thanks for your help. – brietsparks Apr 15 '14 at 17:53