Increment value based on condition

[I-AM-OBODO]

Hi all.

In my database, i have a table where a client chose a service providers to make payments and each bill setup counts as a reward point for the client. Each bill counts as a single point even if the bill is edited after a services might have expired or renewed it should not be counted again as point.

 

to count the number of bill set up by a client i did

 

$stmt = $pdo->prepare("UPDATE reward_points SET num_bill = num_bill + 1 WHERE username = '$username' AND acct_num = '$acct_num'");
$stmt->execute();

 

Its okay if it counts for new bills but i want it not to count for the same service provide and so i did

 

 

$stmt=$pdo->query("SELECT company FROM payment WHERE username = '$username' AND trans_ref = '$trans_ref'");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$coy = $row['company'];
}
//ps: note, $company is a variable from the form
if(strcmp($coy, $company) == 0){

$stmt = $pdo->prepare("UPDATE reward_points SET num_bill = num_bill + 0 WHERE username = '$username' AND acct_num = '$acct_num'");
$stmt->execute();
}elseif(strcmp($coy, $company) != 0){
    
$stmt = $pdo->prepare("UPDATE reward_points SET num_bill = num_bill + 1 WHERE username = '$username' AND acct_num = '$acct_num'");
$stmt->execute();    
}

 

it just wont add up

 

i also tried

 

 

$coy1 = $coy;
$coy2 = $company;
$point = 0;

if(strcmp($coy1, $coy2) == 0){
   
    $add_point = $point + 0;
}else{
   
    $add_point = $point + 1;
}

$stmt = $pdo->prepare("UPDATE reward_points SET num_bill = $add_point WHERE username = '$username' AND acct_num = '$acct_num'");
$stmt->execute();
 

 

still wont work. any idea?

 

[IlaminiAyebatonyeDagogo]

try echoing out the value of $add_point then kill the script. If you get your required result then check your pdo query over again

 

if it dosen't then try declearing your company variable in this same file i think it is returning empty ie the company variable

 

also redeclear the company variable here in this script i think the conditional statement is runing into an empty condition ie the company variable

Edited December 7, 2014 by Ch0cu3r

[mac_gyver]

the correct way of doing this is to INSERT a new row in your reward_points table for each new provider that a client picks. you would also have a unique composite index defined for the user (you should be storing the user id, not the user name) and the provider id. this will enforce uniqueness (i.e. no duplicate user/provider combinations.) to get a count of providers that a client has picked (i.e. the number of reward points), you simply get a COUNT() of the number of rows for any client.

[LeJack]

Since you're doing prepares, you should definitely invest in place holders because it separates SQL from code or you would have to escape all of your client inputs which wastes more lines on your document.

[I-AM-OBODO]

the correct way of doing this is to INSERT a new row in your reward_points table for each new provider that a client picks. you would also have a unique composite index defined for the user (you should be storing the user id, not the user name) and the provider id. this will enforce uniqueness (i.e. no duplicate user/provider combinations.) to get a count of providers that a client has picked (i.e. the number of reward points), you simply get a COUNT() of the number of rows for any client.

I have a unique id and a transaction reference which is also unique and auto generated. All is well on my database. all I want is to increment setup bill + one if It's a new provider and nothing if its the provider on the bill. It's sort of:

 

if provider is new, rewards = + 1 else rewards =nothing or ignore.

[I-AM-OBODO]

Since you're doing prepares, you should definitely invest in place holders because it separates SQL from code or you would have to escape all of your client inputs which wastes more lines on your document.

thanks and noted

[CroNiX]

Well this isn't going to work:

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $coy = $row['company'];
}
if(strcmp($coy, $company) == 0){

$coy will always = the very last entry of the result since you are overwriting the value $coy on each loop, so you are only comparing one item here instead of all of them from the result.

 

You'd probably want to be making $coy an array and putting the values in it, and then checking to see if the new value is in_array() (not strcmp()).

 

There is also no use in adding 0 to a column. That doesn't change a value so why do it? It just takes up unnecessary resources. Only increment the column by one if the provider is new (doesn't exist in the array)

while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
  //put existing values into an array
  $coy[] = $row['company'];
}

//Check to see if this is a new entry (so it wouldn't be in the array if it was new)
if ( ! in_array($company, $coy))
{
  //run query to increment counter by 1
}

Edited December 8, 2014 by CroNiX

[I-AM-OBODO]

thanks all. I managed to get it working, mightn't be the best of approaches but it solved my problem.

I'm replying via my mobile so sending the code on how I did it will be later.

 

but basically, I created another table for it with the columns I need and did an insert if the table is empty and a while to check if it is not. I assigned a value of 0 to point if the variable exists and 1 if not.

 

thanks all. topic should be marked solved for best answer, emmm. I guess all are useful

[mac_gyver]

did an insert if the table is empty and a while to check if it is not

 

 

if you set up the unique composite index, the way i suggested, you don't need to query for anything or loop over anything. you just insert the data. if the client/provider pair doesn't exist, it gets inserted. if it already is in the table, it doesn't get inserted and produces a duplicate index error that you can choose to ignore in your error checking logic.

[I-AM-OBODO]

if you set up the unique composite index, the way i suggested, you don't need to query for anything or loop over anything. you just insert the data. if the client/provider pair doesn't exist, it gets inserted. if it already is in the table, it doesn't get inserted and produces a duplicate index error that you can choose to ignore in your error checking logic.

 

I f you could represent what you saying with example codes, it'll be better

 

[mac_gyver]

your table holding this information would have a unique composite index (key) for the user_id and provider_id. in addition to the normal table definition, you would have/add this - 

  UNIQUE KEY `some_key_name` (`user_id`,`provider_id`)

at the point where you know the user_id and the provider_id in your php code, you would simply run a query to insert that data - 

INSERT INTO your_table (user_id, provider_id) VALUES ($user_id, $provider_id)

assuming you have error checking logic in your code, that's testing if the query returned an error, an error value from that query of 1062 (a duplicate value for the unique index/key), would mean the combined value of user_id/provider_id was already in the table and it wasn't inserted. you can safely ignore this error. any other error value would mean the query failed due to an error that you should find and fix the cause of. if there isn't an error, it means that the combined value wasn't already in the table, but it was inserted by the query.