SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Editting Data in MYSQL

    Okay I'm now very frustrated with this. I know that you use "UPDATE" in in the query, but I can't figure out how to do it. If someone could post a script or someway how to make a script do this, post it!

    This is what I am trying to do:

    Make an index that links to a script that would allow me to edit something based on the id. Like here is how it works:

    index.php displays everything and it links to article.php?id=3232

    What I would like is for something such as admin_list.php to list all the articles and have it link to edit_article.php?id=3232

    That way I can update pages. I got down adding =)

  2. #2
    gingham dress, army boots... silver trophy redux's Avatar
    Join Date
    Apr 2002
    Location
    Salford / Manchester / UK
    Posts
    4,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    without knowing your existing database schema, it's a bit vague. anyway, assuming you do all the "open db connection/select db" stuff, here's what an update query would be...
    PHP Code:
    $query "UPDATE tablename SET field1='new_value1', field2='new_value2', field3='new_value3' WHERE id=$id";
    $result mysql_query($query) or die(mysql_error); 
    Last edited by redux; Apr 18, 2002 at 18:13.

  3. #3
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The basic update query is as follows (borrowed from Kevin Yank's book). Please note that the items in brackets [] are optional:
    Code:
    UPDATE [LOW_PRIORITY] [IGNORE] table_name
         SET column_name=expression, ...
         [WHERE where_definition]
         [ORDER BY ...]
         [LIMIT #]
    Assuming that your table index in MySQL (basically the ID column in the table) is the same as the $id variable (in your example, 3232), you would simply have your form elements or whatnot on the page and when you submit the data, the query would run similar to:
    PHP Code:
    <?php
    $connect
    =@mysql_connect($host,$user,$pass)
         or exit(
    '<p>Error!</p>');
    $select=@mysql_select_db($dbse,$connect)
         or exit(
    '<p>Error!</p>');
    $query=@mysql_query("UPDATE [tablename] SET ".
         
    "[columnname]=[value_from_forms], [columnname]=[value_from_forms], ... ".
         
    "WHERE [ID_column]=3232")
              or exit(
    '<p>Error!</p>');
    echo(
    '<p>Update Successful!</p>');
    ?>
    And that's the basic rule. Just make sure when you upload the form data that the form input field names that you upload correspond to the "[value_from_forms]." Just modify and salt to taste. Good luck.

    -Colin

    EDIT: Well, redux's simplified explanation is right on the mark; I suppose mine is a bit "wordy."
    Last edited by Aes; Apr 18, 2002 at 18:16.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  4. #4
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I got the MYSQL part mostly, but I'm not sure how I would make the form... let me post my information here... It'll be up in a bit.

  5. #5
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I did it, except when it I did I forgot to set the id so it overwrote my test files, so I added the id the way you said and I still get an error. Is the PHP for my coding, tell me what's wrong. thanks

    PHP Code:
    <html>
    <head>
    <title>Inserting to the Database</title></head>
    <body>
    <?
    //Database & MYSQL Server Information
    $host "localhost";
    $user "root";
    $password "";
    $database "gcxl";
    $tablename="cheatdb";


    //Database Connection
    $link mysql_connect ($host$user$password);

    //MYSQL Statements sent to tables

    $query=@mysql_query("UPDATE [$tablename] SET ".
         
    "[title]=[title_update], [body]=[body_update], [date]=[CURDATE()], ... ".
         
    "WHERE [id]=$id");





    //Page confirmation

    if (mysql_db_query ($database$query$link)) {

    print (
    "<font color=green>Cheats Updated Successfully!</font><a href=index.php>View Submission</a>!\n");
    } else {
    print (
    "Failure! Try Again!\n");
    }
    //Close MYSQL
    mysql_close ($link);
    ?>


    </body>
    </html>

  6. #6
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK here this will tell you everything!!!

    http://www.nintendofire.com/cdb/

  7. #7
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I typically pull the data I need out of the database (in this case any data you would like to edit), as well as the corresponding identification (you have the ID system set as $id which is a good practice). So using a SELECT query pull out any data you would like to extract and have PHP display that extracted data in form fields. Name each of the fields a name that corresponds to the column to which that data belongs. Then have the submit button submit the form contents back to the database with the UPDATE query and viola! -- you're all set. If you need a small example let me know!

    -Colin
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  8. #8
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, I would like an example based on my updating form...

  9. #9
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PlayOn, you need to remove the brackets and the ",..." from your UPDATE query. I apologize if I misled you with that part. It was just an example of the syntax.
    PHP Code:
    <?
    //Database & MYSQL Server Information
    $host "localhost";
    $user "root";
    $password "";
    $database "gcxl";
    $tablename="cheatdb";


    //Database Connection
    $link mysql_connect ($host$user$password);

    //MYSQL Statements sent to tables

    $query=@mysql_query("UPDATE $tablename SET ".
         
    "title=title_update, body=body_update, date=CURDATE() ".
         
    "WHERE id=$id");





    //Page confirmation

    if (mysql_query ($database$query$link)) {

    print (
    "<font color=green>Cheats Updated Successfully!</font><a href=index.php>View Submission</a>!\n");
    } else {
    print (
    "Failure! Try Again!\n");
    }
    //Close MYSQL
    mysql_close ($link);
    ?>
    Additionally, I changed the mysql_db_query() to mysql_query since the former is depreciated.

    One additional note, you do not need to use mysql_close() at the end of the script -- that is done automatically!

    -Colin
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  10. #10
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still no go

    Is there any errors in any of my other forms?
    Last edited by PlayOn; Apr 18, 2002 at 19:44.

  11. #11
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PlayOn, what you have in cheats_update.php looks good in terms of pulling the data out of the database. But, use this:
    PHP Code:
    <?php
    error_reporting
    (E_ALL);
    //Database & MYSQL Server Information 
    $host='localhost';
    $user='root';
    $password='';
    $database='gcxl';
    $tablename='cheatdb';

    //Database Connection

    $link=mysql_connect($host,$user,$password); 

    //MYSQL Statements sent to tables

    $query="SELECT title, body, DATE_FORMAT(Date,'%M %d, %Y') AS formatted_date FROM {$tablename} WHERE id={$id}");

    $result=mysql_query($query);

    echo(
    '<table width="500" bgcolor="#000000" cellpadding="3" cellspacing="1"><tr><td bgcolor="#ff9933">');
    echo(
    '<h3 align="center">'.$row['title'].'</h3>');

    while(
    $row=mysql_fetch_assoc($result)){
        
    $date=$row['formatted_date'];
        
    $title=$row['title'];

    echo(
    "<form action=\"form_update.php\" method=\"post\">\n<h3>{$title Cheats}</h3>".
        
    "Added on {$date}<br />\n<input type=\"hidden\" name=\"id\" value=\"{$row['id']}\">\n".
        
    "<input type=\"hidden\" name=\"title_update\" value=\"{$title}\">\n".
        
    "<input type=\"hidden\" name=\"date\" value=\"{$date}\">\n".
        
    "<textarea name=\"body_update\" cols=\"45\" rows=\"10\">$row['body']</textarea>\n".
        
    "<input type=\"submit\" name=\"submit\" value=\"Update\">\n</form>");
    ?>
    If you have any questions regarding why I did what I did, just ask. One of the main things I noticed was that you didn't have a name="" in the submit input area, which is important!

    Now, for your form_update.php, use this:
    PHP Code:
    <?php
    error_reporting
    (E_ALL);
    if(isset(
    $_POST['submit'])){
        
    //Database & MYSQL Server Information 
        
    $host='localhost';
        
    $user='root';
        
    $password='';
        
    $database='gcxl';
        
    $tablename='cheatdb';

        
    //Database Connection

        
    $link=mysql_connect($host,$user,$password); 

        
    //MYSQL Statements sent to tables

        
    $query=@mysql_query("UPDATE {$tablename} SET ".
            
    "title=title_update, body=body_update, date=CURDATE() ".
            
    "WHERE id={$id}");

        
    //Page confirmation

        
    if(mysql_query($query)){
            echo(
    '<font color="green">Cheats Updated Successfully!</font>'.
                
    '<a href="index.php">View Submission</a>!');
        }
        else{
            echo(
    'Failure! Try Again!');
        }
    }
    ?>
    Keep the error_reporting(E_ALL) at the top of your scripts -- that will force PHP to tell you every error and/or warning it encounters. Now, what problems are you experiencing using these modifications?

    -Colin
    Last edited by Aes; Apr 18, 2002 at 20:03.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  12. #12
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay there are a few parse errors, I can probably fix those though. Just a quick question, do I remove the brackets? "{" & "}"

    In cheats_update.php:

    Parse error: parse error, expecting `'}'' in c:\phpdev\www\public\gcxl\cheats_update.php on line 27

  13. #13
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yikes!

    Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in c:\phpdev\www\public\gcxl\cheats_update.php on line 28

    What the? I don't know what that means, here is my coding.

    PHP Code:
    <?php
    error_reporting
    (E_ALL);
    //Database & MYSQL Server Information 
    $host='localhost';
    $user='root';
    $password='';
    $database='gcxl';
    $tablename='cheatdb';

    //Database Connection

    $link=mysql_connect($host,$user,$password); 

    //MYSQL Statements sent to tables

    $query=("SELECT title, body, DATE_FORMAT(Date,'%M %d, %Y') AS formatted_date FROM $tablename WHERE id=$id");

    $result=mysql_query($query);

    echo(
    '<table width="500" bgcolor="#000000" cellpadding="3" cellspacing="1"><tr><td bgcolor="#ff9933">');
    echo(
    '<h3 align="center">'.$row['title'].'</h3>');

    while(
    $row=mysql_fetch_assoc($result)){
        
    $date=$row['formatted_date'];
        
    $title=$row['title']; 

    echo(
    "<form action=\"form_update.php\" method=\"post\">\n<h3>$title Cheats</h3>".
        
    "Added on $date<br />\n<input type=\"hidden\" name=\"id\" value=\"$row['id']\">\n".
        
    "<input type=\"hidden\" name=\"title_update\" value=\"$title\">\n".
        
    "<input type=\"hidden\" name=\"date\" value=\"$date\">\n".
        
    "<textarea name=\"body_update\" cols=\"45\" rows=\"10\">$row['body']</textarea>\n".
        
    "<input type=\"submit\" name=\"submit\" value=\"Update\">\n</form>");
    }
    ?>

  14. #14
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Personally, I use the curly braces {} because it denotes to PHP that there is a variable immediatly between them. They are not required, but they help in that they alleviate the need for the PHP interpreter to decide whether or not a variable is a variable. Try this and see if you continue receiving the curly brace parse error:
    PHP Code:
    <?php
    error_reporting
    (E_ALL);
    //Database & MYSQL Server Information 
    $host='localhost';
    $user='root';
    $password='';
    $database='gcxl';
    $tablename='cheatdb';

    //Database Connection

    $link=mysql_connect($host,$user,$password); 

    //MYSQL Statements sent to tables

    $query="SELECT title, body, DATE_FORMAT(Date,'%M %d, %Y') AS formatted_date FROM {$tablename} WHERE id={$id}");

    $result=mysql_query($query);

    echo(
    '<table width="500" bgcolor="#000000" cellpadding="3" cellspacing="1"><tr><td bgcolor="#ff9933">');
    echo(
    '<h3 align="center">'.$row['title'].'</h3>');

    while(
    $row=mysql_fetch_assoc($result)){
        
    $date=$row['formatted_date'];
        
    $title=$row['title'];

    echo(
    "<form action=\"form_update.php\" method=\"post\">\n<h3>{$title} Cheats</h3>".
        
    "Added on {$date}<br />\n<input type=\"hidden\" name=\"id\" value=\"{$row['id']}\">\n".
        
    "<input type=\"hidden\" name=\"title_update\" value=\"{$title}\">\n".
        
    "<input type=\"hidden\" name=\"date\" value=\"{$date}\">\n".
        
    "<textarea name=\"body_update\" cols=\"45\" rows=\"10\">{$row['body']}</textarea>\n".
        
    "<input type=\"submit\" name=\"submit\" value=\"Update\">\n</form>");
    ?>
    The error lied in the {$title Cheats}, which should have been {$title} Cheats. It was my fault.

    -Colin
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  15. #15
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All righty, I think it's just about bug free. I'm getting one last error though:

    Parse error: parse error in c:\phpdev\www\public\gcxl\cheats_update.php on line 36

    Line 36 is below the closing line ( after the ?>) so I don't know why it's doing that. Any suggestions?

  16. #16
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Again, my fault.

    Typically, when you receive an error after the closing ?> tag, it denotes that you're missing a closing } in the condition statement; in this case I did not close the while($row=mysql_fetch_assoc($result)) loop. Here is the (hopefully) bug-free code:
    PHP Code:
    <?php
    error_reporting
    (E_ALL);
    //Database & MYSQL Server Information 
    $host='localhost';
    $user='root';
    $password='';
    $database='gcxl';
    $tablename='cheatdb';

    //Database Connection

    $link=mysql_connect($host,$user,$password); 

    //MYSQL Statements sent to tables

    $query="SELECT title, body, DATE_FORMAT(Date,'%M %d, %Y') AS formatted_date FROM {$tablename} WHERE id={$id}");

    $result=mysql_query($query);

    echo(
    '<table width="500" bgcolor="#000000" cellpadding="3" cellspacing="1"><tr><td bgcolor="#ff9933">');
    echo(
    '<h3 align="center">'.$row['title'].'</h3>');

    while(
    $row=mysql_fetch_assoc($result)){
        
    $date=$row['formatted_date'];
        
    $title=$row['title'];

        echo(
    "<form action=\"form_update.php\" method=\"post\">\n<h3>{$title} Cheats</h3>".
            
    "Added on {$date}<br />\n<input type=\"hidden\" name=\"id\" value=\"{$row['id']}\">\n".
            
    "<input type=\"hidden\" name=\"title_update\" value=\"{$title}\">\n".
            
    "<input type=\"hidden\" name=\"date\" value=\"{$date}\">\n".
            
    "<textarea name=\"body_update\" cols=\"45\" rows=\"10\">{$row['body']}</textarea>\n".
            
    "<input type=\"submit\" name=\"submit\" value=\"Update\">\n</form>");
    }
    ?>
    -Colin
    Last edited by Aes; Apr 18, 2002 at 20:33.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  17. #17
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Warning: Undefined variable: row in c:\phpdev\www\public\gcxl\cheats_update.php on line 21


    Warning: Supplied argument is not a valid MySQL result resource in c:\phpdev\www\public\gcxl\cheats_update.php on line 23

    I wonder if you could connect to my server and look at it, but last time no one was able to connect to my ip. Anyway here is the code after I fixed a parse error which lef to another.

    PHP Code:
    <?php
    error_reporting
    (E_ALL);
    //Database & MYSQL Server Information 
    $host='localhost';
    $user='root';
    $password='';
    $database='gcxl';
    $tablename='cheatdb';

    //Database Connection

    $link=mysql_connect($host,$user,$password); 

    //MYSQL Statements sent to tables

    $query="SELECT title, body, DATE_FORMAT(Date,'%M %d, %Y') AS formatted_date FROM {$tablename} WHERE id={$id}";

    $result=mysql_query($query);

    echo(
    '<table width="500" bgcolor="#000000" cellpadding="3" cellspacing="1"><tr><td bgcolor="#ff9933">');
    echo(
    '<h3 align="center">'.$row['title'].'</h3>');

    while(
    $row=mysql_fetch_assoc($result)){
        
    $date=$row['formatted_date'];
        
    $title=$row['title'];

        echo(
    "<form action=\"form_update.php\" method=\"post\">\n<h3>{$title} Cheats</h3>".
            
    "Added on {$date}<br />\n<input type=\"hidden\" name=\"id\" value=\"{$row['id']}\">\n".
            
    "<input type=\"hidden\" name=\"title_update\" value=\"{$title}\">\n".
            
    "<input type=\"hidden\" name=\"date\" value=\"{$date}\">\n".
            
    "<textarea name=\"body_update\" cols=\"45\" rows=\"10\">{$row['body']}</textarea>\n".
            
    "<input type=\"submit\" name=\"submit\" value=\"Update\">\n</form>");
    }
    ?>

  18. #18
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    error_reporting
    (E_ALL);
    //Database & MYSQL Server Information 
    $host='localhost';
    $user='root';
    $password='';
    $database='gcxl';
    $tablename='cheatdb';

    //Database Connection

    $link=mysql_connect($host,$user,$password); 

    //MYSQL Statements sent to tables

    $query="SELECT title, body, DATE_FORMAT(Date,'%M %d, %Y') AS formatted_date FROM {$tablename} WHERE id={$id}");

    $result=mysql_query($query);
    $row=mysql_fetch_field($query,0);

    echo(
    '<table width="500" bgcolor="#000000" cellpadding="3" cellspacing="1"><tr><td bgcolor="#ff9933">');
    echo(
    '<h3 align="center">'.$row['title'].'</h3>');

    while(
    $row=mysql_fetch_assoc($result)){
        
    $date=$row['formatted_date'];
        
    $title=$row['title'];

        echo(
    "<form action=\"form_update.php\" method=\"post\">\n<h3>{$title} Cheats</h3>".
            
    "Added on {$date}<br />\n<input type=\"hidden\" name=\"id\" value=\"{$row['id']}\">\n".
            
    "<input type=\"hidden\" name=\"title_update\" value=\"{$title}\">\n".
            
    "<input type=\"hidden\" name=\"date\" value=\"{$date}\">\n".
            
    "<textarea name=\"body_update\" cols=\"45\" rows=\"10\">{$row['body']}</textarea>\n".
            
    "<input type=\"submit\" name=\"submit\" value=\"Update\">\n</form>");
    }
    ?>
    Before you use this, I need you to do one thing. Look at your table and count the number of columns before the title column occurs. It is based on a zero-based index, so the first column (probably the id column) will be 0, the next column will be 1, and so on. Change the $row=mysql_fetch_field($result,0) to the number that the column belongs too. Thus, if the title column occurs directly after the id column, then you would use $row=mysql_fetch_field($result,1). That should take care of both of your errors.

    -Colin
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  19. #19
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. This is getting more complex than I wanted it to be, I'll finish it tomorrow.

  20. #20
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh come off it! It is not that complex or complicated a script!

    Keep in mind some of these changes you are not used to are mostly my personal preferences for optimization and such things -- if you can get the script to work your way, by all means go ahead! I do not mean to impart my preferences onto you.

    Just remember, if you have any questions or such, do not hesitate to ask.

    -Colin
    Last edited by Aes; Apr 18, 2002 at 21:18.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.


Bookmarks

Posting Permissions

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