if…else if…PHP SQL Insert into tbl based on conditions

[elliottdan]

I have a feeling that I am going about this all wrong. I am trying to insert different values into the table based on different criteria. 

if(!$currentWeekNumber<$a AND $EngReleaseDate == NULL){
$sql = "INSERT INTO `$tbl` (`UnitID`, `$a`, `$c`, `$d`) VALUES ('$UnitID', '$WeeklyHours', '$WeeklyHours', '$WeeklyHours')";
$conn->query($sql);
echo $sql;
echo "<br>";
echo "<br>";
var_dump($conn);
echo "<br>";
echo "<br>";
print_r($conn);
echo "<br>";
echo "<br>";
}

else if ($currentWeekNumber>$a AND $EngReleaseDate == NULL){
$sql = "INSERT INTO `$tbl` (`UnitID`, `$b`, `$e`, `$f`) VALUES ('$UnitID', '$WeeklyHours', '$WeeklyHours', '$WeeklyHours')";
$conn->query($sql); 
echo $sql;
echo "<br>";
echo "<br>";
var_dump($conn);
echo "<br>";
echo "<br>";
print_r($conn);
echo "<br>";
echo "<br>";
}

else if ($EngReleaseDate > NULL){
$sql = "INSERT INTO `$tbl` (`UnitID`, `$EngReleaseWeek`) VALUES ('$UnitID', '$WeeklyHours')";
$conn->query($sql); 
echo $sql;
echo "<br>";
echo "<br>";
var_dump($conn);
echo "<br>";
echo "<br>";
print_r($conn);
echo "<br>";
echo "<br>";
} 

The issue I am having is. It gets to the if, enters the first variable right place and skips the rest of the UnitID's . The sql is echoed correctly for the rest of the variables, for the else if's. They are just not being inserted into the table. The over all reason for this is I'm trying to create variables to be able to enter into a graph. With that said is it possible to create variables based off of an if...else if...instead of inserting them into a table, just to pull right back out, to use in the graph.

Below is the overall code for the project. The only reason I am echoing everything out is to make sure it is working as it should. Thank you for any and all help and direction. This one truly has me scratching my head.

