MySQL Add/subtract Quantities?

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 =
'apple'");

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



ADVERTISEMENT

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.

include("../CMS/script-files/db-connect.php");

$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() . "
$SQL
");}

mysql_close($cid);

View 4 Replies View Related

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
[code].........

View 2 Replies View Related

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

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

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

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

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

Multiple Quantities In Shopping Cart...

I have a Page that Displays products (check out www.lgiengineers.com/WebStore/Store/index.php)

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?

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?

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,
[code]....

View 1 Replies View Related

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

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

[code]...

View 3 Replies View Related

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.

[code]....

View 1 Replies View Related

Regular Expression For Matching Quantities And Unit?

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

1 tbsp
1tbsp
300ml
300 ml
10grams
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?

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:
Ingredients:
[code]....

View 1 Replies View Related

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:

orderID
customerID
total
[code]....

View 3 Replies View Related

SUM And SUBTRACT Using SQL?

I am using MySQL and I have two tables:

master_table
ORDERNO
ITEM
QTY
[code]....

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?

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

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

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?

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

10h:34min
+ 07h:46min
-----------
XX:XX

View 1 Replies View Related

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

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

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

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

Add / Subtract One Second From Date?

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);
or
$now = (date("Y-m-d H:i:s") + :01);

View 2 Replies View Related

Subtract Two Dates From Each Other?

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);
[code]...

View 2 Replies View Related

Subtract Time From A Date

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?

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

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

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?

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?

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']);
[code]....

View 2 Replies View Related

Add Or Subtract Values From One Table To Another?

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

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?

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?

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:
$todaysdate=date('Y-m-dG:i:s');
$subtract_date=$todaysdate-30;
//Iknowthisiswrong,butjustwantedtoshowwhatIwant
[code]...

View 11 Replies View Related

Subtract Calculate Options?

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:

$product=$total*(1-$profit);
$product1=$gametotal*(1-0.20);
$product2=$gametotal*(1-0.30);
$product3=$gametotal*(1-0.50);

View 5 Replies View Related

Subtract From Character Class?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved