PDO Insert query not recognizing variable

Unknown98 · June 13, 2013

What I have is a script that calculates sales figures for facilities in an online game. The last part of this script that I need to work out is how to copy the financial data into a second table, which will be used as a ledger to display sales revenue for each facility. Everything works fine except that the insert query won't recognize the $revenue variable. It inserts a new row in the table for each facility like it's supposed to, with all the correct info, except the amount shows up as $0.00 instead of $revenue. If I change this line in the query:

$amount = "$revenue";

to something generic like:

$amount = "11";

It will work correctly, and insert $11.00 as the revenue for each facility. So the problem is somewhere with the $revenue variable, and I can't seem to work it out. The $revenue variable works fine in other areas of the script, such as updating the company's money.

Here's the entire script:

<?php
	$currentTime = time(); // current time (seconds since UNIX epoch)
	$update = "facility_sales";
	
	require_once($_SERVER['DOCUMENT_ROOT'] . '/includes/dbconn.php');

	/* Grab the company facility info from the company_facilities table */
    $stmt = $dbh->query("SELECT * FROM company_facilities");
    while($data = $stmt->fetch()) {

	
		/* Get average wages for all companies */
        $sth = $dbh->prepare("SELECT AVG(employee_wage) FROM company_facilities");
        $sth->execute();
        $avg_wage = $sth->fetchColumn();

        /* Get wages from user's company */
        $sth = $dbh->prepare("SELECT employee_wage FROM company_facilities WHERE id = ?");
        $sth->execute(array($data['id']));
        $wage = $sth->fetchColumn();
		
		/* Get employees from user's company */
        $sth = $dbh->prepare("SELECT employees FROM company_facilities WHERE id = ?");
        $sth->execute(array($data['id']));
        $employees = $sth->fetchColumn();
		
		/* Get managers from user's company */
        $sth = $dbh->prepare("SELECT managers FROM company_facilities WHERE id = ?");
        $sth->execute(array($data['id']));
        $managers = $sth->fetchColumn();
		
		/* Get equipment from user's company */
        $sth = $dbh->prepare("SELECT equipment FROM company_facilities WHERE id = ?");
        $sth->execute(array($data['id']));
        $equipment = $sth->fetchColumn();
		
		/* Get price from user's company */
        $sth = $dbh->prepare("SELECT unit_price FROM company_facilities WHERE id = ?");
        $sth->execute(array($data['id']));
        $price = $sth->fetchColumn();
		
		/* Get units_in_stock from user's company */
        $sth = $dbh->prepare("SELECT units_in_stock FROM company_facilities WHERE id = ?");
        $sth->execute(array($data['id']));
        $units_in_stock = $sth->fetchColumn();
		
		/* Get average unit_price for all companies */
        $sth = $dbh->prepare("SELECT AVG(unit_price) FROM company_facilities WHERE unit_price > 0 AND specialty = '".$data['specialty']."'");
        $sth->execute();
        $avg_price = $sth->fetchColumn();
		
		/* Calculate employee to equipment ratio */
		$ratio = ($equipment/$employees);
		if($ratio > 1){
		$ratio = 1;
		}
		else{
		$ratio = $ratio;
		}
		
		/* Calculate the difference between wage and average wage */
		$difference = ($wage-$avg_wage);
		
		/* Calculate Wage Modifier */
		$wage_modifier = (($difference/$avg_wage)*1.;
		if($wage_modifier > 0){
		$mod = $difference/$avg_wage;
		$wage_modifier = (pow(sqrt($mod),1.2))*1.4;
		}
		
			// Wage modifier only affects production, not sales, for the following facilities.
			if($data['field'] == "Mining" || $data['field'] == "Farming" || $data['field'] == "Production"){
			$wage_modifier = 0;
			}
		
		/* Get the units per hour number */
		if($data['field'] == "Mining"){
		$UPH = 0.672;
		}
		if($data['field'] == "Farming"){
		$UPH = 0.672;
		}
		if($data['specialty'] == "Aluminum" || $data['specialty'] == "Steel" || $data['specialty'] == "Glass" || $data['specialty'] == "Rubber" || $data['specialty'] == "Fabric" || $data['specialty'] == "Plastic"){
		$UPH = 0.336;
		}
		if($data['specialty'] == "Leather" || $data['specialty'] == "Chassis" || $data['specialty'] == "Body" || $data['specialty'] == "Window" || $data['specialty'] == "Tires" || $data['specialty'] == "Interior" || $data['specialty'] == "Engine"){
		$UPH = 0.168;
		}
		if($data['specialty'] == "Vehicle"){
		$UPH = 0.084;
		}
		if($data['field'] == "Retail"){
		$UPH = 0.042;
		}
		
		/* Calculate preliminary sales number */
		$pre_sales = ($UPH*$employees);
		
		/* Calculate the equipment modifier */
		$equipment_modifier = ($ratio/4);
		
			// Equipment modifier only affects production, not sales, for the following facilities.
			if($data['field'] == "Mining" || $data['field'] == "Farming" || $data['field'] == "Production"){
			$equipment_modifier = 0;
			}
			
		/* Calculate the Manager modifier */
		$manager_modifier = sqrt(($pre_sales*(0.1*$managers)));
		
		$manager_mod_max = $employees/10;
		
		if($manager_modifier > $manager_mod_max){
		$manager_modifier = $manager_mod_max;
		}
		
			// Manager modifier only affects production, not sales, for the following facilities.
			if($data['field'] == "Mining" || $data['field'] == "Farming" || $data['field'] == "Production"){
			$manager_modifier = 0;
			}
		
		/* Calculate the price modifier */
		$price_modifier = ((($price-$avg_price)*0.25)*-1);
		
		/* Finally, calculate the total sales */
		$sales = floor(($pre_sales+$equipment_modifier+$wage_modifier+$price_modifier+$manager_modifier));
		
		if($units_in_stock < $sales){
		$sales = floor($units_in_stock);
		}
		
		$revenue = $sales*$price;
		
        /* Update facility units */
	    $sth = $dbh->prepare("UPDATE company_facilities SET units_in_stock = units_in_stock - ?, sold_last_hour = ? WHERE id = ?");
	    $sth->execute(array($sales,$sales,$data['id']));
		
		/* Update company money */
	    $sth = $dbh->prepare("UPDATE companies SET money = money + ? WHERE id = ?");
	    $sth->execute(array($revenue,$data['company_id']));
				
	}
		
		$sth = $dbh->prepare("SELECT * FROM company_facilities");
		$sth->execute();
		$datas = $sth->fetchALL();
		foreach($datas as $data2) {
		
		/* Insert into company finance ledger */
		$stmt = $dbh->prepare("INSERT INTO company_finance (date, company_id, company_name, type, amount, description) VALUES (?, ?, ?, ?, ?, ?)");
		$stmt->bindParam(1, $date);
		$stmt->bindParam(2, $company_id);
		$stmt->bindParam(3, $company_name);
		$stmt->bindParam(4, $type);
		$stmt->bindParam(5, $amount);
		$stmt->bindParam(6, $description);
				
		// insert one row
		$date = "$currentTime";
		$company_id = "".$data2['company_id']."";
		$company_name = "".$data2['company_name']."";
		$type = "revenue";
		$amount = "$revenue";
		$description = "Product Sales (".$data2['facility_name'].")";
		$stmt->execute();
		
	}
	
?>

mac_gyver · June 13, 2013

the only place you are calculating $revenue is inside the loop above that one. are you sure the zero isn't the value leftover from the last pass through that previous loop?

have you echoed $revenue at the point you are trying to use it so that you know what is in it?

BTW - you are killing your server with all those queries inside of loops retrieving the same data you already retrieved in the first query and in the case of the $avg_wage, you are retrieving the same value over and over. you have at least two-times too much code and about 8 times too many queries.

Edited June 13, 2013 by mac_gyver

mac_gyver · June 13, 2013

as a continuation of the above reply, it appears that your final INSERT INTO company_finance query should just be executed inside the main loop, which is where the correct $revenue value for each row exists.

also, one of the points of using prepared queries is if you need to execute them more than once with different data, you prepare them once (not inside of any looping) and execute them when you need to.

Edited June 13, 2013 by mac_gyver

mac_gyver · June 13, 2013

here's a reorganization of your code the way you probably intended it to be -

<?php
    $currentTime = time(); // current time (seconds since UNIX epoch)
    
    require_once($_SERVER['DOCUMENT_ROOT'] . '/includes/dbconn.php');

    /* Get average wages for all companies */
    $sth = $dbh->query("SELECT AVG(employee_wage) FROM company_facilities");
    $avg_wage = $sth->fetchColumn();
    
    /* prepare the Get average unit_price for all companies query */
    $pq_select_ave_price = $dbh->prepare("SELECT AVG(unit_price) FROM company_facilities WHERE unit_price > 0 AND specialty = ?");
    
    /* prepare the Update facility units query */
    $pq_update_co_fac = $dbh->prepare("UPDATE company_facilities SET units_in_stock = units_in_stock - ?, sold_last_hour = ? WHERE id = ?");

    /* prepare the Update company money query */
    $pq_update_co = $dbh->prepare("UPDATE companies SET money = money + ? WHERE id = ?");

    /* prepare the Insert into company finance ledger query */
    $pq_insert_co_fin = $dbh->prepare("INSERT INTO company_finance (date, company_id, company_name, type, amount, description) VALUES (?, ?, ?, ?, ?, ?)");
    $pq_insert_co_fin->bindParam(1, $date);
    $pq_insert_co_fin->bindParam(2, $company_id);
    $pq_insert_co_fin->bindParam(3, $company_name);
    $pq_insert_co_fin->bindParam(4, $type);
    $pq_insert_co_fin->bindParam(5, $amount);
    $pq_insert_co_fin->bindParam(6, $description);    

    /* Grab the company facility info from the company_facilities table */
    $stmt = $dbh->query("SELECT * FROM company_facilities");
    while($data = $stmt->fetch()) {

        // the following 6 values are already in $data
        $wage = $data['employee_wage'];
        $employees = $data['employees'];
        $managers = $data['managers'];
        $equipment = $data['equipment'];
        $price = $data['unit_price'];
        $units_in_stock = $data['units_in_stock'];
        
        /* Get average unit_price for all companies */
        // SELECT AVG(unit_price) FROM company_facilities WHERE unit_price > 0 AND specialty = ?
        $pq_select_ave_price->execute(array($data['specialty']));
        $avg_price = $pq_select_ave_price->fetchColumn();
        
        /* Calculate employee to equipment ratio */
        $ratio = $equipment/$employees;
        if($ratio > 1){
            $ratio = 1;
        } else{
            $ratio = $ratio;
        }
        
        /* Calculate the difference between wage and average wage */
        $difference = $wage-$avg_wage;
        
        /* Calculate Wage Modifier */
        $wage_modifier = ($difference/$avg_wage)*1.8;
        if($wage_modifier > 0){
            $mod = $difference/$avg_wage;
            $wage_modifier = (pow(sqrt($mod),1.2))*1.4;
        }
        
        /* Get the units per hour number */
        if($data['field'] == "Mining"){
            $UPH = 0.672;
        }
        if($data['field'] == "Farming"){
            $UPH = 0.672;
        }
        if(in_array($data['specialty'],array("Aluminum","Steel","Glass","Rubber","Fabric","Plastic"))){
            $UPH = 0.336;
        }
        if(in_array($data['specialty'],array("Leather","Chassis","Body","Window","Tires","Interior","Engine"))){
            $UPH = 0.168;
        }
        if($data['specialty'] == "Vehicle"){
            $UPH = 0.084;
        }
        if($data['field'] == "Retail"){
            $UPH = 0.042;
        }
        
        /* Calculate preliminary sales number */
        $pre_sales = $UPH*$employees;
        
        /* Calculate the equipment modifier */
        $equipment_modifier = $ratio/4;
        
        /* Calculate the Manager modifier */
        $manager_modifier = sqrt(($pre_sales*(0.1*$managers)));
        $manager_mod_max = $employees/10;
        if($manager_modifier > $manager_mod_max){
            $manager_modifier = $manager_mod_max;
        }
        
        // modifiers only affect production, not sales, for the following facilities.
        if(in_array($data['field'],array("Mining","Farming","Production"))){
            $wage_modifier = 0;
            $equipment_modifier = 0;
            $manager_modifier = 0;
        }
        
        /* Calculate the price modifier */
        $price_modifier = (($price-$avg_price)*0.25)*-1;
        
        /* Finally, calculate the total sales */
        $sales = floor($pre_sales+$equipment_modifier+$wage_modifier+$price_modifier+$manager_modifier);
        
        if($units_in_stock < $sales){
            $sales = floor($units_in_stock);
        }
        
        $revenue = $sales*$price;
        
        /* Update facility units */
        // UPDATE company_facilities SET units_in_stock = units_in_stock - ?, sold_last_hour = ? WHERE id = ?
        $pq_update_co_fac->execute(array($sales,$sales,$data['id']));
        
        /* Update company money */
        // UPDATE companies SET money = money + ? WHERE id = ?
        $pq_update_co->execute(array($revenue,$data['company_id']));

        /* Insert into company finance ledger */
        $date = $currentTime;
        $company_id = $data['company_id'];
        $company_name = $data['company_name'];
        $type = "revenue";
        $amount = $revenue;
        $description = "Product Sales ({$data['facility_name']})";
        // INSERT INTO company_finance (date, company_id, company_name, type, amount, description) VALUES (?, ?, ?, ?, ?, ?)
        $pq_insert_co_fin->execute();

    }

a couple of suggestions -

1) you should store dates/times in your database using a DATE or DATETIME data type. do not store unix timestamps as they are problematic due to the time zone/dst conversion needed to display them in a human readable format. if you store a YYYY-MM-DD HH:MM:SS value in your database, it will always be that value regardless of any timezone or daylight savings settings in effect on your server. you will also be able to directly use all the mysql date/time functions in your queries to manipulate/process your date/time values.

