CODEWITHSUNDEEP
phpmysqlphpmyadmin
eyalix
eyalix

Reputation: 143

Count in Mysql table values

There is a column in table called "Freq", its meant to count search values, Each search is updated in new row on table, I'm looking for PHP code to count values for each row and update it back in Table. (could be a loop action?)

For example: value rr123456789il, is search 20 times. I want the Freq cell to show 20 on each row with same searched code,

enter image description here

update table

$sql_update = "UPDATE enterlog SET Email='$email', Freq='$frequency'  WHERE LogID='$last_id'";

Upvotes: 0

Views: 98

Answers (3)

syck
syck

Reputation: 3029

Iterate over the Itemcodes and update their respective frequencies:

$db = new mysqli("localhost", "my_user", "my_password", "world");
// fetch itemcodes and their frequencies
$sql = 'SELECT Itemcode, COUNT(*) AS count FROM enterlog GROUP BY Itemcode';
$qq = $db->query($sql);
while (list($itemcode, $count) = $qq->fetch_row()) {
    // update all rows having the itemcode with the appropriate frequence
    $sql = 'UPDATE enterlog SET freq='.$count.' WHERE Itemcode="'.$itemcode.'"';
    $db->query($sql);
}
$qq->free_result();

Just to show the principle. There are some optimization possibilities, like using a prepared statement for the query in the loop, but that I will leave up to you.

There also should be a way to do that with a subquery in a single SQL statement.

Upvotes: 0

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

I think that it will better if you create a new table freq when all frequencies will be there to avoid having redundant data.

CREATE TABLE freq (
    LogID int unsigned not null,
    freq int unsigned DEFAULT 0,
    foreign key(LogID) references enterlog(LogID) ON DELETE CASCADE ON UPDATE CASCADE
);

Then you can rut the following query to initialize you table:

INSERT INTO freq (LogID, freq)
(SELECT LogID, 1
FROM enterlog
)
ON DUPLICATE KEY UPDATE freq = freq + 1;

Also, you have to run this query every time you make an insertion to your enterlog table so frequencies will be up to date.

Upvotes: 1

Audite Marlow
Audite Marlow

Reputation: 1054

Try something like this.

$freq = (int)$row['Freq']; // $row being the result row for $last_id.

$sql_update = 'UPDATE `enterlog` SET
    `Email` = "'.$email.'",
    `Freq` = "'.($freq + 1).'"
WHERE `LogID` = "'.$last_id.'"';

Upvotes: 0

Related Questions