Get value and increment by one for each user

Question

I have this script as a cron job set to run every 5 mins, the problem is it will not increment the energy level as I believe it should. If anyone can tell me what I am doing wrong here it would be great. Remember, this returns no errors, just doesn't work, also the database connect info is all correct, just left out of the post.

{

    $energy = mysql_query("SELECT energy FROM members WHERE  id=$id");
    //get current users energy level
    $energy_max = mysql_query("SELECT energy-Max FROM members WHERE  id=$id");
    //get current users Maximum energy level

        if ($energy < $energy_max)
        // do -if current energy level is less than maximum
            {
                $energy = $energy ++;
                //increment energy by 1
                mysql_query("UPDATE members SET energy= $energy");
                //set the new energy level
            }
                $id++;
                //increment to the next user
}

@njk you don't need to post that all on every post that mentions sql! How does help in answering the question? — Popnoodles, Feb 15 '13 at 00:30
@popnoodles It helps to get rid of `ext/mysql`. The past showed us, that there is no other way than simply reminding everyone over and over again ... — KingCrunch, Feb 15 '13 at 00:33
@njk Umm perhaps *you* should read about how to answer questions. Using a different set of mysql commands has nothing to do with the SQL written. — Popnoodles, Feb 15 '13 at 00:34
@popnoodles I'm not answering the question. I think you may be confused. — Kermit, Feb 15 '13 at 00:37
@njk Nor are you contributing anything useful towards one. http://meta.stackexchange.com/questions/19756/how-do-comments-work/19757#19757 — Popnoodles, Feb 15 '13 at 00:38
@popnoodles So you're saying that OP should continue using `mysql_` functions? — Kermit, Feb 15 '13 at 00:39
@njk No I'm saying posting it on every question that you see that uses mysql_ doesn't contribute towards an answer and doesn't help OP find an answer. Unless of course the question is "should I stop using mysql?" — Popnoodles, Feb 15 '13 at 00:40
@popnoodles Encouraging best practices is a part of answering the question. If you don't feel so, you're free to flag my comments, ignore them or pout about them. — Kermit, Feb 15 '13 at 00:42
@njk Best practices yes but what you're posting is very loosely in context. What you're saying is "hey, I know you've asked about that but instead of fixing it refactor your code first." Is that best practice? — Popnoodles, Feb 15 '13 at 00:43
@popnoodles You're right. I will also add a warning label to inform the OP that this is simply a best practice suggestion and their code will most likely not work in the future, but feel free to use it. Would you like me to add a link to your profile for credit? — Kermit, Feb 15 '13 at 00:46
@njk yes I'd love you to. thanks. I'll look out for it. I'm sure it'd go a long way towards answering or clarifying the question just like the original statement about mysql_ does. In fact why don't you link to a question with an answer like this which does make that statement, after providing an answer. http://stackoverflow.com/questions/14886592/php-echo-is-not-working — Popnoodles, Feb 15 '13 at 00:48
Thank you both for the prompt responses, @popnoodles, you are correct, that other post does explain a lot better as to why not to use it, njk, thanks for bringing that to my attention, I was not aware of it, but maybe next time try to give an answer with that cookie cutter response. Thanks again all. — Walt, Feb 19 '13 at 17:08

score 3 · Answer 1 · answered Feb 15 '13 at 00:29

3

You can solve everything within a single statement

UPDATE `members` SET `energy` = `energy` + 1 WHERE `energy-Max` > `energy`;

This way you have one instead of 3 * number-of-members queries

answered Feb 15 '13 at 00:29

KingCrunch

128,817
21
151
173

@king, how could I get this to work if I only want it to update when a value is equal to a certain number? I have tried querying the database and doing a If statement, but it does not update. – Walt Feb 19 '13 at 07:14
@Walt Your description is to vague to make an assumption, what you are talking about. "When a value is equal to a certain number" covers nearly 80% of all programming tasks – KingCrunch Feb 19 '13 at 07:59
@king, sorry about that but it was late and i was fried. What I mean is I am trying to add a clause to the end basically (" UPDATE 'members' SET 'energy' = 'energy" + 1 WHERE 'energy_max' > 'energy' && 'subbed' == 0 ") – Walt Feb 19 '13 at 13:31
this seems right, and I can run this script right from my browser with no errors, but It is not updating the energy (row i think it is called) in the database. – Walt Feb 19 '13 at 14:02
You can always test the `WHERE`-clause with a regular `SELECT`-statement. – KingCrunch Feb 19 '13 at 14:24
@king - can you give an example? I am sorry to be such a noob, but I am just learning this and do not understand what you mean. – Walt Feb 19 '13 at 16:17
I have also tried using AND instead of the &&, still no luck, I just dont understand what is wrong with that line. – Walt Feb 19 '13 at 16:48
Just in case anyone else has this problem in the future I finally figured out the issue. the actual line should look like this: – Walt Feb 22 '13 at 05:08

PiotrK · Answer 2 · 2013-02-15T00:36:51.983

0

mysql_query doesn't return values, only so-called query id. You have to fetch actual data:

$query = mysql_query("SELECT energy FROM members WHERE  id=$id");
$val = mysql_fetch_assoc($query); // Now the data is in $val['energy']

Of course you can do this quicker, via this (as pointed in other answer):

UPDATE members SET energy = energy + 1 WHERE energy < energy-Max;

edited Feb 15 '13 at 00:36

answered Feb 15 '13 at 00:30

PiotrK

4,210
6
45
65

1

I am not completely sure, but I fear `energy-Max` is a column name :X – KingCrunch Feb 15 '13 at 00:34
1

Please don't use `mysql_` functions. – Kermit Feb 15 '13 at 00:47
@king, yes it is, but it was a typo when typing it in here, i is actually energy_max – Walt Feb 19 '13 at 17:33

score 0 · Answer 3 · answered Feb 15 '13 at 00:30

0

You forgot the WHERE id = $id in the update Statement.

 mysql_query("UPDATE members SET energy= $energy WHERE id = $id");

But KingCrunch's answer is better ..

answered Feb 15 '13 at 00:30

take

2,202
2
19
36

Get value and increment by one for each user

3 Answers3