[SOLVED] PHP/Mysql help required

[wookie]

I'm struggling with this and need a little help. What I want to be able to do is have an text box where I insert a file name for example "myphoto.jpg" and when I click submit it runs the file below that then replaces the "@@" at the end of the query below and updates the database, this will be done once daily.

 

Here is the file.

 

<?php

$dbuser="xx";
$dbpass="xxx";
$dbname="xxxx";
$chandle = mysql_connect("localhost", $dbuser, $dbpass) 
    or die("Connection Failure to Database");
echo "Connected to database server<br>";
mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found." . $dbuser);


$sql = 'update '.$CONFIG['TABLE_PICTURES'].' SET `potd` = 2, WHERE `potd` = 1 AND SET `potd` = 1 WHERE `filename` = @@;
  cpg_db_query($sql);


mysql_close($chandle);

?>

 

The sql is more than likely incorrect, but the field "potd" will be set as "1" and will need to be changed to "2", then the filename field should dictate which row the "potd" field needs to be set at "1"

 

Can you help me or do you need more information to facillitate this request?

 

Many thanks in advance!

 

 

Wookie

[Psycho]

You would first need a form to submit to this page. You didn't specify what the field names would be, so I am making some assumptions here.

 

From what I can deduce, POTD stands for Picture of the Day. So, you want that there is only one and only one. I have no idea why you are using 1 and 2. Instead you should be using 0 (false) and 1 (true). This is a standard programming construct. So, first you want to set all the pictures in the table to 0 (false), then set the new POTD to 1 (true):

 

<?php

$dbuser="xx";
$dbpass="xxx";
$dbname="xxxx";
$chandle = mysql_connect("localhost", $dbuser, $dbpass) 
    or die("Connection Failure to Database");
echo "Connected to database server<br>";
mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found." . $dbuser);

//Set POTD for all pictures to false
$sql = "UPDATE ".$CONFIG['TABLE_PICTURES']." SET `potd` = 0";
cpg_db_query($sql);

//Set new POTD to true
$sql = "UPDATE ".$CONFIG['TABLE_PICTURES']." SET `potd` = 1 WHERE `filename`= '".$_POST['potd']."'";
cpg_db_query($sql);

mysql_close($chandle);

?>

[wookie]

Hi, and thanks for the quick reply! I'll be honest and say I didnt expect an answer of any sort for at least 24 hours!

 

All pics are by default set at "0", set at "1" they are Photo of the day and set at "2" they are archived POTD's, thanks for the info re programming constructs, something new to me.

 

I'll have a crack at this overnight and see how I get on now, thanks once again!

 

Wookie

[wookie]

Okay, I made a few changes and found a few things that needed adding via another forum, I also changed the "filename" to "pid" (picture id number)

 

I need to add one more thing now and that is the time.

 

Next to the field "potd" I have "potd_date" into which I need to record the unixtime. So when I am entering the pid number and the script is changing "potd" to 1 (true), I also want it to add the unixtime to "potd_date".

 

Here is what I have so far and its working very well.

 

 

  <?php
define('IN_COPPERMINE', true);
define('PICMGR_PHP', true);

require('include/init.inc.php');

if (!(GALLERY_ADMIN_MODE || USER_ADMIN_MODE)) cpg_die(ERROR, $lang_errors['access_denied'], __FILE__, __LINE__);

$timestamp = time();

$dbuser="xxxx";
$dbpass="fxxxx";
$dbname="xxxx";  //the name of the database
$chandle = mysql_connect("localhost", $dbuser, $dbpass) 
    or die("Connection Failure to Database");
echo "Connected to database server<br>";
mysql_select_db($dbname, $chandle) or die ($dbname . " Database not found." . $dbuser);

//Set POTD to archive
cpg_db_query("UPDATE ".$CONFIG['TABLE_PICTURES']." SET `potd` = 2 WHERE `potd` = 1 ");


//Set new POTD to true
cpg_db_query("UPDATE ".$CONFIG['TABLE_PICTURES']." SET `potd` = 1 WHERE `pid`= '".$_POST['potd']."'");

mysql_close($chandle);

?>

 

 

Many thanks!

 

Wookie

[Psycho]

Change this

cpg_db_query("UPDATE ".$CONFIG['TABLE_PICTURES']." SET `potd` = 1 WHERE `pid`= '".$_POST['potd']."'");

 

To this:

cpg_db_query("UPDATE ".$CONFIG['TABLE_PICTURES']." SET `potd` = 1, potd_date = NOW() WHERE `pid`= '".$_POST['potd']."'");

[wookie]

Thanks mjdamato!

 

I'll mark this one as solved now.

 

Wookie