Udate Database Fields

[ririe44]

Hey everyone...

 

Okay, I'm putting together a small family budget database.  I have one table called "budget" which has a category (food, transportation, etc) column, sub-category (dining out, auto gas, etc) column, and an amount column.  I've put together a query where it will show me my budget, and I've also included a column with a text field for edit.  I've posted these fields to my update.php file.

 

Now, I know how to add a new row, but I don't know how to modify the contents of a row.  The associated field I have to post with is my sub-category name because it's unique. 

 

So, I want to do something along these lines:  Update/overwrite the amount value associated with this sub-category.

 

Here's my start... how do I fill it in, thanks!

 

$query = "INSERT INTO `$tbl_name` (`amount`) VALUES (????)";

[rhodesa]

UPDATE `$tbl_name` SET `amount` = '123 WHERE `sub-category` = 'abcd'

[ririe44]

So, with specifics, would this be correct?

 

$query = "UPDATE `$tbl_name` SET `amount` = `$budg_mortgage` WHERE `sub-category` = 'Mortgage'";

 

$budg_mortgage = the new amount

[Philip]

$query = "UPDATE `$tbl_name` SET `amount` = `$budg_mortgage` WHERE `sub-category` = 'Mortgage'";

should be:

$query = "UPDATE `$tbl_name` SET `amount` = '$budg_mortgage' WHERE `sub-category` = 'Mortgage'";

 

` are for column names, ' are for values (see change @ $budg_mortgage)

[ririe44]

I'm getting the following error: 

 

Unknown column 'Mortgage' in 'where clause'

 

'Mortgage' isn't a column, it's part of a row... any ideas?

[premiso]

Echo out $query and post it here.

 

Chances are you are either still using ` (back ticks) instead of ' (single quotes) or your data has invalid characters, not being escaped properly.

[ririe44]

Perfect, that did it... thanks!  (My last post was posted before right after the comment from KingPhilip was posted, sorry everyone)

 

 

 

[ririe44]

Now, I'm getting a result that is bothering me:

 

budg_retrieve.php

Print "<form action=budg_update.php>";
Print "<table border cellpadding=3>";
Print "<tr>";
Print "<th>Category</th> <th>Sub-Category</th> <th>Amount</th> <th>Edit</th></tr>";
while($info = mysql_fetch_array( $budg_data ))
{
Print "<tr>";
Print "<td>".$info['category'] . "</td> ";
Print "<td>".$info['sub_category'] . "</td> ";
Print "<td>$".$info['amount'] . "</td>";
Print "<td><input type='text' name='".$info['sub_category']."' value='".$info['amount']."'</td></tr>";
}
Print "<tr>";
Print "<td colspan=4 align=center><input type='submit' name='submit' value='Update'></td>";
Print "</table>"; 
Print "</form>";

 

budg_update.php

$budg_mortgage = $_POST['Mortgage'];

$query = "UPDATE `$tbl_name` SET `amount` = '$budg_mortgage' WHERE `sub_category` = 'Mortgage'";

 

In my field, I'm entering in a value like 15, and it's updating/resulting to 0.  Where's my code wrong here?  Thanks!

[premiso]

You need to post the form your code generated. My but is that "Mortgage" is either not on the form, or it is a different case.

[ririe44]

I did, it's the code called 'budg_retrieve.php', at this line:

 

Print "<td><input type='text' name='".$info['sub_category']."' value='".$info['amount']."'</td></tr>";

 

So, this is pulling the name of the sub_category directly from my database as 'Mortgage', and my text input is named 'Mortgage'.  Then, in the budg_update.php I'm setting the new variable $budg_mortgage as $_POST ['Mortgage'];  which is the same as the database...

 

So, going through the rounds, it doesn't seem like 0.00 should be the value when I'm entering any other value.  I know the code is getting to my 'amount' field of 'Mortgage' because it was a value, then it changed it to 0.00 on me.

[premiso]

No, the code generated from that script, not the script code.

 

