[SOLVED] PHP/SQL Subtracting 2 instead of 1

[honsoworld]

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
?>

[kenrbnsn]

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

[honsoworld]

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

[kenrbnsn]

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

[honsoworld]

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

 

 

[kenrbnsn]

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

[honsoworld]

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.

 

[kenrbnsn]

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

[honsoworld]

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

[kenrbnsn]

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

[honsoworld]

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.

 

[kenrbnsn]

Can you post list.php and a dump of your test database.

 

Ken

[honsoworld]

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

 

[kenrbnsn]

Please mark as "solved"

 

Ken