www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: help w/mysqli update

  1. #1
    Join Date
    Apr 2013
    Posts
    81

    help w/mysqli update

    Hi, someone tell me why this code won't update -
    no error, no announcement, it does echo the date.
    PHP Code:
    <?php
    if(isset($_POST['submit']))
             { 
    $lastused $_POST['lastused']; } 
             
    $dbconnect mysqli_connect('localhost','root','cookie');
             
    mysqli_select_db($dbconnect'homedb') or die( "Unable to select database");
             
    $lastused = (isset($_POST['submit'])) ? mysqli_real_escape_string($dbconnect,  $_POST['lastused']) : '';
             
    $id = (isset($_POST['id'])) ? mysqli_real_escape_string($dbconnect$_POST['id']) : '';         
             
    $result mysqli_query($dbconnect"SELECT * FROM emailtbl");
             echo 
    "<center>";echo date('m/d/y');echo "</center>";
             if (!empty(
    $_POST['update_lastused']))
    {
    $update mysqli_query($dbconnect"UPDATE emailtbl SET lastused = NOW() WHERE id ='$id'"); 
             echo 
    "lastused has been set ...";}   
          
    ?>

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,362
    You have me confused, in that you're escaping post values that don't get used, you have a query that pulls everything from the emailtbl (and at least here does nothing with it); but for now, I'll just focus on the update query section, since that's what you asked about. So, just narrowing things down to that bit, here's how I'd approach it (if I were to use procedural mysqli functions instead of the OOP methods I prefer):
    PHP Code:
    <?php
    // let PHP help you when it can while developing:
    error_reporting(E_ALL);
    ini_set('display_errors'true); // set to false for production

    if(!empty($_POST['update_lastused'])) {
        
    $dbconnect mysqli_connect('localhost','root','cookie');
        
    mysqli_select_db($dbconnect'homedb') or die( "Unable to select database");
        
    // let prepared statements with bound parameters take care of input sanitation
        
    $sql "UPDATE emailtbl SET lastused = NOW() WHERE id = ?";
        
    $stmt mysqli_prepare($dbconnect$sql);
        
    // make sure it worked
        
    if($stmt == false) {
            throw new 
    Exception("Prepare failed".PHP_EOL.mysqli_error($dbconnect).PHP_EOL.$sql);
        }
        
    mysqli_stmt_bind_param($stmt'i'$sql); // assume id is integer?
        
    $update mysqli_stmt_execute($stmt);
        
    // ditto
        
    if($update == false) {
            throw new 
    Exception("Update failed:".PHP_EOL.mysqli_stmt_error($stmt).PHP_EOL.$sql);
        }
    }
    ?>
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    May 2014
    Posts
    936
    As mentioned you've got a lot of unused/gibberish code in there, but my big question would be that if you are using mysqli, why aren't you using it properly? it's like you've just blindly replaced mysql_ with mysqli_ without even using any of the reasons to use mysqli in the first place. Hell, you don't even need a separate select_db line!

    I'd REALLY suggest getting used to using it in the object model version... in terms of JUST your 'set' query (aka the only code that seems to do anything there)

    More than anything though, STOP blindly pasting values into your query strings and using those pointless outdated _real_escape_string methods. Prepared queries exist for a reason, and are one of the entire reasons we're supposed to be using mysqli or PDO in the first place! (personally I prefer the latter)

    Code:
    <?php 
    $db = new mysqli('localhost', 'root', 'cookie', 'homedb'); 
    if ($db->connect_error) die (
    	'Connect Error (' . $db->connect_errno . ') ' . $db->connect_error
    );
    
    if (!empty($_POST['update_lastused'])) {
    	$stmt = $db->prepare('
    		UPDATE emailtbl
    		SET lastused = NOW()
    		WHERE id = ?
    	');
    	$stmt->bindParam('i', $_POST['id']);
    	if ($stmt->execute()) {
    		echo '"lastused" has been set ...';
    	} else echo 'There was a problem setting "lastused" - ', $stmt->error;
    ?>
    Is probably all you need... given the rest of your code didn't actually seem to do anything.

    Oh, and people, lands sake STOP making variables for nothing, particularly on things like fixed query-strings!
    Java is to JavaScript as Ham is to Hamburger.

  4. #4
    Join Date
    Apr 2013
    Posts
    81
    thanks guys and you're right, a lot of time If it doesn't work I try snippets online

  5. #5
    Join Date
    Apr 2013
    Posts
    81
    sorry, doesn't work trying to figure it out.

  6. #6
    Join Date
    Apr 2013
    Posts
    81
    you're right friend, the one objective is to update. I'm presently trying to make it work.

  7. #7
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,348
    Put this code in to a file of its own, place it in a location that is easy for you to remember
    PHP Code:
    <?php      
    // database.php
    $link mysqli_connect('localhost','root','cookie'); 
    mysqli_select_db($dbconnect'homedb') or die( "Unable to select database");      
    ?>
    This is your other script, the one that updates a field in your database
    PHP Code:
    <?php
    include("/path/to/file/database.php");

    if( 
    $_SERVER['REQUEST_METHOD']=="POST" and isset($_POST['submit']) ) { 

        if( 
    mysqli_real_escape_string($linkfilter_var$_POST['id'], FILTER_SANITIZE_STRING )) ){
        
            
    $update mysqli_query($link"UPDATE emailtbl SET lastused=NOW() WHERE id='$id';--") ;  
            
        }else{
        
            echo 
    "Ooops! Something wasn't set ... ";
        }
        
        if(!
    update){
        
            echo 
    "Ooops! Something didn't work -> ".mysqli_error$link );
            exit();
        }else{
        
            echo 
    "Updates complete...";
            
        }

    sleep(4); // pause for 4 seconds, suggest you remove this when you have it working

    // send user to another page    
    header("Location: go/somewhere/else.php");
    }

    ?>
    Most of the items you had were pointless checks, all you need is the users ID, so as long as the users ID is set, your database is updated with the most current time, any errors will be output.
    Last edited by \\.\; 08-22-2014 at 03:38 PM.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  8. #8
    Join Date
    Apr 2013
    Posts
    81
    php
    // database.php
    $link = mysqli_connect('localhost','root','cookie');
    mysqli_select_db($dbconnect, 'homedb') or die( "Unable to select database");
    ?>
    gets this message:
    Warning: mysqli_select_db() expects parameter 1 to be mysqli, null given in C:\xampp\htdocs
    Would u please define?

  9. #9
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,348
    I forgot to alter one of the items...

    PHP Code:
    <?php       
    // database.php 
    $link mysqli_connect('localhost','root','cookie');  
    mysqli_select_db($link'homedb') or die( "Unable to select database");       
    ?>
    Try that.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  10. #10
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,348
    and I noticed another...

    PHP Code:
    if( $id=mysqli_real_escape_string($linkfilter_var$_POST['id'], FILTER_SANITIZE_STRING )) ){ 
    I tell you, these pain meds are wrecking my head.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  11. #11
    Join Date
    May 2014
    Posts
    936
    Quote Originally Posted by \\.\ View Post
    I tell you, these pain meds are wrecking my head.
    Welcome to my world. Between the Parkinsonism, non-24, fibromyalgia (Latin for "we don't know what's wrong with you"), Lupus and gross neuroprathy, I'm on enough pain meds to kill an elephant.

    The loss of gross motor skills while still having fine motor skills being one of the fun side-effects of one of these meds I'm on. I can type, but I can't hold a mug or walk across the room. I'd almost rather deal with the pain.
    Java is to JavaScript as Ham is to Hamburger.

  12. #12
    Join Date
    Apr 2013
    Posts
    81
    messages=
    Ooops! Something wasn't set ... Updates complete...
    Warning: Cannot modify header information - headers already sent by
    (output started at C:\xampp\htdocs\home\emaildrop.php:223) in
    C:\xampp\htdocs\home\lastused.php on line 27

    PHP Code:
    <?php       
    // database.php 
    $link mysqli_connect('localhost','root','cookie');  
    mysqli_select_db($link'homedb') or die( "Unable to select database");       
    ?>
    I tried to send screenshot to show that I'm getting desired menu and display, just no update. Seems that we're in the boat-just add memory
    loss(or subtract memory LOL)

    PHP Code:
     <?php 
    include("database.php"); 
    if( 
    $_SERVER['REQUEST_METHOD']=="POST" and isset($_POST['submit']) ) { 
        if( 
    $id=mysqli_real_escape_string($linkfilter_var$_POST['id'], FILTER_SANITIZE_STRING )) ){  
            
    $update mysqli_query($link"UPDATE emailtbl SET lastused=NOW() WHERE id='$id';--") ;   
      }
        else
      {      
            echo 
    "Ooops! Something wasn't set ... "
      }        
        if(!
    update)
      {      
            echo 
    "Ooops! Something didn't work -> ".mysqli_error$link ); 
            exit(); 
      }
         else

     {      
            echo 
    "Updates complete...";          
     } 
    sleep(4); // pause for 4 seconds, suggest you remove this when you have it working 

    // send user to another page  *******why? ***** 
     
    header("Location: emaillist.php"); 
     } 
    ?>
    ------------------------------------------
    <BODY onload="startclock()"><center>
    <FORM name=clock method="post" action="" onsubmit=0>
    <INPUT size=11 value=....Initializing.... name=face><p>

    PHP Code:
     <?php  // **************** line 223 ***************

    include ("lastused.php");
    include (
    "counter.php");
    $id="''";
        
    $con=mysqli_connect("localhost","root","cookie","homedb");
        
        
    // ============== check connection

        
    if(mysqli_errno($con))
        {echo 
    "Can't Connect to mySQL:".mysqli_connect_error();}
        else
        {echo 
    "</br>";}

    // ==========This creates the drop down box using records in the table

           
    echo "<select name= 'target'>";
        echo 
    '<option value="">'.'---select email account ---'.'</option>';
        
    $query mysqli_query($con,"SELECT target FROM emailtbl");
        
    $query_display mysqli_query($con,"SELECT * FROM emailtbl");
        while(
    $row=mysqli_fetch_array($query))
        { echo 
    "<option class=highlight value='"$row['target']."'>".$row['target']
        .
    '</option>';}
        echo 
    '</select>';
        
    ?>
    <input type="submit" name="submit" value="Submit"/>
        </form>
         <?php
            
    if(isset($_POST['target']))
      {
        
    $id $_POST['id'];
        
    $name $_POST['target'];
        
    $fetch="SELECT target, username, password, emailused, lastused, purpose, saved FROM emailtbl WHERE target = '$name'";
        
    $result mysqli_query($con,$fetch);
        if(!
    $result)
        {echo 
    "Error:".(mysqli_error($con));}

    // =============================== this displays the table

        
    echo '<table border="1">'.'<tr>'.'<td bgcolor="#FFD47F" align="center">''email menu''</td>'.'</tr>';
        echo 
    '<tr>'.'<td>'.'<table border="1">'.'<tr>'.'<td bgcolor="#ccffff">'.'target'.'</td>'.'<td bgcolor="#ccffff">'.'username'.'</td>'.'<td bgcolor="#ccffff">''password' .'</td>'.'<td bgcolor="#ccffff">''emailused''</td>'.'<td bgcolor="#FFD47F">''lastused' .'</td>'.'<td bgcolor="#ccffff">''purpose''</td>'.'<td bgcolor="#ccffff">''saved' .'</td>'.'</tr>';
    //    while( $row = mysqli_fetch_assoc( $result ) )
    // while($data = mysqli_fetch_row($fetch))
        
    while($data=mysqli_fetch_row($result))
        {echo (
    "<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td><td>$data[4]</td><td>$data[5]</td><td>$data[6]</td></tr>");}
        echo 
    '</table>'.'</td>'.'</tr>'.'</table>';
      }
        
    ?>
        </body></html>

  13. #13
    Join Date
    May 2014
    Posts
    936
    First off you're using mysqli, so you don't have to do this:

    Code:
    $link = mysqli_connect('localhost','root','cookie');   
    mysqli_select_db($link, 'homedb') or die( "Unable to select database");
    That's mysql_ style.

    Hence:

    Code:
    $link = new mysqli('localhost', 'root', 'cookie', 'homedb'); 
    if ($link->connect_error) die (
    	'Connect Error (' . $link->connect_errno . ') ' . $link->connect_error
    );
    THIS:
    Code:
    if( $_SERVER['REQUEST_METHOD']=="POST" and isset($_POST['submit']) ) {  
        if( $id=mysqli_real_escape_string($link, filter_var( $_POST['id'], FILTER_SANITIZE_STRING )) ){   
            $update = mysqli_query($link, "UPDATE emailtbl SET lastused=NOW() WHERE id='$id';--") ;    
      }
    Is NOT how you are supposed to use mysqli. You do NOT blindly paste values into a query string, even if you use the _real_escape_string nonsense. It is also extremely unlikely you actually want a low priority order AND.

    Guessing a bit, that "--" isn't really valid mysql-side. I'm assuming you are doing that as you're showing ID's as starting at 1 when in the table they start at zero. (which IMHO is just a silly waste of time).

    Code:
    if (
    	$_SERVER['REQUEST_METHOD']=="POST" &&
    	isset($_POST['submit'])
    ) {  
    	$stmt = $link->prepare('UPDATE emailtbl SET lastused = NOW() WHERE id = ? ');
    	$stmt->bindParam('i', $_POST['id']--);
    	$stmt->execute();
    }
    Basically, STOP trying to use mysqli like it was mysql_! (This is ANOTHER reason I prefer PDO, it generally forces you to relearn. Of course I think whoever thought mysqli needed procedural wrappers should be put down like Old Yeller.)

    As to your current error, you cannot send header() AFTER you output ANYTHING. So all those echo before the HEADER HAVE to go after... again though, NOT that I'd be wasting time with some silly reload when you could just 'include' the resulting page instead of wasting bandwidth and handshakes like that. Much less that 'sleep' just TRYING to hang the server for Christmas only knows what...

    I don't know who's been telling people to use header like that, but they SERIOUSLY need a good swift boot in the patoot.
    Last edited by deathshadow; 08-23-2014 at 12:07 AM.
    Java is to JavaScript as Ham is to Hamburger.

  14. #14
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,348
    Thing is with mysqli is that they have http://uk1.php.net/manual/en/mysqli.select-db.php and other functions that are the same as mysql_ versions, both procedural and OO style.

    standard sql uses -- as the start of a comment and ; terminates a query string which means that if someone trued appending a hack to the end of the current query to be run, it would be ignored as a comment.

    As far as I am aware PHP uses standard sql strings, unless they have changed and use C style comments, then they have kept things very quiet on that front.

    So assume the visitor is trying to inject some code to append a string and you don't ;-- comment out the end of your string you could end up with an appended command to drop your members table or your catalog or entire news repository. This is well documented subject by googling sql injection examples and you will see that some use ' as part of the attack.

    The query about _real_escape_string, why are you saying this is nonsense? if you don't escape your query string and it has an errant apostrophe or quote, it breaks the query string so it needs to be escaped and you need to sanitize any incoming data in case it is a hack attempt.

    As for the sleep(), it is only in the demo for the sole purpose of hanging the page so that the OP can see if the output error "Ooops! Something wasn't set ... " displays before the user is sent to the designated page.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  15. #15
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,348
    Quote Originally Posted by 12Strings View Post
    messages=
    Ooops! Something wasn't set ... Updates complete...
    Warning: Cannot modify header information - headers already sent by
    (output started at C:\xampp\htdocs\home\emaildrop.php:223) in
    C:\xampp\htdocs\home\lastused.php on line 27
    Getting that error means your form submission had a field called id with nothing in it.

    Perhaps you should post your HTML page, especially the form and we can then get a fuller picture instead of groping around in the dark because you have indicated that you have copied and pasted code, you might not have the needed field or some other reason why the field in question isn't working.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles