Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

record locking with PHP and MS SQL

Status
Not open for further replies.
ideasworking

ideasworking

Programmer
Dec 2, 2001
120
CA
Hello,

It seems I have created a bit of a cluster bleep... and I need some help sorting it out. Here's the situation.

I have a table that updates the most recent record every minute. However there are times when I need to manually update the same record. When I attempt to manually update the record it sometimes causes a crash and PHP can no longer link to MS SQL. I then have to completely reboot the windows2003 server to get PHP working again.

Maybe I'm wrong about it being a record locking problem... but this is the most reasonable cause for locking up PHP that I can think of. What should I do to prevent this?

Thanks,
Lou


 
Sort by date Sort by votes
What would be locking the record? An UPDATE query from PHP to should only have the record locked for the split second it takes to perform the update. After that, the record should be available to every process.

What is performing the per-minute update of the most recent record? How are you defining "the most recent record"?


Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
Hello,

Ok, maybe it's not not a record sharing violation that is causing PHP to loose it's connection to MS SQL.

Here's are the details...

Everyday a new record is created in the table and every minute that record is updated using a trigger from another table. This is a consolidated record from multiple sources that create a daily record.

About 90% of the time I can update the record manually through PHP, however 10% of the time something happens and PHP fails to connect to MS SQL. Then I have to restart the server to get it working again.

Does this help identify the problem?

TIA,
Lou
 
Upvote 0 Downvote
Unfortunately, saying "when I restart my system, the problem goes away" really doesn't say much. What we need to know is the restart which service, daemon, piece of software, whathaveyou regains functionality. Can you cause it to lock up, then dig around in the services panel, seeing the restart of what service reqains functionality?



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
Hello,

I'm sorry you are correct... the info provided was not very helpful. The next time this problem occurs I will attempt to narrow down the culprit.

In the mean time I wondered if seeing the PHP page I created to update these records might help identify the problem. This page is a little complicated as it's not just a single straight update.

If you have time, please have a look at my code and let me know if there are any identifiable problems.

thanks,
Lou

Code: 
<HTML>
<style type="text/css">
<!--
.BoldText {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 12px;
	font-weight: bold;
}
.BigBoldText {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 36px;
	font-weight: bold;
}
-->
</style>
<body>
<?PHP
$PreviousDate  = date("Y-m-d",strtotime($_POST["RecordDate"])-86400);

$SQLstring = "Update [DailyTotals] SET ";
$SQLstring = $SQLstring."[CityWater] = '".$_POST["CityUsage"]."'"; 
$SQLstring = $SQLstring." ,[RiverWater] = '".$_POST["RiverUsage"]."'";
$SQLstring = $SQLstring. " WHERE [DateAndTime] = '".$_POST["RecordDate"]."'";

$SQL1string = "DELETE FROM [WaterMeters] WHERE [DateAndTime] = '".$_POST["RecordDate"]."'";

$SQL2string = "INSERT INTO [WaterMeters] ([DateAndTime], [RiverWater], [CityWater]) Values (";
$SQL2string = $SQL2string."'".$_POST["RecordDate"]."','";
$SQL2string = $SQL2string.$_POST["NewRiverWaterReading"]."','";
$SQL2string = $SQL2string.$_POST["NewCityWaterReading"]."')";

// Connect to the SQL server and update the record

$db = mssql_connect("SQLSERVER\INSTANCE","sa","password") or die("Unable to connect to server");

mssql_select_db("Reporting");

