How to update multiple sql

PHP
1

I am confused with my logic here… I have read that I should use insert when the table is emptied, otherwise to use update but I have the following page, where the user can upgrade to a premium membership. Currently, he or she has not opted for memberships, they just have a free member section. Should I do an insert in this case or update?

1748×508 35.9 KB

I am also wondering if I can get this to work using update because I can get the user to select one plan at a time but not two plans or three plans…

611×968 29.2 KB

Here are my codes but I can’ get the last part to work…


<!DOCTYPE html>
<html>
<head>
	<title></title>
	<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>








<?php
session_start();




if(!isset($_SESSION['u_uid']) || !isset($_POST['submit'])) {
header("Location: update.php?subscription=notset");
exit ();
} else {
include_once 'includes/dbh.php';

$subscriptionplan = mysqli_real_escape_string($conn, $_POST['subscriptionplan']);
$subscriptionplan2 = mysqli_real_escape_string($conn, $_POST['subscriptionplan2']);
$subscriptionplan3 = mysqli_real_escape_string($conn, $_POST['subscriptionplan3']);
                                      

         $sql = "SELECT * FROM memberships WHERE user_uid = '".$_SESSION['u_uid']."'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0) {
  while($row = mysqli_fetch_assoc($result)) {
   


                                    












// Select from the database



		// insert into plan A

$fees = 100;
$fees2 = 150;
$fees3 = 200;
$totalfees = 0;
$paid = 0;
$overdue = 0;


      
                          

if ($subscriptionplan == '' || $subscriptionplan2 == '' || $subscriptionplan3 == '') {
   header("Location: update.php?subscription=mustchooseallfields");
   exit();

 } else {
      if ($subscriptionplan == 'Level 1' && $subscriptionplan2 == 'Level 1' && $subscriptionplan3 == 'Level 1') {
         header("Location: update.php?subscription=mustchoosedifferentlevels");
         exit();

      } else {
          if ($subscriptionplan == 'Level 2' && $subscriptionplan2 == 'Level 2' && $subscriptionplan3 == 'Level 2') {
              header("Location: update.php?subscription=mustchoosedifferentlevels");
              exit();

      } else {
            if ($subscriptionplan == 'Level 3' && $subscriptionplan2 == 'Level 3' && $subscriptionplan3 == 'Level 3') {
            header("Location: update.php?subscription=mustchoosedifferentlevels");
            exit();

      } else {
         if ($row['activate'] == 0) {
            header("Location: update.php?subscription=notactivated");
            exit();

         } else {
            if ($row['subscriptionplan'] != 'None') {
               header("Location: update.php?subscription=planAtaken");
               exit();

             } else {
             	   if ($row['subscriptionplan2'] != 'None') {
                 header("Location: update.php?subscription=planBtaken");
                 exit();

             } else {
             	   if ($row['subscriptionplan3'] != 'None') {
                 header("Location: update.php?subscription=planCtaken");
                 exit();

             } else {

            if ($subscriptionplan == 'Level 1' && $row['subscriptionplan'] == 'None') {
            $totalfees += $fees;
            $sql = "UPDATE memberships
                    SET subscriptionplan = '$subscriptionplan', subscriptionplandate = now(), fees = $fees, paid = $paid, expirydate = now() + interval '1' day, paidbydate = now() + interval '1' day, overdue = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'
                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
               if ($subscriptionplan == 'Level 2' && $row['subscriptionplan'] == 'None') {
            $totalfees += $fees2;
            $sql = "UPDATE memberships
                    SET subscriptionplan = '$subscriptionplan', subscriptionplandate = now(), fees = $fees2, paid = $paid, expirydate = now() + interval '1' day, paidbydate = now() + interval '1' day, overdue = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'
                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
                  if ($subscriptionplan == 'Level 3' && $row['subscriptionplan'] == 'None') {
            $totalfees += $fees3;
            $sql = "UPDATE memberships
                    SET subscriptionplan = '$subscriptionplan', subscriptionplandate = now(), fees = $fees3, paid = $paid, expirydate = now() + interval '1' day, paidbydate = now() + interval '1' day, overdue = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'
                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
                  if ($subscriptionplan2 == 'Level 1' && $row['subscriptionplan2'] == 'None') {
            $totalfees += $fees;
            $sql = "UPDATE memberships
                    SET subscriptionplan2 = '$subscriptionplan2', subscriptionplandate2 = now(), fees2 = $fees, paid2 = $paid, expirydate2 = now() + interval '1' day, paidbydate2 = now() + interval '1' day, overdue2 = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'
                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
                 if ($subscriptionplan2 == 'Level 2' && $row['subscriptionplan2'] == 'None') {
            $totalfees += $fees2;
            $sql = "UPDATE memberships
                    SET subscriptionplan2 = '$subscriptionplan2', subscriptionplandate2 = now(), fees2 = $fees2, paid2 = $paid, expirydate2 = now() + interval '1' day, paidbydate2 = now() + interval '1' day, overdue2 = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'
                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
                  if ($subscriptionplan2 == 'Level 3' && $row['subscriptionplan2'] == 'None') {
            $totalfees += $fees3;
            $sql = "UPDATE memberships
                    SET subscriptionplan2 = '$subscriptionplan2', subscriptionplandate2 = now(), fees2 = $fees3, paid2 = $paid, expirydate2 = now() + interval '1' day, paidbydate2 = now() + interval '1' day, overdue2 = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'
                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
                 if ($subscriptionplan3 == 'Level 1' && $row['subscriptionplan3'] == 'None') {
            $totalfees += $fees;
            $sql = "UPDATE memberships
                    SET subscriptionplan3 = '$subscriptionplan3', subscriptionplandate3 = now(), fees3 = $fees, paid3 = $paid, expirydate3 = now() + interval '1' day, paidbydate3 = now() + interval '1' day, overdue3 = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'
                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
                if ($subscriptionplan3 == 'Level 2' && $row['subscriptionplan3'] == 'None') {
            $totalfees += $fees2;
            $sql = "UPDATE memberships
                    SET subscriptionplan3 = '$subscriptionplan3', subscriptionplandate3 = now(), fees3 = $fees2, paid3 = $paid, expirydate3 = now() + interval '1' day, paidbydate3 = now() + interval '1' day, overdue3 = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'
                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
                  if ($subscriptionplan3 == 'Level 3' && $row['subscriptionplan3'] == 'None') {
            $totalfees += $fees3;
            $sql = "UPDATE memberships
                    SET subscriptionplan3 = '$subscriptionplan3', subscriptionplandate3 = now(), fees3 = $fees3, paid3 = $paid, expirydate3 = now() + interval '1' day, paidbydate3 = now() + interval '1' day, overdue3 = $overdue, totalfees = $totalfees
                    WHERE user_uid = '".$_SESSION['u_uid']."'

                   ";
            mysqli_query($conn, $sql);
            header("Location: update.php?subscription=success");
            exit();

             } else {
                  if ($subscriptionplan == 'Level 1' && $row['subscriptionplan'] == 'None' && $subscriptionplan2 == 'Level 2' && $row['subscriptionplan2'] == 'None' && $subscriptionplan3 == 'None' && $row['subscriptionplan3'] == 'None') {
           
            $sql = "UPDATE memberships
                    SET subscriptionplan = '$subscriptionplan', subscriptionplandate = now(), fees = $fees, paid = $paid, expirydate = now() + interval '1' day, paidbydate = now() + interval '1' day, overdue = $overdue
                    WHERE user_uid = '".$_SESSION['u_uid']."'

                   ";
            mysqli_query($conn, $sql);

            $sql2 = "UPDATE memberships
                     SET subscriptionplan2 = '$subscriptionplan2', subscriptionplandate2 = now(), fees2 = $fees2, paid2 = $paid, expirydate2 = now() + interval '1' day, paidbydate2 = now() + interval '1' day, overdue2 = $overdue
                     WHERE user_uid = '".$_SESSION['u_uid']."'

                     ";
            mysqli_query($conn, $sql2);
            
            header("Location: update.php?subscription=success");
            exit();
             }
        }
      }
 }
}
}

}

}
}
}
}


}
}
}
}
}
}
}
}
}
}

