[SOLVED] Unable to add date to mysql

seeya · January 19, 2008

the date isnt added in my sql. did i miss out something?

the field in my sql is called 'sDate' and its datatype is 'DateTime'

<?php
require_once("config.php");

verify_member_logined();


if (!isset($_POST['Submit_frmCheckout'])) {
header('Location: checkout.php');
exit();
}

// Connecting, selecting database
$link = mysql_connect($mysql_host, $mysql_user, $mysql_password) or die('Could not connect: ' . mysql_error());
mysql_select_db($mysql_database) or die('Could not select database');

$strNewOrderID_flag = FALSE;

// Performing SQL query to generate unique OrderID
while (!$strNewOrderID_flag) {

$strNewOrderID = date("YmdHis");

// Performing SQL query
$query = "SELECT * FROM tbl_order WHERE sOrderid='" . trim($strNewOrderID) . "'";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Fetch record to check whether new company already exists in database.
if (!($row = mysql_fetch_array($result, MYSQL_ASSOC))) {
	$strNewOrderID_flag = TRUE;
}
// Free resultset
mysql_free_result($result);
}

// Performing SQL query to insert order  and order's details
$query = "INSERT INTO tbl_order (sOrderid,SNRIC,sCardtype,sCardnumber,sCardexpiry,sDeliverycost,sGrandtotal,sStatus,sDate) VALUES ('". trim($strNewOrderID) . "','".trim($_SESSION['SNRIC'])."','".trim($_POST['sPayVia']). "','".trim($_POST['sCardNumber1'])."-".($_POST['sCardNumber2'])."-".($_POST['sCardNumber3'])."-".($_POST['sCardNumber4'])."','".trim($_POST['dtExpiryDay'])."-".($_POST['dtExpiryMonth'])."-".($_POST['dtExpiryYear'])."',".trim($_POST['dblShippingCost']).",".trim($_POST['dblGrandTotal']).",'new')";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

if ($result) {
if ($_SESSION['arrProductName'] <> "") {
	foreach ($_SESSION['arrProductName'] as $key => $value) {

		$query = "INSERT INTO tbl_orderdetails (sOrderID,Pcode,sQuantity,sProductprice) VALUES ('". trim($strNewOrderID) . "','" . trim(stripslashes($value)) . "'," . $_SESSION['arrProductQty'][$key] . "," . $_SESSION['arrProductUnitPrice'][$key] . ")";
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());
	}
}
}


// Performing SQL query to extract member's particulars
$query = "SELECT * FROM tbl_members WHERE SNRIC='" . trim($_SESSION['SNRIC']) . "'";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Fetch record to be edited
if ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

$strUsername = trim($row['SNRIC']);
$strName = trim($row['sName']);
$strAddress = trim($row['sAddress']);
$strPostalCode = trim($row['sPostalcode']);
$strEmail = trim($row['sEmail']);
$strContact = trim($row['sContactnumber']);

// Close connection
mysql_close($link);

} else {
// Close connection
mysql_close($link);

header('Location: member_logout.php');
exit();
}


require_once("header.php");


?>

