Evan Evan - 3 months ago 14
MySQL Question

mysqli_stmt::bind_param() error: query with join

I have a code for form that gets information from database, based on id of city(idci). I want to update the information using this form, but I get this error message:


mysqli_stmt::bind_param(): Number of variables doesn't match number of
parameters in prepared statement


queries work fine, i checked them in phpmyadmin sql imput.

if in $query i leave only "select idci" or "select *", I can see in the form correct id for city(idci), but the rest fields are empty.

if $query is the way it is written in the code example, I get information in the form for 1st result in the table, instead of the chosen result to edit.

Thanks for any help!

isset($_GET['idci']) ? $idci=$_GET['idci'] : $idci='';
isset($_GET['nameci']) ? $nameci=$_GET['nameci'] : $nameci='';
isset($_GET['nameco']) ? $nameco=$_GET['nameco'] : $nameco='';
isset($_GET['idco']) ? $idco=$_GET['idco'] : $idco='';
isset($_GET['submit']) ? $submit=true : $submit=false;


if(!$submit){
$query = "select idci, nameci, nameco, idco
from city
JOIN country on country.idco=city.country_idco
";
if($stmt = $mysqli->prepare($query))
{
$stmt-> bind_param('i', $idci);
$stmt->execute();
$stmt->bind_result($idci, $nameci, $nameco, $idco);
$stmt->fetch();

$stmt->close();
$mysqli->close();

echo'
<!DOCTYPE html>
<html>
<head>
<style>h1{color:red;}label{color:darkred;}</style>
<title>Edit user</title>
<meta charset=”UTF-8”>
</head>
<body>
<h1>Edit city:</h1>
<form action="" method="GET">
<label>cityID </label><input readonly name="idci" value="'.$idci.'"><br>
<label>city </label><input type="text" name="nameci" value="'.$nameci.'"><br>
<label>country </label><input type="text" name="nameco" value="'.$nameco.'"><br>
<label>countryID </label><input readonly name="idci" value="'.$idco.'"><br>

<input type=reset value=Clear>
<input type=submit value=Edit name=submit></form>

</body>
</html>
';
}
}else{
$query1 = "
UPDATE city
JOIN country
ON city.country_idco=country.idco
SET city.nameci =?, country.nameco=?
WHERE idci = ?";

if($stmt = $mysqli->prepare($query1))
{

$stmt->bind_param('ssii', $nameci, $nameco, $idco, $idci);

$stmt->execute();
$stmt->close();
}
$mysqli->close();

Answer

Your query has no placeholders, period. So there's NOTHING for bindParam() to operate on, and you get that exact error message:

$query = "select idci, nameci, nameco, idco
        from city 
        JOIN country on country.idco=city.country_idco 
        ";
if($stmt = $mysqli->prepare($query))
{
$stmt-> bind_param('i', $idci);
                    ^---one parameter to bind, but absolutely NO ? in the query.