Results 1 to 2 of 2

Thread: Incremented Variable not Reflecting in Database

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Incremented Variable not Reflecting in Database

    I currently have a MySQL data table that has a field called householdId and it is meant to act as a primary key for multiple rows even though it is not specified as a primary key. So for example, I may have a husband and wife stored as two separate rows where the husband's primary key ID is 1 and the wife's is 2, but they both share the same householdId of 1.

    Right now, what I'm having an issue with is adding a new row to my data table and incrementing the largest returned householdId by 1. This is what I currently have:
    PHP Code:
      try {
        
    // Get the highest householdId value from customers
        
    $stmt $db->prepare("SELECT MAX(householdId) FROM customers;");

        
    // Return the largest householdId
        
    $householdID intval($stmt->fetch(PDO::FETCH_ASSOC));

        
    // Insert the values
        
    $stmt $db->prepare("INSERT INTO customers (householdId, firstName, lastName, suffix, dateOfBirth, referral, phone, email, address, city, state, zip) VALUES (:householdId, :firstName, :lastName, :suffix, :dob, :referral, :phone, :email, :address, :city, :state, :zip);");

        
    // Increment the hosueholdID variable by 1
        
    $stmt->bindValue(':householdId', ++$householdIDPDO::PARAM_INT);
        
    $stmt->bindValue(':firstName'$_POST['firstName'], PDO::PARAM_STR);
        
    $stmt->bindValue(':lastName'$_POST['lastName'], PDO::PARAM_STR);
        
    $stmt->bindValue(':suffix'$_POST['suffix'], PDO::PARAM_STR);
        
    $stmt->bindValue(':dob'$_POST['dob'], PDO::PARAM_STR);
        
    $stmt->bindValue(':referral'$_POST['referral'], PDO::PARAM_STR);

        
    // Remove any non digit characters
        
    $stmt->bindValue(':phone'preg_replace("/\D/"""$_POST['phone']), PDO::PARAM_STR);
        
    $stmt->bindValue(':email'$_POST['email'], PDO::PARAM_STR);
        
    $stmt->bindValue(':address'$_POST['address'], PDO::PARAM_STR);
        
    $stmt->bindValue(':city'$_POST['city'], PDO::PARAM_STR);
        
    $stmt->bindValue(':state'$_POST['state'], PDO::PARAM_STR);
        
    $stmt->bindValue(':zip'$_POST['zip'], PDO::PARAM_STR);

        
    // Execute the command
        
    $stmt->execute();

        
    // Explicitly close the connection
        
    $db null;
      } catch(
    PDOException $ex) {
        
    // debug mode, simply echo the exception
        
    echo $ex->getMessage();
      } 
    The current PHP code will insert a row into the data table, but here is the situation: if there are no rows in the data table then the householdId is set to 1 (which is what I want), after inserting the second row it sets the householdId value to 1 when I expect it to insert a value of 2. Why is it that the householdId value is not incrementing?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,652

    Re: Incremented Variable not Reflecting in Database

    A PrimaryKey is supposed to be unique in a table. Means, no two rows in a table can have the same PrimaryKey value.

    I assume that you have already another field in your customers table, which act as a PrimaryKey.

    As a suggestion, why not create another table called "household", with fields like "householdId" (primarykey of that table), "houseName", "address", etc. and use that id to insert the data to the "customers" table, so that you can make a relation between these two tables using that "householdId" ?

    That being said, to answer your question regarding "householdId" not getting incremented, are you aware of the fact that the $stmt->fetch(PDO::FETCH_ASSOC) statement would return an associative array?
    If the query fails, it would return false and otherwise, it would return an associative array containing the values. Ref: http://php.net/manual/en/pdostatement.fetch.php
    So I believe, when you don't have any rows in your database, its return FALSE, which when used with intval() and increment operator, becomes 1.

    Here's live test result: https://ideone.com/EhwphG

    On the other hand, when there's a row in the database, it would return the array. So again when you use intval() on that array, you are not actually using the value returned from the db. Instead, you are working on the array.

    Hope this will help

    If my post was helpful to you, then express your gratitude using Rate this Post.
    And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video)
    My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet

    Social Group: VBForums - Developers from India


    Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,...

Posting Permissions

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



Click Here to Expand Forum to Full Width