We have an Windows IIS 7 server running MySQL (v. 5.0.51a) and PHP (v. 5.2.14). I have set up a PHP calendar application which works great, and am now developing a "trouble ticket" application. However, the trouble ticket application doesn't update the MySQL tables like it should, and I am flummoxed as to why.
My original development was on a WAMP (Windows, Apache, MySQL and PHP) running on my laptop. I've extensively tested on that, and it works like a champ. But on the IIS server, the code APPARENTLY executes (an SQL Insert to MySQL) and - like it should! - redirects to a worklist page where the operator can view all open trouble tickets. HOWEVER, there is no actual update of the MySQL table with the new data.
I know the database is communicating with the application, because it allows me to login and checks against a table of usernames and passwords, and it also populates an employees list, an equipment list, a severity list, etc. from data tables in the trouble ticket app. I also can enter the resulting SQL statement into PHPMyAdmin, and it executes (inserts) just fine. There is something that is preventing the insert SQL from properly executing via the application.
I would be happy to post code and PHP info screen shots, but I suspect its a setting in either the PHP or MySQL .ini files. Does anyone offhand know why an insert would execute on one version (my development WAMP server) and not on another (the live WIMP server)? If you could point me in the right direction, that'd be great.
Let me know if I need to post code. Thanks in advance!
Dan sends...
MySQL Insert not working on IIS 7
Moderator: General Moderators
Re: MySQL Insert not working on IIS 7
The problem is most likely in your code which we will need to see.
If it's working on one server configuration and not a different server configuration, your code probably isn't portable. I would guess some superglobal issues.
If it's working on one server configuration and not a different server configuration, your code probably isn't portable. I would guess some superglobal issues.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Re: MySQL Insert not working on IIS 7
Did you check the MySQL error log? you should also try to catch database errors whenever you submit a query - it would clue you in as to what the problem is. Possibly the user you have set up on your server doesn't have INSERT priveleges? just a wild guess.
Post the code around the INSERT query so we can have a look at it
Post the code around the INSERT query so we can have a look at it
Re: MySQL Insert not working on IIS 7
Quick note: The database user in the connection DOES have Insert privileges (in fact, ALL privileges), and we have tested the ability to insert via CLI as well, using the user specified in the connection.php file.
FILE: connection.php
FILE: trouble_tickets1.php
Once again, thanks for all the help!
FILE: connection.php
Code: Select all
<?php
//database connection code
$db = mysql_connect("wpmerl1vweb01", "acsadmin", "domybidding");
if (!$db) {
echo "error!";
}
mysql_select_db("production_tracker");
?>
Code: Select all
<?php
session_start();
//session used to keep track of logged in operator
//process initial report from form data in file trouble_tickets.php
include("connection.php");
$nowdatetime = date("Y-m-d H:i:s");
$service_call_placed = $_POST["cpyear"]."-".$_POST["cpmonth"]."-".$_POST["cpddate"]." ".$_POST["cphhours"].":".$_POST["cpmmins"].":00";
$service_call_returned = $_POST["scryear"]."-".$_POST["scrmmonth"]."-".$_POST["scrddate"]." ".$_POST["scrhhours"].":".$_POST["scrmnmins"].":00";
$estimated_tech_eta = $_POST["tetayear"]."-".$_POST["tetammonth"]."-".$_POST["tetaddate"]." ".$_POST["tetahhours"].":".$_POST["tetamnmins"].":00";
$actual_tech_arrival = $_POST["tayear"]."-".$_POST["tammonth"]."-".$_POST["taddate"]." ".$_POST["tahhours"].":".$_POST["tamnmins"].":00";
$machine_returned_to_user = $_POST["tmfyear"]."-".$_POST["tmfmonth"]."-".$_POST["tmfddate"]." ".$_POST["tmfhhours"].":".$_POST["tmfmnmins"].":00";
$addquery = "insert into acs_trouble_tix
(tix_id, reporting_date, workarea_id, reporting_operator, system_id, subsystem_id, status_id, severity_id, issue_title, service_call_placed,
log_number, service_call_returned, estimated_tech_time_on_site, actual_tech_time_on_site, machine_returned_to_user, downtime_hours,
downtime_minutes, issue_notes)
values
(NULL,
'".$nowdatetime."',
'".$_POST["workarea_id"]."',
'".$_POST["reporting_operator"]."',
'".$_POST["equipment"]."',
'',
'".$_POST["status"]."',
'".$_POST["severity"]."',
'".$_POST["issue_title"]."',
'".$service_call_placed."',
'".$_POST["log_number"]."',
'".$service_call_returned."',
'".$estimated_tech_eta."',
'".$actual_tech_arrival."',
'".$machine_returned_to_user."',
'".$_POST["downtime_hours"]."',
'".$_POST["downtime_minutes"]."',
'".$_POST["notes"]."')";
$addresult = mysql_query($addquery);
echo "<script>window.location=\"work_trouble_tickets.php\"</script>";
?>
Re: MySQL Insert not working on IIS 7
Add this check when you issue the query -
A couple of other notes -
1. You can redirect using headers, you don't need to (and shouldn't) use Javascript for that. (check the change I made to the code)
2. You need to escape user input using database specific escaping functions to avoid both misuse and intentional abuse of your database. http://php.net/manual/en/function.mysql ... string.php
3. Did you test the generated query in the command line? or did you run a different test query there? if tix_id is an auto-incrementing column, you shouldn't be passing a NULL value into it
Code: Select all
$addresult = mysql_query($addquery);
if($addresult) {
header("Location: work_trouble_tickets.php");
} else {
echo mysql_error(); //For debugging only, log this instead of outputting on a production machine
}1. You can redirect using headers, you don't need to (and shouldn't) use Javascript for that. (check the change I made to the code)
2. You need to escape user input using database specific escaping functions to avoid both misuse and intentional abuse of your database. http://php.net/manual/en/function.mysql ... string.php
3. Did you test the generated query in the command line? or did you run a different test query there? if tix_id is an auto-incrementing column, you shouldn't be passing a NULL value into it
Re: MySQL Insert not working on IIS 7
Putting null into non-null autoincrement column is perfectly fine - it would generate next id and insert that instead.Eran wrote:if tix_id is an auto-incrementing column, you shouldn't be passing a NULL value into it
Re: MySQL Insert not working on IIS 7
I think the problem is solved! Many thanks to all for the good tips.
I was passing it a blank string for the "subsystem_id" field, and the data type is integer. Same for the downtime_hours and downtime_minutes fields. Added some default values, and it now works!
Also thanks for the error debugging code. That helped show me where the offending code was. MUCH appreciated!
You guys ROCK!
Dan sends...
I was passing it a blank string for the "subsystem_id" field, and the data type is integer. Same for the downtime_hours and downtime_minutes fields. Added some default values, and it now works!
Also thanks for the error debugging code. That helped show me where the offending code was. MUCH appreciated!
You guys ROCK!
Dan sends...
Re: MySQL Insert not working on IIS 7
Yes it's fine, it's just completely unnecessary.Putting null into non-null autoincrement column is perfectly fine - it would generate next id and insert that instead.