2) you should not store both the company_id and company_name in the company_facilities and company_finance tables. what happens when someone wants to change their company name? the company_name should only be stored in one place, where it is associated with the company_id (probably in your companies table.) to display the company_name using the company_id, you would simply join whatever table you are querying with the companies table to access the company_name value.

Unknown98 · June 13, 2013

as a continuation of the above reply, it appears that your final INSERT INTO company_finance query should just be executed inside the main loop, which is where the correct $revenue value for each row exists.

also, one of the points of using prepared queries is if you need to execute them more than once with different data, you prepare them once (not inside of any looping) and execute them when you need to.

That's what I first thought, but when I tried it the insert query just failed and didn't enter any data at all.

Thanks for the tip! I'm still learning PDO (and even PHP) every time I dive into it, so I'm sure there's parts where I can improve on.

here's a reorganization of your code the way you probably intended it to be -

<?php
    $currentTime = time(); // current time (seconds since UNIX epoch)
    
    require_once($_SERVER['DOCUMENT_ROOT'] . '/includes/dbconn.php');

    /* Get average wages for all companies */
    $sth = $dbh->query("SELECT AVG(employee_wage) FROM company_facilities");
    $avg_wage = $sth->fetchColumn();
    
    /* prepare the Get average unit_price for all companies query */
    $pq_select_ave_price = $dbh->prepare("SELECT AVG(unit_price) FROM company_facilities WHERE unit_price > 0 AND specialty = ?");
    
    /* prepare the Update facility units query */
    $pq_update_co_fac = $dbh->prepare("UPDATE company_facilities SET units_in_stock = units_in_stock - ?, sold_last_hour = ? WHERE id = ?");

    /* prepare the Update company money query */
    $pq_update_co = $dbh->prepare("UPDATE companies SET money = money + ? WHERE id = ?");

    /* prepare the Insert into company finance ledger query */
    $pq_insert_co_fin = $dbh->prepare("INSERT INTO company_finance (date, company_id, company_name, type, amount, description) VALUES (?, ?, ?, ?, ?, ?)");
    $pq_insert_co_fin->bindParam(1, $date);
    $pq_insert_co_fin->bindParam(2, $company_id);
    $pq_insert_co_fin->bindParam(3, $company_name);
    $pq_insert_co_fin->bindParam(4, $type);
    $pq_insert_co_fin->bindParam(5, $amount);
    $pq_insert_co_fin->bindParam(6, $description);    

    /* Grab the company facility info from the company_facilities table */
    $stmt = $dbh->query("SELECT * FROM company_facilities");
    while($data = $stmt->fetch()) {

        // the following 6 values are already in $data
        $wage = $data['employee_wage'];
        $employees = $data['employees'];
        $managers = $data['managers'];
        $equipment = $data['equipment'];
        $price = $data['unit_price'];
        $units_in_stock = $data['units_in_stock'];
        
        /* Get average unit_price for all companies */
        // SELECT AVG(unit_price) FROM company_facilities WHERE unit_price > 0 AND specialty = ?
        $pq_select_ave_price->execute(array($data['specialty']));
        $avg_price = $pq_select_ave_price->fetchColumn();
        
        /* Calculate employee to equipment ratio */
        $ratio = $equipment/$employees;
        if($ratio > 1){
            $ratio = 1;
        } else{
            $ratio = $ratio;
        }
        
        /* Calculate the difference between wage and average wage */
        $difference = $wage-$avg_wage;
        
        /* Calculate Wage Modifier */
        $wage_modifier = ($difference/$avg_wage)*1.8;
        if($wage_modifier > 0){
            $mod = $difference/$avg_wage;
            $wage_modifier = (pow(sqrt($mod),1.2))*1.4;
        }
        
        /* Get the units per hour number */
        if($data['field'] == "Mining"){
            $UPH = 0.672;
        }
        if($data['field'] == "Farming"){
            $UPH = 0.672;
        }
        if(in_array($data['specialty'],array("Aluminum","Steel","Glass","Rubber","Fabric","Plastic"))){
            $UPH = 0.336;
        }
        if(in_array($data['specialty'],array("Leather","Chassis","Body","Window","Tires","Interior","Engine"))){
            $UPH = 0.168;
        }
        if($data['specialty'] == "Vehicle"){
            $UPH = 0.084;
        }
        if($data['field'] == "Retail"){
            $UPH = 0.042;
        }
        
        /* Calculate preliminary sales number */
        $pre_sales = $UPH*$employees;
        
        /* Calculate the equipment modifier */
        $equipment_modifier = $ratio/4;
        
        /* Calculate the Manager modifier */
        $manager_modifier = sqrt(($pre_sales*(0.1*$managers)));
        $manager_mod_max = $employees/10;
        if($manager_modifier > $manager_mod_max){
            $manager_modifier = $manager_mod_max;
        }
        
        // modifiers only affect production, not sales, for the following facilities.
        if(in_array($data['field'],array("Mining","Farming","Production"))){
            $wage_modifier = 0;
            $equipment_modifier = 0;
            $manager_modifier = 0;
        }
        
        /* Calculate the price modifier */
        $price_modifier = (($price-$avg_price)*0.25)*-1;
        
        /* Finally, calculate the total sales */
        $sales = floor($pre_sales+$equipment_modifier+$wage_modifier+$price_modifier+$manager_modifier);
        
        if($units_in_stock < $sales){
            $sales = floor($units_in_stock);
        }
        
        $revenue = $sales*$price;
        
        /* Update facility units */
        // UPDATE company_facilities SET units_in_stock = units_in_stock - ?, sold_last_hour = ? WHERE id = ?
        $pq_update_co_fac->execute(array($sales,$sales,$data['id']));
        
        /* Update company money */
        // UPDATE companies SET money = money + ? WHERE id = ?
        $pq_update_co->execute(array($revenue,$data['company_id']));

        /* Insert into company finance ledger */
        $date = $currentTime;
        $company_id = $data['company_id'];
        $company_name = $data['company_name'];
        $type = "revenue";
        $amount = $revenue;
        $description = "Product Sales ({$data['facility_name']})";
        // INSERT INTO company_finance (date, company_id, company_name, type, amount, description) VALUES (?, ?, ?, ?, ?, ?)
        $pq_insert_co_fin->execute();

    }

a couple of suggestions -

1) you should store dates/times in your database using a DATE or DATETIME data type. do not store unix timestamps as they are problematic due to the time zone/dst conversion needed to display them in a human readable format. if you store a YYYY-MM-DD HH:MM:SS value in your database, it will always be that value regardless of any timezone or daylight savings settings in effect on your server. you will also be able to directly use all the mysql date/time functions in your queries to manipulate/process your date/time values.

2) you should not store both the company_id and company_name in the company_facilities and company_finance tables. what happens when someone wants to change their company name? the company_name should only be stored in one place, where it is associated with the company_id (probably in your companies table.) to display the company_name using the company_id, you would simply join whatever table you are querying with the companies table to access the company_name value.

Wow thanks for that! I see how you prepared the queries beforehand, and then executed them later on. That's not what I've been doing, but need to start doing.

Yeah I've always just stored the timestamps and converted them into a human readable format. That's just the way I was first showed how to do it... There's always a better way to do something though I guess! I'll research the DATE and DATETIME formats and try converting some of my columns into that format.

Ah... didn't think of that. Right now I don't have a way to change the company name, but it's better to think ahead. Luckily right now it'll be easier to change that than if I had waited longer down the road.

I have 1 more, unrelated question.. I was told a few years ago the proper way to store numbers was the DECIMAL format. Is that still true? That's the format I use for all numbers I store... money, inventory, prices, etc.

The code above works perfectly, and is probably much better than what I had before... I'll study it later tonight when I get off work to see what else I can learn Thanks a ton!!

mac_gyver · June 13, 2013

the DECIMAL data type is the best for storing your money, inventory, prices as it avoids floating point conversion errors. if your inventory is whole units only (integers) you should use an INT type for that.

Unknown98 · June 14, 2013

Alright, thanks again! Pretty much all my numbers are decimal numbers.

Sign In

PDO Insert query not recognizing variable

Recommended Posts

Unknown98

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Unknown98

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Unknown98

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information