{
include_once('pmconnect.php');

$sql = "SELECT tblJobMaster.Engineer, tblJobMaster.SalesHandOffDate,tblUnitMaster.EngReleaseDate, tblUnitMaster.SubmittalPromise, tblUnitMaster.HandoffReply, tblUnitMaster.SubmittalComplete, tblUnitMaster.UnitID, tblUnitMaster.Hours, tblUnitMaster.EngOpsEstHours, tblUnitMaster.JobNumber, tblJobMaster.JobNumber FROM tblJobMaster LEFT JOIN tblUnitMaster ON tblJobMaster.JobNumber = tblUnitMaster.JobNumber WHERE tblJobMaster.Engineer = 'JBL' "; 
$result=$conn->query($sql);
if ($result->num_rows==0)
       {
      echo "<font size=10 color=red>User not found.</font><br>";
      die(0);
        } 
    while($row=$result->fetch_array())
 {       
$Engineer=$row[0];
$SalesHandOffDate=$row[1];
$EngReleaseDate=$row[2];
$SubmittalPromise=$row[3];
$HandoffReply=$row[4];
$SubmittalComplete=$row[5];
$UnitID=$row[6];
$Hours=$row[7];
$EngOpsEstHours=$row[8];


echo $UnitID;
echo "<br>";
echo $Engineer;
echo "<br>";
echo "<font size=2>Submittal Hours = </font>" . $Hours;
echo "<br>";
echo "<font size=2>Eng Ops Hours = </font>" . $EngOpsEstHours;
echo "<br>";
$SchedOffSet = 9;
echo "<font size=2>8 Weeks from this week  = </font>" . $SchedOffSet;
echo "<br>";
$CloseSchedOffSet = 3;
echo "<font size=2>3 Weeks from this week  = </font>" . $CloseSchedOffSet;
echo "<br>";
echo "<br>";

echo "<font size=2>Sales Hand Off = </font>" . $SalesHandOffDate;
echo "<br>";
echo "<font size=2>Eng Release = </font>" . $EngReleaseDate;
echo "<br>";
echo "<font size=2>Submittal Promise = </font>" . $SubmittalPromise;
echo "<br>";
echo "<font size=2>Reply to sales = </font>" . $HandoffReply;
echo "<br>";
echo "<font size=2>Engineer Complete = </font>" . $SubmittalComplete;
echo "<br>";
echo "<br>";

//Figure out the week of the year 
$SalesHandOffWeek = date("W ", strtotime($SalesHandOffDate));
echo "<font size=2>Sales HandOff Week = </font>" . $SalesHandOffWeek;
echo "<br>";

$EngReleaseWeek = date("W ", strtotime($EngReleaseDate));
echo "<font size=2>Eng Release Week = </font>" . $EngReleaseWeek;
echo "<br>";

$SubmittalPromiseWeek = date("W ", strtotime($SubmittalPromise));
echo "<font size=2>Submittal Promise Week = </font>" . $SubmittalPromiseWeek;
echo "<br>";

$HandoffReplyWeek = date("W ", strtotime($HandoffReply));
echo "<font size=2>HandOff Reply Week = </font>" . $HandoffReplyWeek;
echo "<br>";

$SubmittalCompleteWeek = date("W ", strtotime($SubmittalComplete));
echo "<font size=2>Engineer Complete Week = </font>" . $SubmittalCompleteWeek;
echo "<br>";
echo "<br>";

//Figure out the day of the week
$SalesHandOffDayNumber = date('N', strtotime($SalesHandOffDate));
echo "<font size=2>SalesHandOff Day Number = </font>" . $SalesHandOffDayNumber;
echo "<br>";

$EngReleaseDayNumber = date('N', strtotime($EngReleaseDate));
echo "<font size=2>EngRelease Day Number = </font>" . $EngReleaseDayNumber;
echo "<br>";

$SubmittalPromiseDayNumber = date('N', strtotime($SubmittalPromise));
echo "<font size=2>SubmittalPromise Day Number = </font>" . $SubmittalPromiseDayNumber;
echo "<br>";

$HandoffReplyDayNumber = date('N', strtotime($HandoffReply));
echo "<font size=2>HandoffReply Day Number = </font>" . $HandoffReplyDayNumber;
echo "<br>";

$SubmittalCompleteDayNumber = date('N', strtotime($SubmittalComplete));
echo "<font size=2>Engineer Complete Day Number = </font>" . $SubmittalCompleteDayNumber;
echo "<br>";
echo "<br>";

//Figure out Curent week's
$currentWeekNumber = date('W');
echo "<font size=2>This Weeks number = </font>" . $currentWeekNumber;
echo "<br>";

$NextWeekNumber = date('W', strtotime(' +  7days'));
echo "<font size=2>Next Weeks number = </font>" . $NextWeekNumber;
echo "<br>";

$Week3Number = date('W', strtotime(' +  14days'));
echo "<font size=2>Week 3 number = </font>" . $Week3Number;
echo "<br>";

$Week4Number = date('W', strtotime(' +  21days'));
echo "<font size=2>Week 4 number = </font>" . $Week4Number;
echo "<br>";

$Week5Number = date('W', strtotime(' +  28days'));
echo "<font size=2>Week 5 number = </font>" . $Week5Number;
echo "<br>";

$Week6Number = date('W', strtotime(' +  35days'));
echo "<font size=2>Week 6 number = </font>" . $Week6Number;
echo "<br>";

$Week7Number = date('W', strtotime(' +  42days'));
echo "<font size=2>Week 7 number = </font>" . $Week7Number;
echo "<br>";

$Week8Number = date('W', strtotime(' +  49days'));
echo "<font size=2>Week 8 number = </font>" . $Week8Number;
echo "<br>";
echo "<br>";

$WorkingHours = $EngOpsEstHours - $Hours;

$a = $SubmittalPromiseWeek + $SchedOffSet;
$b = $currentWeekNumber + $CloseSchedOffSet;
$c = $SubmittalPromiseWeek + $SchedOffSet + 1;
$d = $SubmittalPromiseWeek + $SchedOffSet + 2;
$e = $currentWeekNumber + $CloseSchedOffSet + 1;
$f = $currentWeekNumber + $CloseSchedOffSet + 2;

$x=3;
$WeeklyHours = $WorkingHours/$x;      

$tbl= "tbl".$Engineer."hours";

echo $tbl;
echo "<br>";

if(!$currentWeekNumber<$a AND $EngReleaseDate == NULL){
$sql = "INSERT INTO `$tbl` (`UnitID`, `$a`, `$c`, `$d`) VALUES ('$UnitID', '$WeeklyHours', '$WeeklyHours', '$WeeklyHours')";
$conn->query($sql);
$query = $conn->query($sql);
echo $sql;
echo "<br>";
echo "<br>";
var_dump($query);
echo "<br>";
echo "<br>";
print_r($query);
echo "<br>";
echo "<br>";
}

else if ($currentWeekNumber>$a AND $EngReleaseDate == NULL){
$sql = "INSERT INTO `$tbl` (`UnitID`, `$b`, `$e`, `$f`) VALUES ('$UnitID', '$WeeklyHours', '$WeeklyHours', '$WeeklyHours')";
$conn->query($sql); 
$query = $conn->query($sql);
echo $sql;
echo "<br>";
echo "<br>";
var_dump($query);
echo "<br>";
echo "<br>";
print_r($query);
echo "<br>";
echo "<br>";
}

else if ($EngReleaseDate > NULL){
$sql = "INSERT INTO `$tbl` (`UnitID`, `$EngReleaseWeek`) VALUES ('$UnitID', '$WeeklyHours')";
$conn->query($sql); 
$query = $conn->query($sql);
echo $sql;
echo "<br>";
echo "<br>";
var_dump($query);
echo "<br>";
echo "<br>";
print_r($query);
echo "<br>";
echo "<br>";
}

Edited November 2, 2016 by elliottdan

[mac_gyver]

I have a feeling that I am going about this all wrong.

 

 

sorry to confirm this, but yes, this is not a correct database design, making it almost impossible to insert, find, or retrieve data.

 

your database tables are laid out like a multi-sheet spreadsheet. you need to do some research on 'database normalization.'

 

basically, one data item will be stored per row in whatever a table is designed for and all the same meaning data will be in the same table. once you store the raw data with any relevant - who, what, when, where, and why information about the data item, it will be simple to write queries to find and retrieve the data the way you want.

[Jacques1]

Besides the database issues, you definitely need to work on your code. The script is riddled with security vulnerabilities, endless repetitions, obsolete HTML fragments (font is deprecated since 1997!) that don't belong there anyway, cryptic variables and arcane formatting.

 

This is a simple task, so the code should be very short and easy to understand. RIght now, it's neither.

 

It's generally a good idea to plan the script before typing, maybe on a piece of paper. This lets you see the bigger picture and come up with smarter solutions. For example, when you have many similar actions, you use loops rather than writing down the same code over and over again.

[elliottdan]

 

 

basically, one data item will be stored per row in whatever a table is designed for

 

 Let me clarify the table for you, mind you this is a temp table as well.  The table is set up as 01-52 being the weeks of the year. I want to dump the info in, and than turn right back around and pull the info back out, based on the current week of the year and the next 7 weeks. The concept is I have 6 people and I want to see a forecast chart of what their next 8 weeks of working hours looks like. 

 

For example. 

 

This Weeks number = 44

Next Weeks number = 45

Week 3 number = 46

Week 4 number = 47

Week 5 number = 48

Week 6 number = 49

Week 7 number = 50

Week 8 number = 51

 

I do not care about weeks 01-43 or week 52 the table will show null for all of those weeks. 

 

 

 

sorry to confirm this, but yes, this is not a correct database design, making it almost impossible to insert, find, or retrieve data

 

Don't be sorry this was just an idea to see if its even possible. I'm simply trying to avoid creating more variables just to be able to chart out the data I'm after.  

 

 

 

 

 you definitely need to work on your code. The script is riddled with security vulnerabilities, endless repetitions, obsolete HTML fragments

 

Every thing you see will be deleted and cleaned up this is more of just a concept idea ruff sketch if you will. All of the echo's and fonts are simply there for debugging purpose. 

Edited November 3, 2016 by elliottdan

[kicken]

With that said is it possible to create variables based off of an if...else if...instead of inserting them into a table, just to pull right back out, to use in the graph.

Sure, just define the variables in the if/else if/else branch accordingly.

if (...){
   $a = 1;
} else if (...){
   $a = 2;
} else {
   $a = 3;
}

echo $a;

The value of $a will change according to how the program flows based on whatever conditions you put in for the ... parts.

 

 

I tried following your code to make suggestions on a better way, but honestly the code just does not make much sense to me at all. First of all, I am guessing this is not at all what you wanted:

if(!$currentWeekNumber<$a AND $EngReleaseDate == NULL){

The ! will convert $currentWeek to a boolean value then compare that to $a, so you get essentially 0 < $a since any valid $currentWeek value would be negated to false / 0. Since $a should only be 1 - 53 then that condition would always be true meaning that branch depends only on the value of $EngReleaseDate.

 

Second, your $a, $c, and $d variables are + 9, + 10, and +11 ahead of $SubmittalPromiseWeek. Your $b, $e, and $f are +3, +4, and +5 weeks ahead of $currentWeekNumber. So the data you're storing doesn't seem to line up with your 8-weeks out goal.

 

Lastly you can probably have the database do a lot of the work for you, but it's hard to say for sure without knowing what data you have and what data you want. This query, as a start, would handle calculating the week numbers and working hours for you saving a fair bit of PHP code.

 

SELECT
    tblUnitMaster.UnitID
    , WEEK(tblUnitMaster.SubmittalPromise,3) as SubmittalPromiseWeek
    , WEEK(NOW(),3) as CurrentWeek
    , tblUnitMaster.EngOpsEstHours - tblUnitMaster.Hours as WorkingHours
    , (tblUnitMaster.EngOpsEstHours - tblUnitMaster.Hours) / 3 as WeeklyHours
FROM tblJobMaster
LEFT JOIN tblUnitMaster ON tblJobMaster.JobNumber = tblUnitMaster.JobNumber
WHERE
    tblJobMaster.Engineer = 'JBL'

If you provide sample data and a clearer picture of your desired output perhaps someone can be of more assistance.

[elliottdan]

Thank you all for your help I fond the leading cause of my issue. I was indeed able to get it to work. Simply was missing a variable.