UPDATE of database table

[jbis2k]

Good morning:

 

This is the output of my test database table:

 

2011-05-30 Chevrolet Monte Carol 1975 9000 15000 James Bond's 007-1212 james@bond.com B I am setting the rocord for bond's most active movies.

2011-05-31 Dodge Stratus 2008 5000 7500 James Cagney 555-1818 jc@hollywood.com S This is not going to be the best of time's. Ain't it?

 

Within my table I have two important fields--the record date-created field shown on the left and the (hidden) EXPIRED field--on which the UPDATE function depends.  This is my code for (wherein lies the problem) UPDATING the record within my table and setting the EXPIRED field to 'Y' for filtering and easy deletion of the record(s).

<?php
// Connect to database.
include("connect.php");
// Access the database 

@mysql_select_db($database,$con) or die( "Unable to select database");

$result = mysql_query("SELECT * FROM Autos"); //read all records into array

while($row = mysql_fetch_array($result)) //loop through array 
{
  if (1 - strcmp(date("Y-m-d"),$row['DateCreated']) == 0) //compare current date with record date and act when result is zero 
  {
    mysql_query("UPDATE Autos SET Expired = 'Y' WHERE Expired == 'N'"); //if ready to expire then update table EXPIRED field  
  } else {
     if ($row['BuyerSeller'] == 'B') {
     echo "Contact buyer below";
   } elseif ($row['BuyerSeller'] == 'S') {
     echo "Contact seller below";
   }
   echo "<br />";
    echo $row['DateCreated'] . " " . $row['Make'] . " " . $row['Model'] . " " . $row['Year'] . " " . $row['MinPrice'] . " " . $row['MaxPrice'] . " " . $row['POC'] . " " . $row['POCPhone'] . " " . $row['POCEmail'] . " " . $row['Notes'];
    echo "<br />";
  }
}
mysql_close($con)
?>

Lastly, does the mysql_close function with the single parameter $con also close the database?

 

MOD EDIT: code tags added.

[fugix]

what exactly is your issue? and mysql_close() will close the connection to the mysql server that is associated with the link identifier...but is not actually necessary here because the connection will automatically close when the script is finished executing...

[mikesta707]

I think you expect that php if statement to restrict the rows where your expired flag is set to yes, but in reality, all that will happen is every single row where expired is "N"is set to "Y", which basically sets all your rows to "Y" expired.

 

If you have a unique or primary ID column, you could use that in your where clause also, something like

mysql_query("UPDATE Autos SET Expired = 'Y' WHERE Expired == 'N' AND id={$row['id']}");

assuming that the column was called id, but this way is somewhat inefficient. You could accomplish what you want to do (basically flagging old entries in the table) with a single update query. see the date and time functions mysql has built in. Note, these only work on the date/time column types, which I am assuming you are using

 

[xyph]

@mikesta707

 

WHERE Expired == 'N'

 

should be

 

WHERE Expired = 'N'

[mikesta707]

@mikesta707

 

WHERE Expired == 'N'

 

should be

 

WHERE Expired = 'N'

 

Oh yes, thank you for pointing this out.

[jbis2k]

Finally getting back to the code.  Okay.  First of all, thanks to all who responded here and now.  After reading your replies, I gave some thought as to what exactly I was trying to accomplish.  I came to the conclusion that it's best to simply DELETE the record which has met its date limit.  In my code you'll see (1 - ....).  This is only for test purposes.  I am learning PHP and came across this code by chance.  So here is my new updated code:

<?php
// Connect to database.
include("connect.php");
// Access the database 

@mysql_select_db($database,$con) or die( "Unable to select database");

$result = mysql_query("SELECT * FROM Autos");

while($row = mysql_fetch_array($result))
{
  if (1 - strcmp(date("Y-m-d"),$row['DateCreated']) == 0) 
  {
   $update_rec = mysql_query("DELETE FROM Autos WHERE AutoNum == $row['AutoNum']");
   if (!$update_rec) {
      die('Invalid query: ' . mysql_error());
    }
  }
}
mysql_close($con)
?>

After reading all records from Autos into the $result array, I loop through each record and compare subtract 1 from the comparison of the current date with the record creation date.  This, I  know, is obvious to you.  It's easier for me if I elaborate too much so thanks for understanding.  For every record where 1 - the comparison is equal to zero, I would prefer to DELETE that record from the actual table. 

 

The code I'm using presents this error:

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in D:\hosting\7359420\html\WA\delete.php on line 16

 

And, yes, line 16 just happens to be where mysql_query("DELETE FROM is located.

[Pikachu2000]

When posting code, enclose it within the forum's

 . . . 

BBCode tags.

[jbis2k]

Oh, I see.  Thanks for the

 . . . 

advice.  I was unaware that I had to do so.

[Pikachu2000]

When including a quoted array index in a quoted string, enclose it in {} curly braces, otherwise you'll get that error.

$update_rec = mysql_query("DELETE FROM Autos WHERE AutoNum = {$row['AutoNum']}");

 

EDIT: The comparison operator in SQL is a single =, not == BTW.

[jbis2k]

Thank you all for the help with the UPDATE (DELETE) answer provided.  I consider this topic closed and solved.