IanJ IanJ - 4 months ago 4
PHP Question

Is there something wrong with my while loops? My code syntax? Is it just too much?

I'm trying to build a dynamic database-driven calendar application. I'm in step 1, trying to set up the calendar. Instead of entering it all in manually, I thought I'd take a shortcut and make a PHP script to add it all into my databases.

The problem is, my script involves a lot of while loops. It's taking forever to load. Could it just take a while to execute the script, or is there an error somewhere? Is there an easier way to make a calendar?

EDIT: I have all of my database configuration in another block before this block. That is not the problem.

EDIT 2: I noticed some of my SQL contained reserved keywords. I changed those to different words.

Here is my code.

<?php


$calsql = "SELECT * FROM callender";

$calquery = mysqli_query($db,$calsql);

$calisfull = mysqli_num_rows($calquery);

if ($calisfull < 1) {
while ($all < 365) {
$year = "2016";
$month = 1;
if ($month == 1){

$dayofmonth = 1;
while ($dayofmonth < 32) {
$d = 0;
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 2){

$dayofmonth = 1;
while ($dayofmonth < 30) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 3){

$dayofmonth = 1;
while ($dayofmonth < 32) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 4){

$dayofmonth = 1;
while ($dayofmonth < 31) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 5){

$dayofmonth = 1;
while ($dayofmonth < 31) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 6){

$dayofmonth = 1;
while ($dayofmonth < 31) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 7){

$dayofmonth = 1;
while ($dayofmonth < 32) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 8){

$dayofmonth = 1;
while ($dayofmonth < 32) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 9){

$dayofmonth = 1;
while ($dayofmonth < 31) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 10){

$dayofmonth = 1;
while ($dayofmonth < 32) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
}
if ($month == 11){

$dayofmonth = 1;
while ($dayofmonth < 31) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
if ($month == 12){

$dayofmonth = 1;
while ($dayofmonth < 32) {
while ($d < 7) {
if ($d == 0){
$day = "Friday";
}

else if ($d == 1){
$day = "Saturday";
}

else if ($d == 2){
$day = "Sunday";
}

else if ($d == 3){
$day = "Monday";
}

else if ($d == 4){
$day = "Tuesday";
}

else if ($d == 5){
$day = "Wednesday";
}

else if ($d == 6){
$day = "Thursday";
}

$fillsql = "INSERT INTO callender (month, date, year, day) VALUES ('$month', '$dayofmonth', '$year', '$day')";
$fquery = mysqli_query($db,$fillsql);
$perfectly = "Perfectly";
$dayofmonth++;
$all++;
}

$d = 0;
}
$month++;
}
}

}

echo "Code worked " . $perfectly;

?>

Answer

Your code is taking a long time because you're executing 365 queries when you could be executing just one. You also don't need to hard code values for each month for each day of the week. Below I've collapsed your huge loop into two lines of code:

$db = null;
$start = strtotime('2016-01-01');
$end = strtotime('2016-12-31');
$current = $start;
$rows =[];

while ($current <=$end){
    //insert eg: ('1', '31', '2016', 'Sunday') for Jan 31 2016.
    $rows[] = strftime("('%m', '%d', '%Y', '%A')",$current);
    $current = strtotime('+1 day',$current); //to next day
}
//build one combined query
$sql='INSERT into callender (month, date, year, day) VALUES '.implode(',',$rows);
$fquery = mysqli_query($db,$sql);

if(!$fquery) die('DB error: '.mysqli_error($db));

echo 'Success: '.mysqli_num_rows($fquery).' rows inserted';

Checkout two of PHP's most awesome functions:

  • strtotime turns human-readable strings into UNIX timestamp
  • strftime turns a UNIX timestamp into a date string in the format of your choice

Now that the solution is out of the way: there is a bigger problem with this approach. It's a waste of DB space and resources to store dates because they can be calculated on the fly faster than it would take to retrieve them from the database. You can adjust the code above into a function that takes $startDate, $endDate and returns an array with all calendar entries to print into your HTML.