iamdiggnified
asked on
Why isn't my database updating a record I make changes to?
Our company hosts their intranet on their own server. I have a phone directory database that i'm trying to update a record in by deleting a cellphone number from a cellphone field. Pretty simple stuff. I can make the change in phpMyAdmin and save it. Then I go to a script page in my browser which refreshes the database and allows my changes to visible in the browser. However, now, when I go to the refresh page and activate the refresh script, the changes I made in phpMyadmin get wiped out and revert back to the original database record. I've been making changes to this database and using the refresher page without a problem for 3 months. I haven't made any changes to the refresher page and I've included the coding for it here (see attached code). My phpmyadmin edits won't revert back to their previous states until I activate my refresh script on my refresher page. Any ideas?
<?php
require('../../include/connect.inc');
$queryd = "delete from phonelistnewcopy";
$resultd = mysql_query($queryd) or die(mysql_error());
//$num_resultsd = mysql_num_rows($resultd);
$query = "select phonelistnew.lastname, phonelistnew.preferredname,
phonelistnew.title,
phonelistnew.extension,
phonelistnew.phone,
phonelistnew.cell,
phonelistnew.sales_nbr,
location.location,
phonelistnew.birthdate,
phonelistnew.showbirthdate,
phonelistnew.hiredate,
phonelistnew.email
from phonelistnew, location
where location.adploc_id=phonelistnew.location and phonelistnew.status='A'
order by phonelistnew.lastname";
$result = mysql_query($query) or die(mysql_error());
$num_results = mysql_num_rows($result);
if ($result)
{
echo ' result ran<BR>';
}
else
echo ' result failed<BR>';
for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
$lastname=stripslashes($row['lastname']);
$preferredname=stripslashes($row['preferredname']);
$title=stripslashes($row['title']);
$extension=stripslashes($row['extension']);
if (stripslashes($row['phone'])!='' && stripslashes($row['phone'])!='()')
$phone=substr(htmlspecialchars(stripslashes($row['phone'])),0,9).'-'.substr(htmlspecialchars(stripslashes($row['phone'])),9,4);
else
$phone='';
if (stripslashes($row['cell'])!='' && stripslashes($row['cell'])!='()')
$cell="(".substr(htmlspecialchars(stripslashes($row['cell'])),0,3).") ".substr(htmlspecialchars(stripslashes($row['cell'])),3,3)."-".substr(htmlspecialchars(stripslashes($row['cell'])),6,4);
else
$cell='';
if (stripslashes($row['sales_nbr'])!='')
$sales_nbr=$row['sales_nbr'];
else
$sales_nbr='';
$location = stripslashes($row['location']);
//------------------------------------------------------------------------------------------
$calc_birthdate = '0000-00-00 00:00:00';
$calc_hiredate = '0000-00-00 00:00:00';
if (stripslashes($row['birthdate'])!='' && stripslashes($row['showbirthdate'])!='N' && stripslashes($row['showbirthdate'])!='n')
{
$birthdate = $row['birthdate'];
//echo '<TD>birthdate='.$birthdate.'</TD> ';
$year = substr($birthdate,(strrpos($birthdate,'/')+1),4);
//echo '<TD> year ='.$year.'--</TD> ';
$month = substr($birthdate,0,(strpos($birthdate,'/')));
//echo '<TD> month ='.$month.'--</TD> ';
$day = substr($birthdate, (strpos($birthdate,'/')+1) , (strrpos($birthdate,'/')-(strpos($birthdate,'/')+1)));
//echo '<TD> day ='.$day.'--</TD> ';
$calc_birthdate = $year.'-'.$month.'-'.$day.' 00:00:00';
//echo '<TD> calc_birthdate ='.$calc_birthdate.'</TD> ';
//calculate this year birthday anniversary-------------------------------------------------------------
$eventdate = date('Y').'-'.$month.'-'.$day.' 00:00:00'; //works------------------------------
//$iquery = "insert into emp_dates values ('".$calc_hiredate."', '".$calc_birthdate."', '".$emp_id."', '".$eventdate."', '".$eventtitle."', '".$eventlink."', '".$eventsource."', '".$eventtype."')";
//$iresult = mysql_query($iquery);
}
else
{
$calc_birthdate = '0000-00-00 00:00:00';
}
if (stripslashes($row['hiredate'])!='')
{
$hiredate = $row['hiredate'];
//echo '<TD>hiredate='.$hiredate.'</TD> ';
$year = substr($hiredate,(strrpos($hiredate,'/')+1),4);
//echo '<TD> year ='.$year.'--</TD> ';
$month = substr($hiredate,0,(strpos($hiredate,'/')));
//echo '<TD> month ='.$month.'--</TD> ';
$day = substr($hiredate, (strpos($hiredate,'/')+1) , (strrpos($hiredate,'/')-(strpos($hiredate,'/')+1)));
//echo '<TD> day ='.$day.'--</TD> ';
$calc_hiredate = $year.'-'.$month.'-'.$day.' 00:00:00';
}
else
{
$calc_hiredate = '0000-00-00 00:00:00';
}
$email=stripslashes($row['email']);
//----------------------------------------------------------------------------------------------------
//echo 'i= '.$i.' $lastname= '.$lastname.' $extension= '.$extension.' $preferredname= '.$preferredname.' $title= '.$title.'<BR>';
$query2 = "insert into phonelistnewcopy (phone, extension, lastname, preferredname, location, sales_nbr, cell, title, birthdate, hiredate, email) values ('".$phone."', '".$extension."', '".addslashes($lastname)."', '".addslashes($preferredname)."', '".$location."', '".$sales_nbr."', '".$cell."', '".addslashes($title)."', '".$calc_birthdate."', '".$calc_hiredate."', '".$email."')";
$result2 = mysql_query($query2) or die(mysql_error());
//echo $result2.'<br>';
}
$TEST='22';
$TEST2='99';
//end print emp phonelist stuff--------------------------------------------------------------------
//echo 'Lastname Firstname <BR>';
//echo 'before echo Lastname Firstname Title to xls <BR>';
exec ("echo 'Lastname Firstname Title Extension Phone Cell Slsp Store Birthday Hiredateemail'>/var/www/intranet/html/employees/temp/phonelist.xls");
//echo 'before rm /var/www/intranet/html/employees/temp/phonelistoutput.txt <BR>';
//exit();
$success=unlink ("/var/www/intranet/html/employees/temp/phonelistoutput.txt");
if ($success)
//echo 'phonelistoutput.txt deleted<BR>';
else
//echo 'fail to delete phonelistoutput.txt<BR>';//this file does not exist on 100.0.0.5
//exit();
//echo 'after rm /var/www/intranet/html/employees/temp/phonelistoutput.txt<BR>';
//exec("/var/www/html/employees/deletephonelist"); //this file does not exist on 100.0.0.5
//$output = shell_exec('sudo /var/www/html/employees/deletephonelist');
//echo "<pre>$output</pre>";
//echo '<BR>after exec <BR>';
//exec ("rm /var/lib/mysql/ringsend/phonelistoutput.txt"); //this file does not exist on 100.0.0.5
//echo 'After Lastname Firstname <BR>';
//mysql -p74tnnh-g3\) ringsend < phonelist.sql
//include('phonelist.sql');
$query3 ="select
lastname,
preferredname,
title,
extension,
phone,
cell,
sales_nbr,
location,
date_format(birthdate,'%b %d'),
date_format(hiredate,'%b %d, %Y'),
email
from phonelistnewcopy
order by lastname
into outfile '/var/www/intranet/html/employees/temp/phonelistoutput.txt' "; // default path is /var/www/html/employees/temp/phonelistoutput.txt
//into outfile '/var/www/html/employees/temp/phonelistoutput.txt' ";
//echo 'before mysql_query($query3) <BR>';
$result3 = mysql_query($query3) or die(mysql_error());
//echo 'after mysql_query($query3) <BR>';
//echo
//passthru("/var/www/html/employees/phonelist"S);
if ($result3)
{
echo ' updempphonelistcopy ran<BR>';
//echo 'before cat /var/www/intranet/html/employees/temp/phonelistoutput.txt >> <BR>';
exec("cat /var/www/intranet/html/employees/temp/phonelistoutput.txt >> /var/www/intranet/html/employees/temp/phonelist.xls");
//exec ("cat /var/www/html/employees/temp/phonelistoutput.txt ");
}
else
echo ' updempphonelistcopy failed<BR>';
?>ASKER
bportlock, thanks for looking into this. Regarding Browsing Caching...I refreshed the browser after clearing my cache and my updates to the record still don't take effect. Please note that running the refresh script somehow alters my phpmyadmin causing the edits I make to the record to revert back to what they were previously.
Regarding the "escaping", I used no special characters. For instance, on one record, i changed a phone number extension form 7609 to 7693 and the change didn't work.
Lastly, I added your code to my php script right after the <?php in the code that is attached and nothing happened. But there is a regular report after I run the refresh script which sent back this message:
ADP source file Looks OK........
deletequotes from the csv file has completed successfully
old records now deleted from phonelist
phonelist updating has finished successfully
updating of employee calendar information has finished
makeblankrecords. has finished
Employee Updating process has finished successfully if no other errors are reported above
Regarding the "escaping", I used no special characters. For instance, on one record, i changed a phone number extension form 7609 to 7693 and the change didn't work.
Lastly, I added your code to my php script right after the <?php in the code that is attached and nothing happened. But there is a regular report after I run the refresh script which sent back this message:
ADP source file Looks OK........
deletequotes from the csv file has completed successfully
old records now deleted from phonelist
phonelist updating has finished successfully
updating of employee calendar information has finished
makeblankrecords. has finished
Employee Updating process has finished successfully if no other errors are reported above
This bit of code looks dodgy
because all the statements are comments the PHP interpreter sees this as
if
else
and that is a syntax error. Comment those lines out as well and try again
if ($success)
//echo 'phonelistoutput.txt deleted<BR>';
else
//echo 'fail to delete phonelistoutput.txt<BR>';//this file does not exist on 100.0.0.5
//exit();
//echo 'after rm /var/www/intranet/html/employees/temp/phonelistoutput.txt<BR>';
//exec("/var/www/html/employees/deletephonelist"); //this file does not exist on 100.0.0.5
//$output = shell_exec('sudo /var/www/html/employees/deletephonelist');
//echo "<pre>$output</pre>";
//echo '<BR>after exec <BR>';
//exec ("rm /var/lib/mysql/ringsend/phonelistoutput.txt"); //this file does not exist on 100.0.0.5because all the statements are comments the PHP interpreter sees this as
if
else
and that is a syntax error. Comment those lines out as well and try again
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The excel spreadsheet solves the problem.
1. Browser caching - perhaps you browser has been updated and is caching more agressively. Make a change then view the database using a different browser or a different computer
2. Escaping - whilst the code you posted does have some mysql_real_escape_string code, maybe one has been missed. Are you inserting data with an apostrophe in it, for example names like Mr O'Reilly?
3. Turn on error checking. Add this line (below) to the start of the PHP after the first <?php tag
ini_set('display_errors',1
or this (below) to ignore NOTICE errors
ini_set('display_errors',1
Try these (particularly number 3) and let us know what you find.