How to get id from a table and insert in another table in prepare stmt?

PHP
1

I am trying to get id from stores and insert into items table with prepare stmt.
was easy in mysql, but dont know how to do it in stmt.
I did 2 querries separetly but didnt work.

my first querry for inserting rows to table

   if(empty($title_err) && empty($details_err) && empty($image_err) && empty($cat_id_err)){
        // Prepare an update statement
        $sql = "INSERT INTO adverts (cat_id, store_id, user_id, title, details, image, seo_url, hits, date, active) VALUES (?, ?, ?, ?, ?, ?, ?, ?,? ,?)";
        if($stmt = $conn->prepare($sql)){
            // Bind variables to the prepared statement as parameters
            $stmt->bind_param("iiissssiss", $param_cat_id, $param_store_id, $param_user_id, $param_title, $param_details, $param_image, $param_seo_url, $param_hits, $param_date, $param_active);

            // Set parameters
            $param_cat_id = htmlspecialchars($cat_id);
            $param_store_id = htmlspecialchars($store_id);
            $param_user_id = htmlspecialchars($_SESSION['id']);
            $param_title = htmlspecialchars($title);
            $param_details = htmlspecialchars($details);
            $param_image = htmlspecialchars($filedestination);
            $param_seo_url = seo_url($title);
            $param_hits = htmlspecialchars(1);
            $param_date = htmlspecialchars($date);
            $param_active = htmlspecialchars(Y);

            // Attempt to execute the prepared statement
            if($stmt->execute()){
                // Records updated successfully. Redirect to landing page
                header("location: index.php");
                exit();
            } else{
                echo "Something went wrong. Please try again later.";
            }
        }

        // Close statement
        $stmt->close();
    }

    // Close connection
    $conn->close();
}

And this is the second one for select from stores (it doesnt list rows).

// Prepare a select statementwhere userid=?
    $sql = "SELECT * FROM stores WHERE store_id=? ORDER BY store_id DESC";
    if($stmt = $conn->prepare($sql)){
        // Bind variables to the prepared statement as parameters
        $stmt->bind_param("i", $param_store_id);
        if($stmt->execute()){
            $result = $stmt->get_result();
            if($result->num_rows == 1){

                $row = $result->fetch_array(MYSQLI_ASSOC);

                // Retrieve individual field value
                $store_id = $row["store_id"];
                $store_name = $row["store_name"];
            } else{
                // URL doesn't contain valid id. Redirect to error page
                header("location: error.php");
                exit();
            }

        } else{
            echo "Oops! Something went wrong. Please try again later.";
        }
    }

    // Close statement
    $stmt->close();

    // Close connection
    $conn->close();

infos coming from a form
example

<form>
  First name:<br>
  <input type="text" name="firstname"><br>
  Last name:<br>
  <input type="text" name="lastname">

</form>
2

I’m not seeing how the two queries relate to each other.

In the second one, do you get an error message, or is $store_id just blank? Incidentally, as you already know the store id before you run the query, I’m not sure of the reason for running the query.

Which is not working, though, the query that retrieves the data from stores, or the query that inserts it into adverts? Also you mentioned inserting into items, can you show that code?

3

its blank I want to put it in a hidden input
`
and insert it into adverts table
$param_store_id = htmlspecialchars($store_id);

Should be something like this but couldnt make it work

 // Check input errors before inserting in database
    if(empty($title_err) && empty($details_err) && empty($image_err) && empty($cat_id_err)){
        // Prepare an update statement
        $sql = "INSERT INTO adverts (cat_id, store_id, user_id, title, details, image, seo_url, hits, date, active)
                SELECT * FROM stores WHERE store_id = ?";
        if($stmt = $conn->prepare($sql)){
            // Bind variables to the prepared statement as parameters
            $stmt->bind_param("i", $identity);
            
            // Set parameters
            $param_cat_id = htmlspecialchars($cat_id);
            $param_store_id = htmlspecialchars($store_id);
            $param_user_id = htmlspecialchars($_SESSION['id']);
            $param_title = htmlspecialchars($title);
            $param_details = htmlspecialchars($details);
            $param_image = htmlspecialchars($filedestination);
            $param_seo_url = seo_url($title);
            $param_hits = htmlspecialchars(1);
            $param_date = htmlspecialchars($date);
            $param_active = htmlspecialchars(Y);
            
            // Attempt to execute the prepared statement
            if($stmt->execute()){
                // Records updated successfully. Redirect to landing page
                header("location: index.php");
                exit();
            } else{
                echo "Something went wrong. Please try again later.";
            }
        }
         
        // Close statement
        $stmt->close();
    }
    
    // Close connection
    $conn->close();

When I use like this with var_dumb its works but when I remove var_dumb then it stop working

     $stmt->close();
    }
		$res = $conn->query("SELECT store_id FROM stores");
var_dump($res->fetch_all());
    
    // Close connection
    $conn->close();
}
?>
4

You do still have the rest of the line? I can’t see why that would change anything.

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.