Not able to insert a record mySQL database

[RameshPeriya]

I am not able to insert values to my mySQL db. Even though I enabled the error reporting, it doesn't show any error. Codes below.

Please help.

<?php

error_reporting(E_ALL);
ini_set('display_errors','On');

$link = mysql_connect('xxxxxx', 'xxxxx', 'xxxxxx');
if (!$link)
{
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

$sql = "INSERT INTO contact_reg (loginip, group_name, place, managerName, managerMobile, managerEmail, category, AstName, AstMobile, AstEmail) VALUES ('1.22.333', 'St. Machael', 'Dubai', 'John', '1233333', 'manager@play1.com', 'kids', 'Ronny', '050123456+', 'tester@test.com')";	

$result = mysql_query($sql);
if ($result) {
    echo 'Record has successfully inserted';
}
mysql_close($link);
?>

[Psycho]

You enabled error reporting for PHP, but you have nothing to tell you if there is a DB error and what it is.

 

 

$sql = "INSERT INTO contact_reg
          (loginip, group_name, place, managerName, managerMobile,
           managerEmail, category, AstName, AstMobile, AstEmail)
       VALUES
          ('1.22.333', 'St. Machael', 'Dubai', 'John', '1233333',
           'manager@play1.com', 'kids', 'Ronny', '050123456+', 'tester@test.com')";    
 
$result = mysql_query($sql);
if (!$result)
{
    echo 'Error running query: {$sql}<br>Error: ' . mysql_error();
}
else
{
    echo 'Record has successfully inserted';
}

[RameshPeriya]

I have figured out that problem occurs when passing variables as follows:

$sql = "INSERT INTO contact_reg
          (loginip, group_name, place, managerName, managerMobile,
           managerEmail, category, AstName, AstMobile, AstEmail)
     VALUES 
         ($loginip, $group, $place, $managerName, $managerMobile, 
        $managerEmail, $category, $AstName, $AstMobile, $AstEmail)";

Am I missing any syntax?

[Muddy_Funster]

what does the mysql_error() return for said problem?

[vinny42]

 

Am I missing any syntax?

 

Ofcourse, you are not quoting any of the strings you put into the query, and you don't escape the strings.

 

I suggest you search for a few tutorials about how to use mysql with PHP because you are making lots of simple but very frustrating mistakes.

[Psycho]

I have figured out that problem occurs when passing variables as follows:

$sql = "INSERT INTO contact_reg
          (loginip, group_name, place, managerName, managerMobile,
           managerEmail, category, AstName, AstMobile, AstEmail)
     VALUES 
         ($loginip, $group, $place, $managerName, $managerMobile, 
        $managerEmail, $category, $AstName, $AstMobile, $AstEmail)";

Am I missing any syntax?

 

I can't believe I missed that. There are requirements that some values - based upon the field type - must be enclosed in quotes. Right now, all the values are simply separated by commas - so think about what would happen if a value was Lastname, Firstname? how would the database know that that is one value and not two? So, quotes are required around all "text" type fields., but not around numeric type fields for example. I don't know the requirements for every single field type, but there is no reason you can't put quotes around values for all field types. So, it is a good practice to always use them. Also, since values can also have quotes in them you need to make sure you are escaping the values first (mysql_real_escape_string - at least until you move to mysqli_ or PDO).

$sql = "INSERT INTO contact_reg
          (loginip, group_name, place, managerName, managerMobile,
           managerEmail, category, AstName, AstMobile, AstEmail)
     VALUES
         ('$loginip', '$group', '$place', '$managerName', '$managerMobile',
          '$managerEmail', '$category', '$AstName', '$AstMobile', '$AstEmail')";

I don't know where you are defining those values, but if you are not escaping the values (as noted above) you could get a failure if any of the values have single quotes in them.

Edited September 6, 2013 by Psycho

[vinny42]

 

 So, quotes are required around all "text" type fields., but not around numeric type fields for example. 

 

MySQL allows quotes around all fieldtypes, including numeric, and because quotes add a little bit more security it's better to allways quote all values in MySQL (and postgresql) queries.

I've seen people build elaborate routines that were supposed to workout if the value should be quoted, only to find out later that the database actually didn' t care :-)

[Barand]

Quote text and date values. Do not quote numeric values. MySQL won't error if you quote numbers but you give it the overhead of converting strings to numeric types.

[vinny42]

 

Do not quote numeric values. MySQL won't error if you quote numbers but you give it the overhead of converting strings to numeric types.

 

Do you have a link to some documentation about that? Given that the quotes in an SQL string merely indicate where the content begins and ends I can't help but think that there is no measurable difference in performance.

[mac_gyver]

the following is from the Cast Functions and Operators section of the mysql documentation -

 

If you are using a string in an arithmetic operation, this is converted to a floating-point number.

 

 

this is of course is really bad if the field is a decimal data type and you are trying to avoid floating point conversion errors in the first place.

[mikosiko]

also:

http://dev.mysql.com/doc/refman/5.5/en/insert.html

 

"You can specify an expression expr to provide a column value. This might involve type conversion if the type of the expression does not match the type of the column, and conversion of a given value can result in different inserted values depending on the data type. For example, inserting the string '1999.0e-2' into an INT, FLOAT,DECIMAL(10,6), or YEAR column results in the values 1999, 19.9921, 19.992100, and 1999 being inserted, respectively. The reason the value stored in the INT and YEAR columns is 1999 is that the string-to-integer conversion looks only at as much of the initial part of the string as may be considered a valid integer or year. For the floating-point and fixed-point columns, the string-to-floating-point conversion considers the entire string a valid floating-point value."

 

[vinny42]

@mac_gyver: I was hoping for something more substantial than a possible floatingpoint error when one fails to use CAST(). :-)

 

@mikosiko: That deals with mismachting values, not about the difference in speed when processing quoted values vs non-quoted values.

 

Either way, this discussion should perhaps be a separate thread...