We help IT Professionals succeed at work.

php mysql statement

doctorbill
doctorbill asked
on
I am trying to get the sql statement (below) to run when my php page loads or from a link in the page - I would  like a solution to both if possible

The statement is as follows:
UPDATE orders SET Delivered='Archived' WHERE Date >= '07/09/2011'

Can someone please show me EXACTLY what the script needs to be to work  as a Page onload and from a link in the page
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2016
Commented:
Couple of problems here... "Date" is a MySQL reserved word, and should not be used for a column name.  And the format of the date string should be the ISO-8601 DATETIME string.  You should define the column with data type DATE or DATETIME.

More information on how to work with dates and times in PHP and MySQL is available in this article:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Please post the CREATE TABLE statement for the "orders" table and I'll try to suggest some ways to achieve what you want.  Thanks, ~Ray
No points please for this post.l

Ray is correct.  I just want to add that your variables and column names should make more sense to the maintenance engineer than "date."  Instead you could headers such as TransactionDate (or TrxDate or TransDate), RegistrationDate (RegDate), ExpirationDate (ExpDate).

Author

Commented:
Here is the code from my php page:

<?php require_once('../../../Connections/Onecallorders.php'); ?>

<?php          ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>


</head>

<body>
</body>
</html>

The <?php require_once('../../../Connections/Onecallorders.php'); ?>
string connects to my database where the orders table resides. This database is already being used by lots of other pages. Is this all that is necesary in order to add the sql statements I need?

The connection string has the following information:
<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_Onecallorders = "localhost";
$database_Onecallorders = "1callorders";
$username_Onecallorders = "root";
$password_Onecallorders = "password";
$Onecallorders = mysql_pconnect($hostname_Onecallorders, $username_Onecallorders, $password_Onecallorders) or trigger_error(mysql_error(),E_USER_ERROR);
?>
To connect, all you need is the computer name (localhost), a user name and a password.  My hope is you aren't actually using "root" as your user name and "password" as your password.

I also suggest that $Onecallorders be called something like $Connection.  


So, I guess the answer is yes.  The caveat is that I'm uncomfortable with the approach.

Author

Commented:
Correct - the password is not password

So - what is the string I need to put into the page between the php brackets to get my script working:
UPDATE orders SET Delivered='Archived' WHERE Date >= '07/09/2011'

Most Valuable Expert 2011
Top Expert 2016
Commented:
Let me try this again...

Please post the CREATE TABLE statement for the "orders" table and I'll try to suggest some ways to achieve what you want.  Thanks, ~Ray

Author

Commented:
I don't understand why I need a create table statement when I am accessing the database table already (see above)

Author

Commented:
Here is the entire page code:

<?php require_once('../../../Connections/Onecallorders.php'); ?>

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_Onecallorders, $Onecallorders);
$query_orders = "SELECT * FROM orders";
$orders = mysql_query($query_orders, $Onecallorders) or die(mysql_error());
$row_orders = mysql_fetch_assoc($orders);
$totalRows_orders = mysql_num_rows($orders);

$sqldpdte = 'UPDATE orders SET Delivered=\'Delivered\' WHERE Date >= \'07/09/2011\'';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>


</head>

<body>
</body>
</html>
-----

This is the line I want to run:
$sqldpdte = 'UPDATE orders SET Delivered=\'Delivered\' WHERE Date >= \'07/09/2011\'';


Most Valuable Expert 2011
Top Expert 2016
Commented:
You don't need the CREATE TABLE statement.  I need the CREATE TABLE statement.  If I have it, I will be able to show you the right way to do what you want to do!
You have to call

 mysql_query ( $sqldpdte );

or

 mysql_query ( $sqldpdte )  or die ( mysql_error() );
Related to Ray's request -- he is trying to help you and he knows what he is talking about when it comes to PHP.  I strongly suggest you give him what he wants.
Reading more carefully, I gave you only part of a solution.  Ray is trying to solve a bigger (and more important) problem.

Author

Commented:
how do I call it

Author

Commented:
CREATE TABLE `1callorders`.`new` (
`ID` INT( 12 ) NOT NULL AUTO_INCREMENT ,
`Delivered` VARCHAR( 56 ) NOT NULL ,
`Date` VARCHAR( 56 ) NOT NULL ,
`test3` VARCHAR( 56 ) NOT NULL ,
PRIMARY KEY ( `ID` )
) ENGINE = InnoDB

Author

Commented:
Thanks very much everyone - it now works:

Code:
<?php require_once('../../../Connections/Onecallorders.php'); ?>

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_Onecallorders, $Onecallorders);
$query_orders = "SELECT * FROM orders";
$orders = mysql_query($query_orders, $Onecallorders) or die(mysql_error());
$row_orders = mysql_fetch_assoc($orders);
$totalRows_orders = mysql_num_rows($orders);

$sqldpdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date > "07/09/2011"';
$updte = mysql_query ( $sqldpdte )  or die ( mysql_error() );
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>


</head>

