How to insert date in MySQL using PHP

Inserting Date Values into MySQL Table using PHP could be the rephrased

To insert the names and last modified dates of all files in a directory into a MySQL database, you can incorporate another function that converts the date input into the MySQL date format. Here’s an alternative solution:

How to insert date into MySQL with php

I have the following php code :

I am attempting to retrieve the files from a directory and insert their names and respective last modified dates (in the format yyyy-mm-dd) into a mysql database. The table «fichier» has four columns, namely Id, Name, Modified, and OwnerId. The data type of the «Modified» column is DATE. Although the code runs without any errors, no data is being inserted into the database. Any assistance in resolving this issue would be greatly appreciated.

Is the timestamp provided by filemtime accurate?

You can be redirected to the notes section of the date function on php.net’s manual by visiting http://php.net/manual/en/function.date.php#refsect1-function.date-notes.

but have you just test that ?

date($format, strtotime(filemtime($file))); 

How to Insert data from PHP dropdown date into a, Teams. Q&A for work. Connect and share knowledge within a single location that is structured and easy to search. Learn more about Teams

Inserting date value into MySQL

At present, I possess a form that allows the user to input a date in the m/d/y format. However, when I tried to insert this date into a table, the value displayed in the table is 0000-00-00. I comprehend that the reason behind this issue is the mismatch of the date format being inserted.

I am uncertain about the appropriate format to insert the data into MySQL’s storage.

Provided is the function responsible for adding information to the table.

public function addUser($array) < $array['password'] = $this->hashPassword($array['password']); $implodeArray = '"'.implode( '","', $array ).'"'; $sql = ('INSERT INTO user (email, password, firstName, lastName, officeID, departmentID, managerID, roleID, username, contractType, startDate, endDate, totalLeaveEntitlement, remainingLeave) VALUES ('.$implodeArray.')'); echo $sql; die(); mysql_query($sql,$this->_db) or die(mysql_error()); mysql_close(); > 

The MySQL DATE format cannot be matched for the values of startDate and endDate due to the usage of implodeArray.

Employing the same technique you used to hash the password, would it not be feasible to add an additional function that converts the date input to the MySQL date format?

public function addUser($array) < $array['password'] = $this->hashPassword($array['password']); $array['startDate'] = $this->mysql_date_format($array['startDate']); $array['endDate'] = $this->mysql_date_format($array['endDate']); $implodeArray = '"'.implode( '","', $array ).'"'; $sql = ('INSERT INTO user (email, password, firstName, lastName, officeID, departmentID, managerID, roleID, username, contractType, startDate, endDate, totalLeaveEntitlement, remainingLeave) VALUES ('.$implodeArray.')'); echo $sql; die(); mysql_query($sql,$this->_db) or die(mysql_error()); mysql_close(); > 

Although it may seem convenient to write queries with one function that generates all parameters, I highly recommend preparing your statements. This will benefit anyone who comes along to support your code.

This enables the utilization of functions like NOW(), DATE_DIFF, and other impressive options.

Although I didn’t provide a direct answer to your question, I strongly suggest taking the necessary time to carefully construct your queries. This can help prevent potential run time errors and attacks from occurring.

Uncertain about the details of your problem, but generally speaking:

$mysql_formatted_date = date("Y-m-d", strtotime($mdy_formatted_date)); 

I think you’ll want STR_TO_DATE()

In my opinion, the correct format is STR_TO_DATE(«%m/%d/%Y») .

Php — inserting data into a mysql table dynamically, Forgive me for lack of a proper question title but my question is as below I have a table with data that look like this: mysql> select * from tablex

Inserting ‘sign-up’ date into MySQL table

I am facing an issue while recording the date of form filling into a MySQL table. Although all the other requested information is being recorded, the date seems to be left out. I have a sign-up form and require assistance in resolving this matter. Can anyone provide me with some guidance?

I have included a portion of my PHP code where I believe the issue lies. Additionally, my database is being managed through PHPmyadmin, which may be pertinent.

You can find the website for signing up at www.3elementsreview.com/sign-up.

 $value = $_POST['First']; $value2 = $_POST['Last']; $value3 = $_POST['City']; $value4 = $_POST['State']; $value5 = $_POST['Country']; $value6 = $_POST['Email']; $value7 = $_POST['Y-m-d H:i:s']; $sql = "INSERT INTO members (First, Last, City, State, Country, Email, Date) VALUES ('$_POST[First]','$_POST[Last]','$_POST[City]','$_POST[State]','$_POST[Country]','$_POST[Email]','$_POST[Date]')"; 