I have named the first sql as sql1 and the 2nd s sql2

2

I would first normalise your database. having indexed column names is a clear sign for trouble. What if you need a fourth or fifth subscription?

3 Likes
3

This is the weirdest advice ever.

When you subscribe to a service and nothing is known about the user you need to INSERT a new user. When the user is already there but you need to change their phone number for example you need to UPDATE the existing user.

create new → INSERT
change existing → UPDATE

regardless of how many rows are already in the database

QFT

2 Likes
4

I am confused because on my very first page, I give the user a choice whether they want a a free or premium membership. They can select no as an option for premium membership, so I guess I would need to use update for this?

5

Is the user already in the database at that point?
No → INSERT
Yes → UPDATE

6

Then, the following sql should work, but it is not updating the second subscriptionplan…

 $sql = "UPDATE memberships
                    SET subscriptionplan = '$subscriptionplan', subscriptionplandate = now(), fees3 = $fees3, paid = $paid, expirydate = now() + interval '1' day, paidbydate = now() + interval '1' day, overdue = $overdue, totalfees = $totalfees, subscriptionplan2 = '$subscriptionplan2'
                    WHERE user_uid = '".$_SESSION['u_uid']."'

                   ";
            mysqli_query($conn, $sql);

It is not updating subscriptionplan2

