Need help with a Download tracker

General PHP Help
#1

Ok so im attempting to write a script for my site that will log to mysql when, what, and who is downloading from my server, my idea is to create a table specificly for this, only problem im having is I havent used php in over a year and im hitting a mental brain fart with wrighting the script, but i think it shuld look something like the script below, but i know im totally wrong. lol :slight_smile:

What I have got to do… “NOTES TO SELF”

create new table
name table download_tracker
7 fields: download count int 34 AI primary key, User_ID int 34, Username varchar 1024, email varchar 1024, File varchar 1024, Date int 1024, Time int 1024
write html / php download links
Get username, user id, email, file, and current time/date
insert username user id, file, current time and date, email, etc… into table download tracker on new line with ai count

i think download links should look something like this

Physical link

<html>
<a href="myurl.com/movieX.php" target="blank">movie X</a>
</html>

pass through page “movieX.php page”

<?php
$ftpname=(get `username` from `users` where `user_id` = `user_id` && `active` = '1')
$ftppass=(get `password` from `users` where `user_id` = `user_id` && `active` = '1')
(insert `username` into `download_tracker`); 
(insert `email` into `download_tracker`);
(insert `user_id` into `download_tracker`);
(insert `moviename` into `download_tracker`);
(insert `time` into `download_tracker`);
index="<?php $ftpname ?>:<?php $ftppass ?>@myurl.com:2121/movieX.mp4");
?>
#2

Well, I am not sure what your code does, but, there are several things you need to think about first.

Your link passes just a name of a PHP page. It does not pass any name of the actual movie. Therefore, how do you tell what movie is being selected. Normally, you would create the link using a PHP code page that would pull all of the movies and encode the URL link with the movie number imbedded in the link. Something like:

<a href=“myurl.com/movieX.php?id=” . <PHP echo “movieid1”; ?> . ">movie "<PHP echo “movie_number”
<a href=“myurl.com/movieX.php?id=” . <PHP echo “movieid2”; ?> . ">movie "<PHP echo “movie_number”

(Where movieid1/2 is pulled from your queries of the movie list and movie_number is the $row count from the query…)

Then, since all movies are sent to your movieX.php file. That code would pull the id of the movie number something like this:

$movie_number = $get[‘id’];

You now have the number of the movie to be played. So, then, you would take the user_id and create your query to save the data inside the database. At this point, you would create your query and then post it to the database. To do this, you have to pull your user_id from somewhere. I assume you have a log in page that stores the current user name and ID in a session variable for later use. You would then pull that out for use in the playing of the movie. Once this info is retrieved, you use code like this to save it in the database:
(Just a sample off the top of my head, not tested…)

