MYSQL need help pulling data from each "user" using php

Question

Here's what I have. This code grabs the data from all of the users and subtracts 1 from user_days then updates every user's user_days row.

$result = mysqli_query($con,"SELECT * FROM users");

while($row = mysqli_fetch_array($result))
  {
  $minusone = $row['user_days']-1;
  mysqli_query($con,"UPDATE users SET user_days=$minusone");
  echo "<br />";
  echo $row['user_days'];
  }

The problem I'm having is this: Instead of subtracting 1 from each user and updating each users field, it's updating each user's field with the value from the first user.

example: before updating user 1 has 30 days user 2 has 60 days

after updating user 1 has 29 days user 2 has 29 days (instead of 59 days)

Any help is appreciated and I hope this question is easy to understand.

Just to clarify, I DO want to update every field. I just don't want the updates to be duplicated from the first result.

Thanks for all of the answers, this has given me a lot of help.

your SQL query updates all records. you should specify some condition into it, for example: update users set user_days=$minusone where id= . $row[id] or whatever. Moreover, you do not need to do that in the loop. Try something like update users set user_days=user_days-1; standalone. — varnie
– varnie, Commented Mar 11, 2013 at 14:58

Vasyl Zhuk · Accepted Answer · 2013-03-11 15:00:01Z

3

Why don't you just run UPDATE users SET user_days = user_days-1 WHERE id=XXXXX? And then select the whole thing?

answered Mar 11, 2013 at 15:00

Vasyl Zhuk

1061 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Baker Over a year ago

If it's being done for all users, why execute n times for each individual user, and not simply run it once for all users?

Marc Audet · Accepted Answer · 2013-03-11 14:58:20Z

0

When you update your record, you need to specify the user id for the record of interest, otherwise you current query updates all the rows in your table.

answered Mar 11, 2013 at 14:58

Marc Audet

47k11 gold badges68 silver badges86 bronze badges

1 Comment

Mark Baker Over a year ago

Or even simpler, use UPDATE users SET user_days=user_days-1 before doing your select

Voitcus · Accepted Answer · 2013-03-11 14:59:32Z

0

You should point which record to UPDATE

mysqli_query($con,"UPDATE users SET user_days=$minusone WHERE id=XXXXX");

answered Mar 11, 2013 at 14:59

Voitcus

4,4564 gold badges27 silver badges40 bronze badges

Comments

Michael L. · Accepted Answer · 2013-03-11 14:59:48Z

0

The problem is with the UPDATE statement. Without a WHERE clause it will apply the SET clause to every row in the database. Assuming you have a unique id column named id in the users table, you could modify your code like this:

while($row = mysqli_fetch_array($result))
{
  $minusone = $row['user_days']-1;
  $user_id = $row['id'];
  mysqli_query($con,"UPDATE users SET user_days=$minusone WHERE id=$user_id");
  echo "<br />";
  echo $row['user_days'];
}

answered Mar 11, 2013 at 14:59

Michael L.

6183 silver badges17 bronze badges

Comments

stixx · Accepted Answer · 2013-03-11 15:06:11Z

0

Use the following Query:

mysqli_query($con,"UPDATE users SET user_days=$minusone WHERE user_ID = '".$row['user_ID']."'");

edited Mar 11, 2013 at 15:06

answered Mar 11, 2013 at 15:00

stixx

1421 silver badge8 bronze badges

1 Comment

Charles Harris Over a year ago

PHP Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

emco · Accepted Answer · 2013-03-11 15:08:09Z

0

You're updating all the users with the same $minusone value. You need a WHERE clause in your update statement, like this:

$result = mysqli_query($con,"SELECT * FROM users");

while($row = mysqli_fetch_array($result))
  {
  $minusone = $row['user_days']-1;
  $id_user = $row['id_user'];
  mysqli_query($con,"UPDATE users SET user_days=$minusone WHERE id_user = $id_user");
  echo "<br />";
  echo $row['user_days'];
  }

Another way of doing what you want would be:

...
$result = mysqli_query($con,"UPDATE users SET user_days = user_days - 1");
...

This assuming you want to substracts 1 to all user_days.

edited Mar 11, 2013 at 15:08

answered Mar 11, 2013 at 15:01

emco

4,7493 gold badges21 silver badges20 bronze badges

Comments

AndrewR · Accepted Answer · 2013-03-11 15:10:03Z

As already pointed out by other answers, the best way to do this is to replace your entire code block with one line.

mysqli_query($con, "UPDATE users SET user_days=user_days-1");

Then you can SELECT and display the information as needed.

Without knowing exactly what you are using user_days for, I'm thinking there may be a better approach to what you are trying to do. I am assuming this is some kind of subscription service, and this code will be run once per day to decrement the number of days to allow them to access the service.

A better approach would be to have a subscriptionExpires field in your database, which would hold a datetime value. Using your approach, if the job that runs this fails, every user will get an extra day. What if a web spider or a user finds your script, your users accounts will expire early. If you use an actual date for when the account expires, there's no guessing if the current value is correct.

Collectives™ on Stack Overflow

MYSQL need help pulling data from each "user" using php

7 Answers 7

1 Comment

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related