Php & mysql. insert into.. select statement

PHP
1

Hi all. I am trying to retrieve row data from a table and insert into the same table using the INSERT INTO… SELECT mysql statement.

		
                <?php

		session_start();
	
		// default user's name
		$user = '';
		
		// if visitor is logged in 
		$loggedIn = (!empty($_SESSION['user']));
			
		// since user is logged in, let us retrieve user's name from $_SESSION
		if ($loggedIn) {
			$user = $_SESSION['user'];
		} else {
			// we only allow logged in user to see this page
			// if visitor not logged in, redirect visitor to login page
			header('Location: index.php');
			exit;
		}

		$speechID = $_GET['id'];
		
		// the file that contains your database credentials like username and password
		require_once('config/database.php');

		// see Lecture Webp_Week13_14_Using_PHPandMySQL(updating).pptx Slide 4 aka Step 1
		$mysqli = new mysqli($database_hostname, $database_username, $database_password, $database_name) or exit("Error connecting to database"); 

		// Slide 5 aka Step 2
                $stmt = $mysqli->prepare("INSERT INTO assignment_speeches_copy (subject, body, tags, image) 
        							SELECT subject, body, tags, image
        							FROM assignment_speeches
           							WHERE id = '?'");
		
		// Slide 6 aka Step 3 the bind params must correspond to the ?
		$stmt->bind_param("i", $speechID); // 1 ? so we use i. we use i because  id is INT
		
		// Slide 7 aka Step 4
		$successfullyCopied = $stmt->execute(); 
		
		// Slide 9 aka Step 6 and 7
		$stmt->close();
		
		$mysqli->close();
		
		// if we successfully delete this, we 
		if ($successfullyCopied) {
			$_SESSION['message'] = 'Successfully copied';
		} else {
			$_SESSION['message'] = 'Unable to copy';
		}
		
		header('Location: homepage.php');

?>

I want the row data to be copied when the user hit the “copy” button. The id is an auto_increment.
There was no data copied into the table even when the page states “successfully copied”.
May I know what is wrong with the code?
Much help needed!! Thanks!

2

Hi,

You said that you want to essentially duplicate a row in the same table, but your SQL statement has two different table names:


$stmt = $mysqli->prepare("INSERT INTO assignment_speeches_copy (subject, body, tags, image)
        				SELECT subject, body, tags, image
        				FROM assignment_speeches
           			 WHERE id = '?'");
3

Oh. My mistake! It should be

$stmt = $mysqli->prepare("INSERT INTO assignment_speeches (subject, body, tags, image)
                        SELECT subject, body, tags, image
                        FROM assignment_speeches
                        WHERE id = '?'");
4

As far as I can see, the SQL looks correct. Although you don’t need the quote marks around the ?, as the parameter is an integer not a string. Have you tried pasting the query into phpMyAdmin or something similar, to see if it actually duplicates the row correctly?

5

Yes, I have tried. But there is an error.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?’ at line 4

6

Don’t forget that you need to replace the ? with an actual value when you’re testing, eg:

INSERT INTO assignment_speeches (subject, body, tags, image)  
SELECT subject, body, tags, image 
FROM assignment_speeches 
WHERE id = 5
7

Oh thanks! That worked if phpmyadmin when I assigned a number to the id.

But how can I make the code work when I want the id to be ?. Since the duplicated data is determined by the id which is auto_increment in phpmyadmin.

8

But I thought you were intending to use an ID passed into your script as a GET variable?