thara

This website will help you.

Can we see your code?

Yes sir, I got it to work. In #8 I asked other method can we use. isn't it?

Yes sir, it is not needed auto_increment id, so I changed my table structure got it to work. At first, when using these data, it should be insert and after inserting it should be use update. Thats why I tried to used INSERT.... ON DUPLICATE KEY UPDATE query. Sir, can we get it to work adding an unique key to "company_name" column, like this UNIQUE KEY (company_name)

Sir, there should be only one record set in the table. Thats why I set 1 for id.

@Barand, I tried it something like this: $sql = "INSERT INTO company_profile ( id , company_name , tel , mobile , fax , email ) VALUES (1, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE company_name= VALUES(company_name) , tel = VALUES(tel) , mobile = VALUES(mobile) , fax = VALUES(fax) , email = VALUES(email)"; But still inserting new record...

I am having a mysql table named `company_profile`. It may have only one record. So I tried to insert and update data of the table using `INSERT.... ON DUPLICATE KEY UPDATE` query. My query is something like this: $sql = "INSERT INTO company_profile ( company_name , tel , mobile , fax , email ) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE company_name= VALUES(company_name) , tel = VALUES(tel) , mobile = VALUES(mobile) , fax = VALUES(fax) , email = VALUES(email)"; $stmt = $mysqli->prepare($sql); $stmt->bind_param('sssss', $company_name , $telephone , $mobile , $fax , $email ); $stmt->execute(); My problem is when I updating the data, it always inserting a new record into my table. Can anybody tell me what would be the problem of this? My table structure looks like this: CREATE TABLE IF NOT EXISTS company_profile ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_name VARCHAR(120) NOT NULL, tel VARCHAR(20) NOT NULL, mobile VARCHAR(20) NOT NULL, fax VARCHAR(20) DEFAULT NULL, email VARCHAR(60) NOT NULL, PRIMARY KEY (id), UNIQUE KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Yes, I understood. We really don't need that bank_id column. But I still have the problem if I have account_number instead of bank_id,

Then how to use bank_ids? It also may have different values.

I do have 3 mysql tables "users", "banks", and "user_bank". Every users must have at least one bank and maximum is two. When users signup to system I need to insert bank details to `user_bank` table also. This is how I tried it: $success=FALSE; if($success == FALSE) { $query = "INSERT INTO user_bank ( beneficiary_id , beneficiary_bank_id , branch_id ) VALUES (?, ?, ?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param('iii', $lastInsertId , $bank_one , $branchId_one ); $stmt->execute(); if ($stmt->affected_rows == 1) { $success=TRUE; if(!empty($bank_two) && !empty($branchId_two)) { $query = "INSERT INTO user_bank ( id , bank_id , branch_id ) VALUES (?, ?, ?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param('iii', $lastInsertId , $bank_two , $branchId_two ); $stmt->execute(); $success=TRUE; } } } if ($success == TRUE) { $messages = array('success'=>true, 'message'=>'You successfully registered.'); } My question is, can anybody tell me I there a way to use a single query for this? If so how its doing? Thank you.

Thank you sir. I referred above provided like and most of things there I can not understand. Can you kindly show me an example how to save history when my addresses update? Thanks again.

