honsoworld Posted April 5, 2008 Share Posted April 5, 2008 I am working on a video download management application with PHP. I am using a MySQL DB to manage all of my data. The idea is that a video can only be downloaded x amount of times. I want my script to subtract 1 from an integer SQL value when the page loads, and when that SQL value is <= 0 I want a message to appear saying that the link is no longer active. I have all of my programming done, but the script is subtracting 2 from the SQL DB not 1. Below is a simplified version of one of my pages. Can anyone help me out here? I can't figure out how to make the script only subtract 1 instead of 2. <?php //Get video id from url $vid_id = $_GET['id'] ; //Connect With SQL DB $conn = @mysql_connect ( "localhost", "username", "password" ) or die( " Can Not Connect" ); $rs = @mysql_select_db ( "database", $conn ) or die("Error with database"); //Main DB Query $sql = "SELECT ID, Title, File, Date, Downloads_Left FROM videos WHERE ID=$vid_id"; $rs = mysql_query( $sql,$conn ); //No Rows found Error if(mysql_num_rows($rs) == "0") Echo "Wrong Video ID"; //Start Subtraction and Stop at 0//////////// //DB Query for detecting downloads left $dl_sql = "SELECT ID, Downloads_Left FROM videos WHERE ID=$vid_id"; $dl_rs = mysql_query( $dl_sql,$conn ); //Downloads Left Output while($dl_row=mysql_fetch_array($dl_rs)){ //Subtract one and store in variable $update_downloads = $dl_row["Downloads_Left"] - 1; //Set downloadable to yes $downloadable = "yes"; //If downloads left <= 0 echo error msg if ($dl_row["Downloads_Left"] <= 0) Echo "This file can no longer be downloaded"; //If downloads left <= 0 set downloadabl to no if ($dl_row["Downloads_Left"] <= 0) $downloadable = "no"; } //If downloadable update DB with new download amount if ($downloadable != "no") mysql_query( "UPDATE videos SET Downloads_Left=$update_downloads WHERE ID=$vid_id",$conn ); //End Subtraction and Stop at 0 //Start Main Output from DB/////////// while($row=mysql_fetch_array($rs)){ //Example of possible output if($downloadable == "yes") echo( "" . $row["File"] . "" ); } //END Main Output from DB ?> Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted April 5, 2008 Share Posted April 5, 2008 The function mysql_fetch_array() returns 2 rows for every entry, one indexed numerically and one with an associative index. You want to use the function mysql_fetch_assoc() instead. Also, I would change the logic like this: <?php //Connect With SQL DB $conn = @mysql_connect ( "localhost", "username", "password" ) or die( " Can Not Connect" ); $rs = @mysql_select_db ( "database", $conn ) or die("Error with database"); //Get video id from url $vid_id = mysql_real_escape_string($_GET['id']); // never trust user input //DB Query for detecting downloads left $dl_sql = "SELECT ID, Downloads_Left FROM videos WHERE ID=$vid_id"; $dl_rs = mysql_query( $dl_sql,$conn ); //No Rows found Error if(mysql_num_rows($dl_rs) == "0") Echo "Wrong Video ID"; //Start Subtraction and Stop at 0//////////// //Downloads Left Output while($dl_row=mysql_fetch_assoc($dl_rs)){ //Subtract one and store in variable $update_downloads = $dl_row["Downloads_Left"] - 1; //Set downloadable to yes $downloadable = true; //If downloads left <= 0 echo error msg if ($dl_row["Downloads_Left"] <= 0) { Echo "This file can no longer be downloaded"; $downloadable = "no"; } } //If downloadable update DB with new download amount if ($downloadable) { $q = "UPDATE videos SET Downloads_Left=$update_downloads WHERE ID=$vid_id"; mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); } //End Subtraction and Stop at 0 //Start Main Output from DB/////////// //Main DB Query $sql = "SELECT ID, Title, File, Date, Downloads_Left FROM videos WHERE ID=$vid_id"; $rs = mysql_query( $sql) or die("Problem with the query: $sql<br>".mysql_error()); while($row=mysql_fetch_assoc($rs)){ //Example of possible output if($downloadable) echo( "" . $row["File"] . "" ); } //END Main Output from DB ?> Ken Quote Link to comment Share on other sites More sharing options...
honsoworld Posted April 5, 2008 Author Share Posted April 5, 2008 Thanks for the speedy reply. I tried just tried this out, the code is cleaner (thanks) but for some reason it is still subtracting 2. I am new at MySQL in PHP, so I am still unfirmilliar with alot of things. Anyother suggestions or place for for me to start investigating? Thanks Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted April 5, 2008 Share Posted April 5, 2008 I made one small error, which may or may not be causing the problem. Replace: <?php $downloadable = "no"; ?> with <?php $downloadable = false; ?> Also, how many videos are in the database with the same ID? Ken Quote Link to comment Share on other sites More sharing options...
honsoworld Posted April 5, 2008 Author Share Posted April 5, 2008 I am still having problems. I have been tinkering around a bit and eliminated some portions that I did not think where necessary. Here is my current code: <?php //Connect With SQL DB $conn = @mysql_connect ( "localhost", "username", "password" ) or die( " Can Not Connect" ); $rs = @mysql_select_db ( "databse", $conn ) or die("Error with database"); //Get video id from url $vid_id = mysql_real_escape_string($_GET['id']); //DB Query for detecting downloads left $dl_sql = "SELECT ID, Downloads_Left FROM videos WHERE ID=$vid_id"; $dl_rs = mysql_query( $dl_sql,$conn ); //No Rows found Error if(mysql_num_rows($dl_rs) == "0") Echo "Wrong Video ID"; //Start Subtraction and Stop at 0//////////// //Downloads Left Output $dl_row=mysql_fetch_assoc($dl_rs); //Set downloadable to yes $downloadable = true; //If downloads left <= 0 echo error msg if ($dl_row["Downloads_Left"] <= 0) { Echo "This file can no longer be downloaded"; $downloadable = "false"; } //If downloadable update DB with new download amount if ($downloadable) { $q = "UPDATE videos SET Downloads_Left=Downloads_Left - 1 WHERE ID=$vid_id"; mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); } //End Subtraction and Stop at 0 //Start Main Output from DB/////////// //Main DB Query $sql = "SELECT ID, Title, File, Date, Downloads_Left FROM videos WHERE ID=$vid_id"; $rs = mysql_query( $sql) or die("Problem with the query: $sql<br>".mysql_error()); while($row=mysql_fetch_assoc($rs)){ //Example of possible output if($downloadable) echo( "" . $row["File"] . "" ); } //END Main Output from DB ?> Also, the video ID is my primary key, so it is only in the database once. I think the problem involves this bit of code: <?php //If downloadable update DB with new download amount if ($downloadable) { $q = "UPDATE videos SET Downloads_Left=Downloads_Left - 1 WHERE ID=$vid_id"; mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); } ?> I think it is either resolving twice, or being executed twice. I am leaning more towards being resolved twice in the db. Any thoughts? Thanks Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted April 5, 2008 Share Posted April 5, 2008 Try this: <?php //Connect With SQL DB $conn = @mysql_connect ( "localhost", "username", "password" ) or die( " Can Not Connect" ); $rs = @mysql_select_db ( "databse", $conn ) or die("Error with database"); //Get video id from url $vid_id = mysql_real_escape_string($_GET['id']); //DB Query for detecting downloads left $dl_sql = "SELECT ID, Downloads_Left FROM videos WHERE ID=$vid_id"; $dl_rs = mysql_query( $dl_sql); //No Rows found Error if(mysql_num_rows($dl_rs) == "0") Echo "Wrong Video ID"; else { //Start Subtraction and Stop at 0//////////// //Downloads Left Output $dl_row=mysql_fetch_assoc($dl_rs); if ($dl_row["Downloads_Left"] > 0) { $q = "UPDATE videos SET Downloads_Left=Downloads_Left - 1 WHERE ID=$vid_id"; $rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); } else { Echo "This file can no longer be downloaded"; } } //Start Main Output from DB/////////// //Main DB Query $sql = "SELECT ID, Title, File, Date, Downloads_Left FROM videos"; $rs = mysql_query( $sql) or die("Problem with the query: $sql<br>".mysql_error()); while($row=mysql_fetch_assoc($rs)){ //Example of possible output if($row['Downloads_Left'] > 0) echo( "" . $row["File"] . "" ); } //END Main Output from DB ?> Ken Quote Link to comment Share on other sites More sharing options...
honsoworld Posted April 5, 2008 Author Share Posted April 5, 2008 That resulted in displaying every $row["File"] in the database, instead of only showing the data for the one video. And it still is subtracting 2. I think that <?php //Main DB Query $sql = "SELECT ID, Title, File, Date, Downloads_Left FROM videos"; $rs = mysql_query( $sql) or die("Problem with the query: $sql<br>".mysql_error()); ?> Still needs to be: <?php //Main DB Query $sql = "SELECT ID, Title, File, Date, Downloads_Left FROM videos WHERE ID=$vid_id"; $rs = mysql_query( $sql) or die("Problem with the query: $sql<br>".mysql_error()); ?> Is it possible that this is resolving twice? <?php if ($dl_row["Downloads_Left"] > 0) { $q = "UPDATE videos SET Downloads_Left=Downloads_Left - 1 WHERE ID=$vid_id"; $rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); } else { Echo "This file can no longer be downloaded"; } ?> Similarly to how we used mysql_fetch_assoc instead of mysql_fetch_array, is there some other PHP/MySQL command that would replace mysql_query? This seams like a pretty easy thing to do, but I am stumped by it. Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted April 5, 2008 Share Posted April 5, 2008 I took off the "where" clause since it wasn't clear that you just wanted to display the one video. No, there is only one mysql_query() function. It's time to start putting in echo statements at key points to see if you're executing this segment of code more than once. <?php //Connect With SQL DB $conn = @mysql_connect ( "localhost", "username", "password" ) or die( " Can Not Connect" ); $rs = @mysql_select_db ( "databse", $conn ) or die("Error with database"); //Get video id from url $vid_id = mysql_real_escape_string($_GET['id']); echo 'Debug --- vid_id: . $vid_id . '<br>'; //DB Query for detecting downloads left $dl_sql = "SELECT ID, Downloads_Left FROM videos WHERE ID=$vid_id"; $dl_rs = mysql_query( $dl_sql); //No Rows found Error if(mysql_num_rows($dl_rs) == "0") Echo "Wrong Video ID"; else { echo 'Debug -- One video found<br>'; //Start Subtraction and Stop at 0//////////// //Downloads Left Output $dl_row=mysql_fetch_assoc($dl_rs); echo 'Debug -- Downloads_left: ' . $dl_row['Downloads_Left'] . '<br>'; if ($dl_row["Downloads_Left"] > 0) { $q = "UPDATE videos SET Downloads_Left=Downloads_Left - 1 WHERE ID=$vid_id"; echo 'Debug -- query: ' . $q . '<br>'; $rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); } else { Echo "This file can no longer be downloaded"; } } //Start Main Output from DB/////////// //Main DB Query $sql = "SELECT ID, Title, File, Date, Downloads_Left FROM videos where ID=$vid_id"; $rs = mysql_query( $sql) or die("Problem with the query: $sql<br>".mysql_error()); while($row=mysql_fetch_assoc($rs)){ echo 'Debug -- <pre>row: ' . print_r($row,true) . '</pre>'; //Example of possible output if($row['Downloads_Left'] > 0) echo( "" . $row["File"] . "" ); } //END Main Output from DB ?> Ken Quote Link to comment Share on other sites More sharing options...
honsoworld Posted April 5, 2008 Author Share Posted April 5, 2008 Before I accessed the page I had 23 downloads left for this video. After loading the page this is what was displayed: Debug --- vid_id: . 11 . ' Debug -- One video found Debug -- Downloads_left: 22 Debug -- query: UPDATE videos SET Downloads_Left=Downloads_Left - 1 WHERE ID=11 Debug -- row: Array ( [iD] => 11 [Title] => Video Title 11 [File] => video_11.wmv [Date] => 2007-12-24 [Downloads_Left] => 21 ) video_11.wmv Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted April 5, 2008 Share Posted April 5, 2008 Those debug outputs tell me that you're starting with 22 downloads left and you're ending with 21. How did you determine that you had 23? Ken Quote Link to comment Share on other sites More sharing options...
honsoworld Posted April 5, 2008 Author Share Posted April 5, 2008 My mini app is two files. File 1 (list.php) queries my database, and lists all of the entries. It puts it into an HTML table that shows the title, date, and downloads left for all database entries. Clicking on one of the table items sends you to the File 2 (download.php) with a hyperlink like downloads.php?id=id. On my list page, before I clicked on a video to download, it displayed 23 downloads left. I confirmed that by going to my MySQL manager from my webhost and indeed, before download.php is executed there was 23 downloads. I know that my list.php file is not causing the problem because it does not send or modify any SQL data, it is only displaying it. Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted April 5, 2008 Share Posted April 5, 2008 Can you post list.php and a dump of your test database. Ken Quote Link to comment Share on other sites More sharing options...
honsoworld Posted April 5, 2008 Author Share Posted April 5, 2008 I figured it out. This was not a PHP problem at all. On my list page each file was listed like this: <table border="0" width="100%" cellspacing="0" cellpadding="0" id="vod_vid_content_id_25" style=" font-size: 12pt"> <tr class="pointer" bgcolor="#FFFFFF" onMouseOver="this.bgColor='E9E7CF';" onMouseOut="this.bgColor='#FFFFFF'; " onclick="document.location.href='download.php?id=25'; "> <td><a href="download.php?id=25">2008-02-03 Video Title</a></td> <td width="23%" align="center">21</td> </tr> </table> Notice that I had both a hyperlink and a JavaScript onclick. Apparently the download page was being requested twice, once for each method of page progression By removing the hyperlink, keeping the JavaScript onclick, and using some css to make the cursors behave properly, I was able to have the same desired affect, and now it only subtracts once. I feel really dumb now. Thanks for your time and help, even though it was not PHP related, I still would not have found this without you. I only caught this because I was removing excess code so the page I would have posted would have been cleaner. Thanks again Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted April 5, 2008 Share Posted April 5, 2008 Please mark as "solved" Ken Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.