Trouble Inserting Value to Mysql database:

Question

I am attempting to put a value into a Mysql database using php. I am only at the earliest stages, to ensure it works before applying it to my project. I have looked at different ways to implement through other people's questions, and I am not sure why this isn't inserting the value.

Am I supposed to assign a particular row to insert it to? Right now, I am simply trying to insert the value of 3 into the Yield column of the database. I have checked to ensure proper capitalization of the first letter in the column name, and proper name of database table being cost_table.

When I run this, I get the printed statement, "failed to insert.." When I check the database, it is still entirely null.

<?php
//variables as connection info...//
   $LinkID=mysql_connect("$host","$username","$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");



$sql = "INSERT INTO cost_table('Yield') VALUES 3";
$check=mysql_query($sql,$LinkID);
if (!$check){
    die ("failed to insert..");
}
echo "Insert Successful!";
?>

Answer 1

Please learn the basics before trying something

Below links will be useful

• http://dev.mysql.com/doc/refman/5.6/en/insert.html
• http://www.w3schools.com/php/php_mysql_insert.asp

Correct query is

INSERT INTO cost_table(`Yield`) VALUES (3)

Answer 2

Firstly, you're using the wrong identifiers for the column you're wanting to insert into, and you've missing brackets and quotes for the values.

You may have seen a video tutorial somewhere, where the ticks looked like regular quotes; this has often been the case.

Faulty code:

INSERT INTO cost_table('Yield') VALUES 3

Which should read as, and using ticks instead of regular single quotes around the column name:

$sql = "INSERT INTO cost_table (`Yield`) VALUES ('3')";

or, if your column is indeed an int type:

$sql = "INSERT INTO cost_table (`Yield`) VALUES (3)";

If you're later going to want to use $ signs and/or decimals $3.99 then you will need to quote the values VALUES ('$3.99'), otherwise MySQL will complain about that. This type of value will require your column to be VARCHAR though; just a sidenote. For decimal minus the dollar sign, will require a decimal column type.

Having used the following, would have signaled the syntax errors:

$check=mysql_query($sql,$LinkID) or die(mysql_error());

However, you're open to SQL injection using this method and I suggest you use prepared statements before going any further with your project.

Here are a few examples:

An example of a mysqli prepared statement:

$link = mysqli_connect("localhost", "user", "password", "database");

if (mysqli_connect_errno()) {
     printf("Connect failed: %s\n", mysqli_connect_error());
     exit();
} 

    $variable_1 = "Text";
    $variable_2 = "More text";

    $stmt = $link->prepare("INSERT INTO table_name 
                            (column_1, column_2) 
                            VALUES (?,?)");

    $stmt->bind_param('ss', $variable_1, $variable_2);
    $stmt->execute();

• Sidenote: s is for strings

---

An example of a PDO prepared statement:

$dbh = new PDO('mysql:host=localhost;dbname=your_DB', $user, $pass);

$var_1 = "Text";
$var_2 = "More text";

$stmt = $dbh->prepare("INSERT INTO table_name 
                       (column_1, column_2) 
                       VALUES (:var_1,:var_2)");

$stmt->execute(array(':var_1' => $var_1, ':var_2' => $var_2));

---

References:

• mysqli with prepared statements

• PDO with prepared statements

• INSERT: http://dev.mysql.com/doc/refman/5.6/en/insert.html

• Table/column identifiers: http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html