Adding to an Int row in db

[doddsey_65]

Hi, i have a database which houses all of the users of my site. One of the columns is for points which is default 0.

What I would like to do is add 1 to this number each time they upload an image. What code would i use for this? I have an idea but i dont think it would work. My idea is something like:

 

$add= +1

 

$sql ("update users set points=$add")

 

Something like that?

[seksislav]

UPDATE users SET points = points + 1 WHERE id = ... ?

[doddsey_65]

okay cheers mate, also i wanted to add a new feature since users could just add images all the time to increase their points, i was wondering about a 1 hour time delay whereby they could only upload 1 image per hour. Is this possible?

[doddsey_65]

am i right in thinking i need to insert the date and time into the database when they upload an image, and when they go to add anotherone i would need to pull the date of their last one from the database and see if it was atleast an hour before this one?

Soething like

 

$current= date("H:i:s");
$last=("SELECT last FROM users WHERE username='$_POST['username']");
$res=mysql_query($last);

 

Then i get stuck with the if and thats what i need help with.

[joel24]

you'd have it so when a user uploads an image it goes liek

$userid = //whatever.. $_SESSION['id'] etc
$checklast = @mysql_query("SELECT * FROM users WHERE userid = $userid AND last < NOW() - INTERVAL 1 HOUR");
if (mysql_num_rows($checklast)) {
//upload photo
} else {
//error! you've uploaded a photo in the last hour
}

[doddsey_65]

i tried that but i keep getting a 500 error.

[doddsey_65]

Okay this is what i am trying but i keep getting  a 500 error. Can anyone see what is wrong?

 

$time = date("H");

$last = ("SELECT * FROM users WHERE username='$_POST['username']' AND last<$time");

if (mysql_num_rows($last)) {

$sql="INSERT INTO publicgallery (path, name, username, description, thumbpath, last)
VALUES
('$filePath','$_POST['rendername']','$_POST['username']','$_POST['description']', 

'$thumbpath', '$time')";

$result = mysql_query($sql, $db);


echo '<br><br><div id="page"><div id="content"><div class="post">
<p class="meta">Your image has been successfully uploaded  
<img src="images/img08.png" alt="bullet"></p><div class="entry">'; 

echo 'Your new image will now be visible on the Public Gallery. Click <a 

href=public_gallery.php>here</a> to go back.';

echo '</div></div></div>';


$sql=("UPDATE users SET points=points + 1 WHERE username='$_POST['username']'");
$res=mysql_query($sql);



include('footer.php');
}

[premiso]

$last = "SELECT * FROM users WHERE username='{$_POST['username']}' AND last<$time";

 

And:

 

$sql="INSERT INTO publicgallery (path, name, username, description, thumbpath, last)
VALUES
('$filePath','{$_POST['rendername']}','{$_POST['username']}','{$_POST['description']}',

'$thumbpath', '$time')";
$result = mysql_query($sql);

 

And:

 

$sql="UPDATE users SET points=points + 1 WHERE username='{$_POST['username']}'";

 

Those are a few errors you can fix, as will this fix the 500 internal server error, I am not sure.

[doddsey_65]

cheers mate that fixed the 500 but now the screen doesnt display anything when i upload. heres the full code, there may be more errors that you couldnt see before. As for the code to do the time is that right? if i get these errors sorted would tht work?

 

<?php
ob_start();
include('header.php');
include('db.php');    
$db=mysql_connect($db_host,$db_user,$db_pass)
or die ('I cannot connect to the database because: ' . mysql_error());    

mysql_select_db($db_name,$db);

$uploadDir = 'public/';

if(isset($_POST['upload2'])){

$fileName = $_FILES['userfile2']['name'];
$tmpName = $_FILES['userfile2']['tmp_name'];

$filePath = $uploadDir . $fileName;

$result = move_uploaded_file($tmpName, $filePath);

if (!$result) {echo "Error uploading file";exit;}

if(!get_magic_quotes_gpc()){$filePath = addslashes($filePath);} 

   include('resize.php');
   $image = new SimpleImage();
   $image->load('public/' .$fileName);
   $image->resize(100,100);
   $image->save('public/thumbs/' .$fileName);

$thumbpath= 'public/thumbs/' .$fileName;

$time = date("H");

$last = ("SELECT * FROM users WHERE username='{$_POST['username']}' AND last<$time");

if (mysql_num_rows($last)) {

$sql="INSERT INTO publicgallery (path, name, username, description, thumbpath, last)
VALUES
('$filePath','{$_POST['rendername']}','{$_POST['username']}','{$_POST

['description']}', '$thumbpath', '$time')";

$result = mysql_query($sql, $db);


echo '<br><br><div id="page"><div id="content"><div class="post">
<p class="meta">Your image has been successfully uploaded  
<img src="images/img08.png" alt="bullet"></p><div class="entry">'; 

echo 'Your new image will now be visible on the Public Gallery. Click <a 

href=public_gallery.php>here</a> to go back.';

echo '</div></div></div>';


$sql=("UPDATE users SET points=points + 1 WHERE username='{$_POST['username']}'");
$res=mysql_query($sql);



include('footer.php');
}
}
ob_flush(); 
?>

