CODEWITHSUNDEEP
phparraysforeach
user2250471
user2250471

Reputation: 1112

use php foreach to update database

I'm trying to add astrology signs to each user in my database. I have their birthdays stored already, and I have a while loop that puts the information I need into an array, but I can't get the foreach statement to work so that I can use each value in the array to calculate what I need and insert the new information back into the database. This is what I have so far:

while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
    $update = array(
        'user' => $row['username'],
        'day' => $row['bday'],
        'month' => $row['bmon']
    );
}
print_r($update);

print_r($update) successfully prints the last entry in the database like this:

Array ( [user] => johnsmith [day] => 30 [month] => 6 )

I've tried like 100 different things, but it's not working for me. This is what I'm trying to do:

foreach($update) {

    $astrology = "";
    if(($month==1 && $day>19)||($month==2 && $day<19)){
        $astrology = 'Aquarius';
    }else if(($month==2 && $day>18)||($month==3 && $day<21)){
        $astrology = 'Pisces';
    }else if(($month==3 && $day>20)||($month==4 && $day<20)){
        $astrology = 'Aries';
    }else if(($month==4 && $day>19)||($month==5 && $day<21)){
        $astrology = 'Taurus';
    }else if(($month==5 && $day>20)||($month==6 && $day<21)){
        $astrology = 'Gemini';
    }else if(($month==6 && $day>20)||($month==7 && $day<23)){
        $astrology = 'Cancer';
    }else if(($month==7 && $day>22)||($month==8 && $day<23)){
        $astrology = 'Leo';
    }else if(($month==8 && $day>22)||($month==9 && $day<23)){
        $astrology = 'Virgo';
    }else if(($month==9 && $day>22)||($month==10 && $day<23)){
        $astrology = 'Libra';
    }else if(($month==10 && $day>22)||($month==11 && $day<22)){
        $astrology = 'Scorpio';
    }else if(($month==11 && $day>21)||($month==12 && $day<22)){
        $astrology = 'Sagittarius';
    }else if(($month==12 && $day>21)||($month==1 && $day<20)){
        $astrology = 'Capricorn';
    }

    $query = "UPDATE useroptions SET astrology='$astrology' WHERE username='$user'" ;
    $db_conx->query($query);
    }

Upvotes: 0

Views: 107

Answers (3)

Armin
Armin

Reputation: 194

Don't use foreach, just do it in the while loop like Krish-R said, but don't overwrite $query for the update.

A better way would be to use a prepared statement for the update.

