-2

I have a table in my database where I want to track the number of downloads for each uploaded file in my website. I have succeeded in inserting user details after a successful download of a file. Each user that downloads a file the user ip address is save in my database tbl (All_downloads) and the filename the user downloaded. I have a different table (Songs) where I stored all uploaded files and I added a column in the table name "DOWNLOAD COUNTER", now I want to fetch the total number of downloads from tbl ALL_downloads and INSERT the total number into tbl "songs" (I WANT IT TO BE GROUP BY THE SONG NAME).

if (isset($_GET['name']) && basename($_GET['name']) ==  $_GET['name']) {
    $name = $_GET['name'];
    $userip = $_SERVER['REMOTE_ADDR'];
    $query = "SELECT * FROM songs_download_counter WHERE name='$name' && ip='$userip'";
    $result = mysqli_query($con,$query);
    if($result->num_rows==0){
        $insertquery="INSERT INTO songs_download_counter SETip='$userip', name='$name'";
        mysqli_query($con,$insertquery) or die (mysqli_error($con));
    } else {
        $row=$result->fetch_assoc();
        if(!preg_match('/'.$userip.'/i', $row['ip'])) {
            $newip = $_SERVER['REMOTE_ADDR'];
            $updatequery="UPDATE songs_download_counter SET ip='$newip', name='$name' WHERE name='$name'";
            mysqli_query($con,$updatequery) or die (mysqli_error($con));

        }
    }
Qirel
  • 25,449
  • 7
  • 45
  • 62
  • Please add your question in a proper format, Later if someone check our code it will be difficult to understand – Ajith Nov 22 '19 at 06:23
  • 3
    **Warning!** You are _wide open_ for SQL injection attacks! You should use parameterized [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of using completely unescaped user data directly in your queries like that. – M. Eriksson Nov 22 '19 at 06:26
  • `SETip='$userip'` in your INSERT statement should be `SET ip='$userip'` (space after `SET`). – M. Eriksson Nov 22 '19 at 06:27
  • The requirement can be addressed via SQL query. But it seems you are not looking in that direction. The below answer serves the purpose but, your Question is quiet not clear on to what is expected out come. – Nikhil Joshi Nov 22 '19 at 07:01

1 Answers1

0

The basic query to get your counts is:

SELECT count(*)
FROM songs_download_counter
GROUP BY name

The update query looks something like this, depending on the exact name of your songs table:

UPDATE
    songs
SET
    download_counter =
        (
        SELECT count(*)
        FROM songs_download_counter d
        WHERE d.name = songs.name
        )

See here for some discussion: Update query using Subquery in Sql Server

Also note: You would be much better off to have a unique ID for each song name and use that for your match instead of the text of the name. Matching on names might get you some weird results later as your database grows.

Gerdofal
  • 91
  • 7