<?php $movie_id = $get['id']; $dbhost = 'your site address'; $dbuser = 'your DB user id'; $dbpass = 'your DB password'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { // Here display the error, for testing, just kill the code... die('Could not connect: ' . mysql_error()); } else { // Connection good... // Here you load your other variables such as username or whatever you want to store $username = $_SESSION['username']; // loaded from the stored area from the login page... // etc... $sql = "INSERT INTO download_tracker " . "(username, email, user_id, moviename, time) " . "VALUES ". "('$username', '$email', '$user_id', '$moviename', '$time')"; $results = mysql_query( $sql, $conn ); if( !$results ) { die('Could not enter data: ' . mysql_error()); } else { echo "Entered data successfully\n"; // Or actually, here you would use header to relocate to the real movie playback page to start the movie } Something like that. You do not have to validate the username and password for inserting records as you are inserting them into the table. You can insert each value one at a time, but, this submits a query action for each command and slows down the server. Just do it in one query command. Not sure if this helps or not. But, if not, ask your questions and we can help...
#3

Thanks, that does help tremedously, I still have to pull the usernames and passwords to log the users into the ftp server to help keep things a bit mo0re secure rather than leaving it open to the world, but that does help me quite a bit

#4

Well, normally, when a user logs into your site, you would keep a cookie or session variable active to verify they are logged in. So, to do this I always use SESSION variables. The reason I prefer them is that they are quite secure. In your log-in page, you send it to a PHP page for validation. In that page if all is good, you load some session variables with all of their data. If they close the browser or try to open another one, the session variables are dumped and they can not download or access items. (As long as every page has a check for these hidden variables.) Since SESSION variables are only useable SERVER-SIDE, they are not easy to “hack” on the CLIENT-SIDE. So, this is quite secure!

If you handle this info in this manner, the server keeps the data active in the browser’s “session” as long as they are active on the browser. So, then, each page only has to check for the user_id and redirect to a YOU-BAD page stating that they do not have permission to be there. (Or just redirect to the log-in page!)

Also, in this way, you always have the user’s user_id, user_name, user_whatever you might need. So, no complicated code to verify them, just one simple “if (!$_SESSION[‘user_id’])” would check for a valid log-in.
There is little overhead on that as it is just checking one variable SERVER-SIDE which is not much.

Also, if you use encoded numbers, you can protect the number of any given movie and most “general” hackers will never get the real location. So, assuming that you have a “movies” table with all the movies listed, each movie would have a unique ID number. The actual address of the movie would be hidden in the database table for the “movies”. Something, like ID#1 - “My interesting movie” or ID#39 - “Wild animal goes berserk!” whatever. The name of the movie could be the filename and might have the owner’s ID appended to the beginning of the real name. So if the second movie was owned by user_ID #28, the new name saved on the server would be “28 - Wild animal goes berserk!”. No hacker or user would guess that as they would have to know the user_ID’s of all the “owners” of each movie. Impossible… And, in your download code, you can hide that info since it is a PHP script you would be using for the FTP download. (You would use the owner’s ID and the movie name to pull the movie across the FTP connection)

Hmmmm, that sounded complicated, but, it isn’t. Anyway, more food for thought until later today…

Anyway, still tied up for quite awhile yet. I will talk to you further later on…

#5

Creating a db for the movies, giving them unique id’s and salting them with the user id’s, or otherwize hashing sounds like a secrue way to go, I will definently keep that in mind, but my main objective is to track who grabs what, so i can prioratize my network traffic accordingly and montior what is leaving my network to the outside world. and have a log for!

#6

Well, to track a download, you just have to implement a way to enter your log info BEFORE the user actually downloads the movie. Therefore, you just have to have the link to call a secondary page that actually saves all the log info before it allows the download. Depending on what type of site you are actually creating, you can do that by creating a JavaScript or JQuery routine to capture the link “click” and save the log info before letting the link process the download.

I did some research for you and found that one simple way is to just add some Javascript to handle the download. Basically, you add an “onclick=” pointing to a JS routine that handles the tracking logs. If you need updates to the DB, you can make it a JQuery/AJAX call instead and post the info using a called PHP page. This should work well for you as the PHP page would be secure and the JS code would start the download after updating the DB.

Here is a link to one solution, not sure if it will help, but check it out:
http://www.ravelrumba.com/blog/tracking-links-with-javascript/

And, here is a link to an actual click-tracking JS library that might do it, too…
https://github.com/erikvold/click-tracking-js-lib

AND, this one you can download is a library that uses JQuery/Ajax and PHP to track whatever buttons you would like to track on your site. It is set up for seeing how a user “flows” thru your site, but, can be altered to just handle one link or several…
https://github.com/erikvold/click-tracking-js-lib

Hope one of those solves it for you!

#7

Ok so if i insert all my movie names into a table and assign them all unique id’s i can then grab the movie_names by quaring the table for the movie_id’s something like

function movie_id_from_moviename($moviename) {
	$moviename = sanitize($moviename);
	return mysql_result(mysql_query("SELECT `movie_id` FROM `movies` WHERE `moviename` = '$moviename'"), 0, 'movie_id');
}

or is that inproper use of grabbing data from one table to insert into another? my head is spinning from trying to figure this out, as i have never worked with java, jquery, or ajax before, im trying to stick with php. i think i can grab the user id username and email from the session variable i have set up and connect to my database with the script i already have, just aquiring the data from 2 seperate tables and inserting them into another has me cross-eyed XD

#8

never mind, i was confusing myself so i have greatly simplified it, assuming I already have all 3 databases set up, users, movies, and download_tracker, how would i fix the following to make it function properly?

download link:

<a href="/movies/moviehandaler.php$=1" alt="" >movie x </a>
<a href="/movies/moviehandaler.php$=2" alt="" >movie y </a>
<a href="/movies/moviehandaler.php$=3" alt="" >movie z </a>

moviehandaler.php:

<?php
$user_info = ('username', 'email', 'user_id' FROM `users` WHERE 'user_id' = $session_user_id); //is this right?
$movie_id = get['']; //how would i grab the number from the url after "$="
$movie_info = ('movie_name' FROM `movies` WHERE '$movie_id' = 'movie_id'); // is this right?
mysql_query(INSERT '$user_info', '$movie_info', 'time', INTO `download_tracker`); // I know this is wrong, how do i fix it?
header('Location: /movies/<?php print $movie_info ?>'); //is this even posible?
?>
#9

Well, first you need to change the movie’s HREF. Your current version:
href="/movies/moviehandaler.php$=1" This sends the link to the moviehandler.php file, but, does not pass the movie ID correctly. It should be more like this:
href="/movies/moviehandaler.php?id=1"

What this change does is pass the number “1” into the posted variable called “id”…

Inside the moviehandaler.php code, the first thing you do is “GET” the id number so you can use it.
Something like:
$current_movie=$_GET[‘id’]; (This pulls the posted variable named “id” and gets it’s value.

Next, you have the movie id at this point and you can do the rest of the tracking. Now on to that part.

Your current code:

<?php $user_info = ('username', 'email', 'user_id' FROM `users` WHERE 'user_id' = $session_user_id); //is this right? $movie_id = get['']; //how would i grab the number from the url after "$=" $movie_info = ('movie_name' FROM `movies` WHERE '$movie_id' = 'movie_id'); // is this right? mysql_query(INSERT '$user_info', '$movie_info', 'time', INTO `download_tracker`); // I know this is wrong, how do i fix it? header('Location: /movies/<?php print $movie_info ?>'); //is this even posible?

?>
This is a bit mixed up, but basically correct. It should be more like this: (I added some comments for you)
[php]

<?php $movie_id = get['id']; // Grab the movie id passed from the link... // Here you would have to pull the data you want to store that is not the movie ID. // So, items like email and username would be needed if you want to store it in the DB table... // Create a query to use to store into the database table $query = "INSERT INTO download_tracker (username, email, user_id, movie_id, movie_name) VALUES ($username, $email, $user_id, $movie_id, $movie_name)"; // Store the data using the query to save the logging info... mysql_query($query) or die(mysql_error()); // Note if there is an error it displays it and dies... header('Location: /movies/<?php print $movie_info ?>'); //is this even posible?

?>
[/php]

Now, there is a couple of parts that I did not explain. Usually when someone logs into your site, you save the info about them into SESSION variables. This is done once the username and password have been checked and made sure they are a valid user. Then, you would save things like username and email into SESSION variables. Then, anywhere in your site that you need to use these, you would just use the SESSION variable to place them where needed. So, if you had done that during the log in process, the query would be more like this:

$query = “INSERT INTO download_tracker (username, email, user_id, movie_id, movie_name) VALUES ($_SESSION[‘username’], $_SESSION[‘email’], $_SESSION[‘user_id’], $movie_id, $movie_name)”;

This saves a lot of time as you do NOT have to run a query to get their info, it was already stored during login time. If you do not do that, you must do a query using their user_id on the user DB table and load these variables with the correct data. (I noted that in the code…)

Well, that should get you started. Let us know if you need further help…

#10

thanks again, i think im just about close enough to taste the finish line now, your awsome, need to test this when i get home before i can officially call it “solved”, but i think im there!

also could i append this to add a timestamp?

date_default_timezone_set('UTC');

$date = date('l jS \of F Y h:i:s A');

$query = "INSERT INTO download_tracker  (username, email, user_id, movie_id, movie_name, date) VALUES ($username, $email, $user_id, $movie_id, $movie_name, $date)";
#11

Yes, that would work. Sorry, I was going to add that, but, forgot. Sometimes dates are tricky. If your database field is set for “datetime” it must be stored in that format. I often use text strings for dates or timestamps. It really depends on how you are planning on using the info once it is in place.

Your version should work for this… Good luck, and let us know if you run into an issue…

#12

tested it exactly as i have it laid out here and i still cant get it to work, any ideas, punctuation or syntax maybe?

[code]<?php
include ‘core/init.php’;
protect_page();
include ‘includes/overall/header.php’;
?>

<?php date_default_timezone_set('UTC'); $date = date('l jS \of F Y h:i:s A'); $movie_id = get['id']; $movie_info = ('movie_name' FROM movies WHERE '$movie_id' = 'movie_id'); mysql_query("INSERT INTO download_tracker (user_id, username, email, file, time) VALUES('$_SESSION['user_id']', '$_SESSION['username']', '$_SESSION['email']', '$movie_info['movie_name']', '$date', ) "); echo 'it worked!'; ?> <?php include 'includes/overall/footer.php'; ?>[/code]
#13

LOL, yep, syntax errors… First, no such thing as “get” unless you have a Javascript routine named that.
Also, what is that “$movie_info” used for? Is that supposed to be getting the name from a query to the database? So, to fix it, first change the “get” to “$_GET” so it pulls the variable from the link.
That should get some results.

Now, if the “$movie_info” variable is a query to the DB, it should be more like this:
$movie_info = mysql_query("SELECT movie_name FROM movies WHERE movie_id = ‘$movie_id’ ");

Note the quotes and double-quotes… Try that and let us know…

#14

Sorry, I just saw one more error. ( Partly my fault ! )

When you do a query SELECTing data from the DB, it is not useable as-is.
You have to pull it from the array that is returned from the actual query. My flying-fingers…

So, this explanation is bad:

Now, if the “$movie_info” variable is a query to the DB, it should be more like this:
$movie_info = mysql_query("SELECT movie_name FROM movies WHERE movie_id = ‘$movie_id’ ");

It should be:

Now, if the “$movie_info” variable is a query to the DB, it should be more like this:
// Select movie information from DB
$result = mysql_query("SELECT movie_name FROM movies WHERE movie_id = ‘$movie_id’ ");
// Take movie name out of the resulting array (only one row of data from the DB should be present)
$row = mysql_fetch_assoc($result);
$movie_info = $row[‘movie_name’];

Hope that didn’t confuse you. If you want to see a tutorial on accessing databases using MySQL and PHP, there are several online. I use these two as the best ones:
http://www.w3schools.com/php/php_mysql_intro.asp Keep pressing continue or next
http://www.tizag.com/mysqlTutorial/ Same, keep pressing continue…
Both of these are simple to understand and both explain simple steps for accessing the DB.
(Might help you understand what I wrote…)

Hope this does it for you… I am sure you are very close to the solution…

#15

ok so i changed it up a bit to fix some stuff and debug, so i wrote this:

[code]<?php
include ‘core/init.php’;
protect_page();
include ‘includes/overall/header.php’;
?>

<?php date_default_timezone_set('UTC'); $date = date('l jS \of F Y h:i:s A'); $movie_id = $_GET['id']; echo $user_data{'username'}; echo'
'; echo $user_data{'email'}; echo'
'; echo $user_data{'user_id'}; echo'
'; echo $date; echo'
'; echo $movie_id; echo '
'; mysql_query("INSERT INTO `download_tracker` (`user_id`, `username`, `email`, `file`, `time`) VALUES ($user_data{'user_id'}, $user_data{'username'}, $user_data{'email'}, $movie_id, $date)"); print(mysql_error()); ?> <?php include 'includes/overall/footer.php'; ?>[/code]

and the output looks like this:

THC_Butterz [email protected] 17 Tuesday 23rd of July 2013 11:58:39 PM 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{'user_id'}, Array{'username'}, Array{'email'}, 1, Tuesday 23rd of July 2013 11:' at line 1

#16

so i added individual markup fo the userdata and now it looks like this:

[code]<?php
include ‘core/init.php’;
protect_page();
include ‘includes/overall/header.php’;
?>

<?php date_default_timezone_set('est'); $date = date('l jS \of F Y h:i:s A'); $movie_id = $_GET['id']; $track_username = $user_data{'username'}; echo $track_username; echo'
'; $track_email = $user_data{'email'}; echo $track_email; echo'
'; $track_user_id = $user_data{'user_id'}; echo $track_user_id; echo'
'; echo $date; echo'
'; echo $movie_id; echo '
'; mysql_query("INSERT INTO `download_tracker` (`user_id`, `username`, `email`, `file`, `time`) VALUES ($track_user_id, $track_username, $track_email, $movie_id, $date)"); print(mysql_error()); ?> <?php include 'includes/overall/footer.php'; ?>[/code]

and im getting this output:

THC_Butterz [email protected] 17 Tuesday 23rd of July 2013 08:18:31 PM 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com, 1, Tuesday 23rd of July 2013 08:18:31 PM)' at line 1

#17

Try removing the backward “ticks”. I have never needed them and they are really just cosmetics.
On some systems they can cause errors. Also, I do not understand what these lines formatted like this
actually do?

$user_data{‘username’}

If the $user_data is an array, shouldn’t it be $user_data[‘username’] ???

Since this $user_data is used in the query, and it is saying the first use of the backwards “ticks” are the error, then either the fieldname (user_id) is bad which means remove the “ticks” or the value ($user_data{‘user_id’}) is bad. Also, normally, your values are enclosed using quotes. So, my original line did not include them as it was just a sample. It would be more like:

$query = “INSERT INTO download_tracker (username, email, user_id, movie_id, movie_name, date) VALUES (’$username’, ‘$email’, ‘$user_id’, ‘$movie_id’, ‘$movie_name’, ‘$date’)”;

Your code changed between posts so not sure about all this. But, good idea on the debugging displays… That is the way to debug.

#18

Okay, I researched your code. The line:

echo $user_data{‘username’};

It is using ESCAPE characters. So, this is not any type of standard syntax for PHP.
This basically is the exact same as this line:

echo $user_datausername;

Is that what you planned? If so, then, okay, I think the issue is that you need single quotes around the
VALUES of the data to be inserted.

Also, if you set up the query as I suggested as a string ($query=) then you could echo that and really see what the error is inside the query. If you set up the query as a string and then echo it, you will see exactly how it is and the error will jump out at you.

Hope that helps…

#19

thank you tramendously, it works now !!! :slight_smile:

#20

Really! Great! Sorry it took so many posts… (I’m tired today! LOL)

So, I will mark this solved if you are sure!

Sponsor our Newsletter | Privacy Policy | Terms of Service