Your code poses a threat as it is vulnerable to SQL injection attacks.

To begin with, it should be noted that the use of mysql_* library is no longer recommended.

If you decide to utilize mysql_* functions, it’s crucial to apply mysql_real_escape string to EACH of your POST fields.

Thirdly this will solve your issue:

$value = mysql_real_escape_string($_POST['First']); $value2 = mysql_real_escape_string($_POST['Last']); $value3 = mysql_real_escape_string($_POST['City']); $value4 = mysql_real_escape_string($_POST['State']); $value5 = mysql_real_escape_string($_POST['Country']); $value6 = mysql_real_escape_string($_POST['Email']); $sql = "INSERT INTO members (First, Last, City, State, Country, Email, Date) VALUES ('$value','$value2','$value3','$value4','$value5','$value6',NOW())"; 

The NOW() function in MySQL is utilized to provide the present date and time.

If your parameter has a different name than Y-m-d%20H:i:s=____ , it is advisable to gather a different variable like $_POST[‘date’] and then convert it into a date by assigning it to a variable.

$value7 = $_POST['date']; $value7 = date('Y-m-d H:i:s', $value7); 

Entering Dates into mysql database using sql INSERT, Entering Dates into mysql database using sql INSERT. Ask Question Asked 12 years, 10 months ago. PHP MySQL database problem. 1075. Insert into a MySQL table or update if exists. 4. PHP …

Inserting data into a mysql table dynamically

I apologize for not having a suitable title for my inquiry, but my question is stated below.

My data table appears as follows:

 mysql> select * from tablex; +-------+---------+-----+ | id | post_id | pid | +-------+---------+-----+ | 14549 | 7195 | 27 | | 14551 | 7195 | 34 | | 14556 | 7195 | 1 | | 14564 | 7196 | 51 | | 14566 | 7196 | 11 | | 14571 | 7196 | 37 | | 14576 | 7197 | 36 | | 14578 | 7198 | 11 | | 14586 | 7199 | 15 | | 14612 | 7201 | 42 | +-------+---------+-----+ 

Upon counting for duplicates, a value of data structure is obtained as such:

mysql> select count(*), post_id from tablex group by post_id; +----------+---------+ | count(*) | post_id | +----------+---------+ | 3 | 7195 | | 3 | 7196 | | 1 | 7197 | | 1 | 7198 | | 1 | 7199 | | 1 | 7201 | +----------+---------+ 

I am inquiring about effective approaches to modify the aforementioned data by utilizing php/mysql, in order to transform tabley into the desired format.

mysql> select * from tabley order by meta_id desc; +---------+---------+------------------+---------------+ | meta_id | post_id | meta_key | meta_value | +---------+---------+------------------+---------------+ | 7575 | 7195| multiple | 3 | | 7574 | 7195| multiple_0 | 27 | | 7573 | 7195| multiple_1 | 34 | | 7572 | 7195| multiple_2 | 1 | | | | | | +---------+---------+------------------+---------------+ 

Upon observation, it can be seen that post_id 7195 is repeated thrice. Therefore, the initial measure would be to assign the meta_key value as 3 for multiplicity.

How can I easily generate key 0-2, along with meta keys multiple_0 — multiple_2, and insert values 27, 34, and 1 for post_id 7195 by running a loop? This information is readily available in tablex.

Ensure to modify the servername, username, password, and database as needed before using the provided code.

$conn = mysqli_connect('localhost', 'root', 'password','database'); $sql=mysqli_query($conn,"select count(*) as count,post_id from tablex group by post_id"); while($row=mysqli_fetch_array($sql)) < $count[]=$row["count"]; $postid[]=$row["post_id"]; >foreach (array_combine($postid,$count) as $pid=>$cnt) < for($i=0;$i<=$cnt;$i++)< $pstid = $pid; if($i==0)< $multiple = "multiple"; $meta= $cnt; >else < $x=$i-1; $multiple = "multiple_".$x; $query=mysqli_query($conn,"select pid from tablex where post_id='$pid'"); while($row=mysqli_fetch_array($query))< $id[]=$row["pid"]; >$meta = $id[$i-1]; > $query2=mysqli_query($conn,"INSERT INTO tabley(post_id,meta_key,meta_value) VALUES('$pid','$multiple','$meta')"); > > 

For this operation, only mysql is permissible.

