Solved

Why isn't my database updating a record I make changes to?

Posted on 2011-03-22
5
256 Views
Last Modified: 2012-05-11
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>';
?>

Open in new window

0
Comment
Question by:iamdiggnified
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35189708
There's a lot of code to run through in your question, so here are my initial thoughts that you can easily check and see whether they point out any solutions or problems

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); error_reporting( E_ALL );

or this (below) to ignore NOTICE errors

ini_set('display_errors',1); error_reporting( E_ALL & ~E_NOTICE );


Try these (particularly number 3) and let us know what you find.
0
 

Author Comment

by:iamdiggnified
ID: 35190042
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
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35190523
This bit of code looks dodgy

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

Open in new window


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
0
 

Accepted Solution

by:
iamdiggnified earned 0 total points
ID: 35190639
bportlock, I just found out that when i go to the refresh page and run the script it automatically updates the database from an excel spreadsheet. So that excel spreadsheet, when updated,overwrites the table called "phonelistnew" in the database every time. If i want to make a change to the records then I have to do it through the employee directory excel spreadsheet. Sorry for the inconvience and thank you for looking into this .
0
 

Author Closing Comment

by:iamdiggnified
ID: 35225337
The excel spreadsheet solves the problem.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question