PHP Code Help Needed

[dual_alliance]

Hello,

l attempted to make a code which would run via CRON once a day that made it so it -1 from anyone who has been banned.  However after trying it didn't work... and l deleted the code by mistake.  But could someone please show me how to make the code that will update the banned days column and subtract 1 from any user that is their?

Thankyou,

dual_alliance

[b]Edit: [/b] Found the code of my hd, see bottom of page for it except it doesnt work.

[.josh]

we have no way of giving you an exact query string without knowing what your fields are or how it is you are going about keeping track of banned people.  please give some more info.

p.s.- moving to sql forums.

[printf]

Can you show your db scheme, relating to the ban user table...


me!

[dual_alliance]

Oh ok ... DB is as follows:

DB name: ban_mail

Rows:
ban.id
ban.username
ban.days
ban.reason

I hope that helps

[b]Edit: [/b] I no how to do the MySQL side of it, its just the PHP side of things.  And l think l posted by accident in the wrong catagory.

[.josh]

okay if you already know your query string then just do a simple connection and query script:

[code]
<?php
  $conn = mysql_connect('localhost','username','password');
  $db = mysql_select_db('dbname',$conn);
  $sql = "query string here";
  $result = mysql_query($sql, $conn);
?>
[/code]

that's a barebones version.  dunno if you wanna throw in some error trapping/logging or whatever. Anyways. just make a file called blah.php or whatever and point your cron job to it.

edit: p.s.- I guess this belongs in the php forums after all ;D

[dual_alliance]

Hey l found backup of the code after searching my hole hd ;D, well heres what l had...

[code=php:0]<?php
require_once('settings.php');
// Connect to the database
@mysql_connect("$dbhost", "$dbusername", "$dbpassword") or die ('Cannot connect to MySQL database');
@mysql_select_db("$dbname") or die ('Cannot select database');

$i = 1;
$sql = "SELECT `ban.days` FROM `banned` WHERE `ban.days` >= '$i' ";
$result = mysql_query($sql) or die("Problem with the query: $sql <br>" . mysql_error());
$row = mysql_fetch_assoc($result);
$i2 = $row['ban.days'];
$ri = $i2 - $i;
$sql1 = "UPDATE `banned` SET `ban.days` = '$i2 - 1'  WHERE `ban.days`= '$i2' ";
$result1 = mysql_query($sql1) or die(mysql_error());
?>[/code]

Maybe this gives you more of an idea of what l am trying to do.

[.josh]

okay so now that you have your old script back...problem solved?

[dual_alliance]

Not really cause it doesn't work, as it only updates the first user it find then converts all the days to that so e.g first user is banned for 4 days, next 6, next 8.  When l run the code it converts them all to 3.

[.josh]

[code]
<?php
  $conn = mysql_connect('localhost','username','password');
  $db = mysql_select_db('dbname',$conn);
  $sql = "update tablename set column = column - 1 where column > 0";
  $result = mysql_query($sql, $conn);
?>
[/code]

[dual_alliance]

What! that simple, l was thinking l was going to need while loops and all that.  Thankyou very much Crayon Violent! ;D

[.josh]

thus is the magic of db queries :)

p.s.- like i said earlier, that script is barebones and does nothing for error trapping/logging so you might wanna look into all that, unless you don't care about any of that...

[dual_alliance]

Well l have named the file random so no one will be able to guess it manually, so l don't think logging will be nesasary.

[.josh]

i was thinking more like if for some reason the script failed to connect to the db or update the db properly, you should have an error trapping/logging mechanism in place to log that stuff so if something goes wrong, you will have clues.

[dual_alliance]

Hmm you got a point there...  l probably will in the future.

Since l don't need any more help l'll lock this :)