Trouble with insert statement to add record

[chrscote]

I am trying to create a query to insert data into a table in my Access database.  I have the following query:

INSERT INTO Issues (DateRequested, CustomerID, ComputerID, Issue, ItemsIncl, ImageName) VALUES (#1/14/2015#, 1, 1, "Computer freezes while I'm on the internet.", "AC Adapter", "none.gif")

which should be performed from within my PHP page.  However, when I check the database afterward, the new record isn't there.  I then tried performing the query directly in Access and it worked fine.  Why would it work in Access, but not when I run the same query in PHP?

 

 

Chris

[CroNiX]

What DB driver are you using to query your db within PHP?

Edited January 14, 2015 by CroNiX

[chrscote]

What do you mean by DB driver?  I have php_pdo_mssql, and php_pdo_odbc selected in my extensions.

[CroNiX]

Sorry, meant extension not driver. Ok, so you have pdo_mysql and pdo_odbc enabled, but which one are you using to connect to the db and query?

Edited January 15, 2015 by CroNiX

[chrscote]

I'm using PD to connect to the database and for my queries.  What I don't understand is that I have other insert queries that are working fine.  In fact, before the query from above, I use 2 other insert statements that add a customer's name and info in one table and their computer info in another.  But for some reason, when it gets to this query, just afterwards, it doesn't do anything.  I don't even get an error message from my try..catch:

 

try {

$db->query($sqlIssue);

echo "Added Issue<br /> $sqlIssue<br />";

} catch (Exception $e) {

echo "Error adding issue: ".$e->getMessage();

}

 

where $sqlIssue is the string of the query.

Edited January 15, 2015 by chrscote

[mac_gyver]

I don't even get an error message from my try..catch

 

 

after you made the database connection, did you enable exceptions for that connection? if not, the default is PDO::ERRMODE_SILENT and exceptions won't be thrown.

[ginerjm]

Could we see all the code involved with this specific insert? Assume the connection is working and the select, show us the query that you built, then the prepare(?) and the query execution and how you check the results and how you retrieve the results.

 

Be sure php error checking is turned on too. (in my sign.)

[chrscote]

OK, here is the code for this particular query:

	//Now, we're ready to add the issue to the Issues table
	$dateReq = date('n/j/Y');
	$issue = $_POST["issue"];
	$added = $_POST["added"];
	$custID = 1;
	$compID = 1;
	
	$sqlIssue = 'INSERT INTO Issues (DateRequested, CustomerID, ComputerID, Issue, ItemsIncl, ImageName) '.
				'VALUES (#'.$dateReq.'#, '.$custID.', '.$compID.', "'.$issue.'", "'.$added.'", "'.$imgName.'")';
	try {
		$db->query($sqlIssue);
		echo "Added Issue<br /> $sqlIssue<br />";
	} catch (Exception $e) {
		echo "Error adding issue: ".$e->getMessage();
	}

I added the 2 lines from ginerjm's signature and still do not get any error messages.

 

Chris

[ginerjm]

I would say you are not recognizing the query failure. What does your query method look like?

[maxxd]

Why are you wrapping the date in '#'? Also, that would need to be quoted as it would then be a string, right?

[ginerjm]

As maxdd points out - that is probably why your query is failing. Seeing the query method code could help us to see why he is not getting an error reported.

 

OP - show us the top of your code where you enabled error checking too.

[CroNiX]

Surrounding the date with # is how you insert a date in MS Access. Not sure if it needs to be quoted or not though.

[programming2music]

I think your problem is you're using single quotes in your query and not escaping them.

[ginerjm]

Nah - the quotes are alright. I really wish the OP was listening and could post the things we've asked for. To that list I'd like to add "show us an echo of your query statement before you try and run it".

[mac_gyver]

in addition to needing PDO's exception mode turned on (see reply #6 in this thread) for your try/catch to do anything, are you sure that the code where your query is at is even being ran? are you getting the "Added Issue<br /> $sqlIssue<br />" output? and on the chance that your code is doing a header() redirect with output_buffing turned on, so that you wouldn't see any output from your code, what exact end result in the browser are you seeing when you run your code?

[chrscote]

I was trying to simplify things by just showing you the query I'm calling and how I created it.  Here is the whole page with a couple of items using static values that already exist in the database for the issue query:

	$dbName = $_SERVER["DOCUMENT_ROOT"] . "/Ridley/RLCompRepair.accdb";
	//echo $dbName."<br />";
	if (!file_exists($dbName)) {
		die("Could not find database file.<br />".$dbName);
	}
	try {
		$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};;Dbq=$dbName");
		//echo "DB is connected: $dbName<br />";
	} catch(PDOException $e) {
		echo "Error: ".$e->getMessage()."<br />";
		$db = null;
	}
	
	error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');

	//Set default computer ID and Image Name values to check later
	$imgName = "none.gif";
	$target_dir = "images/";
	$imageUploaded = false;
	
	//Get values to edit the Customers table
	//This needs to be here so we can use it in the receipt.
	$fName = $_POST["fName"];
	$lName = $_POST["lName"];
	$street = $_POST["address"];
	$city = $_POST["city"];
	$state = $_POST["state"];
	$zip = $_POST["zip"];
	$phone = $_POST["phone"];
		
	$compID = 0;
	$custID = $_POST["custId"];
	$contactChg = $_POST["contactChg"];
	$compChg = $_POST["compChanged"];
	$retCust = $_POST["returnCust"];
	
	
	
	//This is a new customer who has not been added to the database.
	try {
		$sql = "INSERT INTO Customers (FirstName, LastName, Street, City, State, Zip, Telephone) ";
		$sql .= "VALUES ('$fName', '$lName', '$street', '$city', '$state', '$zip', '$phone')";
		$db->query($sql);
		echo "New Customer $sql<br />";
		
		$sqlID = "SELECT @@identity AS ID FROM Customers";
		$result = $db->query($sqlID);
		$row = $result->fetch();
		$custID = $row["ID"];
	} catch (Exception $e) {
		echo $e.getMessage();
	}
	
	//Get values for Computers table
	$model = $_POST["model"];
	$sn = $_POST["sn"];
	if ($sn=="") {
		$sn = "---";
	}
	$login = $_POST["login"];
	$pw = $_POST["pw"];
	
	$compID = $_POST["compSel"];
	echo "compChg=$compChg, compID=$compID<br />";
	if ($compID == "0") {
		//We are adding a new computer for this customer
		$sql = "INSERT INTO Computers (ComputerModel, ComputerSN, LogIn, Password, CustomerID)".
				" VALUES ('$model', '$sn', '$login', '$pw', '$custID')";
		$db->query($sql);
		echo "New computer $sql<br />";
		
		$sqlCompID = "SELECT @@identity AS ID FROM Computers";
		$result = $db->query($sqlCompID);
		
		while ($row = $result->fetch()) {
			$compID = $row["ID"];
		}
	}
	
	//Now, we're ready to add the issue to the Issues table
	$dateReq = date('n/j/Y');
	$issue = $_POST["issue"];
	$added = $_POST["added"];
	$custID = 1;
	$compID = 1;
	
	$sqlIssue = 'INSERT INTO Issues (DateRequested, CustomerID, ComputerID, Issue, ItemsIncl, ImageName) '.
				'VALUES (#'.$dateReq.'#, '.$custID.', '.$compID.', "'.$issue.'", "'.$added.'", "'.$imgName.'")';
	try {
		$db->query($sqlIssue);
		echo "Added Issue<br /> $sqlIssue<br />";
	} catch (Exception $e) {
		echo "Error adding issue: ".$e->getMessage();
	}

When I submit my form from the previous page, the first 2 queries work great.  I get new customer and computer records.  

Also, I get the following text written printed from the echo lines:

New Customer INSERT INTO Customers (FirstName, LastName, Street, City, State, Zip, Telephone) VALUES ('New', 'Newberg', '345 abc lane', 'Unlucky', 'CT', '66634', '643-234-5689')
compChg=1, compID=0
New computer INSERT INTO Computers (ComputerModel, ComputerSN, LogIn, Password, CustomerID) VALUES ('Dell', '---', 'nnewberg', 'nnewberg!', '9')
Added Issue
INSERT INTO Issues (DateRequested, CustomerID, ComputerID, Issue, ItemsIncl, ImageName) VALUES (#1/17/2015#, 1, 1, "Testing", "Testing", "none.gif")

I don't get any of the error echo lines.

 

Chris

[ginerjm]

Why would you add the error checking code AFTER your db efforts???? The whole point of turning it on is to catch Any and All errors that could occur.

 

Move those lines to the top right after your 'session_start()' command.

 

Do you get the "added issue" message after the insert succeeds?? You should