View the source of the page with the form on it and paste the generated HTML here.  (But if you checked the values and that is not the issue then do not worry about it).

 

If you are sure the form value is being populated to $budg_mortgage, you may have to cast this to be a double variable:

 

$budg_mortgage = (double) $_POST['Mortgage'];

 

I am also not sure if surrounding that value with single quotes in the query is the right way to go, you may try leaving the single quotes off since this is a numerical value.

[ririe44]

Nope, the (double) didn't work, and neither did removing the single quotes off of the value in the query. 

 

Here's my source, and yes, I had checked it... as you can see, unless I'm not reading it right, 'Mortgage' is correct.

 

<form action=budg_update.php>
<table border cellpadding=3>
<tr>
<th>Category</th> 
<th>Sub-Category</th> 
<th>Amount</th> 
<th>Edit</th>
</tr>
<tr>
<td>Debt</td> 
<td>Mortgage</td> 
<td>$0.00</td>
<td><input type='text' name='Mortgage' value='0.00'</td>
</tr>

[premiso]

Using this form, are you changing that value from 0.00 to something else. From the sounds of it, given the data in the DB it is doing it right. I am not sure what you have/havn't tried. It should work though given what you have shown me.

[ririe44]

No, the value was originally not 0... this set of codes are changing it to 0... I'll go to my database manually, change it back while testing, and it still changes it to 0...    ???

 

Could there be some sort of work around on this one?  Maybe fixing it won't work, but I have to work around it somehow...

[premiso]

No, the value was originally not 0... this set of codes are changing it to 0... I'll go to my database manually, change it back while testing, and it still changes it to 0...    ???

 

Could there be some sort of work around on this one?  Maybe fixing it won't work, but I have to work around it somehow...

 

What data type is your mysql column set to for amount?

[ririe44]

decimal(65,2)

 

(not that I would ever make a transaction that would include 65 characters...  )

[premiso]

I did a quick mock up of your code.

 

<?php
mysql_connect("localhost", "root", "");
mysql_selectdb("test");
if (isset($_POST['submit'])) {
$value = $_POST['Mortgage'];
if (is_numeric($value)) {
	$value = (double)$value;
}else {
	$value = 0;
}

mysql_query("UPDATE mortgage SET amount = " . $value . " WHERE name = 'Mortgage'");
}

$output = '<form method="post" action="test.php">';
$result = mysql_query("SELECT mid, amount, name FROM mortgage");
while ($row = mysql_fetch_array($result)) {
$output .= '<input type="text" name="' . $row['name'] . '" value="' . $row['amount'] . '" />';
}

$output .= '<input type="submit" name="submit" value="Process" />
</form>';

echo $output;
?>

 

This works exactly as expected. Updated the DB to the correct result. Where you are going wrong, I have no clue.  But the above worked great for me. Updated the DB with the correct value each time I ran it.

 

Why yours does not work, either your logic is flawed somewhere, which it sets it to be 0.00, or the value is not being modified via the form and it just keeps posting 0.00.

 

You have only posted part of your code, maybe posting more (or even the full thing) will help diagnose the problem.

[ririe44]