<?php
// prepare the update statement
if (!($stmt = $db_conx->prepare("UPDATE useroptions SET astrology = ? WHERE username = ?")) {
    die('Error preparing statement.');
}
// create vars and bind them
$username = '';
$astrology = '';
if(!($stmt->bind_param('ss', $astrology, $username))) {
    die('Error binding variables');
}
// loop over table
while($row...) {
    $update = ...;

    /*weird long ifelses to set $astrology*/

    $username = $update['user'];

    // execute the statement for each row
    if(!($stmt->execute()) {
        error_log("Update failed for username:'{$username}' with astrology:'{$astrology}' due to " . mysql_error($db_conx));
    }
}
// close the prepared statement
$stmt->close();

?>

The fastest way would probably be the use of a MySQL Function to calculate the astrology on the fly, that way you can update all data with just one simple query, like here: http://sqlfiddle.com/#!2/0c6fa/1

Basically its a

UPDATE users SET astrology = 
 IF ((bmonth = 1 && bday > 19)||(bmonth = 2 && bday < 19),
 'Aquarius',
 IF ((bmonth = 2 && bday > 18)||(bmonth = 3 && bday < 21),
 'Pisces',
 IF ((bmonth = 3 && bday > 20)||(bmonth = 4 && bday < 20),
 'Aries',
 IF ((bmonth = 4 && bday > 19)||(bmonth = 5 && bday < 21),
 'Taurus',
 IF ((bmonth = 5 && bday > 20)||(bmonth = 6 && bday < 21),
 'Gemini',
 IF ((bmonth = 6 && bday > 20)||(bmonth = 7 && bday < 23),
 'Cancer',
 IF ((bmonth = 7 && bday > 22)||(bmonth = 8 && bday < 23),
 'Leo',
 IF ((bmonth = 8 && bday > 22)||(bmonth = 9 && bday < 23),
 'Virgo',
 IF ((bmonth = 9 && bday > 22)||(bmonth = 10 && bday < 23),
 'Libra',
 IF ((bmonth = 10 && bday > 22)||(bmonth = 11 && bday < 22),
 'Scorpio',
 IF ((bmonth = 11 && bday > 21)||(bmonth = 12 && bday < 22),
 'Sagittarius',
 IF ((bmonth = 12 && bday > 21)||(bmonth = 1 && bday < 20),
 'Capricorn',
 'Alien'
 ))))))))))));

Upvotes: 0

ernie
ernie

Reputation: 6356

When accessing the month and day, you'll need to do access the array elements, and you don't need a loop for that. I'd modify your code like this:

$astrology = "";
$month = $update['month'];
$day = $update['day'];

if(($month==1 && $day>19)||($month==2 && $day<19)){
    $astrology = 'Aquarius';
}elsif(($month==2 && $day>18)||($month==3 && $day<21)){
    $astrology = 'Pisces';
}elsif(($month==3 && $day>20)||($month==4 && $day<20)){
    $astrology = 'Aries';
}elsif(($month==4 && $day>19)||($month==5 && $day<21)){
    $astrology = 'Taurus';
}elsif(($month==5 && $day>20)||($month==6 && $day<21)){
    $astrology = 'Gemini';
}elsif(($month==6 && $day>20)||($month==7 && $day<23)){
    $astrology = 'Cancer';
}elsif(($month==7 && $day>22)||($month==8 && $day<23)){
    $astrology = 'Leo';
}elsif(($month==8 && $day>22)||($month==9 && $day<23)){
    $astrology = 'Virgo';
}elsif(($month==9 && $day>22)||($month==10 && $day<23)){
    $astrology = 'Libra';
}elsif(($month==10 && $day>22)||($month==11 && $day<22)){
    $astrology = 'Scorpio';
}elsif(($month==11 && $day>21)||($month==12 && $day<22)){
    $astrology = 'Sagittarius';
}elsif(($month==12 && $day>21)||($month==1 && $day<20)){
    $astrology = 'Capricorn';
}

$user = $update['user'];

$query = "UPDATE useroptions SET astrology='$astrology' WHERE username='$user'" ;
$db_conx->query($query);
}

As an aside, a more flexible way of doing this would be to use native datestamp formats in your database and a date/time object in the PHP, rather than having to handle month/day the way you are.

Upvotes: 1

Krish R
Krish R

Reputation: 22711

Try this,

    while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {               
        $user = $row['username'];
        $day = $row['bday'];
        $month =  $row['bmon'];

                 $astrology = "";
                if(($month==1 && $day>19)||($month==2 && $day<19)){
                    $astrology = 'Aquarius';
                }else if(($month==2 && $day>18)||($month==3 && $day<21)){
                    $astrology = 'Pisces';
                }else if(($month==3 && $day>20)||($month==4 && $day<20)){
                    $astrology = 'Aries';
                }else if(($month==4 && $day>19)||($month==5 && $day<21)){
                    $astrology = 'Taurus';
                }else if(($month==5 && $day>20)||($month==6 && $day<21)){
                    $astrology = 'Gemini';
                }else if(($month==6 && $day>20)||($month==7 && $day<23)){
                    $astrology = 'Cancer';
                }else if(($month==7 && $day>22)||($month==8 && $day<23)){
                    $astrology = 'Leo';
                }else if(($month==8 && $day>22)||($month==9 && $day<23)){
                    $astrology = 'Virgo';
                }else if(($month==9 && $day>22)||($month==10 && $day<23)){
                    $astrology = 'Libra';
                }else if(($month==10 && $day>22)||($month==11 && $day<22)){
                    $astrology = 'Scorpio';
                }else if(($month==11 && $day>21)||($month==12 && $day<22)){
                    $astrology = 'Sagittarius';
                }else if(($month==12 && $day>21)||($month==1 && $day<20)){
                    $astrology = 'Capricorn';
                }

                $query = "UPDATE useroptions SET astrology='$astrology' WHERE username='$user'" ;
                $db_conx->query($query);
    }

Upvotes: 1

Related Questions