[premiso]

It depends on what your database stores TIME as, if it is just the H then yea, if not and it is a timestamp you need to use strtotime to convert it in order to test it.

 

With the poor indentation it is hard to see if you do display anything if the upload is successful, so that could be the reason for the blank page.

[doddsey_65]

here is what should appear when succesful:

 

echo '<br><br><div id="page"><div id="content"><div class="post"><p class="meta">Your image has been successfully uploaded  <img src="images/img08.png" alt="bullet"></p><div class="entry">'; echo 'Your new image will now be visible on the Public Gallery. Click <a href=public_gallery.php>here</a> to go back.';echo '</div></div></div>';

 

Also the table is Int(2)

[doddsey_65]

Okay i have worked it out now, i didnt have an else statement if the query was false. I do now but i dont know why it keeps coming back as false, the db table int is currently 0 and i echoed time to get 09. but my query is if 0<09 which is true but it returns false. Does anyone know why this is?

[doddsey_65]

ok i have now changed it to this:

 

$time = time();
$interval = 3600;
$last = ("SELECT * FROM users WHERE username='{$_POST['username']}' AND last<$time-$interval");

 

in my db the table 'last' is currently set to TIME which isnt working, what do i need to set it to?

 

when i echo time i get: 1259249644

and interval is: 1259246044

 

and in the db it is - 00:00:00

[joel24]

mysql has built in time functions, at the moment your trying to compare a timestamp int to a TIME field... you can use mysql to convert the TIMESTAMP to a time format or you can put the time in the mysql format HH:MM:SS, or you can do it all in mysql as follows.

$last = ("SELECT * FROM users WHERE username='{$_POST['username']}' AND last < NOW() - INTERVAL 1 HOUR");

 

also you should probably change the "last" field to DATETIME... and then when you upload set "last = NOW()", otherwise the user could upload a photo on the 14th of the month at 9am then go to upload on the 15th at 9.30am and it would say it hasn't been an hour yet.

 

[doddsey_65]

Cheers for the replies. I am not getting any errors now but it will let mepost more than one within an hour.

 

Here is the full code: Db table last is set as datetime.

 

$last = ("SELECT * FROM users WHERE username='{$_POST['username']}' AND last < NOW() - INTERVAL 1 HOUR");

if (mysql_query($last)) {

$sql="INSERT INTO publicgallery (path, name, username, description, thumbpath)
VALUES
('$filePath','{$_POST['rendername']}','{$_POST['username']}','{$_POST['description']}', '$thumbpath')";

$result = mysql_query($sql, $db);


echo '<br><br><div id="page"><div id="content"><div class="post">
<p class="meta">Your image has been successfully uploaded  
<img src="images/img08.png" alt="bullet"></p><div class="entry">'; 

echo 'Your new image will now be visible on the Public Gallery. Click <a href=public_gallery.php>here</a> to go 

back.';

echo '</div></div></div>';


$sql=("UPDATE users SET points=points + 1, last=NOW() WHERE username='{$_POST['username']}'");
$res=mysql_query($sql);
}
else
{

echo '<br><br><div id="page"><div id="content"><div class="post">
<p class="meta">Your image has not been uploaded  
<img src="images/img08.png" alt="bullet"></p><div class="entry">'; 

echo 'You can only upload 1 render per hour.';

echo '</div></div></div>';
}

[joel24]

thats because you're not checking to see if the query returns a row, you're just checking if the query works

 

$last = @mysql_query("SELECT * FROM users WHERE username='{$_POST['username']}' AND last < NOW() - INTERVAL 1 HOUR");

if (mysql_num_rows($last)) {
upload photo
} else {
don't upload
}