Cannot execute sql INSERT query (mysql_query) in php script. PHP/MySQL — Time Sensitive

Question

UPDATE: NOW RESOLVED - Thanks everyone!

Fix: I had a column named "referred_by" and in my code it's called "referred_by_id" - so it was trying to INSERT to a column that didn't exist -- once I fixed this, it decided to work!

I have limited time left to work on this project. The clock is ticking.

I'm trying to INSERT $php_variables into a TABLE called "clients".

I've been trying for hours to get this script to work, and I got it to work once, but then I realized I forgot a field, so I had to add another column to the TABLE and when I updated the script it stopped working. I reverted by but now it's still not working and I'm just frustrating myself too much.

<?php

error_reporting(E_ALL);
ini_set("display_errors", 1);

if (!isset($_COOKIE["user"]))
{
    header ("Location: ./login.php");
}

else
{
    include ("./source.php");
    echo $doctype;
}

$birthday = $birth_year . "-" . $birth_month . "-" . $birth_day;
$join_date = date("Y-m-d");

$error_type = 0;

$link = mysql_connect("SERVER", "USERNAME", "PASSWORD");

if (!$link)
{
    $error = "Cannot connect to MySQL.";
    $error_type = 1;
}

$select_db = mysql_select_db("DATABASE", $link);

if (!$select_db)
{
    $error = "Cannot connect to Database.";
    $error_type = 2;
}

