#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Burb of Detroit, Michigan
    Posts
    90
    Rep Power
    77

    Mysqli_query bafflement


    I have been testing on a local server and thought to myself that I would test it out on the web server. It works perfectly locally, but not remotely. I have done Google searches, search this forum and change my code around what seems like a billion times. What it's doing it is deleting instead of updating the text content portion of the Table (it doesn't delete the row) on the server, so I think my query is working fine, but not the info isn't being sent? Here's a small portion of my code:

    PHP Code:
    if (isset($_POST['submit'])) {
                
                
    //$content = $result['content'];
                
                
    $id mysql_prep($_GET['page']);
                
    $content mysql_prep($_POST['content']); 
                
    //print_r($content);
                
                
    $query "UPDATE pages SET content='$content' WHERE id=1";
                
    $query mysqli_query($db$query) or die (mysqli_error($db));             
                
            } 
    I wrote tighter code, but when I start getting this problem I just trying to get it to work. I have taken out the mysql_prep() function out of the equation and get the same results. My best guess at this moment is either something isn't compatible with the PHP versions between the local and remote server, MySQL database is wrong on the remote server (Though gone through that with a fine tooth comb), or the most logical choice is I not doing something right. Any help would be greatly appreciated, even if it's just pointing me in the right direction or area will be fine. If more code is need I can post that also.

    Thanks John
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    the first thing you should do is get rid of all this stuff you somehow carried over from the old MySQL library.

    MySQLi has prepared statements, so no more fumbling with query strings and variables. At least you escape the values. Though I have a feeling you're not doing it right, because I don't see you passing the database object to your mysql_prep() function (which is necessary to determine the character encoding).

    Also, please throw away this "or die(mysqli_error())" stuff. I don't know who invented that, but it's a really, really terrible way of handling errors -- except for script kiddies trying to "hack" your website. They'll get great help while optimizing their attacks on your database

    After you've done that, what is the content of $content?

    Comments on this post

    • Strider64 agrees : Thanks
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Burb of Detroit, Michigan
    Posts
    90
    Rep Power
    77
    One more thing that is baffling me is how to update the table, I understand the logic of getting information from database. It's actually simple, but change the results is a different story. I know I have to use something like the following - "UPDATE pages SET content=$content WHERE menu_name=?". When I go to the PHP Manual they give an example how to insert to a table, but not update. So I thought I go to MySQL Reference manual on the syntax on how to do it, but that just seems to confuse me more. I am doing a local test script to see if I can get it working before I rewrite my final code. I do appreciate the help, even if I can just be pointed in the right direction like before. I have been doing Google Searches, but I think I being to vague or not wording the search correctly. Thanks once again for the help.


    PHP Code:
    $link mysqli_connect("localhost""root""******""example_db");

    /* check connection */
    if (mysqli_connect_errno()) {
        
    printf("Connect failed: %s\n"mysqli_connect_error());
        exit();
    }

    $menu "Green Cay";
    function 
    display_content($menu) {
         global 
    $link;
         
    /* create a prepared statement */
         
    if ($stmt mysqli_prepare($link"SELECT content FROM pages WHERE menu_name=?")) {
         
            
    /* bind parameters for markers */
            
    mysqli_stmt_bind_param($stmt"s"$menu);
         
            
    /* execute query */
            
    mysqli_stmt_execute($stmt);
         
            
    /* bind result variables */
            
    mysqli_stmt_bind_result($stmt$content);
         
            
    /* fetch value */
            
    mysqli_stmt_fetch($stmt);
         
            
    //printf("Content is : %s \n", $content);
         
            /* close statement */
            
    mysqli_stmt_close($stmt);
            
            return 
    $content;
         }
    }

    $content display_content($menu);
    $content .= " this is a test";

    function 
    update_content($menu$content) {
        
         
    }

    $content update_content($menu$content);

    echo 
    $content display_content($menu);

    /* close connection */
    mysqli_close($link); 
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    First of all, good work rewriting the code to prepared statements.

    Doing an UPDATE is actually even simpler, because it's over as soon as you've executed the prepared statement. Note that you need a query parameter for each value you want to insert, so in your case you have two: one for $content, one for $menu.

    You might wanna use the object-oriented API of the MySQLi extension, because it's somewhat less cumbersome:

    PHP Code:
    <?php

    $database 
    = new mysqli('localhost''USER''PW''DB');

    // define sensible test values
    $menu 'foo';
    $content 'bar';

    $update_stmt $database->prepare('
        UPDATE
            pages
        SET
            content = ?
        WHERE
            menu_name = ?
    '
    );
    $update_stmt->bind_param('ss'$content$menu);
    $update_stmt->execute();

    Comments on this post

    • Strider64 agrees : Thanks, I do like the Object-oriented API better
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Burb of Detroit, Michigan
    Posts
    90
    Rep Power
    77
    Just like to say Thank You for the help, it diffidently steered me in the right direction. I Know have a lot more to learn in PHP, but I think now I'm steered going in the right direction. My next adventure will be learning PDO, but for right now going in the mysqli direction is better that the old mysql.

    Comments on this post

    • aeternus agrees : mysqli prepared statements are just as good if you stick to the same database type

IMN logo majestic logo threadwatch logo seochat tools logo