PHP - A Field That Updates With Server Time On Any Row Update - Mysql

How to output a MySQL timestamp field to correct local time with daylight saving with PHP?   Local time is set in php.ini with: date.timezone = "Europe/London"   Confirmed set with:   <?php echo date_default_timezone_get(); ?>   But when I echo the timestamp field it does not display correct UK time for the record (an hour behind correct UK time).   <?php echo date('jS F Y - g:ia', strtotime($row['Date'])); ?>  

Ok this has been driving me crazy for days now. I need to update my DB with multiple data parsed from an XML feed. I need some help in putting together the query.

Currently I have:
Code: [Select]
$xml= 'test-feed.xml'; // URL for feed.



try{
  $feed = new SimpleXMLElement($xml, null, true);
}catch(Exception $e){
  echo $e->getMessage();
  exit;
}


$sqlxml = "";
$arr = array();

foreach($feed->property as $property) {

 $propertyid = (string)$property->id;
 
 foreach($property->images->image as $image) {
 $i = 0;
  $url = (string)$image->url;
  $arr[] = "UPDATE property SET url = '$url' WHERE prop_id = '$propertyid', ";
 $i++;
 
 
 }

}

foreach($arr as $result) $sql .= $result;


$sql = rtrim($sql, ",");

echo $sql;


if(!mysql_query($sql)){
  echo '<h1 style="color: red;">Error</h1><p>', mysql_error(), '</p>';
}
else
{

  echo '<h1 style="color: red;">Property data successfully added to database!</h1>';
}

This structures the query correctly for a single update but repeats it which then throws a MYSQL Syntax error. I am not sure of the correct syntax to use for multiple inserts??

What I get returned at the moment is:

Code: [Select]
UPDATE property SET url = 'ImageId=X1000245' WHERE prop_id = 'A1234', UPDATE property SET url = 'ImageId=X1000296' WHERE prop_id = 'A1234', UPDATE property SET url = 'ImageId=P3&ImgId=X1000237' WHERE prop_id = 'ABC1234',
Need some intervention guys

Thanks in advance

GT

Would there be any feasible way of combining the two Update statement below into one?

The Members Table has id, username, password, and application_id
The Application Table has the application id, the users application information and whether or not the application is approved.


if($_GET['approved']=="update"){
$approved=$_GET['approved'];
$approved=sanitize($approved);
if($approved=="y"){
$id=(int)$_GET['id'];
$username=$_GET['username'];
$username=sanitize($username);
$password=$_GET['password'];
$password=sanitize($password);
$approved_sql='UPDATE members SET username="$username" password="$password" application_id="$id"';
$approved_result=mysql_query($approved_sql);
$approved_rows=mysql_affect_rows();
$update_approved_sql='UPDATE application SET approved="y" WHERE id="$id"';
$update_approved_result=mysql_query($update_approved_sql);
$update_approved_rows=mysql_affect_rows();
if($approved_rows==1 && $update_approved_rows==1{
header("Location: ./index.php??admincp=investors&view=applications&id=1&approved=updated");
}
}
}

Hi i have this simple update form and scrip but somehow it doesnt seem to be update the field on the database can someone help out please.
The html form is the second form bellow where the action send to status_update.php
HTML FORM
Code: [Select]
<?php                include("../header.html");            ?>
<?php                include("header_news.html");            
extract($_REQUEST,EXTR_SKIP);
?><?php            
/*                print("sfilm_refnum = $sfilm_refnum<BR>");                
print("sfilm_addr01 = $sfilm_addr01<BR>");                
print("sfilm_postcode = $sfilm_postcode<BR>");                
print("Film Client = $fclient<BR>");            */             
?>           
<form id="search" action="list.php" method="post" name="search">               
<table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee">                   
<tr>                       
<td align="right" width="140"></td>                       
<td width="320"><span class="hofblack10">                           
</span>                           
</td>                       
<td align="center" width="100"><input type="hidden" name="lstart" value="<?php print("$lstart"); ?>" /><input type="hidden" name="lend" value="<?php print("$lend"); ?>" /><input type="hidden" name="lamount" value="<?php print("$lamount"); ?>" /></td>                        <td align="center" width="100"></td>                       
<td align="right"></td>                   
</tr>               
</table>           
</form>           
<table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#4050c4">               
<tr>                 
<td width="60" class="hofwhite10">action</td>                   
<td width="140"><span class="hofwhite14">DATE</span></td>                   
<td width="80"><span class="hofwhite14">ID</span></td>                   
<td><span class="hofwhite14">News Titile</span></td>                   
<td width="100"><span class="hofwhite14">Status</span></td>               
</tr>               
<tr height="0">                 
<td bgcolor="white" width="60"></td>                   
<td bgcolor="white" width="140" height="0"></td>                   
<td bgcolor="white" width="80" height="0"></td>                   
<td bgcolor="white" height="0"></td>                   
<td bgcolor="white" width="100" height="0"></td>               
</tr>           
</table><table width="780" border="0" cellspacing="0" cellpadding="4"><tr>                   
<td width="60"></td>                 
<td width="80"></td>                   
<td><a class="blueullrg" href="add.php">Add News</a></td>                   
<td align="right" width="120"></td>               
</tr>               
<tr height="0">                   
<td width="60" height="0"></td>                 
<td width="80" height="0"></td>                   
<td height="0"></td>                   
<td align="right" width="120" height="0"></td>               
</tr>           
</table>  <?php          
//get the DB connection variables    
include("../../../includes/config.php");        
//connect to DB        
$connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT.");        
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select DATABASE.");                
$query2="SELECT * FROM news WHERE !(news_status='deleted')";        
$result2 = mysql_query($query2) or die("Couldn't execute QUERY - Select NEWS Qty");        
$fqty = mysql_num_rows($result2);            
//SELECT or FIND the same USERNAME        
$query3="SELECT * FROM news WHERE !(news_status='deleted') ORDER BY news_id DESC";        
$result3 = mysql_query($query3) or die("Couldn't execute QUERY - Select NEWS");        
while ($row = mysql_fetch_array($result3))            
{                
$news_id                 = $row['news_id'];                
$news_title                = $row['news_title'];                
$news_story                = $row['news_story'];                
$news_image                = $row['news_image'];                
$news_image_caption        = $row['news_image_caption'];                
$news_image_link        = $row['news_image_link'];                
$news_date_day            = $row['news_date_day'];                
$news_date_month        = $row['news_date_month'];                
$news_date_year            = $row['news_date_year'];                
$news_status            = $row['news_status'];                
$news_website            = $row['news_website'];                
$news_date_created        = $row['news_date_created'];        
        $news_date_modified        = $row['news_date_modified'];    ?>           
        <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee">               
        <tr>                 
        <td width="60"><span class="hofblack10">                   
         <?php                    
         if($news_status=="deleted"){                        
         print("<a class='hifblack10'>deleted</span>");                    
         }ELSE{                        
         print("<a class='blueul' href='edit.php?id=$news_id'>edit</a>");                    
         }                    
         ?>                 
          </span></td>                   
          <td width="140"><span class="titlegrey12">                     
          <?php                      
          if(!$news_date_day) 
          { echo "00"; }
          else{ echo $news_date_day; }                      
          echo "/";                     
           if(!$news_date_month) { echo "00"; }else{ echo $news_date_month; }                      
           echo "/";                      
           if(!$news_date_year) { echo "0000"; }else{ echo $news_date_year; }                    
           ?>                   
           </span></td>                   
           <td width="80"><span class="titlegrey12"><?php                    print("$news_id");                    ?></span>
           </td>                   
           <td><?php                    
           if($news_status=="deleted")
           {                        
           print("<class='hofblack10'>$news_title</span>");                    
           }ELSE{                        
           print("<a class='blueul' href='edit.php?id=$news_id'>$news_title</a>");                    
           }                    
           ?></td>                   
           <td width="100">                       
           <form id="list_update" action="status_update.php" method="post" name="list_update">                           
        <select name="newnstatus" size="1">                               
           <option  <?php if($row['news_status'] == "") 
           {
            print("selected");
           } ?>
           selected="selected" value="">Status...</option>                               
           <option  <?php if($row['news_status'] == "on")
           {
            print("selected");
           } ?> value="on">On</option>
               <option  <?php if($row['news_status'] == "off")
           {
            print("selected");
           } ?> value="off">Off</option>
            <option  <?php if($row['news_status'] == "deleted")
           {
            print("selected");
           } ?> value="deleted">Delete</option>
        </select>
         <input type="hidden" name="nstatus" value="<?php echo $row[news_status]; ?>" />                           
           <input type="hidden" name="id" value="<?php echo $row[news_id]; ?>" />                       
           <input type="submit" name="update" value="update" />                       
           </form>                     
            </td>               
            </tr>               
            <tr height="0">                 
            <td bgcolor="white" width="60"></td>                   
            <td bgcolor="white" width="140" height="0"></td>                   
            <td bgcolor="white" width="80" height="0"></td>                   
            <td bgcolor="white" height="0"></td>                   
            <td bgcolor="white" width="100" height="0"></td>               
            </tr>           
            </table>
            <?php            }        mysql_close($connection);//}?>           
            <table width="780" border="0" cellspacing="0" cellpadding="4">         
            <tr>                   
            <td width="60"></td>                 
            <td width="80"></td>                   
            <td><a class="blueullrg" href="add.php">Add News</a></td>                   
            <td align="right" width="120"></td>               
            </tr>           
            </table><?php                // include("list_navigation.html");            ?>
            <?php                include("../footer.html");            ?>
            </div></body></html>
The action script php
Code: [Select]
<?php
/*    echo "fstatus: ".$fstatus."<BR>";    
echo "id: ".$id."<BR>";    
echo "fclient: ".$fclient."<BR>";    
echo "newfstatus: ".$newfstatus."<BR>";*/    
//set the date of agreement        
$timestamp = date('l jS \of F Y h:i:s A');    
//get the DB connection variables        
include("../../../includes/config.php");
//connect to DB        
$connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT.");        
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select FILMS DATABASE.");    
// All appears well, so enter into database                
$query= "UPDATE news SET news_status = '$newnstatus' WHERE news_id='$id'";

$result = mysql_query($query) or die("could not execute query - Update FILMS Record to DB");    
//setup an email to the Admin @ hof, w/o attachment            
$emailto="xx@xxx.co.uk";        
$emailfrom="no-reply@xxxx.co.uk";        
$emailsubject="xx Record Updated";        
$emailmessage="Hello Registrar\n\n";        
$emailmessage.="News ID: ".$id."\n";        
$emailmessage.="Updated on: ".$timestamp."\n\n";        
$emailmessage.="Status was: ".$nstatus."\n";        
$emailmessage.="Status now: ".$newfnstatus."\n";        
$emailmessage.="Thank you,\n\n";        
$emailmessage.="Web Site ROBOT\n";        
$emailmessage.="(Administrator)\n";        
$emailmessage.="xxx.co.uk | xxx.biz\n";        
$emailmessage.="----------------------------------------------\n";        
$emailmessage.="e. http://www.xxx.co.uk/contact.php\n";        
$emailmessage.="w. http://www.xxx.co.uk\n";        
$emailheader="From: xxx.co.uk<$emailfrom>";
$emailheader  .= 'Cc: xxx@xxx.co.uk'."\r\n";
$emailheader  .= 'Bcc: xxx@xxxxx.co.uk'."\r\n";        
$ok=mail($emailto,$emailsubject,$emailmessage,$emailheader);        
mysql_close($connection);            
if ($ok)
 {        
 header("Location: list.php"); 
 /* Redirect browser */        
 exit;    
 } else {        
 $errmsg="There was a problem, please try later or telephone us direct.";        
 $errsta="1";        
 include("edit_error.html");        
 //echo "<p>Mail could not be sent. Sorry!</p>";        
 exit;    
 }        
 ?> Thanks in advance

Code: [Select]
$new_array2=array_diff($my_array,$itemIds);

$updatedb = mysql_query("UPDATE content_type_ads SET field_expire_value = '666' WHERE $new_array2 = field_item_id_value");
"$new_array2" has only 12 digit numbers for each item in the array, and I want to match them to the "field_item_id_value" field in the table, updating the "field_expire_value" field for the record where they match.  I know I am close because the UPDATE code works before I add the WHERE statement (it of course adds '666' to EVERY field, but for a noobie it's a start!).

Let's say I grab all the user's information from the users table.

It's stored into a variable as an array: $pun_user.

Now, to get access this user data, it selects all the data each refresh and sets it to $pun_user for extraction later.

My idea:

Create a $_SESSION['user_id'] = $pun_user['user_id']

And then when using updating stuff via ajax requests, I can just do SELECT blah from users where where user_id = $_SESSION['user_id']

instead of: calling the main query to enter all the data into $pun_user, then do that query AGAIN and do SELECT blah from users where user_id = $pun_user['user_id']

Is the $_SESSION way going to take a performance hit on the server? Or is the MYSQL way a more detrimental approach? Which way is faster, and less intrusive on the server?
Edited by Monkuar, 22 January 2015 - 10:35 PM.

Let's say I have 1 table and 100 rows in this table. The table is simple and only contains an Auto Increment ID field and a load_count field.

PHP 5.2.6

We rent PHP server space on a server in California. Several of my pages generated by PHP show the current date and time, so when the page is printed, the user knows when the data was printed. Our users could be anywhere in the US, I am in Michigan. How do I convert the server time (PDT) into local time, regardless of where the user is?

Thanks.

Hi,

The hosting provider for my domain confirm me that the server time is in EST (UTC-5). They also sent me a grab supporting that
Code: [Select]
server time is now:
----------------------
Sun Nov 7 02:20:01 EST 2010
-------------------

But when I use the following code, the output I get is has 1 hr difference (UTC-6)
echo date("d M, Y h:i:s A",time());

Output==> 07 Nov, 2010 01:20:01 AM

But it suppose to give the same time as the server, ryt? Can anybody please explain?

Thanks,

This isn't exactly an application design question, but rather a system design one.   I am about to install an Inventory Control System inside this store I work in.   The store itself also owns a Linode VPS running Centos 6.4 which hosts our website.   This new Inventory System will come built in with a Microsoft SQL Server, and supposedly it is a SQL Anywhere database, but I'm not too sure what that means.     I need to make this database publicly accessible, but only via the Linode VPS.  Surely, setting restrictions is easy enough to address that issue.  That isn't my question.   My first idea is to put this server into the DMZ, easy.  But it doesn't exactly sound safe.  So my next idea was to put a middleman server in the DMZ, this way the Linode can send queries to that middleman server and it will send that data to the SQL Server and back.  This is very vaguely described I know, but I don't want to get too much into details, but rather, understand how I can create that middleman server, and what could Install onto it that would allow me to securely process queries?   My first thought was to install a webservice, that accepts an XML/JSON request and returns an XML/JSON response. Then, I realized directly afterwards that I don't have any experience setting up a webservice like that. What kind of options are there out there?
  Ultimately, my question is, should I just put the Server in the DMZ or should I create the middleman, and if so, can someone point me in the right direction as to getting a webservice set up?
Edited by Zane, 15 July 2014 - 11:28 PM.

Can anyone post a generic update function to update mysql table.
The manual approach:

update $tablename
set $column1='a', $column2='b'
where $id=$value;

Hi:
I'm going crazy trying to do the following:
I'm making a job registration process where the user registers on one php page to the website, must acknowlege and email receipt using an activate php page, then is directed to upload their C.V. (resume) based on the email address they enter in the active page output. I then run an upload page to store the resume in teh MySQL db based on the users email address in the same record.

If I isolate the process of the user registering to the db, it works perfectly.
If I isolate the file upload process into the db, it works perfect.
I simply cannot upload teh file to the existing record based on teh email form field matching the user_email field in the db.
With the processes together, teh user is activated, but teh file is not uploaded.

Maybe I've simply been at this too long today, but am compeled to get through it by end day.
If anyone can help sugest a better way or help me fix this, I will soo greatly appreciate it.

My code is as follows for the 2 pages.

---------activate.php-------
<?php
session_start();
include ('reg_dbc.php');
if (!isset($_GET['usr']) && !isset($_GET['code']) )
{
$msg = "ERROR: The code does not match..";
exit();
}
$rsCode = mysql_query("SELECT activation_code from subscribers where user_email='$_GET[usr]'") or die(mysql_error());
list($acode) = mysql_fetch_array($rsCode);
if ($_GET['code'] == $acode)
{
mysql_query("update subscribers set user_activated=1 where user_email='$_GET[usr]'") or die(mysql_error());
echo "<h3><center>Thank You! This is step 2 of 3. </h3>Your email is confirmed. Please upload your C.V. now to complete step 3.</center>";
} else
{ echo "ERROR: Incorrect activation code... not valid"; }

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Job application activation</title>
</head>
<body>
<center>
<br/><br/><br/>
<p align="center">
        <form name="form1" method="post" action="upload.php" style="padding:5px;">
        <p>Re-enter you Email : <input name="email" type="text" id="email"/></p></form>
          <form enctype="multipart/form-data" action="upload.php" method="POST">
          <input type="hidden" name="MAX_FILE_SIZE" value="4000000">
          Upload your C.V.: <input name="userfile" type="file" id="userfile">
          <input name="upload" type="submit" id="upload" value="Upload your C.V."/></form>
</p>
</center>
</body>

</html>
--------upload.php----------
<?php
session_start();
if (!isset($_GET['usr']) && !isset($_GET['code']) )
{
$msg = "ERROR: The code does not match..";
exit();
}
if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];
$email = $_POST['email']['user_email'];

$fp      = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}

include 'reg_dbc.php';

$query = "UPDATE subscribers WHERE $email = user_email (name, size, type, content ) ". "VALUES ('$fileName', '$fileSize', '$fileType', '$content')";

mysql_query($query) or die('Error, query failed');
mysql_close($dbname);
}
?>
<center>
<br/>
<br/>
<br/>
<br/>
Thank you for uploading your <?php echo "$fileName"; ?> file, completing your registration, and providing us your C.V. for this position.
<br/>
<br/>
<br/>
We will contact you if your canditature qualifies.
</center>

I'm not sure if this is the place to ask this question, but please advise

<?php
echo "time=" . time();
?>

does the time change when viewing in other countries?
E.g. Is echo the same when viewed in China, US, New Zealand?

Thanks!

I just recalled using date(); time() for lots of things in my database, including post times etc. At first I simply assumed this used server time or something, but when I changed my PC's time, it changed ! Now, shortly after this I simply assumed this was because of me using a localhost? But I am not certain anymore..

If I would place this online, would date() and time() return you server time or user time? I couldnt find it on google =-(

im wondering is there any easy way to echo server time and local time... without converting them from unix to timestamp

i have found thi code, but i cant tell is it server or local
Code: [Select]
echo date("H:i" ,time());