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.

What I have is a page with a list of cars, each with a checkbox next to them. <?php $sth = null; $count = 0; $sth = $dbh->prepare("SELECT * FROM garage WHERE warehouse_id = ? ORDER BY car_name ASC"); $sth->execute(array($_POST['ware_id'])); echo "<table>"; echo "<tr>"; echo '<td>Choose Vehicles:</td>'; echo "</tr>"; echo "<tr>"; echo "<td>"; echo "<form action='warehouse_ship_3.php' method='POST'>"; while($row = $sth->fetch()){ echo "<input type='checkbox' name='cars' value='".$row['uci']."' /> ID: ".$row['uci'].", ".$row['car_year']." ".$row['car_name']."<br />"; } echo "<input type='hidden' name='ware_id' value='".$_POST['ware_id']."' />"; echo "<hr />"; echo '<div class="center"><input class="myButton" type="submit" name="submit" value="Next" /></div></form>'; echo "</td>"; echo "</tr>"; echo "</table>"; ?> That works fine. What I'm trying to do with this second page, is select all the car's info from a table called "garage" which stores data for all the cars (car name, year, etc) and display it. UCI stands for Unique car id, every car has a different id. It works when the user only selects one car on the previous page, but if they select two or more cars, only the car with the highest UCI number shows up. How would I work it to display info on every car that they selected? <?php if(empty($_POST['cars'])) { echo("You didn't select any cars."); } else { $sth = $dbh->prepare("SELECT * FROM `garage` WHERE `uci` = ?"); $sth->execute(array($_POST['cars'])); while($cars = $sth->fetch()){ echo" ".$cars['uci'].", ".$cars['car_year']." ".$cars['car_name']." "; } } ?>

I've heard of PDO before, just never looked into it too much yet. Once I learn it I will probably re-write all my code to PDO. For now I'm just trying to get a working concept. I tried manually entering the owner_name and warehouse_id in the query, instead of using $userdata['user_name'] and $id, but that still did not work. It's not the quotes either, when I replace the single quotes with double quotes I get: Parse error: syntax error, unexpected T_STRING on line 2

Hmm, Well that didn't work. It just does the same thing, saying there are no cars selected when there are eligible cars in the DB table.

What am I doing wrong here with the WHERE clause? The following works fine without the where clause, it shows all cars in the table and the total next to them. But then when I add the where clause it says there are no cars selected, when I know for a fact there are cars in the database with the owner name and warehouse id that I specify. <?php $sql = 'SELECT `car_year`, `car_name`, COUNT(*) as `total` FROM `company_inventory` WHERE owner_name = '.$userdata['user_name'].' AND warehouse_id = '.$_GET['id'].' GROUP BY `car_name`, `car_year` ORDER BY `car_name` ASC'; $result = mysql_query($sql) or trigger_error($sql . ' has an error<br />' . mysql_error()); if(mysql_num_rows($result) > 0) { while($row = mysql_fetch_assoc($result)) { echo $row['car_name'] . ' | ' . $row['total'] . '<br />'; } } else { echo 'There are no cars selected!'; } ?>

Thanks! That works perfect

I have a mysql table that holds data for cars - each car has it's own ID, name, and year. Some cars are repeated. Example: (ID - year - car name) 0001 - 2009 - Honda Civic 0002 - 2008 - Toyota Prius 0003 - 2009 - Honda Civic 0004 - 2008 - Toyota Prius 0005 - 2008 - Toyota Prius 0006 - 2007 - Honda Civic How would I count how many of the same type of cars I have in that table and then display the quantity next to the car name? Like for the above example it would be: 2009 Honda Civic: 2 2007 Honda Civic: 1 2008 Toyota Prius: 3 Thanks in advance for any help

Oh. Haha, yes. Silly me. Thanks!

This is strange.. I have the following code that checks if there is currently a cap set on requests. $result = mysql_query("SELECT cap FROM requests"); while($data = mysql_fetch_array($result)) { if ($data['cap'] == '1') { echo"<h1>Requests are currently disabled at this time.</h1>"; }else{ echo'<h1><a href="requestlivery.php">Request Livery</a> | <a href="requestlogo.php">Request Logo</a></h1>'; } } For some reason, this: <h1><a href="requestlivery.php">Request Livery</a> | <a href="requestlogo.php">Request Logo</a></h1> is printed out twice on the webpage. Like: Request Livery | Request LogoRequest Livery | Request Logo I even put that code on a brand new blank page, and it does the same thing. Any ideas as to why would it do that? Unk

That was the problem. I wasn't calling the session_start() on the check login page Thanks!

I'm working on a small project that requires a user login function. There's only going to be about 10 members in the database. I've followed a tutorial of how to make a login system. I've never really worked with sessions before, but I did some research and get the general concept of them. What I want to do is store the user's username in a session when they log in, so I can run queries and such later specific to that user. How would I do that? I was able to manually set a session, but I need to set the session equal to what their username is on login. I've tried doing that below, but I don't think it's working. Either that, or I'm not calling the session correctly in the other page where I want to display their username. Here's the code for the page that checks if the login is correct: // username and password sent from form $myusername=$_POST['myusername']; $mypassword=$_POST['mypassword']; $encrypted_mypassword=md5($mypassword); $sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$encrypted_mypassword'"; $result=mysql_query($sql); // To protect MySQL injection (more detail about MySQL injection) $myusername = stripslashes($myusername); $mypassword = stripslashes($mypassword); $myusername = mysql_real_escape_string($myusername); $mypassword = mysql_real_escape_string($mypassword); // Mysql_num_row is counting table row $count=mysql_num_rows($result); // If result matched $myusername and $mypassword, table row must be 1 row if($count==1){ // Register $myusername, $mypassword and redirect to file "login_success.php" $_SESSION['myusername'] = $myusername; $_SESSION['password'] = $encrypted_mypassword; header("location:login_success.php"); } else { echo "Wrong Username or Password"; } ?> Thanks in advance for any replies