SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP & MYSQL. INSERT INTO.. SELECT STATEMENT

    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.


    Code PHP:
                    <?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. #2
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,267
    Mentioned
    33 Post(s)
    Tagged
    5 Thread(s)
    Hi,

    You said that you want to essentially duplicate a row in the same table, but your SQL statement has two different table names:
    PHP Code:
    $stmt $mysqli->prepare("INSERT INTO assignment_speeches_copy (subject, body, tags, image) 
                            SELECT subject, body, tags, image
                            FROM assignment_speeches
                            WHERE id = '?'"
    ); 

  3. #3
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh. My mistake! It should be

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

  4. #4
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,267
    Mentioned
    33 Post(s)
    Tagged
    5 Thread(s)
    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. #5
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,267
    Mentioned
    33 Post(s)
    Tagged
    5 Thread(s)
    Quote Originally Posted by xxx12345 View Post
    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
    Don't forget that you need to replace the ? with an actual value when you're testing, eg:

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

  7. #7
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,267
    Mentioned
    33 Post(s)
    Tagged
    5 Thread(s)
    But I thought you were intending to use an ID passed into your script as a GET variable?


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •