copy data from one sql table to another having less number of columns and a different column

Question

I have a SQL table called books which has columns Name, ID, Author, Language, Pages, Genres, and Status. Now, I want to copy data from this table's Name, ID, Author, and Status columns to another table called log which has columns Name, ID, Author, Status and TimeStamp.. I'm getting the TimeStamp from the PHP code which I want to send through the INSERT statement.

Here's the code I've been trying:

$queryBook = $_GET['ID'];
$currentDate = date('d-m-Y H:i:s');
$stmt4 = $conn -> prepare("INSERT INTO log (Name, ID, Author, Status) SELECT (Name, ID, Author, Status), ? FROM books WHERE ID = '$queryBook'");
$stmt4 -> bind_param('s', $currentDate);
$stmt4 -> execute();

I tried this code as well:

$stmt4 = $conn -> prepare("INSERT INTO log * SELECT Name, ID, Author, Status FROM books WHERE ID = '$queryBook'");
$stmt4 -> execute();

I'm getting this error: Fatal error: Uncaught Error: Call to a member function execute() on boolean

I want to add the value of ```$currentDate``` so I've used ```?``` to bind the param. — Vishal A., Dec 26 '20 at 14:46
Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Dec 26 '20 at 14:47
do not do: `$stmt4 = $conn -> prepare("INS...`, this `$conn->prepare("INS...` seems better — Luuk, Dec 26 '20 at 14:48
I see no date column. Where are you trying to add the date? `d-m-Y H:i:s` is also not mysql date format. — user3783243, Dec 26 '20 at 14:49
I want to add the date to TimeStamp column. SInce the server is giving the time in it;s time zone, I've formatted it to string. — Vishal A., Dec 26 '20 at 14:50
I see no `TimeStamp` in screenshots, nor column list. Why not set the timezone of the server, or use GMT everywhere and convert on output? — user3783243, Dec 26 '20 at 14:57

score 1 · Answer 1 · answered Dec 26 '20 at 14:46

1

You should be setting the datetime in the table using a default value. Hence, you don't need to pass that in. On the other hand, you should be sending in $queryBook as a parameter. So, I would recommend defining the table log as :

create table log (
   . . . 
   timestamp datetime default now()
);

Then your code would look something like this:

$stmt4 = $conn -> prepare("INSERT INTO log (Name, ID, Author, Status) SELECT Name, ID, Author, Status FROM books WHERE ID = ?");
$stmt4 -> bind_param('s', $queryBook);
$stmt4 -> execute();

answered Dec 26 '20 at 14:46

Gordon Linoff

1,242,037
58
646
786

The problem with adding the now() function is, it is giving me the time of the server. I want it to give me the time in my timezone which is IST. – Vishal A. Dec 26 '20 at 14:48
time is time, so your local time should be the same as the time from the server (taking into account the difference in timezone) – Luuk Dec 26 '20 at 14:52
That's fine @Luuk, but I want it to look the same as my timezone. – Vishal A. Dec 26 '20 at 14:53
@GordonLinoff, let's say I have a different column and not the datetime column. How do you suggest I should solve this problem? – Vishal A. Dec 26 '20 at 14:56
1

@VishalA.: [PHP: Show time based on user's timezone](https://stackoverflow.com/questions/15149186/php-show-time-based-on-users-timezone) – Luuk Dec 26 '20 at 15:18
1

@VishalA. . . . You are much, much safer using the server time for the rows in the database. After all, the application time can vary -- for instance, people can access the application from anywhere in the world. or people might travel, and so on. – Gordon Linoff Dec 26 '20 at 16:13

score 0 · Accepted Answer · answered Dec 26 '20 at 15:17

0

I tried a few other things and eventually, I've got it working.

Here's the working code:

$stmt4 = $conn -> prepare("INSERT INTO log (Name, ID, Author, Status, TimeStamp) SELECT Name, ID, Author, Status, ? FROM books WHERE ID = ?");
$stmt4 -> bind_param('ss', $currentDate, $queryBook);
$stmt4 -> execute();

answered Dec 26 '20 at 15:17

Vishal A.

1,373
8
19

Change `timestamp` to a `datetime` and use `$currentDate = date('Y-m-d H:i:s');` this will save you tons of work down the road. – user3783243 Dec 26 '20 at 16:04

copy data from one sql table to another having less number of columns and a different column

2 Answers2