INSERT INTO tabley ( post_id, meta_key, meta_value ) SELECT t1.post_id, 'multiple' AS multiple, COUNT(*) FROM tablex AS t1 GROUP BY t1.post_id UNION SELECT t2.post_id, REPLACE( CONCAT( 'multiple_', @curRow:=CASE WHEN @postId = t2.post_id THEN @curRow + 1 ELSE 0 END, @postId:=t2.post_id ), t2.post_id, '' ) AS multiple, t2.pid FROM tablex AS t2 ORDER BY post_id, multiple; 

The initial step involves using MySQL syntax with INSERT . SELECT . Subsequently, execute the second select with UNION . You can evaluate SELECT without the need for INSERT .

The next step involves obtaining the row numbers for rows that have a matching post_id .

Php date will not insert into mysql database, php date will not insert into mysql database [closed] Ask Question Asked 9 years, mysql_query(«INSERT INTO booking_info (customer_id, booking_ref, Insert into a MySQL table or update if exists. 1096. PHP array delete by value (not key)

Источник

How to insert date in MySQL using PHP

In this article, you going to see how to insert date in MySQL using PHP in a detail with a simple example and step by step.

If you are looking for this topic then you are at the right place.

As many users are facing the issue of inserting date into the MySQL database.

It is because many of the users don’t know why the date is not get inserted into the MySQL database.

But from this article, you come to know what is the actual problem of date, which is not get inserted into the database.

This is the important point that every user should know, that MySQL receives and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format.

The date can be store in this format only.

So when writing a query for inserting a date using PHP, make sure to use the default date and time format as provided by MySQL i.e. ‘YYYY-MM-DD’.

Below is the default date and time format are as follow:

DATE: YYYY-MM-DD Example: 2005-12-26 DATETIME: YYYY-MM-DD HH:MI:SS Example: 2005-12-26 23:50:30 TIMESTAMP: YYYY-MM-DD HH:MI:SS Example: 2005-12-26 23:50:30 YEAR: YYYY or YY

Let’s start with the topic of how to insert date in MySQL using PHP.

First, create the table into which have to insert date.

Below is a query that is to be executed for creating a table name “checkdate” in the database.

CREATE TABLE `checkdate` ( `id` int(11) NOT NULL, `name` varchar(250) NOT NULL, `createdat` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

If you want to know more about phpMyAdmin, check with the below link book which is a step-by-step instructional guide to get you started easily with phpMyAdmin and teach you to manage and perform database functions on your database.

After creating a table our next step is to create a form that consists of an input field of a date.

Use jQuery Datepicker to populate date into the input field of the form.

As the date get populated in the input field, the next step is to submit the form.

On submit form both the input field of name and date is get post.

As the form get the post, the first step is to convert the date format of the post date field to the default date and time format as provided by MySQL.

Convert the date using date format.

Check the date in the database using a select query.

$originalDate = $_POST['datepicker']; $newDate = date("Y-m-d", strtotime($originalDate)); // SQL query $sql = "SELECT * FROM checkdate WHERE DATE(createdat) = '$newDate'"; 

Use the Insert query to insert the date into the database.

Date get insert as post date is not found into the database with the use of form post.

Below is the code snippet from which you can understand completely, the best of doing php insert date into mysql.

 if(isset($_POST['submit']) && $_POST['submit'] =='Submit' ) < $originalDate = $_POST['datepicker']; $newDate = date("Y-m-d", strtotime($originalDate)); // SQL query $sql = "SELECT * FROM checkdate WHERE DATE(createdat) = '$newDate'"; $result = mysqli_query($conn, $sql); $name = $_POST['name']; $datepicker = $newDate; if (mysqli_num_rows($result) == 0) < // output data of each row $sql = "INSERT INTO `checkdate` (`id`, `name`, `createdat`) VALUES ('','$name','$datepicker');"; if (mysqli_query($conn, $sql)) < echo "New record created successfully"; >else < echo "Error: " . $sql . "
" . mysqli_error($conn); > > else < echo "Error: " . $sql . "
" . mysqli_error($conn); > > ?>          
How to insert date in MySQL using PHP
 
Name: 
Date: 
 0) < while($row = mysqli_fetch_array($result1,MYSQLI_ASSOC))< ?>
 
Output Date Inserted in Database
 
ID
Name
Date
 
 
 > ?>
 
 
  

Finally, we have done with point how to insert date in MySQL using PHP or Insert Date Mysql PHP.

I hope you like this article, if you feel like we missed out on anything, please comment below.

Источник