0

I want to dynamically add the values in database which contains the foreign key, using PHP prepare and execute statement. I have concatenated the variable of SQL "SELECT" query but it gives me following error, Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in C:\xampp\htdocs\app\php\setCity.php on line 6.
Here is my source code: 

//index1.php
<?php 
include 'php/connect.php';
include 'php/setState.php';
include 'php/setCity.php';
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Welcome | Admin panel</title>
</head>
<body>
    <form action="index1.php" method="POST">
        <br><b>Enter State Name: </b><input type="text"     name="state_name"><br>
        <br><b>Enter City Name: </b><input type="text" name="city_name"><br>
        <input type="submit" name="submitNames" value="Update to database">
    </form>
</body>
</html>


//setCity.php

<?php
include 'connect.php';

if (isset($_POST['submitNames'])) 
{
    $stateid = "SELECT state_id FROM state WHERE state_name =            .$_POST['state_name'].;";
    $cityname = mysqli_real_escape_string($_POST['city_name']);
    $sql = "INSERT INTO city (state_id, city_name) VALUES (?,?);";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($conn, $sql)) 
    {
        echo "SQL ERROR OCCURED";
    }
    else
    {
        mysqli_stmt_bind_param($stmt, "is", $stateid ,$cityname);
        mysqli_stmt_execute($stmt);
    }
}

?>

//setState.php

<?php
include 'connect.php';

if (isset($_POST['submitNames'])) 
{
    $data = mysqli_real_escape_string($conn, $_POST['state_name']);
    $sql = "INSERT INTO state(state_name) VALUES (?);";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) 
    {
        echo "SQL ERROR.";
    }
    else
    {
        mysqli_stmt_bind_param($stmt, "s", $data);
        mysqli_stmt_execute($stmt);
    }
}
?>

Note: It gives error to concatenated SQL query, in 'setCity.php' page on line no 6.

Suhem Bali
  • 52
  • 1
  • 8
  • You cannot use a query as a parameter to another query. – Uueerdo Jun 04 '18 at 19:51
  • 1
    You need to execute `$stateid`, fetch the result, then store it. You also are open to SQL injections with that statement, and the concatenation is incorrect. Parameterize everywhere. Additionally you don't need to escape with prepared statements (also escaping requires the first parameter be the connection link) – user3783243 Jun 04 '18 at 19:52
  • @Uueerdo as I have to insert value in table from another table which is linked by foreign key, is there any possible way to achieve this? – Suhem Bali Jun 04 '18 at 19:52
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Jun 04 '18 at 20:02
  • Since you're using placeholder values, which is fantastic, it's important to *not* escape any values being bound. If you do that you'll double escape things and mangle your data. Let `bind_param` do the encoding for you. – tadman Jun 04 '18 at 20:03
  • You also can't bind a subquery as a param. Run that query independently, get the data, then supply that data to the second query. Alternatively construct some kind of `INSERT INTO x SELECT ... FROM y` query to transpose from one table to another. – tadman Jun 04 '18 at 20:04
  • 1
    @tadman Thanks Man! The advice you gave me change the procedural style, I will surely work on that as even I was thinking about the same. Plus, Thanks for the solution you gave to run `$stateid` first and etc... Now will you please help me by saying me how can I accept the answer by marking it green in stackoverflow? – Suhem Bali Jun 04 '18 at 20:48

0 Answers0