Jump to content

Selecting multiple rows from a table, but the rows are being added together?

matt.sisto

By matt.sisto
in PHP Coding Help

Recommended Posts

matt.sisto Regular Member

matt.sisto

Posted
Posted

Hi all, getting to the invoicing part in my system. I have written the form which the user selects a from date, and a to date, and then selects a organisation, these values are passed to another script at which I have written an if statement to select the clients from a table that match the selected organisation (parent organisation that represents multiple employees/clients). the thing is when I output the variable value of client_id it is adding the total together rater than having multiple values.  ???

<?php
session_start();
  if (!isset($_SESSION['username']))
    {
   header("Location: login.php");
   exit();
  }

  require "dbconn2.php"; 
  
  $org_id = $_POST["org_id"]; 
  $month1 = $_POST["month1"];
  $day1 = $_POST["day1"];
  $year1 = $_POST["year1"];
  $month2 = $_POST["month2"];
  $day2 = $_POST["day2"];
  $year2 = $_POST["year2"];

  $invoice_start = $year1."-".$month1."-".$day1." ".$_POST["event_start"];
  $invoice_end = $year2."-".$month2."-".$day2." ".$_POST["event_end"];

if ($org_id !=null){
$result = mysql_query("SELECT SUM(client_id) FROM client WHERE org_id='$org_id'");
    if($result !=null){
      $client_id = mysql_result($result, 0);
    }
}
echo $client_id;
?>
<html>
<head>
<title>Book</title>
</head>
<body>
</body>
</html>
<?php
session_start();

  if (!isset($_SESSION['username']))
    {
   header("Location: login.php");
   exit();
  }
  
  require "dbconn2.php"; 
  
  $email_address = mysql_real_escape_string($_POST['email_address']);
  $unit = $_POST['unit'];
  $service = $_POST['service'];
  $quantity = $_POST['quantity'];
  $con_id1 = $_POST['con_id1'];
  $con_id2 = $_POST['con_id2'];
  $con_id3 = $_POST['con_id3'];
  $extra_info = $_POST['extra_info'];
  $start_time_hr = $_POST['start_time_hr'];
  $start_time_min = $_POST['start_time_min'];
  $time_sec = '00';
  $address_first_line = mysql_real_escape_string($_POST['address_first_line']);
  $post_code = $_POST['post_code'];
  $country = mysql_real_escape_string($_POST['country']);
  $month = mysql_real_escape_string($_POST["month"]);
  $day = $_POST["day"];
  $year = $_POST["year"];
  $today = date('Y-m-d'); 
  $event_start = $year."-".$month."-".$day." ".$_POST["event_start"]; 
  $start_time = $start_time_hr.":".$start_time_min.":".$time_sec." ".$_POST["start_time"];

  if ($event_start <= $today){
      $url = "Location: bookingform.php?error4=true";
      header($url);
      exit();
}	

if ($con_id1 !=null){
$result = mysql_query("SELECT SUM(rate) FROM consultant WHERE con_id='$con_id1'");
    if($result !=null){
      $rate = mysql_result($result, 0);
    }

  $result = mysql_query("SELECT SUM(fee) FROM rate_service WHERE service = '".$service."' AND unit ='".$unit."' AND rate='".$rate."'");
   if($result !=null){
      $fee = mysql_result($result, 0);
    }

  $x = (int) $quantity;
  $y = (int) $fee;
  $eventFee1 = $y * $x;
}

if ($con_id2 !=null){
$result = mysql_query("SELECT SUM(rate) FROM consultant WHERE con_id='$con_id2'");
    if($result !=null){
      $rate = mysql_result($result, 0);
    }

  $result = mysql_query("SELECT SUM(fee) FROM rate_service WHERE service = '".$service."' AND unit ='".$unit."' AND rate='".$rate."'");
   if($result !=null){
      $fee = mysql_result($result, 0);
    }

  $x = (int) $quantity;
  $y = (int) $fee;
  $eventFee2 = $y * $x;
}

if ($con_id3 !=null){
$result = mysql_query("SELECT SUM(rate) FROM consultant WHERE con_id='$con_id3'");
    if($result !=null){
      $rate = mysql_result($result, 0);
    }

  $result = mysql_query("SELECT SUM(fee) FROM rate_service WHERE service = '".$service."' AND unit ='".$unit."' AND rate='".$rate."'");
   if($result !=null){
      $fee = mysql_result($result, 0);
    }

  $x = (int) $quantity;
  $y = (int) $fee;
  $eventFee3 = $y * $x;
}

  $eventTotal = (int) $eventFee1 + (int) $eventFee2 + (int) $eventFee3; 

