Solved

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

Posted on 2011-03-22
5
253 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
The excel spreadsheet solves the problem.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now