<style type="text/css">
<!--
body {
background-image: url(background.jpg);
}
-->
</style>
      <tr>
        <td width="700" rowspan="2" valign="top" bgcolor="#FFFFFF">
	<table width="700" border="0" cellspacing="2" cellpadding="2">
        <tr>
            <td>
			<img border="0" src="images/spacer.gif" width="1" height="10"><br>
			<table width="100%" borderColor="#000000" cellSpacing="0" cellPadding="4" align="center" border="1">
			<tr>
				<td bgcolor="#EEEEEE" class="font14" colspan="5" align="left">Complete</td>
			</tr>
			<tr>
				<td bgcolor="#FFFFAA" class="font14" colspan="5" align="left">[1] Order's Confirmation</td>
			</tr>
			<tr>
				<td colspan="5"><font color="FF0000"><STRONG>Please print out a copy of this confirmation for your reference. Thanks!</STRONG></font></td>
			</tr>
			<tr>
				<td colspan="2" valign="bottom"><strong>Order ID</strong></td><td colspan="3"><?php echo(trim($strNewOrderID)); ?></td>
			</tr>
			<tr>
				<td colspan="2" valign="bottom"><strong>Order Date</strong></td><td colspan="3"><?php echo(date("j M Y")); ?></td>
			</tr>
			<tr>
				<td colspan="2" valign="bottom"><strong>Pay via</strong></td><td colspan="3"><?php echo(trim($_POST['sPayVia'])); ?></td>
			</tr>
			<tr>
				<td bgcolor="#FFFFAA" class="font14" colspan="5" align="left">[2] Member's Shipping Particulars</td>
			</tr>
			<tr>
				<td colspan="2" valign="bottom"><strong>Name</strong></td><td colspan="3"><?php echo($strName); ?></td>
			</tr>
			<tr>
				<td colspan="2" valign="bottom"><strong>Address</strong></td><td colspan="3"><?php echo($strAddress); ?></td>
			</tr>
			<tr>
				<td colspan="2" valign="bottom"><strong>Postal Code</strong></td><td colspan="3"><?php echo($strPostalCode); ?></td>
			</tr>
			<tr>
				<td colspan="2" valign="bottom"><strong>Email</strong></td><td colspan="3"><?php echo($strEmail); ?></td>
			</tr>
			<tr>
				<td colspan="2" valign="bottom"><strong>Contact</strong></td><td colspan="3"><?php echo($strContact); ?></td>
			</tr>
			<tr>
				<td bgcolor="#FFFFAA" class="font14" colspan="6" align="left">[3] Cart Items</td>
			</tr>
			<tr>
				<td width="5%" valign="bottom">S/N</td>
				<td width="32%" valign="bottom">Product ID/Title</td>
				<td width="20%" valign="bottom">Unit Price</td>
				<td width="20%" valign="bottom">Qty</td>
				<td width="23%" valign="bottom">Sub-Total</td>
			</tr>
			<?php
			if ($_SESSION['arrProductName'] <> "") {
				$intCounter = 0;
				$dblTotalAmt = 0;
				foreach ($_SESSION['arrProductName'] as $key => $value) {
					$dblTotalAmt += $_SESSION['arrProductUnitPrice'][$key]*$_SESSION['arrProductQty'][$key];
				?>
				<tr>
					<td valign="top"><?php echo ($intCounter+1); ?></td>
					<td valign="top"><?php echo $key . "<br>" . stripslashes($value); ?></td>
					<td valign="top">SGD$<?php echo number_format($_SESSION['arrProductUnitPrice'][$key],2); ?></td>
					<td valign="top"><?php echo $_SESSION['arrProductQty'][$key]; ?></td>
					<td valign="top">SGD$<?php echo number_format($_SESSION['arrProductUnitPrice'][$key]*$_SESSION['arrProductQty'][$key],2); ?></td>
				</tr>
				<?php
					$intCounter++;
				}
				$dblShippingCost = 5;
				$dblGrandTotal = $dblTotalAmt + $dblShippingCost;
				echo("<tr><td bgcolor=#FFFFFF class=font14 colspan=4 align=right>Total Cart Amount: SGD$</td><td bgcolor=#FFFFFF class=font14 align=left>" . number_format($dblTotalAmt,2) . "</td></tr>");
				echo("<tr><td bgcolor=#FFFFFF class=font14 colspan=4 align=right>Shipping Cost: SGD$</td><td bgcolor=#FFFFFF class=font14 align=left>" . number_format($dblShippingCost,2) . "</td></tr>");
				echo("<tr><td bgcolor=#FFFFFF class=font14 colspan=4 align=right>Grand Total: SGD$</td><td bgcolor=#FFFFFF class=font14 align=left>" . number_format($dblGrandTotal,2) . "</td></tr>");
				$_SESSION['arrProductName'] = "";
				$_SESSION['arrProductQty'] = "";
				$_SESSION['arrProductUnitPrice'] = "";
			}
			?>
			</table>
            </td>
        </tr>
        </table>
        </td>