if ($referred_by != "")
{
    $result = mysql_query("
    SELECT id FROM clients WHERE referral_code = $referred_by
    ");

    if (!$result)
    {
        $error = "Cannot find referral.";
        $error_type = 3;
    }

    while ($row = mysql_fetch_array($result))
    {
        $referred_by_id = $row['id'];
    }
}

else
{
    $referred_by_id = 0;
}

$first_name = mysql_real_escape_string($_POST['first_name']);
$last_name = mysql_real_escape_string($_POST['last_name']);
$birth_month = mysql_real_escape_string($_POST['birth_month']);
$birth_day = mysql_real_escape_string($_POST['birth_day']);
$birth_year = mysql_real_escape_string($_POST['birth_year']);
$email = mysql_real_escape_string($_POST['email']);
$address = mysql_real_escape_string($_POST['address']);
$city = mysql_real_escape_string($_POST['city']);
$state = mysql_real_escape_string($_POST['state']);
$zip_code = mysql_real_escape_string($_POST['zip_code']);
$phone_home = mysql_real_escape_string($_POST['phone_home']);
$phone_cell = mysql_real_escape_string($_POST['phone_cell']);
$referral_code = mysql_real_escape_string($_POST['referral_code']);
$referred_by = mysql_real_escape_string($_POST['referred_by']);
$organization = mysql_real_escape_string($_POST['organization']);
$gov_type = mysql_real_escape_string($_POST['gov_type']);
$gov_code = mysql_real_escape_string($_POST['gov_code']);

$test_query = mysql_query
("
INSERT INTO clients (first_name, last_name, birthday, join_date, email, address, city, state, zip_code,
phone_home, phone_cell, referral_code, referred_by_id, organization, gov_type, gov_code)
VALUES ('".$first_name."', '".$last_name."', '".$birthday."', '".$join_date."', '".$email."', '".$address."', '".$city."', '".$state."', '".$zip_code."',
'".$phone_home."', '".$phone_cell."', '".$referral_code."', '".$referred_by_id."', '".$organization."', '".$gov_type."', '".$gov_code."')
");

if (!$test_query)
{
    die(mysql_error($link));
}

if ($error_type > 0)
{
    $title_name = "Error";
}

if ($error_type == 0)
{
    $title_name = "Success";
}

?>


<html>
    <head>
        <title><?php echo $title . " - " . $title_name; ?></title>
        <?php echo $meta; ?>
        <?php echo $style; ?>
    </head>
    <body>
        <?php echo $logo; ?>
        <?php echo $sublogo; ?>
        <?php echo $nav; ?>
        <div id="content">
            <div id="main">

                <span class="event_title"><?php echo $title_name; ?></span><br><br>

                <?php

                if ($error_type == 0)
                {
                    echo "Client was added to the database successfully.";
                }

                else
                {
                    echo $error;
                }

                ?>

            </div>
            <?php echo $copyright ?>
        </div>
    </body>
</html>

please output an error message with mysql_query($query) or die(mysql_error()); — Headshota, Aug 19 '11 at 5:30
just an advice: use parametrized queries, otherwise you will get into much more trouble (SQL Injection, invalid SQL, etc). — Bernhard Kircher, Aug 19 '11 at 5:34
This isn't a public page so I don't really have to worry about injection :/ — tactzer0, Aug 19 '11 at 5:36
Or move your connection statements above the mysql_real_escape_string() calls. Also - I'm concerned about the constant 'query' error - don't see that in the code. — Doug Kress, Aug 19 '11 at 6:07

Tuga · Accepted Answer · 2011-08-19 05:52:26Z

up vote 0 down vote accepted

You've an error on line 81:

else
{
    $referred_by_id = 0;
}

I don't see an IF construct before that, make the appropriate correction and run the script again.

answered Aug 19 '11 at 5:52

Tuga
2,6231038

Fixed, updated code in question - but still doesn't work :/ – tactzer0 Aug 19 '11 at 6:08

It was this in combination with a mislabeled column - thanks Tuga! – tactzer0 Aug 19 '11 at 6:20

I'm glad I could help :) – Tuga Aug 19 '11 at 6:34

add comment

John Green · Answer 2 · 2011-08-19 05:53:44Z

Definitely not working as is. Looks you have a 500 error, since you have an else with a missing if:

else
{
    $referred_by_id = 0;
}

Otherwise, you'll need to post your DB schema.

Also, note that you're really taking the long way around with this code, which makes it difficult to read & maintain. You're also missing any sort of checks for SQL injection... you really need to pass things through mysql_real_escape_string (and really, you should use mysqli, since the mysql interface was basically deprecated years ago).

$keys = array('first_name',
    'last_name',
    'birthday', 
    'join_date', 
    'email', 
    'address', 
    'city', 
    'state', 
    'zip_code',
    'phone_home', 
    'phone_cell', 
    'referral_code', 
    'referred_by_id', 
    'organization', 
    'gov_type', 
    'gov_code');

$_REQUEST['birthdate'] = $_REQUEST['birth_year'].'-'.$_REQUEST['birth_month'].'-'.$_REQUEST['birth_day'];
$_REQUEST['join_date'] = date('Y-m-d',time());

$params = array();
foreach ($keys as $key)
{
    $params[] = mysql_real_escape_string($request[$key]);
}

$sql = 'INSERT INTO clients ('.implode(',', $keys).') ';
$sql .= ' VALUES (\''.implode('\',\'', $params).'\') ';

$params should be set as $params[] = '"'. mysql_real_escape_string($_REQUEST[$key]) .'"', I believe. — Doug Kress, Aug 19 '11 at 5:51
@Doug Kress - Thank you, that's what I get for writing code off the top of my head. : ) — John Green, Aug 19 '11 at 5:55

Doug Kress · Answer 3 · 2011-08-19 06:21:51Z

Without looking at the table structure to make sure all the fields are there, I'm going to assume it's something with the data.

Any quotes in the data will lead to problems (including SQL injection security holes). You should wrap each $_POST[] with mysql_real_escape_string(), such as:

$first_name = mysql_real_escape_string($_POST['first_name']);

EDIT: Further debugging...

As someone suggested (sorry, can't find the comment), try:

$sql = "
    INSERT INTO clients (first_name, last_name, birthday, join_date, email, address, city, state, zip_code,
    phone_home, phone_cell, referral_code, referred_by_id, organization, gov_type, gov_code)
    VALUES ('".$first_name."', '".$last_name."', '".$birthday."', '".$join_date."', '".$email."', '".$address."', '".$city."', '".$state."', '".$zip_code."',
        '".$phone_home."', '".$phone_cell."', '".$referral_code."', '".$referred_by_id."', '".$organization."', '".$gov_type."', '".$gov_code."'
    )";

// Debug:
print "<pre>". $sql ."</pre>";

mysql_query($sql);

The SQL statement should be printed out when submitting the form. Take that SQL statement and try to execute it directly in MySQL to see if it works, or if it generates an error.

I'm not too concerned about injection since it's not a public page. But I'll try this anyway if it helps with errors. I'll report back in a minute or two. — tactzer0, Aug 19 '11 at 5:43

asked	2 years ago
viewed	1724 times
active	2 years ago

Explore our sites

Cannot execute sql INSERT query (mysql_query) in php script. PHP/MySQL — Time Sensitive

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql database insert or ask your own question.

Community Bulletin

Hot Network Questions

Explore our sites

Cannot execute sql INSERT query (mysql_query) in php script. PHP/MySQL — Time Sensitive

3 Answers

Your Answer

Sign up or login

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql database insert or ask your own question.

Community Bulletin

Related

Hot Network Questions