PHP MySQL adding to existing value in database

Question

I am a little bit stuck with a PHP script.

The script is for a radio station and it needs to do the following:

Count how many times a radio presenter has booked a slot for the current day. The slots are stored in a table in a database and I would use the code date("l") to determine the current day and then search for all rows which have "day" set to whatever the value of date("l") is.
Then I would need to find out how many hours the presenter already has stored in the "monthly_slots" column in the "users" table in the row which has their username. "SELECT monthly_slots FROM users"
Then I'd need to add whatever the value of the amount of slots the presenter has completed today with the amount of slots which already exist in the monthly_slots column.

Below is my code for this

$currentday = date("l");
$currentslots = mysql_query("SELECT monthly_slots FROM users");
mysql_query("
    UPDATE users
    SET slots = (SELECT COUNT(*) + "$currentslots" 
                 FROM timetable 
                 WHERE timetable.username = users.username 
                   AND day = "$currentday"
                )
");

Can anyone see where I am going wrong?

Thanks

why don't you add the "$currentslots" outside the query i.e. first count the exiting one's and then add 1 to it. Also, the correct format would be ".$currentslots." — abhig10
– abhig10, Commented May 6, 2012 at 13:32
or, since you're using double quotes, the format would be: "normal text $variable more normal text" — Nadir Sampaoli
– Nadir Sampaoli, Commented May 6, 2012 at 13:32
also, $currentslots is a resource variable, you would have to get the number of rows first. — abhig10
– abhig10, Commented May 6, 2012 at 13:33

Logan · Accepted Answer · 2012-05-06 13:48:34Z

1

get the value returned by the resource

$currentday = date("l");
$result = mysql_query("SELECT monthly_slots FROM users");
$currentslots = mysql_fetch_assoc($result);


mysql_query("
    UPDATE users
    SET slots = (SELECT COUNT(*) + "$currentslots" 
                 FROM timetable 
                 WHERE timetable.username = users.username 
                   AND day = "$currentday"
                )
");

edited May 6, 2012 at 13:48

Logan

612 silver badges9 bronze badges

answered May 6, 2012 at 13:28

Gpak

3,3902 gold badges23 silver badges20 bronze badges

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

1 Comment

Logan Over a year ago

At the moment when I run the code it replaces the values in monthly_slots with how many slots the presenter has booked for that day. It doesn't add the existing amount to that

Del Pedro · Accepted Answer · 2012-05-06 13:15:26Z

0

$currentslots is a mysql resource not a number, use mysql_num_rows($currentslots) instead

answered May 6, 2012 at 13:15

Del Pedro

1,21212 silver badges32 bronze badges

2 Comments

Del Pedro Over a year ago

$currentslotsResult = mysql_query("SELECT monthly_slots FROM users"); $currentslots = mysql_num_rows($currentslotsResult);

Logan Over a year ago

That causes the monthly_slots to be updated with the amount of rows that are in the users table

abhig10 · Accepted Answer · 2012-05-06 16:47:28Z

// Current date

$currentday = date("l");

// Find the number of hours in the users table

$exec_monthlyslots = mysql_query("SELECT monthly_slots FROM users");
$currentslots=mysql_fetch_assoc($exec_monthlyslots);

// instead of counting all columns, use the column id you want to count COUNT(*)

$new_query="UPDATE users
            SET slots = (
                            (
                                SELECT COUNT(*)
                                FROM timetable 
                                WHERE timetable.username = users.username 
                                AND day = ".$currentday."
                            )
                            + ".$currentslots['monthly_slots']."
                        )
            ";

mysql_query($new_query);

This should help you out. Tell me if it doesn't.

Collectives™ on Stack Overflow

PHP MySQL adding to existing value in database

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related