Update not updating
Results 1 to 9 of 9

Thread: Update not updating

  1. #1
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    Update not updating

    this has got to be the simplest script in the world, but it don't work. I'm clueless as usual. I modified this script from one that actually worked. I'm at a loss. Here's what I'm trying to make work:

    PHP Code:
    <?php


    $host
    ="localhost";
    $user="root";
    $password="g1G3m#1989";
    $Dbname "CallsToDispatch";


    $cxn mysql_connect($host,$user,$password)
    or die (
    mysql_error() . "couldn’t connect to server");


    mysql_select_db($Dbname$cxn)
    or die(
    mysql_error() . "couldn't select database");


    $result mysql_query("SELECT * FROM CallCountSlim" $cxn)
        or die (
    'Santa Claus is watching you  ' mysql_error());

        while (
    $row mysql_fetch_array($result))
            {
                
    $i++;
                
    $TechNum $row['TechNum'];
                
    $CellNum $row['CellNum'];
        
                    echo 
    $i ' ' $TechNum ' ' $CellNum '<br />';
                    
                
    $result1 mysql_query("UPDATE CallCountSlim SET IdNum = '$i'")
                    or die (
    "cannot update   "$TechNum);

            
            }
            
    ?>
    $TechNum & $CellNum return the proper values, but the update fails. What simple thing am I overlooking? Oh, don't tell me that the mysql commands have been deprecated. mysql is what I know, and right now, I don't have the time to learn another language.

  2. #2
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    Exclamation

    Persistance sometimes pays off, sortta.

    This at least will update the table:

    PHP Code:
    <?php


    $host
    ="localhost";
    $user="root";
    $password="g1G3m#1989";
    $Dbname "CallsToDispatch";


    $cxn mysql_connect($host,$user,$password)
    or die (
    mysql_error() . "couldn’t connect to server");


    mysql_select_db($Dbname$cxn)
    or die(
    mysql_error() . "couldn't select database");
        
                
    $Quest "SELECT * FROM CallCount";
                 
    $result mysql_query($Quest$cxn)
                    or die (
    'Santa Claus is watching you' mysql_error());

                while (
    $row mysql_fetch_array($result))
                    {
                        
    $TechNum $row['TechNum'];
                        
    $CellNum $row['CellNum'];
                        
                        
    $i++;
                        
                        echo 
    $i ' ' $TechNum '<br />';
                        
                        
    $result1 mysql_query("UPDATE `CallCount` SET `IdNum` = '$i'")
                            or die (
    "cannot update   "$TechNum);
                            
                    }
                    
                    
    ?>
    Now the question is, How do I read from one row, write to that row, and then move on to the next row. The way the script is set up now, the update sets `IdNum` = $i, and doesn't advance to the following record. So, the script writes $i to all 52,215 rows, advances to the next number, and writes that on all 57,215 records, and on and on, and on.

  3. #3
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,885
    What are you actually wanting to do? You have an UPDATE statement that sets IdNum to $i, but without any WHERE conditions to narrow down exactly which row or rows you want updated, it will update all of them, as you've found. What should those conditions be? We can't necessarily divine what the objective of the code if it doesn't work because it is - by definition - wrong. (This is where comments and useful error messages come in: the code tells you what is being done, the comments explain why it is being done, and the error messages say what broke.)

    Looking at that code (though, remember, it is wrong so my guesses may be wrong as well) I'm guessing that the whole thing could be replaced by a single UPDATE statement, without any need for SELECTs or a loop to "read from one row, write to that row, and then move on to the next row".


    Incidentally, since you seem to be finding answers even while you're writing posts, have you considered getting yourself a rubber duck?
    Last edited by Weedpacket; 01-31-2013 at 07:48 PM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  4. #4
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    It's fairly obvious what I'm trying to accomplish. I want to set IdNum on the first row to 1. I want to set IdNum to 2 on the next row and so on until all 57,215 rows are numbered. I have tried different 'WHERE' clauses in the updater, but none have worked so far.

  5. #5
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,885
    There is no inherent order to rows in a relational database table - it is necessary to supply one when selecting via an ORDER BY clause.

    You will need to determine some way of distinguishing one row from another based on information in the table, so that you can craft a WHERE clause to update that row and not another.

    You could I guess read all the rows into an array; delete them from the table; then reinsert them all back into the table in a loop, with the value of the loop counter included. (Or maybe create another table that has the same structure as the existing table but with this ID column autoincrementing, then copy everything except the id column into the new table, empty the original table, then copy everything (including the id column) back.)

    I don't know what MySQL would do if you tried to add an autoincrementing primary key column to an existing table.
    Last edited by Weedpacket; 01-31-2013 at 10:13 PM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  6. #6
    Member
    Join Date
    Dec 2004
    Posts
    38
    Quote Originally Posted by Weedpacket View Post
    I don't know what MySQL would do if you tried to add an autoincrementing primary key column to an existing table.
    It works well, as it will auto increment each existing row within the table. I just tested it with a new table and a bunch of rows. It labeled them from the top down 1-50.

  7. #7
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,428
    Quote Originally Posted by bemore View Post
    It works well, as it will auto increment each existing row within the table. I just tested it with a new table and a bunch of rows. It labeled them from the top down 1-50.
    The top... of what? Without an ORDER BY clause, the rows are "displayed in no particular order" (reference).

  8. #8
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    At the time of this post, I had no key to this table, hence the reason to add an IdNum field. I finally took the wimp's way out and re-imported the CSV file, this time with an index.

  9. #9
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,885
    Quote Originally Posted by timstring
    re-imported the CSV file, this time with an index.
    If I'd known you were importing the data from elsewhere, that's what I would have suggested.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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