Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Problem trying to Update a mysql record

    Hi everyone,

    I am a complete beginner when it comes to coding, i cobbled my results system from varying websites, i run a small website for a local junior football team and am having trouble with a results ticker http://test.nethertonjfc.com/results/results.php that i am trying to implement into the site. It used to work but now with the new season about to get under way i retested to make sure it was all ok and the error as started .

    I can add a new record and also delete an existing record, i cannot for some reason edit a record even though the output from the form says it was successful. Any help with tracking down my problem will be greatly appreciated

    this is the page where the work is done. (A TEST PAGE)
    http://test.nethertonjfc.com/results/list_records.php

    These are the 2 files that are used in the update process.

    http://test.nethertonjfc.com/results/update.php
    Code:
    <p>
      <?php
    $host="myhost"; // Host name
    $username="myuser"; // Mysql username
    $password="mypass"; // Mysql password
    $db_name="mydb"; // Database name
    $tbl_name="mytable"; // Table name
    
    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");
    
    // get value of id that sent from address bar
    $id=$_GET['id'];
    
    // Retrieve data from database
    $sql="SELECT * FROM $tbl_name WHERE id='$id'";
    $result=mysql_query($sql);
    
    $rows=mysql_fetch_array($result);
    ?>
    
    
    <p>
    <table width="100%" border="0" align="center" cellpadding="0" cellspacing="1">
    <tr>
    <form name="form1" method="post" action="update_ac.php">
    <td>
    <table width="105%" border="0" align="center" cellpadding="0" cellspacing="1">
    <tr>
    <td width="5%">&nbsp;</td>
    <td colspan="8"><strong>Update Results</strong> </td>
    </tr>
    <tr>
      <td align="center">&nbsp;</td>
      <td align="center"><strong>Age Group</strong></td>
      <td width="11%" align="center"><strong>Division/Cup</strong></td>
      <td width="12%" align="center"><strong>Home</strong></td>
      <td width="3%" align="center">&nbsp;</td>
      <td width="2%" align="center">&nbsp;</td>
      <td width="2%" align="center">&nbsp;</td>
      <td width="12%" align="center"><strong>Away</strong></td>
      <td width="33%" align="center"><strong>Goals</strong></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td width="20%" align="center">
    <input name="age_group" type="text" id="name" value="<? echo $rows['age_group']; ?>" size="5" maxlength="3">
    </td>
    <td align="center">
    <input name="division" type="text" id="division" value="<? echo $rows['division']; ?>" size="10" maxlength="15">
    </td>
    <td>
    <input name="home_team" type="text" id="home_team" value="<? echo $rows['home_team']; ?>" size="15">
    </td>
    <td><input name="home_team_score" type="text" id="home_team_score" value="<? echo $rows['home_team_score']; ?>" size="3" maxlength="2"></td>
    <td><strong>V</strong></td>
    <td><input name="away_team_score" type="text" id="away_team_score" value="<? echo $rows['away_team_score']; ?>" size="3" maxlength="2"></td>
    <td><input name="away_team" type="text" id="away_team" value="<? echo $rows['away_team']; ?>" size="15"></td>
    <td><input name="goals" type="text" id="goals" value="<? echo $rows['goals']; ?>" size="40"></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td>
    <input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>">
    </td>
    <td align="center">&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td align="center"><input type="submit" name="Submit" value="Submit"></td>
    </tr>
    </table>
    </td>
    </form>
    </tr>
    </table>
    
    <?php
    // close connection
    mysql_close();
    ?>
    http://test.nethertonjfc.com/results/update_ac.php
    Code:
    <?php
    $host="myhost"; // Host name
    $username="myuser"; // Mysql username
    $password="mypass"; // Mysql password
    $db_name="mydb"; // Database name
    $tbl_name="mytable"; // Table name
    
    
    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");
    
    // update data in mysql database
    $sql="UPDATE $tbl_name SET age_group='$age_group', division='$division', home_team='$home_team', home_team_score='$home_team_score', away_team='$away_team', away_team_score='$away_team_score', goals='$goals' WHERE id='$id'";
    $result=mysql_query($sql);
    
    // if successfully updated.
    if($result){
    echo "Successful";
    echo "<BR>";
    echo "<a href='/results/list_records.php'>Back to Results</a>";
    }
    
    else {
    echo "ERROR";
    }
    
    ?>
    I am not sure if this is all the info needed to help me but please ask if i missed something out.

    Regards

    Nigel
    Last edited by njnorcliffe; 09-04-2012 at 10:05 PM. Reason: resolved

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,641 Times in 4,603 Posts
    Since you have ZERO code in there that checks to see if the UPDATE actually is successful, your message about successful is not telling you anything.

    Anyway, first things first: DEBUG DEBUG DEBUG. And also add in code to catch the error.

    Code:
    $sql="UPDATE $tbl_name SET age_group='$age_group', division='$division', 
            home_team='$home_team', home_team_score='$home_team_score', 
            away_team='$away_team', away_team_score='$away_team_score', 
            goals='$goals' WHERE id='$id'";
    echo "<hr/>DEBUG SQL: " . $sql . "<hr/>\n";
    
    $result=mysql_query($sql) or die( mysql_error() );
    Why do you have apostrophes around what are almost certainly numbers? Surely scores, goals, and id are numeric fields, no?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    njnorcliffe (09-03-2012)

  • #3
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    First off thanks for taking the time to look at this for me.

    I created the code by using the site phpformgenerator http://phpformgen.sourceforge.net/new_demo/phpformgen/

    And just played around with it after creating the forms, To be honest i have no idea why the apostrophes are there. Like i mentioned above i am a complete beginner, i thought i had done quite well altering things and getting it all to work, only for this problem to suddenly appear.

    Anyways I added the code and this is what the browser brings up in the header then the normal successful message. I obviously have no idea but like to learn, thanks again for your help

    Code:
    DEBUG SQL: UPDATE njfc_phpbb3_results SET age_group='', division='', home_team='', home_team_score='', away_team='', away_team_score='', goals='' WHERE id=''

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,641 Times in 4,603 Posts
    Well, it's pretty obvious from that.

    You don't *HAVE* ANY values for *ANY* of those PHP variables.

    Code:
    SET age_group='', division='', home_team='', home_team_score='', away_team='', away_team_score='', goals='' WHERE id=''
    All of the values that should be there are totally blank.

    So where is the PHP code that reads--or tries to read?--the values from the form posting?

    The form on the first page says
    Code:
    <form name="form1" method="post" action="update_ac.php">
    So where do you use PHP to read those posted form values?

    That is, where is all your code that does $_POST["...name of form field..."] ??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    njnorcliffe (09-04-2012)

  • #5
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I see, so i used my insert form and copied the relevant $_POST entries, this still threw up a problem with the id but having asked the form to get the info from the browser and then telling it to $_POST it. Thanks to your knowledge and eagle eye it seems it is now working.

    My appreciation is massive, I will be saving the DEBUG line in a special file for future use , thanks again.

    Nigel

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,641 Times in 4,603 Posts
    What I do: Leave the DEBUG in the code but just comment it out. That way, if you ever need it again, just remove the comment marks (e.g., // or /*...*/) and it's right there ready to use.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    thats a good idea, thanks again.


  •  

    Posting Permissions

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