CODEWITHSUNDEEP
phpmysql
BaconJuice
BaconJuice

Reputation: 3779

Inserting a value based on another values result into MYSQL using PHP

I need a bit of help on a sticky situation.

Currently I have an array that looks like this

Array
(
[0] => Array
    (
        [Task_ID] => 59
        [email] => [email protected]
    )

[1] => Array
    (
        [Task_ID] => 59
        [email] => [email protected]
    )

[2] => Array
    (
        [Task_ID] => 59
        [email] => [email protected]
    )

[3] => Array
    (
        [Task_ID] => 60
        [email] => [email protected]
    )

[4] => Array
    (
        [Task_ID] => 60
        [email] => [email protected]
    )

[5] => Array
    (
        [Task_ID] => 61
        [email] => [email protected]
    )

)

Here is my current insert statement.

        if(is_array($myArr)){
        $valuesArr = array();
        foreach($myArr as $row){

            $sql = "INSERT INTO user_data (Task_ID, email, url) values ";
            $TaskID = (int) $row['Task_ID'];
            $email = mysql_real_escape_string( $row['email'] );

            $valuesArr[] = "('$TaskID', '$email')";
            $sql .= "('$TaskID', '$email')";
            mysql_query($sql) or exit(mysql_error());
        }
    }

What I would like to do is insert a new value that is NOT in the array which is the URL value.

For the URL I would like to increment a number with each unique email, where the same email will get the same URL value.

An example of what the SQL syntax SHOULD look like.

INSERT INTO user_data (TaskID, email, url) values ('60', '[email protected]','1')
INSERT INTO user_data (TaskID, email, url) values ('80', '[email protected]','1')
INSERT INTO user_data (TaskID, email, url) values ('82', '[email protected]','2')
INSERT INTO user_data (TaskID, email, url) values ('62', '[email protected]','2')
INSERT INTO user_data (TaskID, email, url) values ('42', '[email protected]','2')
INSERT INTO user_data (TaskID, email, url) values ('61', '[email protected]','3')
INSERT INTO user_data (TaskID, email, url) values ('81', '[email protected]','3')
INSERT INTO user_data (TaskID, email, url) values ('92', '[email protected]','4')
INSERT INTO user_data (TaskID, email, url) values ('32', '[email protected]','4')
INSERT INTO user_data (TaskID, email, url) values ('72', '[email protected]','4')

Notice the URL increasing by one for each email change. Please note that TaskID is NOT the PK and the emails are not in order.

Thank you for reading very much appreciated.

Upvotes: 0

Views: 100

Answers (1)

Stormherz
Stormherz

Reputation: 376

Give it a try

if (is_array($myArr)) {
    $valuesArr = array();
    $addedEmails = array();

    $cnt = 0;
    foreach($myArr as $row){

        $sql = "INSERT INTO user_data (Task_ID, email, url) values ";
        $TaskID = (int) $row['Task_ID'];
        $email = mysql_real_escape_string( $row['email'] );

        if (!in_array($email, $addedEmails)) {
            $cnt++;
            $addedEmails[] = $email;
        }

        $valuesArr[] = "('$TaskID', '$email', '$cnt')";
        $sql .= "('$TaskID', '$email', '$cnt')";
        mysql_query($sql) or exit(mysql_error());
    }
}

Upvotes: 1

Related Questions