SQL INSERT INTO SELECT Statement syntax issue

Question

I have 4 pages, across which I need to share the same restaurant_ID. I need to be able to insert the ID into all 4 tables in the database. I am using a INSERT INTO SELECT statement to do so. But I am receiving the following error:

POSSIBLE Syntax Error (check preceding valid syntax error) unexpected: identifier 'SELECT'

POSSIBLE Syntax Error (check preceding valid syntax error) unexpected: identifier 'Resturant_ID'

POSSIBLE Syntax Error (check preceding valid syntax error) unexpected: identifier 'Rest_Dets'

This is the sql I am using:

(Original):

  INSERT INTO Product (Resturant_ID)
  SELECT Resturant_ID Rest_Dets;

(Edited):

  INSERT INTO Product (Resturant_ID)
  SELECT Resturant_ID FROM Rest_Dets;

I have also tried

  $rest_id = mysqli_real_escape_string($dbc, $_SESSION['Resturant_ID']);
  INSERT INTO Product (Resturant_ID)
  SELECT Resturant_ID FROM Rest_Dets  WHERE Resturant_ID = $rest_id ;

I have looked all over the internet and it doesn't seem like I should have a problem, the webpage is also connected successfully.

Rest_Details(The table i want to get the Restaurant_ID from)

     CREATE TABLE `Rest_Details` (
    `Resturant_ID` bigint(255) NOT NULL AUTO_INCREMENT,
    `Resturant_name` varchar(100) NOT NULL,
    `Resturant_des` varchar(200) NOT NULL,
    `Res_Address_Line_1` varchar(200) NOT NULL,
    `Res_Address_Line_2` varchar(200) DEFAULT NULL,
    `City_name` varchar(100) NOT NULL,
    `Resturant_Postcode` varchar(8) DEFAULT NULL,
    `Cat_ID` tinyint(11) NOT NULL,
    `Avg_Del` tinyint(11) NOT NULL,
    `Est_Del` tinyint(11) NOT NULL,
    `Email1` varchar(200) NOT NULL,
    `Email2` varchar(200) DEFAULT NULL,
    `Min_ord` tinyint(11) NOT NULL,
     PRIMARY KEY (`Resturant_ID`),
     UNIQUE KEY `Resturant_name` (`Resturant_name`),
     UNIQUE KEY `Resturant_ID` (`Resturant_ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Products

   CREATE TABLE `Product` (
  `Product_Id` bigint(255) NOT NULL AUTO_INCREMENT,
  `Resturant_ID` bigint(255) NOT NULL,
  `Product_Name` varchar(100) NOT NULL,
  `Product_Desc` text NOT NULL,
  `Product_Price` decimal(65,0) NOT NULL,
  `Add_On_ID` int(11) NOT NULL,
   PRIMARY KEY (`Product_Id`),
   UNIQUE KEY `Product_Id` (`Product_Id`),
   UNIQUE KEY `Resturant_ID` (`Resturant_ID`)
   ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

Answer 1

As referenced by Fred-ii- you can read https://dev.mysql.com/doc/refman/5.5/en/insert-select.html which will give you exactly the answer you are looking for:

INSERT INTO Product (Resturant_ID) SELECT Resturant_ID FROM Rest_Dets WHERE <condition>;

Additional

WHERE Resturant_ID = $rest_id ;

Your string (if it is a string) should be encased in single quotes:

 WHERE Resturant_ID = '$rest_id' ;

And properly concatenated, regardless of if it is a string or a number:

 WHERE Resturant_ID = '".$rest_id."' ;

---

As an aside you can achieve exactly what you want by simply doing:

INSERT INTO Product(Resturant_ID) VALUES ('".$rest_id."')

(You've also spelt Restaurant wrong. )

---

Solution

The solution from your comments I think is this:

Your current PHP code is something like:

<?php
...
$rest_id = mysqli_real_escape_string($dbc, $_SESSION['Resturant_ID']);
  INSERT INTO Product (Resturant_ID)
  SELECT Resturant_ID FROM Rest_Dets  WHERE Resturant_ID = $rest_id ;

You are typing SQL commands directly into PHP script, which is giving you the errors you state in your question, so what you should actually be doing is running the SQL queries through an interface manager (in this case PHP functions that setup and execute MySQL commands to the server).

Please read http://php.net/manual/en/book.mysqli.php which will give you a good guide on using MySQLi functions in PHP script to communicate with your database.

Your reference to mysqli_real_escape_from_newyork_string is the sort of functions I'm on about, you should be able to clearly see from the below code what (I think) you've missed:

<?php
/** Error reporting: **/
    error_reporting(E_ALL);
    ini_set('display_errors', 1);

/** Set up the mysqli connection: **/
    $dbc = mysqli_connect("localhost", "my_user", "my_password", "my_db");

/** Next clean your input **/ 
    $rest_id = mysqli_real_escape_string($dbc, $_SESSION['Resturant_ID']);

/** Next run the query **/
    mysqli_query($dbc, "INSERT INTO Product (Resturant_ID)
  SELECT Resturant_ID FROM Rest_Dets WHERE Resturant_ID = ".$rest_id."") 
    or die(__LINE__.": yourSQL Error: ".mysqli_error($dbc));

/** It is good practise for beginniners to add the or die to the end of the code to 
display MySQLi errors. NOT FOR PUBLIC/LIVE WEBSITES **/

/** Finally a success statment! **/
    print "If you can read this then your query worked ok!!";

Copy and paste the above code into your page and think about the logic and adjust fitting it around your current code but this I think is the key you're missing. Please read up the following articles:

• PHP MySQL Query
• Outputting fetched data from MySQL in PHP
• Using MySQLi error reports
• Displaying PHP errors

NOTES:

• Remember if your $rest_id is a string then it needs to be encased in single quotes inside the mysqli_query()
• localhost may not be local (on the same machine the PHP is running on) and may be on another server.
• ALWAYS clean any input as thoroughly as possible. Never trust any user input, ever.
• NEVER use MySQL_ functions, they are no longer supported. Use MySQLi (as here) or PDO.