SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: UPDATE problem.

  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATE problem.

    Hi,

    Wondering if comeone could help me with a major problem i'm having understanding & solving.

    When i'm grabbing a row from my database and then trying to UPDATE those details it's not updating and I don't see why:

    DB Structure
    story_id (int 1)
    published_web_date_time (timestamp / datetime 0000-00-00 00:00:00)

    Now in the structure above i've tried both published_web_date_time as both a timestamp and datetime field as I was told you can't update a timestamp field in MYSQL using PHP, which I thought was wierd? But nomatter what I change published_web_date_time to field wise it still does not update.

    If you look at my form below when I GET the data I get the published_web_date_time split into two textfields as shown in the form below, but when it comes to updating these textfields with new values it does not save the data to the database upon sumbit?

    Any ideas?

    Thanks

    PHP Code:
    <?php


    // ** Get the requested story id from the database **
    if(isset($_GET['story_id'])) 

        
    $result mysql_query("Select * From cms_test where story_id=".$_GET['story_id']); 
        
    $row mysql_fetch_array($result); 
        
    $date_time_array explode(" ",$row['published_web_date_time']);
        
    $published_web_date =  $date_time_array[0];
        
    $published_web_time $date_time_array[1];    


    // ** If Submit is hit do your stuff **
        
    if (isset($_POST['Submit'])) {

        
    $published_web_date_time $_POST['published_web_date_time'];

    // ** Check for Required Fields with IF statements **
           
    if (empty($published_web_date)){
            
    $error "** no time! **"
           }  else if (empty(
    $published_web_time)){
            
    $error "** no date! **";

    // ** If all of the statements are true then **
        
    } else {
        
    $query mysql_query("Update cms_test set published_web_date_time='$published_web_date $published_web_time' where story_id=".$_GET['story_id']); 
        
    header('Location: done.php');  

    // ** And finally run the query to add data to the database **
         
    $link mysql_connect;
         
    $result mysql_query($query) or die('Query failed: ' mysql_error()); 
         
    mysql_close(); 


    }
    ?>
    <FORM ACTION="".php" method="post" NAME="frmAdd">

      Date: <INPUT NAME="published_web_date" TYPE="text" ID="published_web_date" VALUE="<?php echo $published_web_date?>" SIZE="18">
       <br />
       <br / >
      Time: <INPUT NAME="published_web_time" TYPE="text" ID="published_web_time" VALUE="<?php echo $published_web_time?>" SIZE="18"><br />
       <br />
      <INPUT TYPE="Submit" NAME="Submit" VALUE="Submit" CLASS="button">
    </form>

  2. #2
    SitePoint Member
    Join Date
    Jul 2004
    Location
    us
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can definately insert a timestamp from PHP. However, if you don't include the timestamp field in your insert statement then mysql will insert the current timestamp for you.

    PHP Code:
        $query mysql_query("Update cms_test set published_web_date_time='$published_web_date $published_web_time' where story_id=".$_GET['story_id']);
        
    header('Location: done.php');  

    // ** And finally run the query to add data to the database **
         
    $link mysql_connect;
         
    $result mysql_query($query) or die('Query failed: ' mysql_error());
         
    mysql_close(); 
    Is header('Location: done.php'); in there on purpose? The way it is, you code will never execute the update. It just redirects to "done.php".

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi bucabay,

    Appreciate your help

    Bit confused by this - I want to update, the insert has already been done. Basically here is my workflow:

    1) I enter a story online (with a timestamp)
    2) I see the story's timestamp is wrong
    3) I then want to edit the timestamp from that row in the database.

    PHP Code:
    $query mysql_query("Update cms_test set published_web_date_time='$published_web_date $published_web_time' where story_id=".$_GET['story_id']); 
    So i'm wondering why this line won't UPDATE the published_web_date_time. My format for my INSERT into page is pretty much the same, and the code does execute the update using INSERT when then re-directing.

    So i'm still confused as to why my code for UPDATE (ablove) won't work.

    Thanks

    Chris

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    have you tried your UPDATE statement directly in mysql, rather than through php?

    just to establish where the problem might be

    by the way, you do not have to SELECT a row before you can UPDATE it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    Yep in phpmyadmin I use this command:

    Code:
    UPDATE `cms_test` SET `published_web_date_time` = '2008-05-05 12:16:00' WHERE `cms_test`.`story_id` =1
    Which I believe would be the same as this in eqivalent of php:

    Code:
    Update cms_test set published_web_date_time='$published_web_date $published_web_time' where story_id=".$_GET['story_id'])
    Thanks

    Chris

  6. #6
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to connect to databse before performing any operations. To connect to database server use mysql_connect() and to select the required db mysql_select_db.

  7. #7
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi mwasif,

    Yep, I have that cheers - just deleted it from my example!

    Thanks

    Chris

  8. #8
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try in this way...
    PHP Code:
    $query "Update cms_test set published_web_date_time='$published_web_date $published_web_time' where story_id=".$_GET['story_id'];
    // ** And finally run the query to add data to the database ** 
        
    $link mysql_connect
        
    $result mysql_query($query) or die('Query failed: ' mysql_error()); 
        
    mysql_close(); 
        
    header('Location: done.php');   
        exit; 
    And consider validating the value of $_GET['story_id'] using intval() or is_numeric.

  9. #9
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all,

    Thanks for that query, although I still can't get it to work. If you take a look at:

    http://www.slougheaz.org/test.php?story_id=1

    ...and try and update one of the values and then go back and re-fresh you will see that it does not update

    Strange!

    Chris

    PHP Code:
    <?php

    // Start the connection to the database        

    $link mysql_connect("")
    or die(
    "Could not connect");

    $db mysql_select_db("")
    or die(
    "Could not select database");

    // End the connection to the database  


    // ** Get the requested story id from the database **
    if(isset($_GET['story_id'])) 

        
    $result mysql_query("Select * From cms_test where story_id=".$_GET['story_id']); 
        
    $row mysql_fetch_array($result); 
        
    $date_time_array explode(" ",$row['published_web_date_time']);
        
    $published_web_date =  $date_time_array[0];
        
    $published_web_time $date_time_array[1];    


    // ** If Submit is hit do your stuff **
        
    if (isset($_POST['Submit'])) {

        
    $published_web_date_time $_POST['published_web_date_time'];

    // ** Check for Required Fields with IF statements **
           
    if (empty($published_web_date)){
            
    $error "** no time! **"
           }  else if (empty(
    $published_web_time)){
            
    $error "** no date! **";

    // ** If all of the statements are true then **
        
    } else {
    $query "Update cms_test set published_web_date_time='$published_web_date $published_web_time' where story_id=".$_GET['story_id']; 
    // ** And finally run the query to add data to the database ** 
        
    $link mysql_connect
        
    $result mysql_query($query) or die('Query failed: ' mysql_error()); 
        
    mysql_close(); 
        
    header('Location: done.php');    
        exit; 


    }
    ?>
    UPDATE: Date & Time in MYSQL test <br />
    <FORM ACTION="".php" method="post" NAME="frmAdd">
      Date: 
      <INPUT NAME="published_web_date" TYPE="text" ID="published_web_date" VALUE="<?php echo $published_web_date?>" SIZE="18">
       <br />
       <br / >
      Time: <INPUT NAME="published_web_time" TYPE="text" ID="published_web_time" VALUE="<?php echo $published_web_time?>" SIZE="18"><br />
       <br />
      <INPUT TYPE="Submit" NAME="Submit" VALUE="Submit" CLASS="button">
    </form>

  10. #10
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the purpose of this
    PHP Code:
    $link mysql_connect

  11. #11
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It holds the details of my connection - ie:

    PHP Code:
    $link mysql_connect("localhost""my_db_username""my_db_password"
    ...But I did not want to give them away!

  12. #12
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    echo $query and see if the resultant query is correct.

  13. #13
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm,

    Yep, it's not updating. If you take a look at

    http://www.slougheaz.org/echo.php?story_id=1

    and hit submit the query echoed back is the same as the values I started with. It does not like the new data I enter in the textboxes?

    Thabks

    Chris

  14. #14
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers Guys,

    Worked it out myself. I was $POST to the wrong thing:

    should have been to:

    $published_web_date = $_POST['published_web_date'];
    $published_web_time = $_POST['published_web_time'];

    rather than

    $published_web_date_time = $_POST['published_web_date_time'];

    Cheers for the advice though

    Chris


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •