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 ༼'


Mysql Command - Subtract (-1)

Jun 12, 2006

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 View Related

Subtract From And Update Mysql Database Field?

Dec 21, 2010

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 View Related

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

Mar 7, 2011

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

View 1 Replies View Related

Updating Quantities

Feb 25, 2004

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 View Related

Adjust Stock Quantities

Dec 17, 2004

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 View Related

Large Data Quantities

Nov 23, 2007

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 View Related

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

May 8, 2011

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 View Related

Multiple Quantities In Shopping Cart...

Jan 30, 2002

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 View Related

Select Products From Db, Into Form With Quantities?

Feb 12, 2006

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 View Related

Update Quantities On Hand, For Inventory?

Jul 29, 2010

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 View Related

An Array Of Eligible Quantities Returned?

Jan 22, 2010

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 View Related

Users To Update Quantities In The Database?

Nov 20, 2010

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 View Related

Drop Down Menu, Add/Remove Quantities?

Jul 20, 2010

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 View Related

Regular Expression For Matching Quantities And Unit?

Aug 7, 2010

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 View Related

Cakephp/SQL To Define A Hasandbelongstomany Relationship With Quantities?

Mar 30, 2011

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 View Related

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

May 4, 2011

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 View Related


Jul 16, 2011

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 View Related

When To Subtract When I Extract?

Nov 27, 2004

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 View Related

Subtract 100 From Column

Nov 29, 2005

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 View Related

Subtract Strings

Mar 26, 2007

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 View Related

Add And Subtract Time?

May 5, 2010

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

+ 07h:46min

View 1 Replies View Related

Subtract One Word Set From Another ?

Nov 6, 2009

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 View Related

Subtract Time?

Nov 7, 2007

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 View Related

Subtract Time

Apr 19, 2007

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 View Related

Subtract Dates

May 30, 2007

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 View Related

Add / Subtract One Second From Date?

Jun 18, 2011

I am trying to work around something and need to add and subtract one second to the times. I'm using the format:

$now = date("Y-m-d H:i:s");

I need to ultimatley have the results such as:

$now = (date("Y-m-d H:i:s") - :01);
$now = (date("Y-m-d H:i:s") + :01);

View 2 Replies View Related

Subtract Two Dates From Each Other?

Feb 22, 2010

I need help with subtracting two dates from each other and getting the result in days.

Code: [Select]
$current_time= strtotime("now");
$current_date= date('Y-m-d',$current_time);

View 2 Replies View Related

Subtract Time From A Date

Mar 29, 2007

I want to get a starttime. To get this I have to subtract the runtime from a
unit e.g. 08:40:15 from a date e.g. 2007-03-29 16:48:30. So the starttime is
2007-03-29 08:08:15.

Is there any function in php to do so, or must I do it by myself?

View 3 Replies View Related

Group And Subtract Two Tables?

Feb 9, 2007

I am trying to do is take two tables group them, count them then subtract the results. I think I may need a table join but I am not sure? Code:

View 3 Replies View Related

Add, Subtract Percentage In A Column

Dec 21, 2006

I need the query to add all the numbers in a column, then subtract a percentage from it to produce a final number. I came up with this. Code:

SELECT SUM(price)/100*20
FROM style
WHERE collection_month_id = &#3906;-06-a'
GROUP BY collection_month_id;

this gives me 20 percent of the total.

View 5 Replies View Related

Subtract 2 Dates/times

Jan 20, 2006

I have two dates with times like this:

Start date/time = 2006-01-01 08:12
End date/time = 2006-01-07 10:45

I want to take the End date/time and subtract Start date/time from it. I want the results in days, hours and minutes. Like this example: 4 days, 3 hours and 52 minutes.

View 1 Replies View Related

Subtract 6 Hours From Timestamp?

Jun 18, 2011

I have this code to display a date and time from the database. How would I modify it to subtract 6 hours from the timestamp. date('m-d g:Ga', strtotime($row['time_stamp']))

View 2 Replies View Related

Date - Subtract Two Different Times?

May 5, 2011

This seems like a fairly simple question, but I'm having trouble with it!In my database, I have two fields that have times in them. Let's say one field, named clockin, reads "2:29:39 pm," and another field, named clockout, reads "2:29:39 pm."Then I have two other fields, one titled "breakin" which reads 2:28:37 pm and breakout which reads "2:28:55 pm". I want to subtract breakout from break in to get the difference, and then take that number and subtract it from the difference between clockin and clockout.How can I do this? Here's what I've tried:

$clockout = new DateTime($row['clockout']);
$clockin = new DateTime($row['clockin']);

View 2 Replies View Related

Add Or Subtract Values From One Table To Another?

Jul 21, 2010

i have two tables in mysql . i want to add or subtract values from one table from another.

View 1 Replies View Related

Subtract Two Values From The Same Row In One Query.?

Jul 1, 2009

I'm having a hard time here figuring out how to subtract two values from the same row in one query.Here's what I'm trying to do.

$newcredits = $points_me->points - $_POST['amount']-5;
$result = mysql_query("UPDATE `users` SET `points` = '".mysql_real_escape_string($newpoints)."' WHERE `name`='".mysql_real_escape_string($_SESSION['Name'])."'");

As you can see, $_POST['amount'] is value1 and -5 is value2.Now instead of subtracting the -5 with the query, it simply doesn't understand it, and of obvious reasons sets the field to '0'.

View 1 Replies View Related

Subtract 30 Days From A Date?

Nov 17, 2010

I'm pulling a date value (not a datetime value) from a MySQL database, but I need to subtract 30 days from that date, and not sure how to do it. I found a sample using mktime() to break up the day-month-year and subtracting 30 days from the 'day' component, but the result is all wacky.

View 2 Replies View Related

Datetime - Subtract Days?

May 22, 2010

I have a members database. I want to display a "NEW" icon for members that have been members less than let's say 30 daysI use a DateTime field in the database for dates when people sign up.How can I do this?Somethi like

PHP Code:

View 11 Replies View Related

Subtract Calculate Options?

Jul 31, 2010

i have written a piece of code that adds a % of a whole when a button is selected, what i need to do is to have the option of un selecting the button and having the % taken off.
here is the code i have for adding the % and totalPHP Code:


View 5 Replies View Related

Subtract From Character Class?

Dec 3, 2010

Is there a way to subtract characters or a character range from another character class?I need to find a substring within a string, which should only contain characters, but without "<" and ">".

[[:print:]] - ('<' | '>')

Its because "<" and ">" are delimiters and should not occur within the string itself.

<abc> // valid
<ab<c> // invalid
<ab c> //invalid

View 2 Replies View Related

Recursively Subtract From An Array?

Feb 1, 2010

I'm pulling the url parameter and I'm trying to create a breadcrumb out of it. So, if I pull:

$url = 'contact/jane/now';

and I do:

$path = explode("/",$url);

How can I put it in a loop so that it breaks each part into a path like this:

<a href="/contact">contact</a>
<a href="/contact/jane">jane</a>
<a href="/contact/jane/now">now</a>

View 4 Replies View Related

Get A SUM From 2 Diff Queries And Then Subtract Them?

Mar 20, 2010

im trying to get a SUM from 2 diff queries and then subtract them but i dont know how. im trying something like this

PHP Code:

$suma=mysql_query("select SUM(cantidad) as total from 'movimientos' where tipo='0'",$link);


View 4 Replies View Related

Subtract One Day From Yesterday Not Working?

Mar 10, 2009

The following code is part of a script used to scrape data from a page arranged by descending dates. Here is code:

//time variables

View 1 Replies View Related

Subtract First Character Of String?

Aug 28, 2010

I know this is simple, and I've done it before, but I've forgotten how to do it.How might I, in php, subtract the first letter of a string?For example:

FOOBAR would become OOBAR

View 2 Replies View Related

Find A Way To Subtract Two Times

May 27, 2007

I have spent the last few hours trying to find a way to subtract two times, none of the information that I find seems relevant to what I am trying to do...  A technician is filling out a form, posting the time he logs in and the time he logs out, both of which are being recorded in the database.

But prior to the UPDATE, I need to calculate how many hours he was there, e.g. 2.5 hours, then post that also ... then multiply that times his hourly rate and post that also, which ultimately gives the times he was there, how many hours total and how much his time cost against the work, but I think I'm getting off subject... here's my latest failure for code. Code:

View 6 Replies View Related