Solved

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

Posted on 2011-03-22
5
254 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Amazon Redshift 2 25
hiding/removing php extension - best practice 4 24
javascript date picker in php while loop 5 22
Scope of $_SESSION 17 27
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

20 Experts available now in Live!

Get 1:1 Help Now