How to code database insert on buttonclick

[larry29936]

I have a download area in my index.php. I'd like to insert a row in the connected database when a user downloads a file. Here's what I have at the start of index.php:

<?php
session_start();
// start of script every time.

//  setup a path for all of your canned php scripts
$php_scripts = '/home/larry/web/test/php/'; // a folder above the web accessible tree
//  load the pdo connection module  
require $php_scripts . 'PDO_Connection_Select.php';
require $php_scripts . 'GetUserIpAddr.php';

//*******************************
//   Begin the script here
$ip = GetUserIpAddr();
if (!$pdo = PDOConnect("foxclone")):
{
	echo "Failed to connect to database" ;
    exit;
}
else:
{
    $filename = NULL;
    $stmt = $pdo->prepare("INSERT INTO download (IP_ADDRESS, FILENAME) VALUES (?, ?)");
    $stmt->execute([$ip,$filename]) ;

}

endif;
//exit();
?>

<DOCTYPE html>

Here's what my download area looks like:

      <?php
        $files = glob('download/*.iso');
        $file = $files[count($files) -1];
        $filename =  basename($file);
      ?>

        <div class="container">
           <div class="divL">                       
              <h3>Get the "<?php echo "{$filename}";?>" file (approx. 600MB)</h3>
                <center>  <a href="<?php echo "/{$file}";?>"><img src="images/button_get-the-app.png" alt=""></a>  </center>

How do I execute "$stmt->execute([$ip,$filename]) ;" when the download button is pressed?

[ginerjm]

Uh, where is this 'download button'?  You want to execute a query when the user clicks on 'something'.  Are you not wanting to go to the server to do this?

[larry29936]

The last line in what I provided in the download area is the button. The statement that I want to execute would insert a row in the database connection established in the first section.

[ginerjm]

Doesn't look like a button.  Looks like an anchor tag, sometimes called a 'link.

Anyway - what about my question?

And where is the download code?  BTW - by download do you mean server to client or client to server?

Looking further - it appears that you are trying to "go" to the filename.  Is that possible?

Edited April 6, 2020 by ginerjm

[larry29936]

OK, it's a link on a button. <a href="<?php echo "/{$file}";?>"><img src="images/button_get-the-app.png" alt=""></a>

It's a download from the server to the client. It's all working, but I want to insert a row in the database on the server when a download is made. The connection to the database is established in the script at the top of index.php and a row is inserted into the database. The download area is further into index.php

[ginerjm]

If all of this is happening in this script I don't understand why you are asking the question about doing a simple query.  

BTW - you don't need those colons on an if statement if you simply don't use an "endif".  Just less typing that way

[larry29936]

I guess I didn't explain my problem properly. When a user opens the webpage, a connection to the database is established and a row is inserted into a table with the user's ip address. If the user decides to download a file, another row needs to be inserted into the same table with the user's ip address and filename. 

The php statement, "$stmt->execute([$ip,$filename]) ;" is what does the insertrow.

What I'm asking is how to trigger that line when the button (link) is clicked.

Edited April 6, 2020 by larry29936

[ginerjm]

So when the user clicks that (unseen) button what happens?  Do you execute another script?  The same script?  What?  When one clicks on an anchor tag usually it takes you somewhere.  Of course this anchor does look quite different to me.  If IT is doing the download for you I"m out of my depth here.

[larry29936]

Yes, it is doing the download. Thanks for trying.

[ginerjm]

So - an anchor that points to a filename downloads that file?   It doesnt' try and link to it?   Something new to me!

[larry29936]

I refer you to: https://davidwalsh.name/download-attribute. Maybe my research is helpful. 

[ginerjm]

I see nothing there to help me understand.  What is this "download" attribute it mentions?

[larry29936]

Sorry, wrong reference. Let me find the correct one.

[Barand]

https://lmgtfy.com/?q=html5+download+attribute

[ginerjm]

Thanks Barand.   Learned how to download a file the quick way.

As for the OP tho - it doesn't solve his question.  And I don't have an answer for him.

[Barand]

12 hours ago, larry29936 said:

What I'm asking is how to trigger that line when the button (link) is clicked.

Instead of linking directly to the target file, link to a php script which updates your db and sends the file for download.

[larry29936]

@Barand - I'm a relative newbie at this this. Do you have a link to something that explains what you're suggesting?

[Barand]

Sample HTML

<!DOCTYPE>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Sample</title>
</head>
<body>
<a href="mydloader.php?ip=xyz&filename=banner.png">banner.png</a><br>
<a href="mydloader.php?ip=xyz&filename=punchcard.jpg">punchcard.jpg</a><br>
<a href="mydloader.php?ip=xyz&filename=demo_app.html">demo_app.html</a><br>
</body>
</html>

mydloader.php

<?php
include 'db_inc.php';         # YOUR CONNECTION
$db = pdoConnect('test');     # GOES HERE


if (isset($_GET['ip'])) {
    $add = $db->prepare("INSERT INTO download (IP_ADDRESS, FILENAME) VALUES (?, ?)");
    $add->execute( [ $_GET['ip'], $_GET['filename'] ] );
    
    header('Content-Type: octet-stream');
    header('Content-Disposition: attachment; filename="'.$_GET['filename'].'"');
    header('Pragma: no-cache');
    header('Expires: 0');
    
    readfile("download/{$_GET['filename']}");
}
?>

mysql> select * from download;
+-------------+------------+---------------+
| download_id | ip_address | filename      |
+-------------+------------+---------------+
|           1 | xyz        | banner.png    |
|           2 | xyz        | punchcard.jpg |
|           3 | xyz        | demo_app.html |
+-------------+------------+---------------+

 

[larry29936]

OK, I see how that does the insert into the table. Does that call go after the download, which in my case is done like this:

<a href="<?php echo "/{$iso}";?>"><img src="images/button_get-the-app.png" alt=""></a>  

or do I need to modify how I'm doing the download?

Edited April 7, 2020 by larry29936

[larry29936]

OK, does the following section do the actual download?

   header('Content-Type: octet-stream');
    header('Content-Disposition: attachment; filename="'.$_GET['filename'].'"');
    header('Pragma: no-cache');
    header('Expires: 0');
    
    readfile("download/{$_GET['filename']}");

Would this work?

<a href="mydloader.php?ip="$ip"&filename="<?php echo "/{$iso}";?>"><img src="images/button_get-the-app.png" alt=""></a> demo_app.html"

Edited April 7, 2020 by larry29936

[Barand]

15 minutes ago, larry29936 said:

Would this work?

I doubt it. That will link to ...

<a href="mydloader.php?ip="

... because you put quotes in the middle of the query string. Don't know why you've stuck demo_app at the end.

[larry29936]

Didn't mean to have "demo_app" at the end. Are you saying no quotes around the variables, i.e. $ip not "$ip"?

[Barand]

Check my sample

 

[larry29936]

I understand. I took the php variable and converted it like so:

$filename = "<?php echo "/{$iso}";?>

<a href="mydloader.php?ip=$ip&filename=$filename><img src="images/button_get-the-app.png" alt=""></a>

 

[larry29936]

@Barand -  this doesn't work. Nothing happens when I click on the button. I put an echo near the top of mydload.php and it never gets there. Could this be because I'm using an image button, not an html button?

Edited April 7, 2020 by larry29936