Yes now its working. @Barand, Here I would like to know is there a way to keep history of record updates in MySQL? I just simply created another mysql table like below and try to insert address changes. CREATE TABLE IF NOT EXISTS users_access_log ( log_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, action TEXT NOT NULL, previous_data TEXT NOT NULL, current_data TEXT NOT NULL, changed_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (log_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Can I know is there best approach to do it? Thank you.

@Barand, Thank you. Its working but its inserting a record to the postal address data. That mean if a user only have residential address and when he update it (without postal address), residential address is updating, but inserting a new row to postal address too. In that row only adding user_id and address_type. other fields are blank. Is it a problem? Thank you.

Thank you. I am still doubt how to do it. Sir, can you kindly show me an example showing how to do it? Thanks again.

Thank you again. I am getting values from address editing form something like this : $street_no = filter_input(INPUT_POST, 'street_no', FILTER_SANITIZE_STRING); $street_name = filter_input(INPUT_POST, 'street_name', FILTER_SANITIZE_STRING); $city = filter_input(INPUT_POST, 'city', FILTER_SANITIZE_STRING); if (!empty($_POST['p_street_no'])) { $p_street_no = filter_input(INPUT_POST, 'p_street_no', FILTER_SANITIZE_STRING); } else { $p_street_no = ''; } if (!empty($_POST['p_street_name'])) { $p_street_name = filter_input(INPUT_POST, 'p_street_name', FILTER_SANITIZE_STRING); } else { $p_street_name = ''; } if (!empty($_POST['p_city'])) { $p_city = filter_input(INPUT_POST, 'p_city', FILTER_SANITIZE_STRING); } else { $p_city = ''; } So can you tell me how I make this `INSERT ... ON DUPLICATE KEY` statement? INSERT INTO user_addresses (user_id, address_type, street_no, street_name, city) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE street_no = VALUES(street_no), street_name = VALUES(street_name), city = VALUES(city) ; Do I need two queries?

Thank you. One question. What happen if both addresses exists for one user?

This is my table structure: CREATE TABLE IF NOT EXISTS user_addresses ( address_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, address_type TINYINT(1) NOT NULL, # -- 1=Residencial 2=Postal street_no VARCHAR(50) NOT NULL, street_name VARCHAR(100) NOT NULL, city VARCHAR(20) NOT NULL, PRIMARY KEY (address_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

@Barand I will try to explain my best if it is not clear for you. I am getting user's address details when they registering to the system. There are 2 type of addresses. One for residential and other one for postal. When user registering to the system it is not necessary to add postal address. Its optional. After registered, users can edit their address details in his profile section. In edit address form, there are two section to view and edit their addresses. Again one for residential and other one is for postal address. I am creating a select query (#1) to display user's address details in there editing form. (Values they provide in their registration process) I am not a problem till this step and its working properly. But my problem is when I trying to update user address. When updating I have to consider some important things. 1. I need to check has user provided both addresses in registration process. If so I need to update both addresses if user is edited. 2. If user have only residential address and he has provided a new postal address through his/her address editing form I have to insert in into database as a new record rather than updating it. Thats the where I got stuck and looking for help from someone. Any help would be greatly appreciating. Thank you.

@Barand, #1 and #3 values is database values. #7 is user input values from address edit form. So I need to update addresses with the values of #7. Reason is to select database value is I need to display existing values for users in address editing form.

Yes Sir, This is how I getting values from users. (From edit form) $user_id = (int)$_POST['user_id']; $street_no = filter_input(INPUT_POST, 'street_no', FILTER_SANITIZE_STRING); $street_name = filter_input(INPUT_POST, 'street_name', FILTER_SANITIZE_STRING); $city = filter_input(INPUT_POST, 'city', FILTER_SANITIZE_STRING); if (!empty($_POST['p_street_no'])) { $p_street_no = filter_input(INPUT_POST, 'p_street_no', FILTER_SANITIZE_STRING); } else { $p_street_no = ''; } if (!empty($_POST['p_street_name'])) { $p_street_name = filter_input(INPUT_POST, 'p_street_name', FILTER_SANITIZE_STRING); } else { $p_street_name = ''; } if (!empty($_POST['p_city'])) { $p_city = filter_input(INPUT_POST, 'p_city', FILTER_SANITIZE_STRING); } else { $p_city = ''; }

Oh.. Sorry its my mistake. But its still not updating my table for residential address.

Thank you. Then I got another problem when I trying make update queries for both addresses. When user is registering to the system, postal address is optional. But when he/she edit their profiles they can edit both addresses. So I need to check from database, which addresses are available for this user. This is how I check it now. // Get existing data $prep_stmt = "SELECT address_type , street_no , street_name , city FROM user_addresses WHERE user_id = ?"; $stmt = $mysqli->prepare($prep_stmt); if ($stmt) { $stmt->bind_param('i', $user_id); $stmt->execute(); $stmt->store_result(); $stmt->bind_result( $db_address_type , $db_street_no , $db_street_name , $db_city ); $addresses = array(); while($stmt->fetch()) { $addresses[$db_address_type] = array($street_no, $db_street_name, $db_state, $db_city); //echo '<pre>', print_r($addresses).'</pre>'; } $stmt->close(); unset($stmt); } When editing it is still optional for postal address. If user edit only residential address I need to update the table with new values. and if user edit postal address also then I need to check postal address is available for this user before making the update query. And also if it is not availble, I need to make a insert query for postal address. @Barand can you tell me how can I do it. I tried it something like this. But its not work for me foreach($addresses as $address_type => $values) { //if address is residential if($address_type == 1) { // Make the update query: $q = 'UPDATE user_addresses SET street_no = ? , street_name = ? , city = ? WHERE user_id = ? address_type = 1 LIMIT 1'; $update_stmt = $mysqli->prepare($q); $update_stmt->bind_param('sssi', $street_no , $street_name , $city , $user_id ); $update_stmt->execute(); } else { // update postal address } } Thank you.

I need to create two array from mysql select query. One for residential address and other one for postal address. This is my select query: $prep_query = "SELECT address_type , street_no , street_name , city FROM addresses WHERE user_id = ?"; $stmt = $mysqli->prepare($prep_query); if ($stmt) { $stmt->bind_param('i', $user_id); $stmt->execute(); $stmt->store_result(); $stmt->bind_result( $address_type , $street_no , $street_name , $city ); while($row = $stmt->fetch()) { //echo '<pre>',print_r($row[$street_name]).'</pre>'; } } Residential address is compulsory and postal address is optional for all users. So output from this query may be one row or two. How to create two arrays for these two addresses?

@Jacques1, Thank you for your previous details comment. It greatly helped me. Still I am learning and doing things to make this registration system more secure. Again I updated my script according to your previous post and this is what I do so far: // MySQL error codes define("MYSQL_ER_DUP_ENTRY", 23000); if ($_SERVER['REQUEST_METHOD'] == "POST") { //echo '<pre>', print_r($_POST, true).'</pre>'; // Sanitize and validate the data passed in // Check for the full name: if (!empty($_POST['name'])) { $name = filter_input(INPUT_POST, 'name', FILTER_SANITIZE_STRING); if (!preg_match ('/^[A-Z \'.-]{2,80}$/i', $name)) { // Not a valid name $error[] = 'First Name does not match the required format.'; } } // Check for the username: if (!empty($_POST['username'])) { $username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING); if (!preg_match ('/^[a-z0-9_-]{3,15}$/', $username)) { // Not a valid username $error[] = 'Username does not match the required format.'; } } // Check for the email: $email = filter_input(INPUT_POST, 'email', FILTER_SANITIZE_EMAIL); $email = filter_var($email, FILTER_VALIDATE_EMAIL); if (!filter_var($email, FILTER_VALIDATE_EMAIL)) { // Not a valid email $error[] = 'The email address you entered is not valid'; } // Check for a password and match against the confirmed password: if ( !empty( $_POST['password'])) { if (preg_match ('/^(\w*(?=\w*\d)(?=\w*[a-z])(?=\w*[A-Z])\w*){6,20}$/', $_POST['password']) ) { if ($_POST['password'] == $_POST['password_conform']) { $password = $_POST['password']; } else { $error[] = 'Your password did not match the confirmed password!'; } } else { $error[] = 'You are NOT entered a valid password! (use atleast 1 number & 1 capital letter)'; } } else { $error[] = 'Your password field can not be empty!'; } if (empty($error)) { // Create a hashed password $options = [ 'cost' => 12, ]; $hash_password = password_hash($password, PASSWORD_BCRYPT, $options); //echo $hash_password; // Insert the new user into the database $query = "INSERT INTO users ( name , username , email , password ) VALUES (?, ?, ?, ?)"; $insert_stmt = $mysqli->prepare($query); if ($insert_stmt){ // Bind variable for placeholder: $insert_stmt->bind_param('ssss', $name, $username, $email, $hash_password); // Execute the prepared query. $insert_stmt->execute(); if ($insert_stmt->affected_rows == 1) { // Success massege $success = "The account has been created successfully."; // Store success massege in SESSION: $_SESSION['success'] = $success; // Redirect page to same page $url = 'register.php'; // Define the URL. header("Location: $url"); exit(); // Quit the script. } else { // if registration fail: if (MYSQL_ER_DUP_ENTRY == $mysqli->sqlstate) { $error[] = 'You or somebody else has tried to register at our website with this username and e-mail address, but these details already exists in our database.'; } else { header("Location: error.php?err=Registration failure: INSERT"); exit(); } } } else { echo $mysqli->error; } } } I am greatly appreciating yours valuable suggestion and comments. Thank you.

@Jacques1, can you kindly elaborate it for better understand? Thank you.