PHP mysql_query syntax error?

Question

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

did you try echoing the error. also please update your code to use mysqli or pdo! — Logan Murphy
– Logan Murphy, Commented Apr 15, 2014 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
– brietsparks, Commented Apr 15, 2014 at 17:28

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 15, 2014 at 17:36

AyB

11.6k4 gold badges34 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

brietsparks Over a year ago

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?

AyB Over a year ago

@bsapaka From the error it seems to be referring to the entries table. You should probably re-check it.

brietsparks Over a year ago

I did recheck it. Turns out I have dislexia Thanks for your help.

Collectives™ on Stack Overflow

PHP mysql_query syntax error?

1 Answer 1

Note:

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Note:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related