www.webdeveloper.com
Results 1 to 14 of 14

Thread: Can't get PHP to update MySQL DB

  1. #1
    Join Date
    Aug 2014
    Posts
    7

    Can't get PHP to update MySQL DB

    Hi all,

    I'm pretty much completely new to PHP and I've been gathering bits of code from around the internet to try and get a little database going.

    I can get the exisiting data to display in a table but I can't seem to update it - I don't recieve any errors so I'm not sure where I'm going wrong.

    Here is the page which shows the existing data:

    PHP Code:
    <?php
    include("database_config.php");

    include(
    "database_connect.php");

    // Find value of id from URL
    $id=$_GET['id'];

    // Fetch the data from the database
    $sql="SELECT * FROM $tbl_name WHERE id='$id'";
    $result=mysql_query($sql);

    $rows=mysql_fetch_array($result);
    ?>


    <table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <form name="form1" method="post" action="submit_update.php">
    <td>
    <table width="100%" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <td>&nbsp;</td>
    <td colspan="3"><strong>Update data in mysql</strong> </td>
    </tr>
    <tr>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    <td align="center">&nbsp;</td>
    </tr>
    <tr>
    <td align="center">&nbsp;</td>
    <td align="center"><strong>Company Name</strong></td>
    <td align="center"><strong>Firstname</strong></td>
    <td align="center"><strong>Lastname</strong></td>
    <td align="center"><strong>Email</strong></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td align="center">
    <input name="companyname" type="text" id="companyname" value="<? echo $rows['companyname']; ?>">
    </td>
    <td align="center">
    <input name="firstname" type="text" id="firstname" value="<? echo $rows['firstname']; ?>" size="15">
    </td>
    <td align="center">
    <input name="lastname" type="text" id="lastname" value="<? echo $rows['lastname']; ?>" size="15">
    </td>
    <td>
    <input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="15">
    </td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td>
    <input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>">
    </td>
    <td align="center">
    <input type="submit" name="Submit" value="Submit">
    </td>
    <td>&nbsp;</td>
    </tr>
    </table>
    </td>
    </form>
    </tr>
    </table>

    <?php
    // Exit connection
    mysql_close();
    ?>
    And here is the page which I'm trying to send the data to:

    PHP Code:
    <?php
    include("database_config.php");

    include(
    "database_connect.php");

    // Update SQL statment
    $sql="UPDATE $tbl_name SET companyname='$companyname', firstname='$firstname', lastname='$lastname', email='$email' WHERE id='$id'";
    $result=mysql_query($sql);

    // Result messages
    if($result){
    echo 
    "Your changes have been saved!";
    echo 
    "<BR>";
    echo 
    "<a href='view.php'>View result</a>";
    }

    else {
    echo 
    "Oops! Something went wrong.";
    }

    ?>
    I'm assuming (and I'm probably wrong) that the submit page is looking for $companyname, $firstname etc. but can't find it on the update page.

    Could anyone point out where I'm going wrong?

    Thanks!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,430
    The problem with using code snippets found in random locations on the 'net is that many of them are out-dated (or just plain bad).

    The update script might have worked many years ago when the standard behavior of PHP was to populate variables from submitted form fields, but now you should get them from the $_POST or $_GET array (depending on what method the form uses). Therefore you would want to access $_POST['companyname'] instead of $companyname.

    On top of that, you should "sanitize" any external inputs being used in DB queries before you're sorry. On top of that, the old MySQL PHP extension (which contains all those mysql_*() functions) is deprecated and may not be supported in future versions of PHP, so it should be avoided. Instead, use the MySQLi or PDO extensions, instead. (I prefer PDO, as it is DBMS-agnostic.)

    So I'll leave you for now to do some searching/reading, and if no one comes along with more specific fixes for you, I'll try to check back later tonight or this weekend.
    "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
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    819
    Be sure to turn on error checking to help you debug your many many coding errors.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  4. #4
    Join Date
    Aug 2014
    Posts
    7
    Thanks for the reply

    This is where I get a little lost - If I was to change my current code to use $_POST as you suggest, I assume that would go in the form page?

    The only place I have come across POST so far is in the form for the method value (method="post") - Are the 2 related or completely seperate things?

    Thanks

  5. #5
    Join Date
    Aug 2014
    Posts
    7
    Quote Originally Posted by ginerjm View Post
    Be sure to turn on error checking to help you debug your many many coding errors.
    Thanks - I didn't think of that, I'll see what it throws up.

  6. #6
    Join Date
    Aug 2014
    Posts
    7
    Quote Originally Posted by NogDog View Post
    The problem with using code snippets found in random locations on the 'net is that many of them are out-dated (or just plain bad).

    The update script might have worked many years ago when the standard behavior of PHP was to populate variables from submitted form fields, but now you should get them from the $_POST or $_GET array (depending on what method the form uses). Therefore you would want to access $_POST['companyname'] instead of $companyname.

    On top of that, you should "sanitize" any external inputs being used in DB queries before you're sorry. On top of that, the old MySQL PHP extension (which contains all those mysql_*() functions) is deprecated and may not be supported in future versions of PHP, so it should be avoided. Instead, use the MySQLi or PDO extensions, instead. (I prefer PDO, as it is DBMS-agnostic.)

    So I'll leave you for now to do some searching/reading, and if no one comes along with more specific fixes for you, I'll try to check back later tonight or this weekend.
    I think this is where I get confused!

    My form method value is post. Does what you're saying mean change that to $_POST or am I am on the complete wrong track?

  7. #7
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    819
    It means you can't be looking for data in $_GET - everything comes in thru $_POST. And the indices have to be in quotes.

    I'm not sure how the two blocks of code are connected. Your first block is not too far off - error checking at the beginning will help. The html is so poor though. All those styles need to be done thru css as many are deprecated and in general it's a mess to read thru. Also - you have a table encasing another table. Why? The main thing I don't see is where you are getting an 'id' value. Is that really coming in thru a GET that you are not showing us?

    You've spent a lot of time researching and trying to pull together a quick and dirty appl. You aren't learning anything from this and are basically putting poor code into production. Your time would be better spent learning how things are supposed to be done correctly and then doing it yourself.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  8. #8
    Join Date
    Aug 2014
    Posts
    7
    Quote Originally Posted by ginerjm View Post
    It means you can't be looking for data in $_GET - everything comes in thru $_POST. And the indices have to be in quotes.

    I'm not sure how the two blocks of code are connected. Your first block is not too far off - error checking at the beginning will help. The html is so poor though. All those styles need to be done thru css as many are deprecated and in general it's a mess to read thru. Also - you have a table encasing another table. Why?
    So in terms of the current code, where would $_POST be used? I'm just trying to get my head around it all.

    I'll take a look at the HMTL at some point, the main purpose of this script was just to get the function of it working.

    Quote Originally Posted by ginerjm View Post
    The main thing I don't see is where you are getting an 'id' value. Is that really coming in thru a GET that you are not showing us?

    You've spent a lot of time researching and trying to pull together a quick and dirty appl. You aren't learning anything from this and are basically putting poor code into production. Your time would be better spent learning how things are supposed to be done correctly and then doing it yourself.
    The id value is coming through a previous page which is here:

    PHP Code:
    <?php
    include("database_config.php");

    include(
    "database_connect.php");

    $sql="SELECT * FROM $tbl_name";
    $result=mysql_query($sql);
    ?>

    <table width="610" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <td width="608">
    <table width="1000" border="1" cellspacing="0" cellpadding="3">
    <tr>
    <td colspan="4"><strong>List data from mysql </strong> </td>
    </tr>

    <tr>
    <td width="20" align="center"><strong>ID</strong></td>
    <td width="123" align="center"><strong>Company Name</strong></td>
    <td width="67" align="center"><strong>Firstname</strong></td>
    <td width="72" align="center"><strong>Lastname</strong></td>
    <td width="42" align="center"><strong>Email</strong></td>
    <td width="54" align="center"><strong>Update</strong></td>
    </tr>

    <?php
    while($rows=mysql_fetch_array($result)){
    ?>

    <tr>
    <td><? echo $rows['id']; ?></td>
    <td><? echo $rows['companyname']; ?></td>
    <td><? echo $rows['firstname']; ?></td>
    <td><? echo $rows['lastname']; ?></td>
    <td><? echo $rows['email']; ?></td>

    <td align="center"><a href="update.php?id=<?php echo $rows['id']; ?>">update</a></td>
    </tr>

    <?php
    }
    ?>

    </table>
    </td>
    </tr>
    </table>

    <?php
    mysql_close
    ();
    ?>
    Is there a better way of doing this?

    Thanks

  9. #9
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    819
    Yeah - stop encasing a table inside of a table. Whatever are you thinking?

    ok - so this last code gets you an id from the user. What do you want to happen with it? Your original two pieces of code do two different things - read a table and output a table of all the values of that id. The second is supposed to be doing an update but you don't grab the input from anywhere.

    A form submits data thru the input tags via the name attribute. Each name= will exist as an index in the $_POST array (since you are doing a method='post'> which you then have to grab before you can do an update. And you need to do some sanitizing on those grabbed vars before putting them into a query. Of course since you are going to stop using MySQL_* functions and switch PDO, you don't have to worry about the last step if you simply use a prepared query.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  10. #10
    Join Date
    Aug 2014
    Posts
    7
    I've a feeling this may be a lot easier to start from scratch and write the code myself rather than picking different bits up from around the web!

  11. #11
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    819
    Very good grasshopper! You have learned your first lesson!

    Advice: read up on what's current in html and what's not anymore. Read up on how to use css to add styling to your html (to replace those deprecated things you are using). Then read a basic tutorial on php and learn how to structure your code, keeping php separate from html as much as possible. A few evenings getting some knowledge about the tools of your 'new' trade will be well worth the time.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  12. #12
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,376
    1. you need to SANITIZE you inputs.

    something like this.
    PHP Code:
    $id filter_var($_GET['id'],FILTER_SANITIZE_NUMBER_INT);

    // Fetch the data from the database 
    $sql_query_string sprintf("SELECT * FROM %s WHERE id='%s';--",$tbl_name,$id); 
    $result=mysql_query($dbh,$sql_query_string); 
    2. Connection Handler

    notice the $dbh, this is the variable that should be set with your
    include("database_connect.php"); script, you would
    PHP Code:
    $dbh mysqli_connect("server_location","username","password","database") or die("Error " mysqli_error($dbh));; 
    this will connect to a database, if your server encounters a problem the script will die and output the server error. Useful.

    3. I prefer to use the string print format function, its tidy, allows you to read the query string, its much clearer and you should add the ;-- to the end of the query string so that it is terminated, in the case of an injection attack the hacker could inject code to try and gain access. See an example http://www.rackspace.com/.../sql-injection-in-mysql a similar process is used to inject code in to a PHP script that breaks the PHP script to insert code that then allows or exposes the code to the person who injected the code.

    If memory serves me, several months ago this site was cracked and some program was placed on the server to allow the visitor to view the server contents, the other web domains of developer.com, what it did was expose the underlying system, what server was being used, etc. I am not going to go in to specifics but even with the best kept web server and forum, it is possible to be hacked because of underlying weakness in the supporting systems, what you have to do is limit the threat as best you can.

    4. In your update script, remember to "close" it...

    5. familiarize yourself with the mysqli_ methods as the mysql_ functions as indicated are depreciated and at some point in the near future, PHP will stop supporting altogether these functions.
    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?

  13. #13
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    819
    All good points. Except for that depreciation thingie.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  14. #14
    Join Date
    Aug 2014
    Posts
    7
    Ok, so ignoring the above code and going to my first page (where data is first input):

    PHP Code:
    $companyname mysqli_real_escape_string($con$_POST['companyname']);
    $firstname mysqli_real_escape_string($con$_POST['firstname']);
    $lastname mysqli_real_escape_string($con$_POST['lastname']);
    $email mysqli_real_escape_string($con$_POST['email']);

    $sql="INSERT INTO tbl_customers (companyname, firstname, lastname, email)
    VALUES ('
    $companyname', '$firstname', '$lastname', '$email')";

    if (!
    mysqli_query($con,$sql)) {
      die(
    'Error: ' mysqli_error($con)); 
    Is there any more I can do from this to help prevent SQL injections? Or am I still missing something?

    Thanks

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