MySQL Add/subtract Quantities?

Jul 17, 2005

Two mySQL Questions:

1.) how to numerically increase or decrease the value of a mySQL column

2.) what is the best column structure (e.g. "varchar" or "char" etc.)
for speediest increasing/decreasing

Here is the idea:

I sell fruit. I start with 100 apples, 100 bananas, and 100 grapes.
Visitor purchases 8 apples so now I have 92 of them left. What is the
exact mysql query statement that I would use (the following is wrong,
but gives idea what I'm trying to do):

mysql_query("update fruit_table set quantity_left = 92 where fruit =

Also, what would be the best table structure for the quantity part. Is
this the best?
CREATE TABLE fruit_table (
fruit tinytext NOT NULL,
quantity_left int(3) unsigned zerofill NOT NULL default ༼'

View 4 Replies


Mysql Command - Subtract (-1)

This code is for use in a shopping cart, I am not sure how to get it to subtract Ƈ' from the 'quantity' row in the table.


$SQL = " UPDATE products SET";
$SQL = $SQL . " quantity = '??? MINUS 1' WHERE id = '$id'";

#execute SQL statement
$result = mysql_db_query($db,"$SQL",$cid);

# check for error
if (!$result) { echo("ERROR: " . mysql_error() . "
"); }


View 4 Replies

Subtract From And Update Mysql Database Field?

I am trying to do the following1. receive input via a form2. From the submitted variables, check the email in the database table and then pick the number field i want to subtract from3. subtract the value from the submitted form from the value in the amount field in the database (I want to verify that the submitted value is less than the value in the database field)4. Update the database with the value of the subtraction...5. redirect to another pageI have written a script and when i run it, it does nothing.. I need experienced php programmers to please take a look and modify if necessary.

$host="localhost"; // Host name
$username="root"; // Mysql username

View 2 Replies

Mysql Database Join - How To Subtract The Consumption Using T2 And Want To Display In T1?

from t1 i have quantity in one column how to subtract the consumption using t2 and want to display in t1.

View 1 Replies

Updating Quantities

I want to update the quantity (which is a text field) of a specific item (having an item#) BUT if there multiple rows chosen in the item list, (i.e you can have 4 rows and you update the quantities on each. How can you update the Database with a single submit button? So that the corresponding item# gets updated with the correct quantity. All values of the list are chosen from an mySql query.

View 1 Replies

Adjust Stock Quantities

I’m building a site with live stock figures on, where account customers can order online and the order goes into a database, which someone can then retrieve at a later point. But I don’t know the correct formula to minus the ordered amount off the original quantity, I know how to store the details of the order in the database but not how to make the database update the quantity.

View 1 Replies

Large Data Quantities

I'm makigna rather big database with some information. I have one table at the moment, expected to be round about 5 more in the future.

I have about 11 milion rows in my single table right now, expected to be round about 100 - 500 million. My tables are in a structured way, to further increase optimation, only two columns.

My tables basically stores words/letters and similar things.. My problem is that working with these large tables/databases is not the fact that it takes allot of disk space, but the actual load time. Code:

View 1 Replies

Add 60 Days To A Mysql Stored DATE Then Subtract The Current Date

how to add 60 days to a mysql stored DATE, then subtract the current date from that date to see how many days there are... Its for a 60 day trial and when they signed up, it put the date in which they signed up in the database table... Here's what I have, but it's not working... But I can't get it to add 60 days to a mysql stored DATE,... then of course I have to figure out how to do the subtraction...

Code: [Select]$CustomerSaasDateStarted = $row['CustomerSaasDateStarted'];
$CustomerSaasDateStartedPlusSixtyDays = date("Y-m-d", strtotime($CustomerSaasDateStarted, "+60 days"));
// Test Output
echo 'Date Trial Started: '.date("M d, Y", strtotime($CustomerSaasDateStarted));
echo '<br />';
echo '60 days from Date Trial Started: '.date("M d, Y", strtotime($CustomerSaasDateStartedPlusSixtyDays));
echo '<br />';

View 5 Replies

Multiple Quantities In Shopping Cart...

I have a Page that Displays products (check out

In this page products are listed. The Color/Size combination (if applicable) are listed along with a quantity box for EACH attribute combination. Each Product has a AddToCart button.

What I wanna do is give the Shopper the ability to enter quantities for multiple attribute combinations of the same product and then have those products added to the cart with one button click. PHP Code:

View 1 Replies

Select Products From Db, Into Form With Quantities?

This is my first post, as I couldn't find a solution anywhere in the Forum.
In my db I have a table called products.

When I select them from, I need to have a quantity drop down, which will save
selected products, with quantities to a table called transaction. Code:

View 3 Replies

Update Quantities On Hand, For Inventory?

Right at the moment I have an inventory program that I was developing.It works great, for single quantity items, but now I'm getting to the point of making orders of 2-100, and I need to decrement the count of items when adding them to orders, so the quantities are updated.heres how my sql table looks.

PHP Code:
CREATE TABLE `_products` (
`productID` int(25) NOT NULL auto_increment,
`batch` varchar(10) collate latin1_german2_ci NOT NULL,

View 1 Replies

An Array Of Eligible Quantities Returned?

Building a store application for a client that makes use of a web service from a larger agency as a broker.This web service requires that items for purchase by end users be of a certain quantity or else the order will be considered ineligible for purchase.dropdown option boxes that contain only eligible quantity numbers.A quantity greater than 12 is available, then allow any quantity EXCEPT that which would leave only one item remaining.If a quantity of less than 12 is available and said quantity is even, then allow only even pairs for purchase.

If a quantity of of less than 12 is available and said quantity is odd, then allow any quantity except that which would leave only one item remaining.How conditionals to determine dropdown content should be structured to accommodate this.via conditionals, whether the final quantity vs the user's requested quantity would leave just one for purchase and thereby deny it?imagine why the service issuer did not make it so an array of eligible quantities was returned rather than a hard number.

View 3 Replies

Users To Update Quantities In The Database?

Allow users to update quantities in the database, by providing them some input fields, know that the sql update query is perfect, because if DON'T use the foreach loop, it will submit and update the last textfield only.But need the foreach loop so it will loop through all the textfields and update them all in the database.updating with this foreach loop?

Code:foreach($_POST['items'] as $p=>$q)
$ct->data[$key][0]='<input type="text" value="'.$ct->data[$key][0].'" name="product" />';
$ct->data[$key][1]='<input type="text" value='.$ct->data[$key][1].' id="qty" name="items['


View 3 Replies

Drop Down Menu, Add/Remove Quantities?

I'm new to PHP and i just started an internship that required me to post an inventory on a web page. I've been stuck on this adding a drop down menu to select between the tables and putting in four buttons (ADD/REMOVE/NEW/DELETE) for two days and my deadline is coming up fast. Here's the code.


View 1 Replies

Regular Expression For Matching Quantities And Unit?

I need to extract the quantity and unit from strings like this

1 tbsp
300 ml
10 g

The quantities will always be numbers, then there may or may not be a space then the unit. They may be 15 - 20 different units which can come from a list that we define (perhaps an array)

The solution can be in either javascript or PHP as I need to split them before storing them in a database. ie they need to be stored separately.

View 3 Replies

Cakephp/SQL To Define A Hasandbelongstomany Relationship With Quantities?

I have the following models: Recipe and Ingredient. I want to define the two in a HasAndBelongsToMany relationship, but the complication comes from the following: I want recipes to be able to possess a quantity of each ingredient.Quantity will be defined by two fields: Amount and Unit. So on an abstract level here's an example of what I want to see/be able to query:

Apple Pie:

View 1 Replies

Get Sold Items & There Quantities, In Period Of Time?

I have 2 tables, contains orders, & orders contents (i.e.,orders table, & o_contents), & I'd like to get statistics from these two tables, to see the sold quantities during an interval, a month, week, year, or even yesterday!here are the tables fields:orders table:


View 3 Replies


I am using MySQL and I have two tables:


Master table has duplicate ORDERNO and ITEM values. I have get total QTY using SQL 'GROUP BY' clause.I need to deduct/subtract BAL_QTY from SUM of ITEM (master_table). I've got SUM QTY value using query (actually there are many rows).

View 3 Replies

When To Subtract When I Extract?

I have a table that this board helped me build. However I still need some help. I have over 50 automotive lenders in my table that all have different rates and "discounts".

Their is a base_rate, special_discount, equity_discount, luxury_discount, and other_discount. I have a simple form that a user would type in score, term, year model, loan to value percentage(ltv), amount to finance.

I can list out all of the available discounts no problem. But what I am trying to do is get a net rate base on the information suppled to me. It might take modifying my table and that is fine with me. Their are 4 possible discounts. 1st is the special_discount. This is to be subtracted always, if it is stored in the record it needs to be subtracted from the base_rate. 2nd is the equity discount. This is to only be subtracted from the base rate if the user typed in a lower ltv than the low_ltv in the record. Also, if they are financing over 25k (luxury discount) some will give .25% off. Any ideas on how this newbie can make it work?

View 1 Replies

Subtract 100 From Column

What would be the easiest way to this in sql: I need to subtract 100 from column `foo`, but if doing this makes the value of `foo` less than 0, then I need it to add 1 to column `bar`. Is there an IF command that would allow me to do this, or would i have to compare and subtract the values for each row individually?

View 2 Replies

Subtract Strings

What function simply subtracts one string from another and outputs the result? As in, takes the string 3.145667 and subtracts 3.14 to get 5667. I've tried a few, but for some reason the server didn't recognize the only one I found that seemed to match the description.

View 3 Replies

Add And Subtract Time?

I have always had a problem with adding and subtracting time like for an

+ 07h:46min

View 1 Replies

Subtract One Word Set From Another ?

I have an array of common words: $common = array(a,the,about,is,from,to,go); and a string that is a long paragraph of words. $words(It could of course be turned into and array as well.)What I want to do is to remove the common words from my string and I am not sure if I should do this with a regex, or a foreach loopor if there is a particular array function that would suit the purpose.I have looked through the array function list but can not see anythingspecifically for this. I could perhaps use the change $words into an arrayand use the in_array($words)What would you recommend as the best way to do this ?

View 2 Replies

Subtract Time?

my times are set to $date = date('D M d, Y g:i a'); And this works fine posting data to MySQL but how do I get it so that it can take the time posted in the mysql which has the rowname `postdate` and subtract the time now so it can display something like:
"POSTED PIREP 1365 Minutes Ago" You know? Any suggestions?

View 7 Replies

Subtract Time

I am currently using datetime in mysql was wondering if somebody could point me to a good tutorial so I can figure out how to get:

$timeago = $datetime - now()

Where $timeago will echo something like Posted: 16 minutes ago or 1 day 4 hrs ago etc.

View 10 Replies

Subtract Dates

I think I'm overcomplicating things so I thought I'd ask if there is a better way to subtract two dates. Plus, my way is not very accurate.  Its a few days off... Both dates come from MySQL in the format "YYYY-MM-DD".

   $end_date = $row['end_date']; // (2007-10-10)
   $ts = time();
   $start_date = date("Y-m-d", $ts); // (2007-05-28)

   $end = round(strtotime("$end_date")/84000,5);
   $exploded = explode(".",$end);
           $end_date = $exploded[0];

   $start_date = round(strtotime("$start_date")/84000,5);
   $exploded = explode(".",$start_date);
   $start_date = $exploded[0];
   $expiration_in_days = abs($start_date - $end_date);

View 2 Replies

Copyrights 2005-15, All rights reserved