add one (+1) to a field in mysql database using php

Question

I have a php script that displays records from a database. It's probably not the best script, as I'm very new to php.

I've added an additional column in my table and would like to keep a count in that column to show me how many times each of the records have been viewed.

Heres the part of the code I think i need to add the code to... if i need to post the entire page i will, but i just figured i could add the line to this part.

//Get the details from previous page
$SelectedCounty = $_POST["result"];

//set variable for next SEARCH
$option = '';

// Get the county names from database - no duplicates - Order A-Z 
$query = "SELECT DISTINCT tradingCounty FROM offers ORDER BY tradingCounty ASC";

// execute the query, $result will hold all of the Counties in an array
$result = mysqli_query($con,$query);


 while($row = mysqli_fetch_array($result)) {
$option .="<option>" . $row['tradingCounty'] . "</option>";

}
    }

the new column name is 'views' and i just want to add 1 to it each time a record from the database is viewed.

any help greatly appreciated.

A couple of answers have explained how to do this. Be aware that there's no built-in magic in MySQL that will increment your views column automatically whenever the row is retrieved; you need to program this explicitly. — O. Jones
– O. Jones, Commented Dec 3, 2014 at 13:56

Pupil · Accepted Answer · 2014-12-03 13:51:23Z

6

Add a new field views to the table.

When, user views the page, fire the SQL.

$query = "UPDATE offers SET views = views + 1";

answered Dec 3, 2014 at 13:51

Pupil

24k6 gold badges50 silver badges71 bronze badges

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

1 Comment

Buri Over a year ago

Table is actually offers

Bobby Tables · Accepted Answer · 2014-12-03 13:52:06Z

2

mysqli_query($con,"update offers set views = views + 1");

answered Dec 3, 2014 at 13:52

Bobby Tables

8661 gold badge9 silver badges18 bronze badges

2 Comments

The Humble Rat Over a year ago

Are you sure you didn't just copy Programming Students answer?

Ali Gajani Over a year ago

This is a 2+2 type of question, don't think he copied it. Obvious one

Gordon Linoff · Accepted Answer · 2014-12-03 13:58:10Z

0

If you have added the column, it probably has a NULL value. Either set the value to 0, by doing:

update offers
    set views = 0;

Or use:

update offers
    set views = coalesce(views, 0) + 1;

answered Dec 3, 2014 at 13:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Shubham Soin · Accepted Answer · 2014-12-03 14:14:40Z

0

You can change your code with this rewritten code assuming that your Table has a column views (datatype int).

//Get the details from previous page
$SelectedCounty = $_POST["result"];

//set variable for next SEARCH
$option = '';

// Get the county names from database - no duplicates - Order A-Z 
$query = "SELECT DISTINCT tradingCounty FROM offers ORDER BY tradingCounty ASC";

// execute the query, $result will hold all of the Counties in an array
$result = mysqli_query($con,$query);

if($result){
$query2 = "UPDATE offers SET views=views+1;
mysqli_query($con,$query2); 
}



 while($row = mysqli_fetch_array($result)) {
 $option .="<option>" . $row['tradingCounty'] . "</option>";

 }

Or if you need to track the view counts for individual records, you need to modify your code a bit. And probably you need to add one more field in the database for eg. id (datatype int) which can distinguish between different records.

Please clear your problem properly.

answered Dec 3, 2014 at 14:14

Shubham Soin

1381 silver badge9 bronze badges

3 Comments

UrbanWolf81 Over a year ago

Hi, Yes I need to track the views for each record... At the moment the best i've been able to achieve is to count each record that is shown and place the total of all of them in each records 'views' column, meaning my last test I ended up with the figure '310' listed as the view count for each record. here's the code I've used so far...

UrbanWolf81 Over a year ago

Hi, Yes I need to track the views for each record... At the moment the best i've been able to achieve is to count each record that is shown and place the total of all of them in each records 'views' column, meaning my last test I ended up with the figure '310' listed as the view count for each record. here's the code I've used so far... while($row = mysqli_fetch_row($result)) { mysqli_query($con,"UPDATE offers SET views = views + 1"); echo ("<div..... }

Shubham Soin Over a year ago

hi sturobinson81, please let me know the type of program you are trying to make. Like, you want the total count for each records rite, but how what does your application has, does that have number of articles for example, when someone clicks that you wanna add the +1 entry in the views section or its something else. Kindly elaborate.

Shubham Soin · Accepted Answer · 2014-12-04 19:39:59Z

0

As far as i have analysed your code it brings out the following case.

There are different records for tradingConty, and whenever a user views that particular record(one of the tradingCounty record) by clicking that or any other action specified, the php script is set to increament the view count for that particular entry(we can get that by id) in the database.

If thats the scenario, we can easily generate a code accordingly.

answered Dec 4, 2014 at 19:39

Shubham Soin

1381 silver badge9 bronze badges

Collectives™ on Stack Overflow

add one (+1) to a field in mysql database using php

5 Answers 5

1 Comment

2 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related