How can I create this two arrays in php?

[thara]

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?

[Barand]

while($row = $stmt->fetch()) {        

            $array[$address_type] = array($street_no, $street_name, $city);

        }

 

Edited November 17, 2015 by Barand

[thara]

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.

 

 

[Barand]

WHERE user_id = ? AND address_type = 1

[thara]

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

[Barand]

Where are you getting the value for $street_name etc?

 

In the code I posted earlier, $street_name would be $values[1]

[thara]

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 = '';
}    

[Barand]

Sorry but I have no idea how your code in #1, #3 and #7 above are related. All I see is code snippets with no context.

[thara]

@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.

[thara]

@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.

Edited November 17, 2015 by thara

[Barand]

 

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.

 

What does your DB/table structure look like?

[thara]

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]

Add a UNIQUE KEY (user_id, address_type).

 

When you update the table use INSERT ... ON DUPLICATE KEY statement so that if the postal or residential address exists then it will be updated, but if it doesn't exist then a new record is added. No need for extra SELECT queries to see if the address already exists.

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)
;

[thara]

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

[Barand]

Then both will be updated. If a new address is the same as an existing address then MySQL will ignore the update and do nothing.

[thara]

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?

Edited November 17, 2015 by thara

[Barand]

 

Do I need two queries?

 

No, you have a record for each address so you will need one prepared query, but you will execute it twice,  once for the residential address data and again for the postal address data

[thara]

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.

[Barand]

$sql = "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)";                


$stmt = $db->prepare($sql);

$stmt->bind_param('iisss', $user_id, $address_type, $street_no, $street_name, $city );


// res address

$address_type = 1;

$stmt->execute();


// postal address

$address_type = 2;

$street_no = $p_street_no;

$street_name = $p_street_name;

$city = $p_city;

$stmt->execute();

[thara]

@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.

[Barand]

Sorry. Check for blanks before inserting postal address

[thara]

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]

Triggers

 

http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html

[thara]

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.

[Barand]

Example

CREATE TABLE IF NOT EXISTS users_access_log (
    log_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL,    
    previous_data VARCHAR(250) NOT NULL,    
    current_data VARCHAR(250) NOT NULL,        
    changed_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id),
    KEY (user_id)
);

CREATE TRIGGER log_address_change
    AFTER UPDATE 
    ON user_addresses 
    FOR EACH ROW
		INSERT INTO users_access_log (user_id,action,previous_data,current_data)
		VALUES (
			NEW.user_id,
			CASE NEW.address_type
				WHEN 1 THEN 'Residential change'
				ELSE 'Postal change'
				END,
			CONCAT_WS(', ', OLD.street_no, OLD.street_name, OLD.city),
			CONCAT_WS(', ', NEW.street_no, NEW.street_name, NEW.city)
		);