<?php
require_once("member_corners.php");

require_once("footer.php");
?>

beansandsausages · January 19, 2008

try

$date = NOW()

toplay · January 19, 2008

What burnside means is to use NOW() within an SQL insert.

So, replace your 'new' with now(). Example:

<?php

$query = "INSERT INTO tbl_order (sOrderid,SNRIC,sCardtype,sCardnumber,sCardexpiry,sDeliverycost,sGrandtotal,sStatus,sDate) VALUES ('". trim($strNewOrderID) . "','".trim($_SESSION['SNRIC'])."','".trim($_POST['sPayVia']). "','".trim($_POST['sCardNumber1'])."-".($_POST['sCardNumber2'])."-".($_POST['sCardNumber3'])."-".($_POST['sCardNumber4'])."','".trim($_POST['dtExpiryDay'])."-".($_POST['dtExpiryMonth'])."-".($_POST['dtExpiryYear'])."',".trim($_POST['dblShippingCost']).",".trim($_POST['dblGrandTotal']).",NOW())";

?>

beansandsausages · January 19, 2008

What burnside means is to use NOW() within an SQL insert.

So, replace your 'new' with now(). Example:

<?php

$query = "INSERT INTO tbl_order (sOrderid,SNRIC,sCardtype,sCardnumber,sCardexpiry,sDeliverycost,sGrandtotal,sStatus,sDate) VALUES ('". trim($strNewOrderID) . "','".trim($_SESSION['SNRIC'])."','".trim($_POST['sPayVia']). "','".trim($_POST['sCardNumber1'])."-".($_POST['sCardNumber2'])."-".($_POST['sCardNumber3'])."-".($_POST['sCardNumber4'])."','".trim($_POST['dtExpiryDay'])."-".($_POST['dtExpiryMonth'])."-".($_POST['dtExpiryYear'])."',".trim($_POST['dblShippingCost']).",".trim($_POST['dblGrandTotal']).",NOW())";

?>

ops my bad thanx for fixing it for me.

seeya · January 19, 2008

i get this error

Query failed: Column count doesn't match value count at row 1

beansandsausages · January 19, 2008

means in your insert bit you have a extra value ie :

insert (1,2,3,4,5,6,7,8,9) values (1,2,3,4,5,6,7,8,9,10)

will not run because is 1 to many values for what you have defines if you understand that.

seeya · January 19, 2008

i cant find the section where the order date is to be inserted.

any help?

beansandsausages · January 19, 2008

satrt of your code you got :


$strNewOrderID = date("YmdHis");

try it with


$strNewOrderID = NOW();

looks like it might be here

$query = "INSERT INTO tbl_orderdetails (sOrderID,Pcode,sQuantity,sProductprice) VALUES ('". trim($strNewOrderID) . "','" . trim(stripslashes($value)) . "'," . $_SESSION['arrProductQty'][$key] . "," . $_SESSION['arrProductUnitPrice'][$key] . ")";

i might be wrong

seeya · January 19, 2008

Fatal error: Call to undefined function NOW() in C:\Apache Group\Apache2\htdocs\vestito14.1\complete.php on line 20

btw, the code $strNewOrderID is for order id.

Daniel0 · January 19, 2008

NOW() is a MySQL function and not a PHP function. You must use it inside the query like toplay explained.

seeya · January 21, 2008

ive solve this problem on my own.

turns out, the answer i was looking for was in the code

Sign In

[SOLVED] Unable to add date to mysql

Recommended Posts

seeya

Link to comment

Share on other sites

beansandsausages

Link to comment

Share on other sites

toplay

Link to comment

Share on other sites

beansandsausages

Link to comment

Share on other sites

seeya

Link to comment

Share on other sites

beansandsausages

Link to comment

Share on other sites

seeya

Link to comment

Share on other sites

beansandsausages

Link to comment

Share on other sites

seeya

Link to comment

Share on other sites

Daniel0

Link to comment

Share on other sites

seeya

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information