<body>
<form id="form1" name="form1" method="post" action="<?php $updte ?>">
  <label>
    <input type="submit" name="button" id="button" value="Submit" />
  </label>
</form>
</body>
</html>

----------------
I am using a form to call the query.
Last question - how can I do the same as a body onload action ?
We are going to need to know more about what you are trying to do.

It sounds like you want to call PHP from Javascript.  The problem is that PHP is server sided and finishes running before the page is sent to the client (close enough anyway).  Javascript is client sided and runs only after the client receives the page from the server.

But perhaps there's another way to solve the problem.  That's why I'm asking what you are trying to do (or why you are trying to do it).

Author

Commented:
ok
I have been asked to create a page which allows a user to update all orders from their current status of "Ordered" to "In Progress" where the date of the order is being specified in the script.
As I mentioned above, I have now got this working with all your help.

Script:
$sqlupdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date >= "07/09/2011"';
$updte = mysql_query ( $sqlupdte )  or die ( mysql_error() );

The $updte is being called from a form button in the page.
What would be really great would be if the Date being used was always 2 weeks behind the current date.
Example:
$sqlupdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date >= "2 weeks behind current date"';
I know the php code for current date but I do not know how to substitute it into the $sqlupdte and make it behave as if it was 14 days ago

Anyone please ?
Do this help?



<?php
$TwoWeeksAgo = time() - ( 14 * 24 * 60 * 60 );

printf ( "Two weeks ago = %s\n", date ( "m/d/y", $TwoWeeksAgo ));
?>

Open in new window

So what you could try is (untested)
$TwoWeeksAgo = time() - ( 14 * 24 * 60 * 60 );
$sqlupdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date >= ' . date ( "m/d/y", $TwoWeeksAgo );

Open in new window


or
$TwoWeeksAgo = date ( "m/d/y", time() - ( 14 * 24 * 60 * 60 ));
$sqlupdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date >= ' . $TwoWeeksAgo );

Open in new window

Author

Commented:
Thanks  - I will try this and get back to you

Author

Commented:
None of the scripts above work with the 2 weeks ago :

This does not work:
$TwoWeeksAgo = date ( "m/d/y", time() - ( 14 * 24 * 60 * 60 ));
$sqlupdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date >= ' . $TwoWeeksAgo );

Thos does not work:
$TwoWeeksAgo = time() - ( 14 * 24 * 60 * 60 );
$sqlupdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date >= ' . date ( "m/d/y", $TwoWeeksAgo );

Author

Commented:
I have an inputbox called txtDate in the page form. Could we use this as the input variable so that when the script is run it updates all records older than that date entered in the text box.
I didn't test the code.  I still haven't completely tested the code but this should be better.

<?php
$TwoWeeksAgo = date ( "m/d/y", time() - ( 14 * 24 * 60 * 60 ));

$sqlupdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date >= "' . $TwoWeeksAgo . '"';

echo $sqlupdte . PHP_EOL;
?>

Open in new window


My output is
UPDATE orders SET Delivered = "Delivered" WHERE Date >= "10/24/11"

Open in new window


If that isn't in the form you want, please say what you want (give an example).

Author

Commented:
This is very close to working but the records are being updated even if the "Date" in a record is more recent than the TwoWeeksAgo value:

I have changed the date format to reflect the full year and for d/m/Y format:

<?php
$TwoWeeksAgo = date ( "d/m/Y", time() - ( 14 * 24 * 60 * 60 ));
$sqlupdte = 'UPDATE orders SET Delivered = "In Progress" WHERE Date <  "' . $TwoWeeksAgo . '"';
$updte = mysql_query ( $sqlupdte )  or die ( mysql_error() );
echo $sqlupdte . PHP_EOL;
?>

The print output is as follows and looks correct:
UPDATE orders SET Delivered = "In Progress" WHERE Date < "25/10/2011"
Good point.  We probably have to get the date into MYSQL format.  How about using this
<?php
$TwoWeeksAgo = date ( "Y-m-d H:i:s", time() - ( 14 * 24 * 60 * 60 ));

$sqlupdte = 'UPDATE orders SET Delivered = "Delivered" WHERE Date >= "' . $TwoWeeksAgo . '"';

echo $sqlupdte . PHP_EOL;
?>

UPDATE orders SET Delivered = "Delivered" WHERE Date >= "2011-10-25 11:49:18"

Open in new window

Author

Commented:
Problem: my date format saved in the database is saved as text and has the format:

08/11/2011  (d/m/Y)
That is a problem.  The only solution I can think of is slow.  It works but it's slow.

If it's not possible (or practical) to change the column or add a second column suitable for sorting then the only solution I can think of is to use PHP.  I expect this will be a slow process.  Here goes

Select all records where Delivered != "Delivered"
Loop through them
    if the date is 2+ weeks old, UPDATE the row using a reasonable WHERE

Open in new window


If you have a unique ID for the record use that as the WHERE.  

Hopefully that's enough to solve it (even if it's not elegant).

Author

Commented:
Thanks everyone - very much a team effort

Explore More ContentExplore courses, solutions, and other research materials related to this topic.