PHP updating SQL

[phily245]

Hi,

On the website i'm making, there is a page that shows the special offers. These are stored in a database called shop_specials. When the php function loads the active special offers (1) onto the page, there is another function called which is meant to run the query, then update the special_active column to 1 if the current date is more than or equal to the special_startdate and less than or equal to the special_enddate, or update the special_active column to 0 if the current date is less than the special_startdate or greater than the special_enddate. Here is the code:

 

 

$query = mysql_query("SELECT * FROM shop_specials")or die(mysql_error());

$startdate = date("d m y", $fetch["special_startdate"]);

$enddate = date("d m y", $fetch["special_enddate"]);

    if ($startdate <= date("d m Y") && date("d m Y") <= $enddate){
        $update = mysql_query("UPDATE shop_specials SET special_active = 1 WHERE special_id = " . $fetch["special_id"] ."");
        mysql_query($update);
	}

            elseif (date("d m Y") > $enddate){
            $update = mysql_query("UPDATE shop_specials SET special_active = 0 WHERE special_id = " . $fetch["special_id"] ."");
            mysql_query($update);
		}

            elseif ($startdate > date("d m Y")){
            $update = mysql_query("UPDATE shop_specials SET special_active = 0 WHERE special_id = " . $fetch["special_id"] ."");
            mysql_query($update);
		}
}

Is this just a simple mistake like I've missed something out or a big error?

[phily245]

Obviously, I've connected to the database and the include file is a php file, so all the functions are wrapped in php tags.

[revraz]

You don't say what the actual problem is.

[phily245]

Whoops! n00bish error there on my part, It isn't updating the special_active field and the products display when I don't have the while loop in, but then don't display when I put the while loop in.

[EdwinPaul]

Y is unequal to y in date. Y is 4 digits, y is 2 digits.

[phily245]

I changed the year date values all to y, but still no good. here is the new code:

 

$query = mysql_query("SELECT * FROM shop_specials")or die(mysql_error());
                        while($fetch = mysql_fetch_array($query)){
    $startdate = date("d m y", $fetch["special_startdate"]);
    $enddate = date("d m y", $fetch["special_enddate"]);
    if ($startdate <= date("d m y") && date("d m y") <= $enddate){
        $update = mysql_query("UPDATE shop_specials SET special_active = 1 WHERE special_id = " . $fetch["special_id"] ."");
        mysql_query($update);
	}
            elseif (date("d m y") > $enddate){
            $update = mysql_query("UPDATE shop_specials SET special_active = 0 WHERE special_id = " . $fetch["special_id"] ."");
            mysql_query($update);
		}
            elseif ($startdate > date("d m y")){
            $update = mysql_query("UPDATE shop_specials SET special_active = 0 WHERE special_id = " . $fetch["special_id"] ."");
            mysql_query($update);
		}
}

[EdwinPaul]

<?php
        $update = mysql_query("UPDATE shop_specials SET special_active = 1 WHERE special_id = " . $fetch["special_id"] ."");
        mysql_query($update);
?>

This looks wrong. You perform mysql_query twice.

I rewrote your script:

<?php
$query = "SELECT * FROM shop_specials";
$result=mysql_query($query);
if($update === FALSE){
echo 'SELECT failed: '.mysql_error();
exit();
}

while($fetch = mysql_fetch_array($result)){

    $startdate = date("d m y", $fetch["special_startdate"]);
    $enddate = date("d m y", $fetch["special_enddate"]);

    if ($startdate <= date("d m y") && date("d m y") <= $enddate){

        $query = "UPDATE shop_specials SET special_active = 1 WHERE special_id = '".$fetch['special_id']."'";
        $update = mysql_query($query);
        if($update === FALSE){
		echo 'Update 1 failed: '.mysql_error();
		exit();
}
   }else{

if ((date("d m y") > $enddate) || ($startdate > date("d m y"))){

            $query="UPDATE shop_specials SET special_active = 0 WHERE special_id = '".$fetch['special_id']."'";
            $update = mysql_query($query);
		if($update === FALSE){
			echo 'Update 2 failed: '.mysql_error();
			exit();
		}
    }
}
}
?>

 

[phily245]

I ran it and exactly the same problem (not updating in the database and not displaying the product) happened. On the select query:

$query = "SELECT * FROM shop_specials";
$result=mysql_query($query);
if($update === FALSE){
echo 'SELECT failed: '.mysql_error();
exit();
}

Should it be:

$query = "SELECT * FROM shop_specials";
$result=mysql_query($query);
if($result === FALSE){
echo 'SELECT failed: '.mysql_error();
exit();
}

?

I ran it with that change but it still wouldn't update the db :/

[EdwinPaul]

Yes, my mistake.

Start debugging: put echos between statements with variables concerned.

<?php
$query = "SELECT * FROM shop_specials";
$result=mysql_query($query);
if($result=== FALSE){
echo 'SELECT failed: '.mysql_error();
exit();
}
else{ echo 'SELECT successfull';}                               // debugging

while($fetch = mysql_fetch_array($result)){

    $startdate = date("d m y", $fetch["special_startdate"]);
    echo 'special_startdate:'.$row['special_startdate'];                         // debugging
    echo 'startdate:'.$startdate;                                                         // debugging
    $enddate = date("d m y", $fetch["special_enddate"]);
    echo 'special_enddate:'.$row['special_enddate'];                         // debugging
    echo 'enddate:'.$enddate;                                                         // debugging
    if ($startdate <= date("d m y") && date("d m y") <= $enddate){
        echo 'first if';                                                         // debugging
        $query = "UPDATE shop_specials SET special_active = 1 WHERE special_id = '".$fetch['special_id']."'";
        $update = mysql_query($query);
        if($update === FALSE){
		echo 'Update 1 failed: '.mysql_error();
		exit();
}
   }else{

if ((date("d m y") > $enddate) || ($startdate > date("d m y"))){

            $query="UPDATE shop_specials SET special_active = 0 WHERE special_id = '".$fetch['special_id']."'";
            $update = mysql_query($query);
		if($update === FALSE){
			echo 'Update 2 failed: '.mysql_error();
			exit();
		}
    }
}
}
?>

etcetera

[revraz]

You should be checking your query's each time you execute them for errors.