[SOLVED] Update Table

[ririe44]

I need some direction...

 

This is what I have...

$budg_internet = $_POST['Internet'];
$budg_electricity = $_POST['Electricity'];
$budg_gas = $_POST['Gas'];

$query = ("UPDATE `$tbl_name` SET `amount` = 
CASE
	WHEN `sub_category` = 'Internet'
	THEN '$budg_internet'

	WHEN `sub_category` = 'Electricity'
	THEN '$budg_electricity'

	WHEN `sub_category` = 'Gas'
	THEN '$budg_gas'

	ELSE `amount`
END");

 

The above code updates my table just fine... it's just not very dynamic... 

 

I have a php form which is sending like 50 values to this table update file.  How can I make it dynamic so if I ever change my categories, I don't have to go through and track them down everywhere? 

[ignace]

Interesting, finally a challenge You have another problem everytime you submit the form you submit every field wether or not it needs to be updated which means a serious amount of processing I would suggest using jQuery or something and a few plugins so that you can dubbleclick on a value in the table so it turns into an editable textarea and allows you to edit it, the plugin will then submit the new value to a php script and execute a more precise query, like:

 

UPDATE budgets SET amount = $amount WHERE sub_category = 'Internet';

 

Or if you had modifed the value of electricity the query would have been:

 

UPDATE budgets SET amount = $amount WHERE sub_category = 'Electricity';

[ririe44]

What about this....

 

budget.php (partial):

    while($inc = mysql_fetch_array( $income_accounts ))
    {
    Print "<form method='post' action=post_update_account.php>";
    Print "<tr>";
    Print "<td>".$inc['cat'] . "</td> ";
    Print "<td>".$inc['sub'] . "</td> ";
    Print "<td> </td>";
    Print "<td><input type='text' name='".$inc['sub']."_budget' value='".$inc['budget']."'></td>";
    Print "<td>$".$inc['balance'] . "</td>
    Print "<td><input type=submit value=Update</td></tr>";
    Print "</form>
    }

 

post_update_account.php (partial):

$new_value = $_POST['$inc['sub']."_budget"'];

$query = ("UPDATE `$tbl_name` SET `amount` = WHEN `sub` = '$inc['sub']' THEN '$new_value'");

if (mysql_query($query)) {
    echo "Your budget has been updated!";
}else {
    die(mysql_error());
}

 

I changed it so that every row of my table is a different form with an individual submit button...

 

[ignace]

Yeah that will work. Another solution would be using an object oriented approach:

 

<?php

class Internet extends Category {
    protected $_postKey = 'internet';
    protected $_categoryName = 'Internet';
    ..
}

$budget = new Budget();
$budget->addCategory(new Internet());

/**
* A budget has categories when the form is send it loops over the categories and looks
* if one exists in the provided array ($_POST) if it does it uses that value to update the budgets table
*/
if (sizeof($_POST)) {
    $budget->updateTable($_POST);
}

.. updateTable($array) {
    foreach ($this->_categories as $category) {
        if (isset($array[$category->getPostKey()]) && !empty($array[$category->getPostKey()])) {
            .. UPDATE budgets SET amount = $array[$category->getPostKey()] WHERE sub_category = $category->getCategoryName()
?>

 

Personally i would recommend your code if it works best for you. My code is based upon the Zend Framework Zend_Form and Zend_Db packages where each form field is represented by a class with some proper modification you can make it work in such a way that when you want to add a category all that is needed is a new class and 1 line of code

[ririe44]

Does anybody see any errors with my post_update_account.php code? 

 

am I getting a variable correctly from a posted variable?

[Ken2k7]

$query = ("UPDATE `$tbl_name` SET `amount` = WHEN `sub` = '$inc['sub']' THEN '$new_value'");

Is that a valid SQL?  o.O

[ririe44]

Whoops...

 

Is this better?

$query = ("UPDATE `$tbl_name` SET `amount` = '$new_value' WHERE `sub` = '$inc['sub']'");

 

What about this part... does this work?

$new_value = $_POST['$inc['sub']."_budget"'];

 

[Ken2k7]

I don't know if it works, but for syntax it would be:

$new_value = $_POST['$inc["sub"]'.'_budget'];

[ririe44]

You see what I'm trying to do though, right?  How can I go about that?  (pulling a variable from the last file)

[Ken2k7]

Well, $inc is defined only in the while loop in budget.php, so you can't access it like that in the post_update_account.php file. Problem is that the loop will end up printing out multiple forms and the input with the same name.

 

But since each form is independent (I think), set the name of the field to "budget" rather than $inc['sub']_budget. Then use $_POST['budget'].

[ririe44]

Yeah... great!

 

Now... I need to pick your brain about this same problem (as a result of the last solution)... $inc['sub'] is also coming from the while loop... any thoughts?  How can I identify that variable?

 

$query = ("UPDATE `$tbl_name` SET `amount` = '$new_value' WHERE `sub` = '$inc['sub']'");

[Ken2k7]

    while($inc = mysql_fetch_array( $income_accounts ))
    {
    Print "<form method='post' action=post_update_account.php>";
    Print "<tr>";
    print '<input type="hidden" name="sub" value="' . $inc['sub'] . '" />';
    Print "<td>".$inc['cat'] . "</td> ";
    Print "<td>".$inc['sub'] . "</td> ";
    Print "<td> </td>";
    Print "<td><input type='text' name='".$inc['sub']."_budget' value='".$inc['budget']."'></td>";
    Print "<td>$".$inc['balance'] . "</td>
    Print "<td><input type=submit value=Update</td></tr>";
    Print "</form>
    }

 

$sub = $_POST['sub'];
$query = 'UPDATE `$tbl_name` SET `amount` = "' . $new_value . '" WHERE `sub` = "' . $sub . '"';

[ririe44]

Ahhh HA HA HA!  It's alive! 

 

Thanks everyone!

[ririe44]

Can I update 2 table cells with the same update?

 

for example (which isn't working for me):

$query = ("UPDATE `$tbl_name` SET `budget` = '$new_budget' AND `priority` = '$new_priority' WHERE `sub` = '$for_category'");

 

What do I need to change?

[Ken2k7]

$query = "UPDATE `$tbl_name` SET `budget` = '$new_budget', `priority` = '$new_priority' WHERE `sub` = '$for_category'";

 

Use a comma. Also, why do you wrap everything in parentheses?

[ririe44]

I don't know... I'm no programmer, I just learn as I go...  (i'm sure you could tell)

 

...now I know I don't have too!  Thanks!