Okay, here's the full code, I didn't want it to get too long on here.  (I've removed my database specifics):

 

budg_retrieve.php

<?
include("checksession.php");

$host="host"; // Host name
$db_username="username"; // Mysql username
$db_password="password"; // Mysql password
$db_name="name"; // Database name
$tbl_name="budget"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$db_username", "$db_password")or die(mysql_error());
mysql_select_db("$db_name")or die(mysql_error());

$budg_data = mysql_query("SELECT * FROM `$tbl_name`") or die(mysql_error());

Print "<form action=budg_update.php>";
Print "<table border cellpadding=3>";
Print "<tr>";
Print "<th>Category</th> <th>Sub-Category</th> <th>Amount</th> <th>Edit</th></tr>";
while($info = mysql_fetch_array( $budg_data ))
{
Print "<tr>";
Print "<td>".$info['category'] . "</td> ";
Print "<td>".$info['sub_category'] . "</td> ";
Print "<td>$".$info['amount'] . "</td>";
Print "<td><input type='text' name='".$info['sub_category']."' value='".$info['amount']."'</td></tr>";
}
Print "<tr>";
Print "<td colspan=4 align=center><input type='submit' name='submit' value='Update'></td>";
Print "</table>"; 
Print "</form>";

?>

 

budg_update.php

<?
$host="host"; // Host name
$db_username="username"; // Mysql username
$db_password="password"; // Mysql password
$db_name="name"; // Database name
$tbl_name="budget"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$db_username", "$db_password")or die(mysql_error());
mysql_select_db("$db_name")or die(mysql_error());

$budg_mortgage = $_POST['Mortgage'];
$budg_hoa = $_POST['HOA'];
$budg_mustang = $_POST['Mustang'];
$budg_vulcan = $_POST['Vulcan'];
$budg_school = $_POST['School'];
$budg_auto_gas = $_POST['Auto Gas'];
$budg_auto_insurance = $_POST['Auto Insurance'];
$budg_auto_maintenance = $_POST['Auto Maintenance'];
$budg_internet = $_POST['Internet'];
$budg_electricity = $_POST['Electricity'];
$budg_gas = $_POST['Gas'];
$budg_cell_phones = $_POST['Cell Phones'];
$budg_home_insurance = $_POST['Home Insurance'];
$budg_home_maintenance = $_POST['Home Maintenance'];
$budg_groceries = $_POST['Groceries'];
$budg_dining_out = $_POST['Dining Out'];
$budg_pets = $_POST['Pets'];
$budg_investments = $_POST['Investments'];
$budg_newspaper = $_POST['Newspaper'];
$budg_medical = $_POST['Medical'];
$budg_diapers = $_POST['Diapers'];
$budg_baby_sitting = $_POST['Baby Sitting'];
$budg_movies = $_POST['Movies'];
$budg_clothing = $_POST['Clothing'];
$budg_gifts = $_POST['Gifts'];
$budg_savings = $_POST['Savings'];
$budg_books = $_POST['Books'];
$budg_crafts = $_POST['Crafts'];
$budg_entertainment = $_POST['Entertainment'];
$budg_vacation = $_POST['Vacation'];
$budg_school = $_POST['School'];

$query = "UPDATE `$tbl_name` SET `amount` = '$budg_mortgage' WHERE `sub_category` = 'Mortgage'";

if (!mysql_query($query));
{
echo "Your budget has been updated!  <br> Would you like to make another modification?  <a href='budg_retrieve.php'>Yes</a>";
}
die(mysql_error());

?>

 

Obviously I have intentions of many more categories than just "Mortgage" as we've been testing thus far.  Let me know what you think... otherwise I may start over and use your posted code as a template...

[premiso]

Honestly it looks fine, as you said, you have tried the (double) cast of $amount?

 

A small change I would recommend:

 

if (!mysql_query($query)) {
    echo "Your budget has been updated!  <br> Would you like to make another modification?  <a href='budg_retrieve.php'>Yes</a>";
}else {
    die(mysql_error());
}

 

That could be causing some issues, given the semicolon after an if.

 

If that fixes it great, if not, than I do not know what to tell you. I cannot re-create this issue, and my code posted is pretty close to what your code is (as far as I can tell).

[ririe44]

Yeah, that didn't fix it... unfortunately.  Well, I'll be back, I'm going to give in another whirl.

 

Thanks for your help, even though we didn't resolve the issue, I picked up a bunch of tips along the way!

[premiso]

Edit to the above:

 

if (mysql_query($query)) {
    echo "Your budget has been updated!  <br> Would you like to make another modification?  <a href='budg_retrieve.php'>Yes</a>";
}else {
    die(mysql_error());
}

 

That may fix it, as you were checking if that did not run display the success message.