Page 1 of 1

Code help request

Posted: Sat Dec 11, 2010 12:52 pm
by arclitestudio
Hello,

Let me start with stating I am NOT a PHP programmer but have used snippets and code when needed and found through the internet to get functionality i need in the past. I am SLOWLY trying to learn PHP5 mainly on a trial and error bases. That having been said, the code below doesn't work, I'm of the thought that I have to use a SELECT query first and also that my syntax is wrong. If someone can look simply at the query and advise wher it might be going wrong at I would appreciate it. ( and yes in advance the page does have the requisit <?php and matching close tags etc. etc. etc., but the remainder of the code is just some echoed HTML code)

Code: Select all

require("connect.php");
mysql_connect($hostname,$username, $password) or die ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname) or die('Unable to select database');
$x = $_POST[Payamt];
$id = $_POST[Payidnumber];
@mysql_query("UPDATE addcon SET Balance = (Balance - '$x') WHERE ID_Number = '$id' AND Balance => '$x'");

Thanks,

Robert

Re: Code help request

Posted: Sat Dec 11, 2010 2:18 pm
by jaceinla
You said you don't know much about PHP, so forgive me if I'm assuming too much :)...

You need to set up your database with a primary auto-incrementing ID rather than creating your own ID's. Also, you only want to update ONE record, not several, as your code is currently doing by asking for every field where the balance is greater than payment.

This code I provided is using a single auto-incrementing ID, and a balance...so two fields in the table

Code: Select all

<?php

$dbc = mysqli_connect('localhost','root','root','test');

$id = 1;
$payment = 50;

$query = 'UPDATE balance SET balance = balance - ' . $payment . ' WHERE id=' . $id . '';  //balance = 100

mysqli_query($dbc,$query);  //balance = 50

?>


Re: Code help request

Posted: Sat Dec 11, 2010 3:31 pm
by arclitestudio
Jaceinla,

Thanks for the quick reply, I was able to get the code to work with your help and will post the full code below, however i want to answer your question first. Because we have an existing group of over 300 people with assigned 4 digit id numbers that are tracked on paper only at this time a auto-increment won't work because they want to be able to add the people into the online system as needed instead building an entire pre-filled database.

Also, how do I build or do a comparison between the database results and a fixed value of 0 before allowing the Balance field to be updated? Specifically, I want to be sure that the existing value in Balance field in the database is = or > the $Payment field obtained from the $_POST[Payidnumber]; variable?

Code: Select all

<?php
//Connect To Database
require("connect.php");
$dbc = mysqli_connect($hostname,$username,$password,$dbname);
$id = $_POST[Payidnumber];
$payment = $_POST[Payamt];
$query = 'UPDATE addcon SET Balance = Balance - ' . $payment . ' WHERE ID_Number=' . $id . '';  //balance = 100
mysqli_query($dbc,$query);  //balance = 50
mysqli_close;
?>

Re: Code help request

Posted: Sat Dec 11, 2010 4:31 pm
by Jonah Bron
#1: change this...

Code: Select all

$id = $_POST[Payidnumber];
$payment = $_POST[Payamt];
...to this:

Code: Select all

$id = intval($_POST['Payidnumber']);
$payment = floatval($_POST['Payamt']);
#2: I don't quite understand your question... do you mean like this?

Code: Select all

$query = 'UPDATE addcon SET Balance = Balance - ' . $payment . ' WHERE ID_Number=' . $id . ' AND Balance >= ' . $payment;

Re: Code help request

Posted: Mon Dec 13, 2010 6:47 pm
by jaceinla
Also, how do I build or do a comparison between the database results and a fixed value of 0 before allowing the Balance field to be updated? Specifically, I want to be sure that the existing value in Balance field in the database is = or > the $Payment field obtained from the $_POST[Payidnumber]; variable?
You can do multiple queries such as

1st time have your query grab all the needed info out, if (statement) it passes, then insert it into the DB...this could be a pain if you're unsure as far as DB resources go.