PHP database update question

Discussion in 'Mac Programming' started by barr08, Jul 3, 2007.

  1. macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #1
    Hi, I am working on a project for a MySQL class, and I am creating a sample database in which I need to create a table, add info to this table (name, email, etc.), view the table, and update fields that already exist on the table, using PHP. I have the first three working, but I am having trouble updating fields. I want to be able to enter a name in an html form, and enter information in text boxes to replace the existing information in the database.

    My files are located here:
    http://nemo.mwd.hartford.edu/~bbarr/IIT310/Final/FinalPHPDB/

    This one creates the field:
    http://nemo.mwd.hartford.edu/~bbarr/IIT310/Final/FinalPHPDB/dataEntry.html

    This views the info:
    http://nemo.mwd.hartford.edu/~bbarr/IIT310/Final/FinalPHPDB/retrieveDisplayAll.php

    I want to be able to edit the four fields. Any help or input would be great. I may have explained this in a confusing way, so I'll try and clarify if you have any questions. I am not much of a programmer, I focus on the design aspect of my web design major, but I figured this stuff would be good to have at least a working knowledge of.

    Thanks for looking!
     
  2. macrumors 6502

    Joined:
    Jun 1, 2006
    #2
    Assuming that you just want to have them enter a name and details, if the name does not exist then add all details, otherwise, find the name and replace the details.

    $query = "select * from table where name = $name";
    $result = mysql_query($query);

    if (mysql_num_rows($result) == 0)
    {
    /* New add to the system */
    $insert_query = "insert into ....";​
    }
    else
    {
    /* Update */
    $row = mysql_fetch_assoc($result);

    /* Get the old ID */
    $id = $row["id"];

    /* Create the update query */
    $update_query = "update table set ...... where id = $id";​
    }

    This also assumes the name is unique. Otherwise, you will potentially have multiple rows to update.
     
  3. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #3
    Thanks, i'll try this out, it might be easier to edit the fields based on a unique account number (000x) i have asigned to each account, that way I know I am editing a unique field.
     
  4. macrumors 6502

    Joined:
    Jun 1, 2006
    #4
    If you want to do it that way, in your item listing page, add an edit link that supplies the item ID through GET. Then, when you are displaying the item to the user in a form allowing them to edit the details, add a hidden field containing the id so you will have it in POST.

    A link will be in the form http://site.com/edit_item&id=5

    if (isset($_GET["id"]))
    {
    /* Retrieve the item from the database and prepopulate the form with the existing values */
    $id = $_GET["id"];
    $query = "select * from table where id = $id";

    $row = mysql_fetch_assoc($row);

    /* Display the form with the hidden ID field */​

    }
    else if (isset($_POST["id"]))
    {

    /* Retrieve the new details from the form and update the database entry */
    $id = $_POST["id"];

    $query = "update table .... where id = $id";​

    }

    Note that this contains no error checking (ie. an invalid ID can be supplied). Also, it may be useful to check if the values being updated are in fact changed.
     
  5. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #5
    OK I have tried the first solution posted by antibact1, and I think I am doing something wrong. I have it up at: http://nemo.mwd.hartford.edu/~bbarr/IIT310/Final/FinalPHPDB/
    under updateData.php. My code is basically what antibact1 gave me.

    Any ideas? I know the "....." right above has to be something, but I am not sure what. I am getting an error on the 15th line, "
    if (mysql_num_rows($result) == 0)"

    Thanks
     
  6. macrumors 6502

    Joined:
    Jun 1, 2006
    #6
    Sorry the code I gave was just a template.

    There are several other things you do. Here would be a full example with no error checking.

    $hookup=mysql_connect($server, $user, $pass);

    $dbNow=@mysql_select_db($currentDB, $hookup);

    /* Pull all of the form variables and set them */
    $uname = $_POST["uname"];
    $email = $_POST["email"];
    $acnt = $_POST["acnt"];
    $cost = $_POST["cost"];

    $query = "select * from $currentTable where uname = $uname";
    $result = mysql_query($query);

    if (mysql_num_rows($result) == 0)
    {
    /* New add to the system */
    $insert_query = "insert into $currentTable (uname, email, acnt, cost) values ($uname, $email, $acnt, $cost)";

    $result = mysql_query($insert_query);​

    }
    else
    {
    /* Update */
    $row = mysql_fetch_assoc($result);

    /* Get the old ID */
    $id = $row["id"];

    /* Create the update query */
    $update_query = "update $currentTable set uname = $uname, email = $email, acnt = $acnt, cost = $cost where id = $id";

    $result = mysql_query($update_query);​

    }

    Note that none of the queries escape any special characters that may mess up your query. It also assumes that each of the parameters has been set. Ideally, you would check each of the parameters to determine that they have been set, and construct your queries that way.

    If you need more help, just let me know.
     
  7. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #7
    Oh it was a template. I feel so stupid...

    I'm going to give this a shot, thanks for your help.
     
  8. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #8
    OK so I am still getting an error on the rows line. The error is:

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/MWD/bbarr/www/IIT310/Final/FinalPHPDB/updateData.php on line 21

    I recognize this error from other programs, i think it is pretty broad. Do you have any idea what it could mean in this code?
     
  9. macrumors 6502

    Joined:
    Jun 1, 2006
    #9
    No worries.

    That error means the query is not executing successfully. So when you pass the result resource to mysql_num_rows, it is not a valid result. Is the variable $currentTable defined prior to executing any of that code. Another suggestion is to execute

    echo $query;

    after the query to take a look at the query. Also, you may want to do a

    echo mysql_error();

    to get a better idea of what the error is.
     
  10. macrumors regular

    Joined:
    Mar 6, 2007
    #10
    You are referencing a variable, $currentTable, that is not defined anywhere. So PHP will use an empty value in it's place, making the $query variable look something like:
    "select * from where uname = <some_user>"

    If the mysql table you are selecting from will not change, you can safely replace the $currentTable variable with the name of the table. If it needs to be dynamic, then make sure to set the $currentTable variable prior to using it in the query statement.

    In the case where the table name won't change, the line setting the query variable would look like this:
    Code:
    $query = "select * from <table_name> where uname = '$uname'";
    And you would of course replace <table_name> above with the actual name of your table. Make sense?

    Oh, and one more potential gotcha. You will need to add single-quotes around each variable where text will be used in the query. So for example
    Code:
    $insert_query = "insert into <table_name> (uname, email, acnt, cost) values ('$uname', '$email', '$acnt', '$cost')";
    and
    Code:
    $update_query = "update <table_name> set uname = '$uname', email = '$email', acnt = '$acnt', cost = '$cost' where id = '$id'";
    Technically the $id above deosn't need to be wrapped in single-quotes, but it won't hurt. I hope this helps. Let us know if you have any more questions.
     
  11. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #11
    I have $currentTable defined in the program, it is above the section of the code that I put in this thread. Is this something I still need to do? I am still getting the error on the mysql_num_rows() line.
     
  12. macrumors regular

    Joined:
    Mar 6, 2007
    #12
    Put the single-quotes around the $uname variabe, as mentioned above. And you might as well put the single-quotes around the rest of the variables in your mysql queries, because they will give you errors as well.
     
  13. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #13
    OK so I did this and it is still happening :mad: ! Any other ideas? My code now looks like this:

    Code:
    <?php
    	$server="localhost";
    	$user="bbarr";
    	$pass="******";
    	$currentDB="bbarr";
    	$currentTable="boyfight3";
    
    $hookup=mysql_connect($server, $user, $pass);
    
    $dbNow=@mysql_select_db($currentDB, $hookup);
    
    /* Pull all of the form variables and set them */
    $uname = $_POST["uname"];
    $email = $_POST["email"];
    $acnt = $_POST["acnt"];
    $cost = $_POST["cost"];
    
    $query = "select * from $currentTable where uname = '$uname'";
    $result = mysql_query($query);
    
    if (mysql_num_rows($result) == 0)
    {
    
        /* New add to the system */
        $insert_query = "insert into $currentTable (uname, email, acnt, cost) values ('$uname', '$email', '$acnt', '$cost')";
    
        $result = mysql_query($insert_query);
    
    }
    else
    {
    
        /* Update */
        $row = mysql_fetch_assoc($result);
    
        /* Get the old ID */
        $id = $row["id"];
    
        /* Create the update query */
        $update_query = "update $currentTable set uname = '$uname', email = '$email', acnt = '$acnt', cost = '$cost' where id = '$id'";
    
        $result = mysql_query($update_query);
    
    }
    
    	
    ?>
    
     
  14. macrumors regular

    Joined:
    Mar 6, 2007
    #14
    First of all, it would be best if you remove your user name and password from your code above. ;) Second, add the following line right in between the $query= and the $result= lines:
    Code:
    echo $query;
    And then add the following after the $result= line:
    Code:
    echo mysql_error();
    Let us know what that displays.
     
  15. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #15
    Haha shoot I forgot about the PW. I am so frazzled this is due very soon.

    I added the echos, and I got:

    select * from boyfight3 where uname = ''select * from boyfight3 where uname = ''
    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/MWD/bbarr/www/IIT310/Final/FinalPHPDB/updateData.php on line 23

    boyfight3 is the name of my table
     
  16. macrumors regular

    Joined:
    Mar 6, 2007
    #16
    This confuses me. According to this, the variable $uname is set to "select * from boyfight3 where uname=" which may be your problem. to debug, let's add this to the first line of your code (after the <?php):
    Code:
    print_r($_POST);
    exit();
    In order to view the formatted results, view the source of the returned web page and copy the results there.
     
  17. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #17
    The result is:

    Array
    (
    )

    ?? lol

    thanks for all your help by the way, very clutch!
     
  18. macrumors regular

    Joined:
    Mar 6, 2007
    #18
    OK, I understand the previous results now, too. Your $_POST is not getting populated by the referring page. Are you trying to load this page directly, or by posting a form from a referring page? Because only the latter will give you results that work. Otherwise, as you are seeing now, the $_POST variable (which is the global variable that holds all form POST information) is not getting populated.

    To test the pade by loading directly (instead of using a form submit), enter something like this into the URL:
    http://localhost/yourpage.php?uname=somename&email=someemail&acnt=someacnt&cost=somecost(blue text is the part to add)

    In other words, you are injecting the variables manually into the URL since they are not being passed as POST variables. Also, remove the print_r and exit() lines previously added.
     
  19. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #19
  20. macrumors regular

    Joined:
    Mar 6, 2007
    #20
    Let's try adding the print_r($_POST) back in, with the updated URL.
     
  21. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
  22. macrumors newbie

    Joined:
    Dec 30, 2004
    Location:
    Bristol, UK
    #22
    If you pass the variables in the URL like that, they end up in $_GET, not $_POST.

    You can use a function like:

    function getParam($name) {
    if(isset($_GET[$name])) return $_GET[$name];
    if(isset($_POST[$name])) return $_POST[$name];
    return false;
    }

    If you want to switch between the two.
     
  23. macrumors regular

    Joined:
    Mar 6, 2007
  24. thread starter macrumors 65816

    barr08

    Joined:
    Aug 9, 2006
    Location:
    Boston, MA
    #24
    After changing it to REQUEST, I got this:

    Code:
    Array
    (
        [uname] => somename
        [email] => someemail
        [acnt] => someacnt
        [cost] => somecost
        [__utmz] => 126326804.1183574340.1.1.utmccn=(referral)|utmcsr=hartford.facebook.com|utmcct=/profile.php|utmcmd=referral
        [__utma] => 126326804.1276275613.1183574340.1183574340.1184015861.2
        [WRUID] => 0
        [__utmc] => 126326804
    )
    
     
  25. macrumors regular

    Joined:
    Mar 6, 2007
    #25
    Right, I forgot. Thanks. They will still show in $_REQUEST though.
     

Share This Page