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 12 of 12
  1. #1
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Angry Updating MySQL Records

    Hi

    I am in the process of creating a form which will allow the user to edit existing records in a MySQL database. When the user selects a record, the id of the record can be seen in the url at the bottom of the page, and when the user clicks on the update link, they should be taken to the update.php page which should allow then to change the values of the selected record. the problem is that the selected record does not seem to be displayed when the user clicks on the update link.

    I have three scripts:
    list_records_profiles.php
    update.php
    update_ac.php

    list_records_profiles.php
    PHP Code:
    <html>
        <head>
            <title>All Profiles</title>
        </head>
        
        <div align="center">

        <?php
            
    include ('includes/header.html')
        
    ?>
        
    <body>


        <?php //Connect to Database
            //include ('mysql_connect.php');
        
            //connect to your database
            
    mysql_connect("","","") or die("cannot connect"); //(host, username, password)

            //specify database
            
    mysql_select_db("") or die("Unable to select database"); //select which database we're using
        
    ?>    
        
        <?php // Build SQL Query  
            
    $query "select * from players order by Club"
        
            
    //$numresults=mysql_query($query);
            //$numrows=mysql_num_rows($numresults);
            

            
    $result mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error());
        

                echo 
    "<p> &nbsp </p>";
                echo 
    "<h1>All Profiles</h1></br>";
        
            echo 
    "<table border='1'>";
            echo 
    "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> <th>Edit</th> </tr>";
            
    // keeps getting the next row until there are no more to get
            
    while($row mysql_fetch_array$result )) {
            
    // Print out the contents of each row into a table
            
    echo "<tr><td>"
            echo 
    $row['Name'];
            echo 
    "</td><td>"
            echo 
    $row['DOB'];
            echo 
    "</td><td>"
            echo 
    $row['Club'];
            echo 
    "</td><td>"
            echo 
    $row['Number'];
            echo 
    "</td><td>"
            echo 
    $row['Cost'];
            echo 
    "</td><td>"
            echo 
    $row['Position'];
            echo 
    "</td><td>"
            echo 
    $row['NationalTeam'];
            echo 
    "</td><td>"?>
            <a href="update.php?id=<?php echo $row['PlayerID']; ?>">update</a>    
            
        <?php
            
    echo "</td></tr>"
            } 

            echo 
    "</table>";
            
            echo 
    "<p>&nbsp </p>";            
        
    ?>
        
    </body>

        <div align="center">

        <?php
            
    include ('includes\footer.html');
        
    ?>        
        
    </html>
    update.php
    PHP Code:
    <html>
        <head>
            <title>Edit</title>
        </head>
        
        <div align="center">    
        
        <?php
            
    include ('includes/header.html')
        
    ?>

    <?php

            
    //connect to your database
            
    mysql_connect("","","") or die("cannot connect"); //(host, username, password)

            //specify database
            
    mysql_select_db("") or die("Unable to select database"); //select which database we're using);
            
            

        
    ?>
        
        <?php
            $id
    =$_GET['PlayerID'];

            
    $query "select * from players WHERE PlayerID ='$id'"
        
            
    //$numresults=mysql_query($query);
            //$numrows=mysql_num_rows($numresults);
    ?>

    <?php
            $result 
    mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error());

            echo 
    "<p> &nbsp </p>";
            echo 
    "<h1>Edit</h1></br>";
        
            echo 
    "<table border='1'>";
            echo 
    "<form name='form1' method='post' action='update_ac.php'>";
            echo 
    "<tr> <th>Name</th> <th>DOB</th> <th>Club</th> <th>Number</th> <th>Cost</th> <th>Position</th> <th>National Team</th> </tr>";
            
    // keeps getting the next row until there are no more to get
            
    while($row mysql_fetch_array$result )) {
            
    // Print out the contents of each row into a table
            
    echo "<tr><td>"
            echo 
    $row['Name'];
            echo 
    "</td><td>"
            echo 
    $row['DOB'];
            echo 
    "</td><td>"
            echo 
    $row['Club'];
            echo 
    "</td><td>"
            echo 
    $row['Number'];
            echo 
    "</td><td>"
            echo 
    $row['Cost'];
            echo 
    "</td><td>"
            echo 
    $row['Position'];
            echo 
    "</td><td>"
            echo 
    $row['NationalTeam'];
            echo 
    "</td></tr>"
            } 

            echo 
    "</table>";
            echo 
    "<p>&nbsp </p>";            
        
    ?>        
            
    <table>
    <tr>
    <td>&nbsp;</td>
    <td><input name="PlayerID" type="hidden" id="PlayerID" value="<?php echo $row['PlayerID']; ?>"></td>
    <td align="center"><input type="submit" name="Submit" value="Update"></td>
    <td>&nbsp;</td>
    </tr>
    </table>
    </td>
    <?php echo "</form>"?>
    </tr>
    </table>
        <?php
            
    echo "<p>&nbsp </p>";

            include (
    'includes\footer.html');
        
    ?>
        
            <div align="center">
    </html>
    update_ac.php
    PHP Code:
    <html>
        <head>
            <title>Edit</title>
        </head>
        
        <div align="center">    
        
        <?php
            
    include ('includes/header.html')
        
    ?>

    <?php

            
    //connect to your database
            
    mysql_connect("","",""); //(host, username, password)

            //specify database
            
    mysql_select_db("") or die("Unable to select database"); //select which database we're using);

    // update data in mysql database 
    $sql="UPDATE players SET name='$Name', 
                            dob='$DOB', 
                            club='$Club' 
                            number='$Number', 
                            cost='$Cost' 
                            position='$Position', 
                            nationalteam='$NationalTeam'
                            WHERE PlayerID='$PlayerID'"
    ;
    $result=mysql_query($sql);

    // if successfully updated. 
    if($result){
    echo 
    "Successful";
    echo 
    "<BR>";
    echo 
    "<a href='list_records_profiles.php'>View result</a>";
    }

    else {
    echo 
    "ERROR";
    }

    ?>

        <?php
            
    echo "<p>&nbsp </p>";

            include (
    'includes\footer.html');
        
    ?>
        
            <div align="center">
    </html>
    any help would be greatly appreciated

    thanks in advance

    el nino

  • #2
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    What does your actual markup look like? In other words, do the links look correct and actually work to send a valid `PlayerID` value to the "update.php" script?

    If you load "update.php?id=100" (or whatever a valid `PlayerID` value would be, do you see any difference?

    What does the "update.php" script show when it is loaded? With or without a query string parameter?


    On another note:
    PHP Code:
        
        <?php
            $id
    =$_GET['PlayerID'];

            
    $query "select * from players WHERE PlayerID ='$id'";
    Scary scary. Make sure you a) validate incoming data (i.e. is it supposed to be a numeric value only?) and b) escape the data and/or use a parameterized query. Never let any user-initiated data to touch your database without a proper sanitization.

    You should also make certain to validate data prior to making a database connection. Don't waste resources with the database if the user hasn't passed anything that you actually intend to use to query the database with.

  • #3
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    the PlayerID which is being sent does seem to be correct in that it does relate to the record in the database. However no matter what id is sent no record is displayed on the next page (update.php)

    when the update.php script is loaded it simply shows the headings for the columns but does not show any values

    i've echoed the query, and it seems to be that PlayerID is not making it there
    Last edited by el_nino; 03-06-2010 at 08:55 PM.

  • #4
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Ah. Ok, now I see this:
    PHP Code:
    <a href="update.php?id=<?php echo $row['PlayerID']; ?>">update</a>
    and this:
    PHP Code:
    $id=$_GET['PlayerID']; 
    See what's wrong there? You're passing a GET variable named 'id', and trying to retrieve one named 'PlayerID'. In the receiving script, you need to reference $_GET['id'].

  • Users who have thanked bdl for this post:

    el_nino (03-06-2010)

  • #5
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    problem kinda (see below) solved... thank you
    Last edited by el_nino; 03-07-2010 at 03:03 PM.

  • #6
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    ok, problem kinda solved

    i'm able to output the selected record into a table so the user can view the results but i'm unable to output the results into a form so they can then be edited. i've created the following script which should enable me to do this but i just end up with a blank form

    update_v2.php
    PHP Code:
    <html>
        <head>
            <title>Edit</title>
        </head>
        
        <div align="center">    
        
        <?php
            
    include ('includes/header.html')
        
    ?>

    <?php

            
    //connect to your database
            
    mysql_connect("","","") or die("cannot connect"); //(host, username, password)

            //specify database
            
    mysql_select_db("") or die("Unable to select database"); //select which database we're using);
            
            

        
    ?>
        
        <?php
            
    //$id=$_GET['id'];
            
            
    $id = (is_numeric($_GET['id'])) ? $_GET['id'] : 0;


        
    //$PlayerID = ((isset($_GET['PlayerID'])) ? $_GET['PlayerID'] : 0);
            
            
            
    $query "select * from players WHERE PlayerID ='$id'"
            echo 
    $query;
        
            
    //$numresults=mysql_query($query);
            //$numrows=mysql_num_rows($numresults);
    ?>
    <body>
    <h1>Edit Record</h1>
    <table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <form name="form1" method="post" action="update_ac.php">
    <td>
    <table width="100%" border="0" cellspacing="1" cellpadding="0">
    <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>Name</strong></td>
    <td align="center"><strong>DOB</strong></td>
    <td align="center"><strong>Club</strong></td>
    <td align="center"><strong>Number</strong></td>
    <td align="center"><strong>Cost</strong></td>
    <td align="center"><strong>Position</strong></td>
    <td align="center"><strong>National Team</strong></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td align="center"><input name="Name" type="text" size="15" value="<?php echo $row['Name']; ?>"></td>
    <td align="center"><input name="DOB" type="date" size="20" value="<?php echo $row['DOB']; ?>"></td>
    <td align="center"><input name="Club" type="varchar" size="15" value="<?php echo $row['Club']; ?>"></td>
    <td align="center"><input name="Number" type="int" size="15" value="<?php echo $row['Number']; ?>"></td>
    <td align="center"><input name="Cost" type="text" size="15" value="<?php echo $row['Cost']; ?>"></td>
    <td align="center"><input name="Position" type="text" size="15" value="<?php echo $row['Position']; ?>"></td>
    <td align="center"><input name="NationalTeam" type="text" size="15" value="<?php echo $row['NationalTeam']; ?>"></td>
    </tr>
    <tr><td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> </tr>
    <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td><input name="id" type="hidden" id="id" value="<?php echo $row['PlayerID']; ?>"></td>
    <td align="center"><input type="submit" name="Submit" value="Update"></td>
    <td>&nbsp;</td>
    </tr>
    </table>
    </td>
    </form>
    </tr>
    </table>
    </body>
    <?

    // close connection 
    mysql_close();

    ?>

        <?php
            
    echo "<p>&nbsp </p>";

            include (
    'includes\footer.html');
        
    ?>
        
            <div align="center">
    </html>
    help

    el nino
    Last edited by el_nino; 03-07-2010 at 02:57 PM.

  • #7
    New Coder
    Join Date
    Oct 2007
    Posts
    73
    Thanks
    26
    Thanked 0 Times in 0 Posts
    I'm not much of a coder and I haven't checked your code, but this is how I would do it:
    (not tested)
    PHP Code:
    <html>
        <head>
            <title>Edit</title>
        </head>
        
        <div align="center">    
        
    <?php
            
    include ('includes/header.html')

            
    //connect to your database
            
    mysql_connect("","","") or die("cannot connect"); //(host, username, password)

            //specify database
            
    mysql_select_db("") or die("Unable to select database"); //select which database we're using);
            
            //$id=$_GET['id'];
            
            
    $id = (is_numeric($_GET['id'])) ? $_GET['id'] : 0;


        
    //$PlayerID = ((isset($_GET['PlayerID'])) ? $_GET['PlayerID'] : 0);
            
            
            
    $query "select * from players WHERE PlayerID ='$id'"
            echo 
    $query;
    $numresult mysql_query($query);
    if ( !
    $numresult ) {
                echo 
    "No results...<br>";
            }else {
            
    $numrows mysql_num_rows($numresult);
            if ( 
    == $numrows ) {
                echo 
    "No data found...";

            }    else {

    echo 
    "<form action=update_ac.php method=post>";
            echo 
    "<table><caption>Edit record</caption>";
            echo 
    "<thead><tr>
                <th scope='col'>Name</th>
                <th scope='col'>DOB</th>
                            <th scope='col'>Club</th>
                            <th scope='col'>Number</th>
                            <th scope='col'>Cost</th>
                            <th scope='col'>Position</th>
                            <th scope='col'>National Team</th>
                     </tr></thead>"
    ;
        for ( 
    $i=0$i $numrows$i++ ) {
            
    $name mysql_result $numresult$i'name');
            
    $DOB mysql_result $numresult$i'DOB');
                
    $club mysql_result $numresult$i'club');
            
    $number mysql_result $numresult$i'number');
                
    $cost mysql_result $numresult$i'cost');
            
    $position mysql_result $numresult$i'position');
                
    $nationalteam mysql_result $numresult$i'nationalteam');

        echo 
    "<tr>
            <td><input type=text name=name value=$name></td>
            <td><input type=text name=dob value=$dob></td>
                    <td><input type=text name=club value=$club></td>
            <td><input type=text name=number value=$number></td>
            <td><input type=text name=cost value=$cost></td>
                    <td><input type=text name=position value=$position></td>
            <td><input type=text name=nationalteam value=$nationalteam></td>
                </tr>"
    ;
                    }
         echo 
    "</table>";
                }
        echo 
    "<input type=hidden name=id value=$id>
            <input type=hidden name=action value=update>
            <input type=submit name=Submit value=Update></form>"
    ;
            }
    mysql_close();
    ?>

  • #8
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    @el_nino> Hopefully by now you've realized that you commented out your call to mysql_query(), and that you've omitted the call to return the resultset, e.g.
    PHP Code:
    $resultmysql_query($query);
    while( 
    $rowmysql_fetch_assoc($result) ) {
      
    // handle $row here

    @friz> Well meant, I'm sure, but that's the worst possible way to iterate over a resultset. Using a single call to mysql_fetch_* to retrieve each record is much better than multiple calls to mysql_result().

  • #9
    New Coder
    Join Date
    Oct 2007
    Posts
    73
    Thanks
    26
    Thanked 0 Times in 0 Posts
    @friz> Well meant, I'm sure, but that's the worst possible way to iterate over a resultset. Using a single call to mysql_fetch_* to retrieve each record is much better than multiple calls to mysql_result().
    Thx for the tip

  • #10
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    bdl -thanks. i got that working this morning before seeing your message.

    i can now see the selected record in a form.

    just need to get it to to update the record now, its giving me an error in the update_ac.php page but i think that may be due to my query being incorrect, i'm gonna have a go at solving that now

  • #11
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    ok, so i'v now created update_ac.php which should update the selected record in the database, this follows on from update_v2.php

    update_v2.php
    PHP Code:
    <html>
        <head>
            <title>Edit</title>
        </head>
        
        <div align="center">    
        
        <?php
            
    include ('includes/header.html')
        
    ?>

    <?php

            
    //connect to your database
            
    mysql_connect("","","") or die("cannot connect"); //(host, username, password)

            //specify database
            
    mysql_select_db("") or die("Unable to select database"); //select which database we're using);
            
        
    ?>
        
        <?php
            
    //$id=$_GET['id'];
            
            
    $id = (is_numeric($_GET['id'])) ? $_GET['id'] : 0;


        
    //$PlayerID = ((isset($_GET['PlayerID'])) ? $_GET['PlayerID'] : 0);
            
            
            //$query = "select * from players WHERE PlayerID ='$id'"; 
            //echo $query;
        
        
    $query "select * from players WHERE PlayerID ='$id'";
        
    $result mysql_query($query) or die("Couldn't execute query. MySQL Said: ".mysql_error());
        
    $row mysql_fetch_assoc($result);
        
    //echo $query;
        
            //$numresults=mysql_query($query);
            //$numrows=mysql_num_rows($numresults);
    ?>
    <body>
    <h1>Edit Record</h1>
    <table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <form name="form1" method="post" action="update_ac.php">
    <td>
    <table width="100%" border="0" cellspacing="1" cellpadding="0">
    <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>Name</strong></td>
    <td align="center"><strong>DOB</strong></td>
    <td align="center"><strong>Club</strong></td>
    <td align="center"><strong>Number</strong></td>
    <td align="center"><strong>Cost</strong></td>
    <td align="center"><strong>Position</strong></td>
    <td align="center"><strong>National Team</strong></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td align="center"><input name="Name" type="text" size="15" value="<?php echo $row['Name']; ?>"></td>
    <td align="center"><input name="DOB" type="date" size="10" value="<?php echo $row['DOB']; ?>"></td>
    <td align="center"><input name="Club" type="varchar" size="15" value="<?php echo $row['Club']; ?>"></td>
    <td align="center"><input name="Number" type="int" size="2" value="<?php echo $row['Number']; ?>"></td>
    <td align="center"><input name="Cost" type="text" size="15" value="<?php echo $row['Cost']; ?>"></td>
    <td align="center"><input name="Position" type="text" size="15" value="<?php echo $row['Position']; ?>"></td>
    <td align="center"><input name="NationalTeam" type="text" size="15" value="<?php echo $row['NationalTeam']; ?>"></td>
    </tr>
    <tr><td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>&nbsp;</td> </tr>
    <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td><input name="id" type="hidden" id="id" value="<?php echo $row['PlayerID']; ?>"></td>
    <td align="center"><input type="submit" name="Submit" value="Update"></td>
    <td>&nbsp;</td>
    </tr>
    </table>
    </td>
    </form>
    </tr>
    </table>

        <p>&nbsp </p>  
        <p><a href="loggedin.php"> Take me back to my member page </a></p> 
        <p>&nbsp </p>
        <p><a href="logout.php"> Log me out, i'm done </a></br></p>
        </br>

    </body>
    <?

    // close connection 
    mysql_close();

    ?>

        <?php
            
    echo "<p>&nbsp </p>";

            include (
    'includes\footer.html');
        
    ?>
        
            <div align="center">
    </html>
    update_ac.php
    PHP Code:
    <html>
        <head>
            <title>Edit</title>
        </head>
        
        <div align="center">    
        
        <?php
            
    include ('includes/header.html')
        
    ?>

    <?php

                    $Name 
    trim($_POST['Name']);
                    
    $DOB trim($_POST['DOB']);
                    
    $Club trim($_POST['Club']);
                    
    $Number trim($_POST['Number']);
                    
    $Cost trim($_POST['Cost']);
                    
    $Position trim($_POST['Position']);
                    
    $NationalTeam trim($_POST['NationalTeam']);    

            
    //connect to your database
            
    mysql_connect("","","") or die("cannot connect"); //(host, username, password)

            //specify database
            
    mysql_select_db("") or die("Unable to select database"); //select which database we're using);                            

    // update data in mysql database 
    $sql="UPDATE players SET Name='$Name', 
                            DOB='$DOB', 
                            Club='$Club',
                            Number='$Number', 
                            Cost='$Cost', 
                            Position='$Position', 
                            NationalTeam='$NationalTeam'
                            WHERE PlayerID='$id'"
    ;
                            
            
    $result mysql_query($sql) or die("Couldn't execute query. MySQL Said: ".mysql_error());                        
            echo 
    "&nbsp;";
            echo 
    $query;        
            echo 
    "&nbsp;";
            
    $result=mysql_query($sql);

    // if successfully updated. 
    if($result){
    echo 
    "<h1>Record Successfully Updated</h1>";
    echo 
    "<BR>";
    echo 
    "<h3><a href='show_all.php'>View Updated Record</h3></p>";
    echo 
    "&nbsp;";
    echo 
    "<p><a href='loggedin.php'> Take me back to my member page</a></p>";
    echo 
    "&nbsp;";
    echo 
    "<p><a href='list_records_profiles.php'> I want to edit more profiles</a></p>";
    echo 
    "&nbsp;";
    echo 
    "<p><a href='logout.php'> Log me out, i'm done</a></p>";
    }

    else {
    echo 
    "<h1>ERROR</h1>";
    echo 
    "<a href='list_records_profiles.php'>Click here to go back and try again</a>";
    echo 
    "<a href='loggedin.php'> Take me back to my member page </a>";
    }

    ?>

        <?php
            
    echo "<p>&nbsp </p>";

            include (
    'includes\footer.html');
        
    ?>
        
            <div align="center">
    </html>
    so the problem is that, when the user changes the value of the selected record and clicks on the update button, they are taken to the next page which displays the message "Successful" or "Error". In each case the Successful message is displayed but the record is not being updated

  • #12
    New Coder
    Join Date
    Feb 2010
    Posts
    19
    Thanks
    4
    Thanked 0 Times in 0 Posts
    solved that particular problem


  •  

    Posting Permissions

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