SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Working on project-MySql table field update

    I've done some work on tables in school and at a volunteer job. Now I need to figure out how to update data that is already in the database. How would you go about modifying a fields data and saving it in that field. I am capable of accessing the data but don't know where to start with updating it.
    Pat

  2. #2
    SitePoint Member
    Join Date
    Jun 2002
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Pat,

    You need to use an update statement - imagine that! Let's say you had a table named products with fields product_id, product_price, product_description and you wanted to update the product_price to 15.99 for product_id 00015:

    UPDATE products SET product_price = '15.99' WHERE product_id = '00015'

    Let me know if you need more specific help for the application(s) that you're working with.

    Kind regards,
    Simon.
    http://www.AQHost.com
    Friendly and Efficient Support
    Ultra Fast Dual Processor Servers
    1-877-5-AQHOST ICQ: 163719492 AIM: AQHSales

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update ???

    Simon, If I'm not wrong I believe that update replaces and entire field. So to use this for what I need I would have to first access the field, change it's contents, and then update it with the new value. What I want to set up is a trouble tracker of sorts, so that I can add to the Problem field. Normally you aren't told everything at once. I believe what I'm having a problem trying to figure out is how to be able to access the original in a way that I can add to it then use the update function.
    Pat

  4. #4
    SitePoint Member
    Join Date
    Jun 2002
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, so you want to append to the field rather than completely overwriting it? Gotcha. In that case I'll take my original example but instead of updating, we'll say that the scenario is you want to append further information to product_description:

    PHP Code:
    // I'm assuming here that you've set up a MySQL connection with the handle $connection
    $query "SELECT * FROM products WHERE product_id = '00015'";
    $result mysql_query($query$connection);
    // Read the row of data into an array $row
    $row mysql_fetch_array($result);
    // Set $this_description to the value of the product_description field
    $this_description $row[product_description];
    // Define the string that we want to append
    $data_to_append " Special offer - buy 5, get 1 free!";
    // Append it
    $this_description .= $data_to_append;
    // Update the field
    $query2 "UPDATE products SET product_description = '$this_description' WHERE product_id = '00015'";
    $result2 mysql_query($query2$connection); 
    Best wishes,
    Simon.
    http://www.AQHost.com
    Friendly and Efficient Support
    Ultra Fast Dual Processor Servers
    1-877-5-AQHOST ICQ: 163719492 AIM: AQHSales

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Good as far as Query2

    Simon, I did good until the second query. For some reason when my check for it runs my error statement comes back, meaning the query does not run. I will include what I have up to now for you to look at:

    <?
    // Set variables for db access
    $Host = "localhost";
    $User = "root";
    $Password = "pat2002";
    $DbName = "test";
    $TableName = "work";
    // Set link variable
    $Link = mysql_connect($Host, $User, $Password);

    // Set test number for id field and name append
    $num=1;
    $namechange="rick";
    // The query itself
    $Query = "select * from $TableName where id=$num";

    // Results of the query
    $Result = mysql_db_query($DbName, $Query, $Link);

    if(!$Result) {
    print("The query could not be executed! Check the Server!<br>\n");
    exit();
    }
    else {
    // Read the row of data into an array $row
    $row=mysql_fetch_array($Result);
    print("$row[name]<br>\n");
    // Set this_description to the value of the name field
    $this_description = $row[name];
    print("$this_description<br>\n");
    // Define the string that we want to append
    $data_to_append = $namechange;
    print("$data_to_append<br>\n");
    // Append it
    $this_description .= $data_to_append;
    print("$this_description<br>\n"); //THIS WORKS TO THIS POINT PRINTS "Patrick"

    // Update the field
    $Query2 = "update $TableName set name = $this_description where id=$num";

    $Result2 = mysql_db_query($DbName, $Query2, $Link);

    if(!$Result2) {
    print("The second query could not be executed! Check the Server!<br>\n");
    exit();
    } //HERE SAYS QUERY2 COULD NOT BE EXECUTED
    /*
    else{
    $Row2 = mysql_fetch_array($Result2);
    print("$Row2[name]\n");
    }
    */
    }
    mysql_close($Link);
    ?>

    I'm doing my ground work on a Win2k box running Apache, if you need to know. Wondering if something is wrong with the grants on it, shouldn't be running the script as root.
    Hope you have another suggestion because You have been a great deal of help to me so far.
    Thank You Very Much
    Pat

  6. #6
    SitePoint Member
    Join Date
    Jun 2002
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Pat,

    One quick thought before we delve into this too much further. Your second query you said is like this:

    $Query2 = "update $TableName set name = $this_description where id=$num";

    Try it with single quotation marks around the variable names like so:

    $Query2 = "update $TableName set name = '$this_description' where id='$num'";

    Best wishes,
    Simon.
    http://www.AQHost.com
    Friendly and Efficient Support
    Ultra Fast Dual Processor Servers
    1-877-5-AQHOST ICQ: 163719492 AIM: AQHSales

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple Things

    Yep, that did it. Got the script to work.
    Thank You Very MUCH Simon.
    Pat


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
  •