Jump to content

PHP updating SQL


phily245

Recommended Posts

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?

Link to comment
Share on other sites

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);
		}
}

Link to comment
Share on other sites

<?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();
		}
    }
}
}
?>

 

Link to comment
Share on other sites

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 :/

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.