7

Then the variable contains the same value as the database.

It simply does not happen that in the same query one field is updated and another is not.

8

Probably the reason why it doesn’t update subscriptionplan2…

What do you think this piece of code does?

      if ($subscriptionplan == 'Level 1' && $subscriptionplan2 == 'Level 1' && $subscriptionplan3 == 'Level 1') {
         header("Location: update.php?subscription=mustchoosedifferentlevels");
         exit();

      } else {
          if ($subscriptionplan == 'Level 2' && $subscriptionplan2 == 'Level 2' && $subscriptionplan3 == 'Level 2') {
              header("Location: update.php?subscription=mustchoosedifferentlevels");
              exit();

      } else {
            if ($subscriptionplan == 'Level 3' && $subscriptionplan2 == 'Level 3' && $subscriptionplan3 == 'Level 3') {
            header("Location: update.php?subscription=mustchoosedifferentlevels");
            exit();

      } else {...

Edit: Well… atleast one of the reasons… didnt go through all the code…

Edit2: I hope this is just for learning purposes and not a real project…

9

I’m picking up the faintest notion that the OP doesn’t want to do this for some reason, and the main problem with that is that it would make the code so much less complex, and therefore many of the problems might just not exist.

@piano0011, why are you resisting the many suggestions to split the subscription information into a separate table, where it should be? Is it because you feel it’s quicker to leave it where it is? The complex series of if/then/else just to figure out which columns you need to update should tell you that it is not. Doing a quick-and-dirty database layout to test a concept and get a demo up and running is one thing, but when it stops being quick, it’s time to abandon it and lay things out properly.

5 Likes
11

The OP has been advised on many things besides database normalisation, but has chosen to ignore the advice and continue to do things their own way. As a consequence of this the OP runs into more and more problems as the code complicates itself beyond reason, and comes back to ask for more advice to deal with the problems that arise from ignoring advice. :upside_down:
But if the answers given repeat the unwanted advice previously given, it is simply glossed over and ignored, and the cycle continues. :banghead:
I think in this situation, the only person who can help the OP is the OP themself, by actually listening to what has already been said time and time again, and realising that taking what are perceived as short-cuts, is not the path to quick and easy solutions, but is in fact the road to coding Hell; a place of eternal misery and bad indenting where scripts end like this:-

            exit();
             }
        }
      }
 }
}
}

}

}
}
}
}


}
}
}
}
}
}
}
}
}
}
6 Likes
12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.