-
Sep 25th, 2017, 01:34 PM
#1
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', ++$householdID, PDO::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?
-
Dec 20th, 2017, 12:21 PM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|