mssql_query($SQLstring,$db) or die('<table width="450" border ="0" align="center" cellpadding="1"><tr>
									  Unable to save meter readings. Please check entered values.
									  </tr><tr align="center"><form name="frm" method="post" action="">
									  <input type="button" name="Button" value="Go back to water readings" onClick="history.go(-1)">
									  </form></tr>
									  </table>');

echo "<table width='450' border='0' align='center' cellpadding='1'>";
echo "  <tr>";
echo "    <td class='BoldText'>Daily totals table successfully updated for ".date("F d, Y",strtotime($PreviousDate))."<br>";
echo " City water usage = ".$_POST['CityUsage'].". River water usage = ".$_POST['RiverUsage'].". </td><br>";
echo "  </tr>";
echo "</table>";

mssql_query($SQL1string,$db);
mssql_query($SQL2string,$db) or die('<table width="450" border ="0" align="center" cellpadding="1"><tr>
									  Unable to save meter readings. Please check entered values.
									  </tr><tr align="center"><form name="frm" method="post" action="">
									  <input type="button" name="Button" value="Go back to water readings" onClick="history.go(-1)">
									  </form></tr>
									  </table>');


echo "<table width='450' border='0' align='center' cellpadding='1'>";
echo "  <tr>";
echo "    <td class='BoldText'>Water meter history table successfully updated for ".date("F d, Y",strtotime($RecordDate))."<br>";
echo "City water reading = ".$_POST["NewCityWaterReading"].". River water reading = ".$_POST["NewRiverWaterReading"].". </td><br>";
echo "  </tr>";
echo "  <tr>";
echo "    <td class='BigBoldText'> SUCCESS </TD> ";
echo "  </tr>";
echo "</table>";




?>
<form name="form1" method="post" action="">
  <div align="center">
    <input type="button" name="Button" value="Close Window" onClick="window.close()"> 
    &nbsp;
	<input type="button" name="Button" value="Go back to water readings" onClick="history.go(-1)">
&nbsp;&nbsp;
    <input type="button" name="Button" value="Go to capacity report" onClick="window.location='[URL unfurl="true"]http://www.mydomian.com'">[/URL]
  </div>
</form>
</body></HTML>
 
Upvote 0 Downvote
Amd I reading your code correctly? It seems that you are deleting then recreating a record there. If so, it has always seemed better to update the existing record.


Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
I have narrowed the problem down a little further given your advice. I have learned two things.

1) The problem appears to berelated to hyperlinks. I have two links "Yesterday" and "Tomorrow". Sometimes while clicking on these links the server will stop displaying the pages.

2) I have to stop and restart the World Wide Web Publishing Service to get the pages displaying again. Now here's the funny thing. When the system runs into this problem, I don't think IIS is completely hosed as it still shows the MSSQL query failed to connect message.

Here is a bit of the code I'm using for the hyperlnks. Please let me know if there is something wrong here.

Thanks very much for the ongoing support!
Lou

Code: 
if(is_null($_GET['StartDate'])){ 
$CurrentDate = date("Y-m-d",mktime(0, 0, 0, date("m", time())  , date("d", time()), date("Y", time())));
	} else { 
	$CurrentDate = $_GET['StartDate'];	} 

$PreviousDate  = date("Y-m-d",strtotime($CurrentDate)-86400);
$NextDate = date("Y-m-d",strtotime($CurrentDate)+86400);

<a href="[URL unfurl="true"]http://water/index.php?StartDate=<?PHP[/URL] echo $PreviousDate ?>">&lt;&lt;</a> <?php echo date("l, F d, Y",strtotime($CurrentDate)) ?>&nbsp;<a href="[URL unfurl="true"]http://water/index.php?StartDate=<?PHP[/URL] echo $NextDate ?>">&gt;&gt;</a>
 
Upvote 0 Downvote
I have read some other people's post about having problems with queries and url redirects. I'm wondering if this is a common problem and how is it resolved?

I have several web pages that display records and allow me to modify the records. So what a record modification the browser is redirected back to the original page.

Is this a known problem? Is this how other people handle record manipulation?

Any and all help is appreciated.

Thanks,
Lou
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
233
SashaBuilder3
SashaBuilder3
PeDa
  • Locked
  • Question
Unwanted spaces in php HTML mail 1
Replies
4
Views
203
PeDa
PeDa
Gerrit Broekhuis
  • Locked
  • Question
PHP form with captcha and file upload
Replies
4
Views
140
Gerrit Broekhuis
Gerrit Broekhuis
Mandy_crw
  • Locked
  • Question
How to send sms in PHP using usb gsm modem?
Replies
1
Views
217
vishvajit
vishvajit
Wand850
  • Locked
  • Question
Parsing XML with multiple namespaces using PHP
Replies
3
Views
172
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top