'INSERT INTO fails with no errors

I am trying to do an INSERT INTO with PHP and Mysql.

Here is the SQL Statement, which succeeds in PHPMyadmin:

INSERT INTO purchase_orders VALUES (
    DEFAULT, 
    '501',
    '80999',
    '149179',
    'EB-0408898',
    '1',
    'Thompson Center Barrel Encore 26\" Stainless Steel Fluted 35 Whelen 4767',
    '294.43',
    '4767',
    'UPS Ground',
    '11.61',
    'LA',
    'P',
    '1',
    '264.99', 
    DEFAULT, 
    DEFAULT
)

Here is my PHP statement running the SQL:

$sql = "INSERT INTO purchase_orders VALUES (
    DEFAULT, 
    '".mysql_real_escape_string($po_num)."',
    '".mysql_real_escape_string($order_id)."',
    '".mysql_real_escape_string($product_id)."',
    '".mysql_real_escape_string($product_code)."',
    '".mysql_real_escape_string($amount)."',
    '".mysql_real_escape_string($product)."',
    '".mysql_real_escape_string($price)."',
    '".mysql_real_escape_string($mfg_code)."',
    '".mysql_real_escape_string($shipping_method)."',
    '".mysql_real_escape_string($shipping_cost)."',
    '".mysql_real_escape_string($s_state)."',
    '".mysql_real_escape_string($status)."',
    '".mysql_real_escape_string($dist_code)."',
    '".mysql_real_escape_string($dist_cost)."', 
    DEFAULT, 
    DEFAULT)";
mysql_query($sql, $dbc);
echo mysql_errno($dbc) . ": " . mysql_error($dbc). "\n";

I am completely perplexed why the insert statement is not working with PHP.

I have tried this SQL code also with the same result...

INSERT INTO purchase_orders(po_num, order_id, product_id, product_code, amount, product, price, mfg_code, shipping_method, shipping_cost, s_state, status, dist_code, dist_cost) VALUES ('501','80999','149179','EB-0408898','1','Thompson Center Barrel Encore 26\" Stainless Steel Fluted 35 Whelen 4767','294.43','4767','UPS Ground','11.61','LA','P','1','264.99')
phpmysql


Solution 1:[1]

Do not use DEFAULT in fields:

INSERT INTO purchase_orders (<colname1>,<colname2>,.....) VALUES ( '501','80999','149179','EB-0408898','1','Thompson Center Barrel Encore 26\" Stainless Steel Fluted 35 Whelen 4767','294.43','4767','UPS Ground','11.61','LA','P','1','264.99')

Instead use it like this by associating insert values to the columns.

Solution 2:[2]

If you are trying to just get it to insert the default values, you can set the values for those columns to null. Like this:

INSERT INTO purchase_orders VALUES (null, '501','80999','149179','EB-0408898','1','Thompson Center Barrel Encore 26\" Stainless Steel Fluted 35 Whelen 4767','294.43','4767','UPS Ground','11.61','LA','P','1','264.99', null, null)

The database will figure out that it needs to put defaults in the columns where you asked for null.

Solution 3:[3]

Are you able to insert anything to your DB? Does your mysql_query($sql, $dbc); portion of your code work?

There's no need to insert default values to your table, after all they are default values. Of course it's a nice reminder of your table structure but I'd leave that out.

Otherwise if you do like VIPIN JAIN suggested everything should work. Define the columns you're inserting values. You may leave the single quotes out from the query if the colum's data type is an integer.

Still it's odd you don't get any error message. Are you sure your database connection is working?

Update. Try this. Name the colums and remove the real_escape_string() function. Add it if this works or clean the variables before concatenating the query. If this doesn't work, double check the values and table structure for invalid values. Echo the values you're trying to assign to the $sql before and after the real_escape_string() function. Maybe the it has done something peculiar.

$sql = "INSERT INTO `purchase_orders` (`column1`,`column2`,`column3`,`column4`,'";
$sql .= "`column5`,`column6`,`column7`,`column8`,`column9`,`column10`,";
$sql .= "`column11`,`column12`,`column13`,`column14`)";
$sql .= " VALUES ('{$po_num}','{$order_id}','{$product_id}',";
$sql .= "'{$product_code}','{$amount}','{$product}','{$price}',";
$sql .= "'{$mfg_code}','{$shipping_method}','{$shipping_cost}',";
$sql .= "'{$s_state}','{$status}','{$dist_code}','{$dist_cost}')";

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vipin Jain
Solution 2 Alan Delimon
Solution 3

Related Questions