FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Subtrat values to a mysql table script





mikelll
Hello everybody,

I have one table in MySql that saves "points" of some users. It is a component table that registers points of my users. What I want to do is to create a SQL query that I run everyday (if possible by cron job) that removes one point to each user everyday, keeping the remaining points.

Can anyone help me?

thanks
cemycc
Quote:
UPDATE points_table SET points = points - 1;


or with php :
Quote:
$q = mysql_query("SELECT points, username FROM points_table");
while($row = mysql_fetch_array($q)) {
$q2 = mysql_query("UPDATE points_table SET points = points - 1 WHERE username = '"$row['username']"'");
}


and you set the cronjob to execute the script every 24h. Very Happy
mikelll
My table (wich name is: "jos_alpha_userpoints")have this fields:

Code:
id  userid  referreid  upnid  points  max_points  last_update  referraluser  referrees  blocked  birthdate  avatar  levelrank  leveldate  gender  aboutme  website  phonehome  phonemobile  address  zipcode  city  country  education  graduationyear  job  facebook  twitter  icq  aim  yim  msn  skype  gtalk  xfire  profileviews 


can you be so gentile to adapt your code to the "points" field?

thank you very much!
mikelll
Another thing, It applies to all users ate the same time!

thanks
rickylau
Then perform this
Code:
UPDATE `jos_alpha_userpoints` SET `points` = `points` - 1;
Indeed this statement alters all records at the same time, if you need to update specific records according to some criteria, simply add
Code:
WHERE [Conditions go here]
before the end of statement.
mikelll
rickylau wrote:
Then perform this
Code:
UPDATE `jos_alpha_userpoints` SET `points` = `points` + 1;
Indeed this statement alters all records at the same time, if you need to update specific records according to some criteria, simply add
Code:
WHERE [Conditions go here]
before the end of statement.

by example...
I don't want them to be negative...
How do I put the condition to prevent that the points remove stops at "0" points?

thanks
rickylau
mikelll wrote:
by example...
I don't want them to be negative...
How do I put the condition to prevent that the points remove stops at "0" points?

thanks
Ow forgot you want to deduct points instead of increase...Previous post updated.

Since you are deducting 1 point each time, if an user can't have points under 0, he / she should have at least 1 point before updating. The SQL expression of this condition is
Code:
`points` > 0
Assuming `points` is integral type, > 0 is just equivalent to >= 1. However if this is floating-point field, use
Code:
`points` >= 1
instead.

Then, the full SQL statement for your situation will be
Code:
UPDATE `jos_alpha_userpoints` SET `points` = `points` - 1 WHERE `points` >= 1;
mikelll
Thank you very much!!

Now the only thing that I need to learn is to create a php file with a db connection, and this query in it in order to perform a cron job!

I can't make a direct cron job with a query can I?

Thanks
jmraker
You will need change the 'edit me' below. The first 2 are for database authentication, the 3rd is to specify which database to use, so you'll need to know the user/pass for the database and the name of the database.

Code:
<?php

define('DATABASE_USER', 'edit me');
define('DATABASE_PASS', 'edit me');
define('DATABASE', 'edit me');

$link = mysql_connect('localhost', DATABASE_USER, DATABASE_PASS);
if (!$link)
    die('Could not connect: ' . mysql_error());

$db_selected = mysql_select_db(DATABASE, $link);
if (!$db_selected)
    die ('Can\'t select database : ' . mysql_error());

$query = 'UPDATE `jos_alpha_userpoints` SET `points` = `points` - 1 WHERE `points` >= 1';
$result = mysql_query($query);

?>
Nemesis234
its worth adding in security so that only the server can run the cron, otherwise if someone browses to that php file they could wipe out everyones points if so inclined.
mikelll
I had managed to put it to work! Thank you all!

You are wright about the security! Can i protect that folder with htaccess passeword? The cron job do the job as well?
rickylau
For avoiding remote (unauthorized) execution, apart from htpasswd, I think you may

- Check remote IP by $_SERVER ['REMOTE_ADDR'] in the PHP script, since you are running the script locally (supposedly you are), you may get 127.0.0.1 or the server IP (should be same as $_SERVER ['SERVER_ADDR'])
- Put the script out of HTTP document root, so the PHP script can't be accessed from outside by HTTP but from the server itself. In the cron job you can execute something like
Code:
php /path/to/the/script.php


If you cron job with htpasswd (I mean you execute the script by HTTP), you need to provide URL like
Code:
http://[htpasswd_user]:[htpasswd_password]@[url_of_the_script]
Thus the cron job can pass the HTTP authentication.

You may also record the time of last script execution (in any way), and check that record to ensure the script is running only once a day (or matching your designated schedule).
mikelll
Good tips! thanks!

I put the script outside the html public forlder and it works fine!

Thanks once again!
Related topics
E-Cards with PHP & MySQL
HTTP AUTH with PHP and mySQL
mysql table keyword search
Working with Excel, PHP & MySQL. Any Ideas
mysql problems
PHP Script needed
Reading MySQL values in reverse
Export and Import Mysql Table
How to write a wroth script for write and read from database
Free PHP/MySQL login script
cannot add data to mysql from PHP form
transfering mysql values form one table to another with php
Newbie PHP/MySQL question
What to learn first?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.