Jump to content

Passing a Now() value to a database, HELP.

SauloA

By SauloA
in PHP Coding Help

 Share

Recommended Posts

SauloA Member

SauloA

Posted
Posted
I created a form to insert a record.  There is a a text box that is supposed to submit the date of when record is posted.  I'm trying to submit a value of Now() to the database but my datetime field in my MySQL database stored '0000-00-00 00:00:00'.  The problem seems to be the value of Now().

Here's my code.  The red is where I think the problem lies.  I'd appreciate any help.

<body>
<h1>Admin Home Page</h1>
<p>Welcome <?php echo $_SESSION['MM_Username']; ?> </p>
<p><a href="logout.php">Logout</a></p>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
 <table>
 <caption>Insert New Review</caption>
   <tr valign="baseline">
     <td nowrap="nowrap" align="right">Review Date:</td>
     [color=red]<td><input type="text" name="urev_date" value="Now()" size="32" /></td>[/color]
   </tr>
Link to comment
Share on other sites
SauloA Member

SauloA

Posted
  • Author
Posted
Here's the insertion code I'm using.  I pasted all my code just in case.

<?php require_once('../Connections/connBlog.php'); ?>
<?php require_once('../Connections/connBlog.php'); ?><?php
if (!isset($_SESSION)) {
 session_start();
}
$MM_authorizedUsers = "1,2";
$MM_donotCheckaccess = "false";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {
 // For security, start by assuming the visitor is NOT authorized.
 $isValid = False;

 // When a visitor has logged into this site, the Session variable MM_Username set equal to their username.
 // Therefore, we know that a user is NOT logged in if that Session variable is blank.
 if (!empty($UserName)) {
   // Besides being logged in, you may restrict access to only certain users based on an ID established when they login.
   // Parse the strings into arrays.
   $arrUsers = Explode(",", $strUsers);
   $arrGroups = Explode(",", $strGroups);
   if (in_array($UserName, $arrUsers)) {
     $isValid = true;
   }
   // Or, you may restrict access to only certain users based on their username.
   if (in_array($UserGroup, $arrGroups)) {
     $isValid = true;
   }
   if (($strUsers == "") && false) {
     $isValid = true;
   }
 }
 return $isValid;
}

$MM_restrictGoTo = "default.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {  
 $MM_qsChar = "?";
 $MM_referrer = $_SERVER['PHP_SELF'];
 if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
 if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0)
 $MM_referrer .= "?" . $QUERY_STRING;
 $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
 header("Location: ". $MM_restrictGoTo);
 exit;
}
?><?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
 $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
 $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
 $insertSQL = sprintf("INSERT INTO user_review_tbl (urev_date, shop_id, urev_approved, user_id, urev_review) VALUES (%s, %s, %s, %s, %s)",
                      GetSQLValueString($_POST['urev_date'], "text"),
                      GetSQLValueString($_POST['shop_id'], "int"),
                      GetSQLValueString(isset($_POST['urev_approved']) ? "true" : "", "defined","1","0"),
                      GetSQLValueString($_POST['user_id'], "int"),
                      GetSQLValueString($_POST['urev_review'], "text"));

 mysql_select_db($database_connBlog, $connBlog);
 $Result1 = mysql_query($insertSQL, $connBlog) or die(mysql_error());

 $insertGoTo = "home.php";
 if (isset($_SERVER['QUERY_STRING'])) {
   $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
   $insertGoTo .= $_SERVER['QUERY_STRING'];
 }
 header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_connBlog, $connBlog);
$query_rsShop = "SELECT shop_id, shop_name FROM shop_tbl ORDER BY shop_name ASC";
$rsShop = mysql_query($query_rsShop, $connBlog) or die(mysql_error());
$row_rsShop = mysql_fetch_assoc($rsShop);
$totalRows_rsShop = mysql_num_rows($rsShop);

$maxRows_rsReviews = 10;
$pageNum_rsReviews = 0;
if (isset($_GET['pageNum_rsReviews'])) {
 $pageNum_rsReviews = $_GET['pageNum_rsReviews'];
}
$startRow_rsReviews = $pageNum_rsReviews * $maxRows_rsReviews;

mysql_select_db($database_connBlog, $connBlog);
$query_rsReviews = "SELECT urev_id, urev_date, urev_approved, shop_id FROM user_review_tbl ORDER BY urev_date DESC";
$query_limit_rsReviews = sprintf("%s LIMIT %d, %d", $query_rsReviews, $startRow_rsReviews, $maxRows_rsReviews);
$rsReviews = mysql_query($query_limit_rsReviews, $connBlog) or die(mysql_error());
$row_rsReviews = mysql_fetch_assoc($rsReviews);

if (isset($_GET['totalRows_rsReviews'])) {
 $totalRows_rsReviews = $_GET['totalRows_rsReviews'];
} else {
 $all_rsReviews = mysql_query($query_rsReviews);
 $totalRows_rsReviews = mysql_num_rows($all_rsReviews);
}
$totalPages_rsReviews = ceil($totalRows_rsReviews/$maxRows_rsReviews)-1;
?>
Link to comment
Share on other sites
anatak Advanced Member

anatak

Posted
Posted
$insertSQL = sprintf("INSERT INTO user_review_tbl (urev_date, shop_id, urev_approved, user_id, urev_review) VALUES (%s, %s, %s, %s, %s)",
                      GetSQLValueString($_POST['urev_date'], "text"),
                      GetSQLValueString($_POST['shop_id'], "int"),
                      GetSQLValueString(isset($_POST['urev_approved']) ? "true" : "", "defined","1","0"),
                      GetSQLValueString($_POST['user_id'], "int"),
                      GetSQLValueString($_POST['urev_review'], "text"));

I don't think you can insert Now() like that.

I think but I am not sure you have to insert a Now() value like
INSERT INTO table (urev_date)
SELECT Now();

You can help yourself by printing the query to your screen to have a look at it.
echo $insertSQL
Link to comment
Share on other sites
anatak Advanced Member

anatak

Posted
Posted
I am sorry I am not sure myself how to do this.
I always use datetime fields to get the current time of an update.
an update of a row (or insert of a new ros) will automatically update or insert the current time into the first date time field in a table (NOTE the FIRST).

I suggest you ask the moderator to move this question to mysql help.
or look for a php function to do this.
in php I would do something like
$date = date()
look at the date() function and make sure you format it correctly for the mysql format (YYYY-MM-DD if I am not mistaken)

you have to chose what you want to do php or mysql
your choice is dependant on what you want to do with the date field.

for the mysql option it would be something like

$insertSQL = sprintf("INSERT INTO user_review_tbl (urev_date, shop_id, urev_approved, user_id, urev_review) VALUES (%s, %s, %s, %s, %s)",
                      SELECT Now(),
                      GetSQLValueString($_POST['shop_id'], "int"),
                      GetSQLValueString(isset($_POST['urev_approved']) ? "true" : "", "defined","1","0"),
                      GetSQLValueString($_POST['user_id'], "int"),
                      GetSQLValueString($_POST['urev_review'], "text"));

disclaimer.
I did not have time to test this but I think this is more or less how to do it.
I don't use a lot of insert select statements myself so I might be hugely mistake in SQL syntax

anatak


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.

×
 Share
Go to topic listing
×
×
  • 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.