Page 1 of 1

Insert function not working!

Posted: Thu Dec 02, 2004 8:40 pm
by jim_73_mk1
I've written the function below that builds an INSERT query string and then runs the query. This works for every query I submit with one exception. When adding a suplier with a unique supplier field the query works flawlessly but if I try to add a supplier where the supplier field is a duplicate the query doesn't work.

The only indexed or key field in the suppliers table is the supplier_id field. Line 46 returns a value of 1 on success and nothing on failure. Here is the kicker: If I copy and paste the output from line 45 into a MySQL console where the supplier field is a duplicate it executes and adds the record to the table without error.

Frankly I'm stumped and have no idea how to correct this so I'm asking for help folks.

Thanks,
Jim

Code: Select all

<?php
function insert()
{
	add_slashes();
	$query = 'INSERT INTO ';
	$source = pathinfo($_SERVER['REQUEST_URI']);
	echo '<br>' . $source['basename'];
	switch($source['basename']) {
		case 'add_item.php':
			$columns = array('catagory', 'supplier', 'supplier_item_num', 'item_name', 
				'item_price', 'item_description', 'cost', 'shipping_cost', 'stock_level', 
				'order_qty', 'qty_onhand', 'available');
			$query .= 'suppliers (';
			break;
		case 'add_supplier.php':
			$_SESSION['phone'] = $_SESSION['areacode'] . $_SESSION['prefix'] . 
				$_SESSION['phone'];
			$_SESSION['fax'] = $_SESSION['fax_areacode'] . $_SESSION['fax_prefix'] . 
				$_SESSION['fax_phone'];
			$columns = array('supplier', 'address', 'city', 'state', 'zip', 'website', 
				'order_amount', 'contact', 'email', 'phone', 'fax', 'payment_method', 
				'terms');
			$query .= 'suppliers (';
			break;
		case 'add_catagory.php':
			$columns = array('catagory', 'cat_description');
			$query .= 'catagories (';
			break;
	} // end switch
	$rows = count($columns);
	for ($i = 0; $i < $rows; $i++) { // adds columns to $query
		if ($i == $rows-1) {
			$query .= $columns[$i] . ') VALUES ("';
		} else {
			$query .= $columns[$i] . ', ';
		}
	}
	for ($i = 0; $i < $rows; $i++) { // adds values to $query 
		if ($i == $rows-1) {
			$query .=  $_SESSION[$columns[$i]] . '")';
		} else {
			$query .=  $_SESSION[$columns[$i]] . '", "';
		}
	}
	echo '<br>' . $query;
	if ($result = mysql_query($query)) {
		db_close();
		strip_slashes();
		return true;
	}
	db_close();
	strip_slashes();
	return false;
}
?>

Posted: Thu Dec 02, 2004 8:51 pm
by rehfeld
i see you echo out the complete query syntax before using it.

have you tried copying the query this script echo's and using it in a stripped down php file?

have you tried seeing the contents of mysql_error() ?

did you turn error_reporting off?

there should be some clues available

Posted: Thu Dec 02, 2004 10:02 pm
by Christopher
INSERT will return an error if the key already exists. You need to use REPLACE or UPDATE in that case.