Member Avatar for hiiiiii@

hi
i want to insert date through mysql query

<?php 
 require_once('auth.php');
 require_once('connection/config.php');

    //Connect to mysql server
    $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
    if(!$link) {
        die('Failed to connect to server: ' . mysql_error());
    }

    //Select database
    $db = mysql_select_db(DB_DATABASE);
    if(!$db) {
        die("Unable to select database");
    }
?>
<form method ="post" action ="">
<?php

$name=$_POST['name'];
$quantity=$_POST['quantity'];
$price=$_POST['price'];
$total=$_POST['total'];
$transactioncode=$_POST['transactioncode'];

?>
<input name="transactioncode" type="hidden" value="<?php echo $transactioncode;?>" />
<input name="total" type="hidden" value="<?php echo $total;?>" />

<input name="name" type="hidden" value="<?php echo $name;?>" />
<input name="quantity" type="hidden" value="<?php echo $quantity;?>" />
<input name="price" type="hidden" value="<?php echo $price;?>" /></form>
<?php
$name=$_POST['name'];
$quantity=$_POST['quantity'];
$price=$_POST['price'];
$total=$_POST['total'];
$trasactiondate=date("d/m/Y");
$transactioncode=$_POST['transactioncode']; 

$regnum = $_POST['regnum'];

$data = mysql_query("SELECT * FROM  members where member_id=$transactioncode") or die(mysql_error());  
 while($info = mysql_fetch_array( $data )) {
 $reg = $info['regnum'];
} 
if($regnum != $reg){
echo "wrong regnum Num";
exit(0);
}



mysql_query("INSERT INTO order_items
SELECT * 
FROM cart_items");


?>
<div> 
<a  href="foodorders.php"><img src="images/dine1.png" width="100%" height="80%" />click here view your order </a>
Thank you ! your order has been placed succesfully ... .</div>

im copying all the data from one table to the other table so i want to insert date which is not in the other table .the date should be inserted in new table only.

  • 2 Contributors
  • 2 Replies
  • 223 Views
  • 20 Minutes Discussion Span
  • Latest Post Latest Post by hiiiiii@

Dani AI

Generated

As suggested, the date must be supplied as part of the INSERT ... SELECT — mixing a VALUES(...) clause with a SELECT is invalid SQL. The correct form is to list the target columns and make the last selected expression the date (a literal or a function like NOW()). (dev.mysql.com)

Example (SQL-only):

INSERT INTO order_items
  (item_id, item_name, qty, unit_price, total_price, transaction_code, transactiondate)
SELECT
  item_id, item_name, qty, unit_price, total_price, transaction_code, NOW()
FROM cart_items
WHERE transaction_code = 'the-code-to-match';

Use NOW() for a DATETIME/TIMESTAMP value or CURDATE() for a DATE-only value; MySQL expects ISO-style dates (YYYY-MM-DD / YYYY-MM-DD HH:MM:SS). If generating the date in PHP, format it as date('Y-m-d') or date('Y-m-d H:i:s') to match MySQL. (dev.mysql.com)

If the goal is always “set the insertion time,” consider giving the transactiondate column a default of CURRENT_TIMESTAMP so the column does not need to be included explicitly in the INSERT. That is often simpler and less error-prone for audit timestamps. (dev.mysql.com)

Note on PHP usage: the old mysql_* functions (shown in the thread) were removed from modern PHP. Migrate to mysqli or PDO and use prepared statements for safety. A PDO example could prepare the INSERT...SELECT and bind the transaction code rather than interpolating variables. (php.net)

Troubleshooting tips: always specify column lists on both sides of the INSERT to avoid mismatches; test the SELECT alone first to confirm the column order and types; run the whole operation inside a transaction if rows must be moved atomically (INSERT then DELETE).

Recommended Answers

All 2 Replies

Member Avatar for jstfsklh211

just add the date to your insert/select statement

Member Avatar for hiiiiii@

mysql_query("INSERT INTO order_items(transactiondate) values($transactionsdate)
SELECT *
FROM cart_items");
is this will insert date

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.