if ($unit == 'Day' && $quantity >= 1 && $quantity <= 7)
{
    $d = (int) $quantity;
    $event_end = strtotime("+$d Days", strtotime($event_start));
    $end_date = date('Y-m-d', $event_end);
    $end_Time =$start_time;
   
   $sql = "SELECT * FROM calendar_events WHERE event_start='".$event_start."' AND event_end>='".$end_date."' AND (con_1='".$con_id1."' OR con_2='".$con_id1."' OR con_3='".$con_id1."') AND (con_1='".$con_id2."' OR con_2='".$con_id2."' OR con_3='".$con_id2."') AND (con_1='".$con_id3."' OR con_2='".$con_id3."' OR con_3='".$con_id3."')";
   $result = mysql_query ($sql, $connection) or die ("Could not perform query $sql <br />".mysql_error());
    $row = mysql_fetch_array($result);
    if ($row != null){
      $url = "Location: bookingform.php?error2=true";
      header($url);
      exit();
  }
      
} 
else if ($unit == 'Hour' && $quantity >= 1 && $quantity <= 7)
{
    $h = (int) $quantity;
    $end_time = strtotime("+$h Hours", strtotime($start_time));
    $end_Time = date('G:i:s', $end_time);
    $end_date = $event_start;
   
   $sql = "SELECT * FROM calendar_events WHERE event_start='".$event_start."' AND (start_time<='".$start_time."' AND end_time>='".$end_time."') AND (con_1='".$con_id1."' OR con_2='".$con_id1."' OR con_3='".$con_id1."') AND (con_1='".$con_id2."' OR con_2='".$con_id2."' OR con_3='".$con_id2."') AND (con_1='".$con_id3."' OR con_2='".$con_id3."' OR con_3='".$con_id3."')";
    $result = mysql_query ($sql, $connection) or die ("Could not perform query $sql <br />".mysql_error());
    $row = mysql_fetch_array($result);
    if ($row != null){
      $url = "Location: bookingform.php?error2=true";//ERROR 2
      header($url);
      exit();
  }
}

  $sql = "SELECT client_id FROM client WHERE email_address= '$email_address'";
  $result = mysql_query ($sql, $connection) or die ("Could not perform query $sql <br />".mysql_error());
  $row = mysql_fetch_array($result);
if ($row != null) {
$result = mysql_query( "SELECT SUM(client_id) FROM client" );
if($result)
{
   $client_id = mysql_result($result, 0);
}
else
{
  die ("Could not perform query $sql <br />".mysql_error());
} 

if ($email_address !=null){
$result = mysql_query("SELECT SUM(client_id) FROM client WHERE email_address='$email_address'");
    if($result !=null){
      $client_id = mysql_result($result, 0);
    }
}

  $sql = "INSERT INTO calendar_events VALUES (0,'".$event_start."','".$end_date."','".$client_id."','".$service."','".$unit."','".$quantity."','".$start_time."','".$end_Time."','".$con_id1."','".$con_id2."','".$con_id3."','".$eventTotal."','".$address_first_line."','".$post_code."','".$country."','".$extra_info."')";
echo $sql;
  $result = mysql_query ($sql, $connection)
    or die ("Couldn't perform query $sql <br />".mysql_error());
  header("Location: confirmation.php");
  exit();
}
else {
      $url = "Location: bookingform.php?error1=true";//ERROR 1
      header($url);
  exit();
}

?>

<html>
<head>
<title>Invoice</title>
</head>
<body>
</body>
</html>

Any help appreciated.  8)

matt.sisto Regular Member

matt.sisto

Posted
  • Author
Posted

Sorry about that I seemed to of combined 2 scripts. :)

<?php
session_start();
  if (!isset($_SESSION['username']))
    {
   header("Location: login.php");
   exit();
  }

  require "dbconn2.php"; 
  
  $org_id = $_POST["org_id"]; 
  $month1 = $_POST["month1"];
  $day1 = $_POST["day1"];
  $year1 = $_POST["year1"];
  $month2 = $_POST["month2"];
  $day2 = $_POST["day2"];
  $year2 = $_POST["year2"];

  $invoice_start = $year1."-".$month1."-".$day1." ".$_POST["event_start"];
  $invoice_end = $year2."-".$month2."-".$day2." ".$_POST["event_end"];

if ($org_id !=null){
$result = mysql_query("SELECT SUM(client_id) FROM client WHERE org_id='$org_id'");
    if($result !=null){
      $client_id = mysql_result($result, 0);
    }
}
echo $client_id;
?>

matt.sisto Regular Member

matt.sisto

Posted
  • Author
Posted

This seems to be the issue, I think I might need to set the $client_id as an array which is determined by the sql results, is this correct? Because there are multiple client_id's that are represented by 1 org_id.

<?php
if ($org_id !=null){
$result = mysql_query("SELECT SUM(client_id) FROM client WHERE org_id='$org_id'");
    if($result !=null){
      $client_id = mysql_result($result, 0);
    }
}
echo $client_id;// combines all the values into 1
?>

matt.sisto Regular Member

matt.sisto

Posted
  • Author
Posted

Well I have clients that work for large corporate organisations, but each organisation is responsible for more than 1 client, so when I run the SQL statement to SELECT client_id where org_id=org_id, there is more than 1 result, but at the moment the script is just adding all the values together to create 1.

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.