Unknown98

Wow, awesome in depth tutorial. Thank you!!

Hmm.. I see what you mean. To be honest I haven't heard of either of those and didn't even know things like that were out there. I'll definitely look into them. Do you mean create the insert query (or whatever it happens to be) outside of the class, and then inside the class do something like this: $stmt = $dbh->prepare($sql); Is it better practice to do it that way?

I've begun to play around with object oriented php and I was wondering how I would go about writing a method that inserts values into a table. I have a class, called queries, and I made this very simple function: class queries { public function insert($table, $field, $value){ global $dbh; $stmt = $dbh->prepare("INSERT INTO $table ($field) VALUES (:value)"); $stmt->bindParam(":value", $value); $stmt->execute(); } } $queries = new queries(); Then I call it like this: $queries->insert('members', 'username', $_POST['username']); Which all works fine, but obviously it only inserts the one value, their username. From searching around on the internet I think what I'd have to do is put the fields and values into an associative array, and then somehow loop through that array in my function. Does anyone know of any good tutorials on how to do this?

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

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. 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!!

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(); } ?>

I just figured it out... must have brain problems from coding all afternoon if($units_in_stock < $sales){ $sales = floor($units_in_stock); } That checks if their inventory is less than sales, and if it is, changes the sales number to their inventory number, rounding down (because you can't sell a partial product).

What I have is a formula to calculate sales for a company, for an online game where users create and run their own businesses. So depending on # of employees, wages, and your product price, among other things, it calculates the amount of sales you will generate in 1 hour. The problem that I've run into is, how do I limit the sales based on the current inventory? For example, there will be times when the formula calculates that they should sell 5 products this hour. However there is only 4 products in their inventory. So, their sales would be limited to 4 instead of 5. $units_in_stock is my variable for inventory, and $sales is the variable for their sales. I know what I need to do, I just can't think of how to do it . I thought of doing an if statement, but I couldn't think of how to make it so that it would sell the maximum possible products based on the amount they have available.

Thank you, that works like a charm! As for treating the utilization as an accounting ledger, that makes sense and I'll look into doing that. I should be the only one who is entering data, however it would still be good to do that. Utilization would never go above 24 hours (or possibly 168 hours if I decide to change it to a per week calculation), so I don't think that would be a problem.

// insert route info into the DB $stmt = $dbh->prepare("INSERT INTO routes (flt_num, depart_icao, arrive_icao, equipment, depart_time, arrive_time, days, block_time, distance, ticket_price) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); $stmt->bindParam(1, $flt_num); $stmt->bindParam(2, $depart_icao); $stmt->bindParam(3, $arrive_icao); $stmt->bindParam(4, $equipment); $stmt->bindParam(5, $depart_time); $stmt->bindParam(6, $arrive_time); $stmt->bindParam(7, $days); $stmt->bindParam(8, $block_time); $stmt->bindParam(9, $distance); $stmt->bindParam(10, $ticket_price); // insert one row $flt_num = "".$_POST['flt_num'].""; $depart_icao = "".$_POST['origin'].""; $arrive_icao = "".$_POST['destination'].""; $equipment = "".$_POST['equipment'].""; //Registration number of the aircraft $depart_time = "".$_POST['depart_time'].""; $arrive_time = "".$_POST['arrival_time'].""; $days = "".$_POST['days'].""; $block_time = "".$_POST['block_time'].""; //Total time of the route in HH:MM:SS format $distance = "".$_POST['distance'].""; $ticket_price = "".$_POST['ticket_price'].""; $stmt->execute(); $sth = null; $sth = $dbh->prepare("SELECT * FROM aircraft WHERE registration = ?"); $sth->execute(array($equipment)); while($data = $sth->fetch()){ $utilization = $data['utilization']; //Total utilization hours of the selected aircraft in HH:MM:SS format } //Attepmting to add block time of the route to the total utilization of the aircraft $sth = null; $sth = $dbh->prepare("SELECT ADDTIME('$utilization','$block_time') required_datetime"); $sth->execute(); while($row = $sth->fetch()){ //Update aircraft info with new utilization hours $sth = null; $sth = $dbh->prepare("UPDATE aircraft SET utilization = ? WHERE registration = ?"); $sth->execute(array($row['required_datetime'],$equipment)); } I'm not getting any errors, the database just is not updating the utilization hours. I'm pretty sure that's not how you use the AddTime function, it doesn't seem correct to me anyway. I couldn't really find anything about using it with variables versus actual times. As for the route itself, it is being inserted into the table fine, no problems there.

Is it possible to add (and subtract) time from a column that is in the mysql time() format (00:00:00)? I found the mysql ADDTIME() function: mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998'); Is this what I should be using? I tried it with variables instead of manual times and couldn't get it to work. I need to pull a time from a column, and then add a certain amount of time to it, from a user-input form. Say the column currently has 03:18:00 stored. The user inputs 01:00:00. The new time should be 04:18:00. Something like $newTime = $oldTime + $addedTime. I'm also using PDO, if that makes a difference in how I should be doing this. -Unk

print_r($_POST['cars']); still just returns the word "Array"... so I assume the data isn't being passed all the way through. Sessions... interesting, I hadn't thought of using those. I'll try that, thanks!

So, I have a checkbox, like so: (uci is an ID number) <input type='checkbox' name='cars[]' value='".$row['uci']."' /> That is passed to the next page via the form. However I need to pass the id numbers again, through to a third and final page. Currently I'm trying to do it through hidden inputs, but all I get returned to me is the word, "Array". This is the second page, displaying the data it recieves from the checkbox on the first page and attempting to send it through to the third page via a hidden input. $ids = $_POST['cars']; ... Displays data here... ... <form action='step_3.php' method='POST'> <input type='hidden' name='cars' value='$ids' /> <input type='submit' name='submit' value='Final Step' /> </form> I also tried <input type='hidden' name='cars' value='".$_POST['cars']."' /> but that didn't work either. This is what I'm using to display the data on the final page, as a check to make sure it's working (this is where I'm just getting the word, "Array"): echo"Car Id's: ".$_POST['cars']."<br />"; So, I guess my question is how do I pass the multiple options checked on the first page through more than one page? It works fine displaying the data on the second page, however when I try to display it again on the third page via the hidden input, it doesn't work.

I got it working with Prepared Statements. Here's the PDO query: $ids = $_POST['cars']; foreach($ids as &$val) $val=$dbh->quote($val); //iterate through array and quote $in = implode(',',$ids); //create comma separated list $stmt = $dbh->query( 'SELECT * FROM garage WHERE uci IN('.$in.')' ); while ($cars = $stmt->fetch()) { Thanks again for your help!

Thanks for your help. I did get it working with standard php, I guess just time to research how to do the same with prepared statements now.