Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-22
5
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
What we learned in Webroot's webinar on multi-vector protection.
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 …
Suggested Courses

636 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