Adding stock to a database

go

goingtocry 15 Aug, 2012

Hi Bob

I really need help with this. I have a form and chronoconnection for a very simple Stock control database. I have everything ready, however I just cannot seem to add or subtract stock for each item.

This is my code I have used with php only and modified it for Chronoforms.

    Number: <input type="text" name="number">
    <input type="submit" name="add" value="+"><input type="submit" name="subtract" value="-">


    <?php
    $cf_id = JRequest::getInt('cf_id', '', 'get');
    if(isset($_POST['add'])){
    $add=$_POST['number'];
    $query="update epr1i_stockit_item_entry set stock=stock+$add where cf_id=$cf_id";
    mysql_query($query) or die("Cannot update");
    }


    if(isset($_POST['subtract'])){
    $subtract=$_POST['number'];
    $query="update epr1i_stockit_item_entry set stock=stock-$subtract where cf_id=$cf_id";
    mysql_query($query) or die("Cannot subtract");
    }
    ?>

Iam certain it lies with the cf_id area, and it is driving me nuts!!!

This is my database...
cf_id
cf_uid
cf_created
cf_modified
cf_ipaddress
cf_user_id
cat
loc
item
unt_cst
opn_stk
unit
pkge
ans
number
stock

I plan to compare what has been added or subtracted from the form and compare with the Opening Stock later.

Please can you assist as I will be resorting to narcotics shortly.

Regards

Gr

GreyHead 15 Aug, 2012

Hi goingtocry ,

You are using 'get' to access the cf_id but 'post' for the 'add'/'subtract'. I suspect that you may also need 'post' for the cf_id.

Bob

go

goingtocry 15 Aug, 2012

Hi Bob

Thanks for the reply... Honestly, I pulled this script from where the sun don't set. Is there a way to correct this script?

Correct, the + - Function will add or delete stock, it just does not co-operate.

The Get function for cf_id was copy pasted from one of your other posts, hoped it would work but it didn't.

PPPPLLLLEEEEEEAAAAASSSSEEEE !!!!!!

Max_admin 17 Aug, 2012

Hi,

Why are you using a custom SQL ? a db save or connectivity itself should be fine.

Set the new value before the "DB Save" is executed, and do a new stock query before that, grab the old value then update it:


<?php
$add = 55;
if(isset($form->data['add'])){
$form->data['stock'] = $form->data['stock'] + $add;
}
?>

The code above should be used in a Custom code action before the save.

You just need to test how the core actions provide the results then you will be able to do it🙂

Regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

go

goingtocry 17 Aug, 2012

Hey Max

Thanks for the reply...

Just short of pulling what remains of my hair out...

This part is driving me crazy...

The supposed idea was to enter lets say 100 stock items in the "number" field and you can Add or subtract the stock with the input buttons + and -.

When I put the code in index.php it works, but in chrono it it giving me issues as it will not update the "stock" field in the database. I will however update the "number" field, but the idea is to either add or subtract from the stock field.

I suspect there is something I am doing wrong with the cf_id part. For instance I use this same scrpit below in a normal php file it writes, adds and subtracts perfectly.

    <html>
    <body>
    <form name="form" method="post">
    Number: <input type="text" name="number">
    <input type="submit" name="add" value="+"><input type="submit" name="subtract" value="-">
    </form>
    </body>
    </html>
    <?php
    $conn=mysql_connect("localhost","MY_ACCOUNT","MY_PASSWORD");
    mysql_select_db("MY_DATABASE");

    if(isset($_POST['add'])){
    $add=$_POST['number'];
    $query="update epr1i_stockit_item_entry set stock=stock+$add where cf_id=41";
    mysql_query($query) or die("Cannot update");
    }
    if(isset($_POST['subtract'])){
    $subtract=$_POST['number'];
    $query="update epr1i_stockit_item_entry set stock=stock-$subtract where cf_id=41";
    mysql_query($query) or die("Cannot subtract");
    }
    ?>

If the above code is used like I said in an index.php it works... Please tell me what stupid mistake I have made...

Thanks

Gr

GreyHead 17 Aug, 2012

Hi goingtocry,

Your code does the calculation in the MySQL query; Max does it in PHP and saves the amended value. Either will work in the right circumstances. Yours is probably safer if there are, or could be, frequent updates as the total stock might have changes since the page loaded.

I still can't tell from here how you are passing the value of cf_id??

I'd write your code like this

<?php
$db =& JFactory::getDBO();
$cf_id = $form->data['cf_id'];
$sign = ' - ';
if ( $form->data['add'] ) {
  $sign = ' + ';
}
$add = (int) $form->data['number'];
$query = "
  UPDATE `#__stockit_item_entry`
    SET `stock` = `stock` {$sign} {$add}
    WHERE `cf_id` = {$cf_id};
";
$db->setQuery($query);
$db->query();
?>

Bob

go

goingtocry 18 Aug, 2012

Hi guys...

Cool, I copy pasted the code, I was happy to see that it was doing something, however it is only subtracting. Even if I use the + it still subtracts.

Gr

GreyHead 18 Aug, 2012

Hi going to cry,

Please try changing this line

if ( $form->data['add'] ) {

to

if ( isset($form->data['add']) && $form->data['add'] == '+' ) {

If that doesn't fix it please drag a Debugger action into the On Submit event, then submit the form and post the debug results here.

Bob

go

goingtocry 12 Sep, 2012

Hi Bob

I really thank you for your assistance and always helping people out here...

I twiddled around and got it to work without the +/- button and just use a -10 or +10 to add stock...

Thanks again.