MYSQL prepared statment query not matching WRITTERID with WRITTER

Question

I am trying to query a mysql statement to give match the writterid and writter in a drop down. How should i set this up because everything looks correct?

I have already made the non-prepared statement

<?php
include('includes/config.php');
if(!empty($_POST["catid"])) 
{
 $id=intval($_POST['catid']);
$query=mysqli_query($con,"SELECT * FROM  tblwritter WHERE CategoryId=$id and Is_Active=1");

?>
<option value="">Select Writter</option>
<?php
 while($row=mysqli_fetch_array($query))
 {
  ?>
  <option value="<?php echo htmlentities($row['WritterId']); ?>"><?php echo htmlentities($row['Writter']); ?></option>
  <?php
 }
}

?>

<?php
include('includes/config.php');
if(!empty($_POST["catid"])) 
{

$stmt = $con -> mysqli_query('SELECT * FROM  tblwritter WHERE CategoryId=? and Is_Active=1');

$id=intval($_POST['catid']);

$stmt -> bind_param('i', $id);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($WritterId, $Writter);
$stmt -> fetch();

?>
<option value="">Select Writter</option>
<?php
 while($row=mysqli_fetch_array($stmt))
 {
  ?>
  <option value="<?php echo htmlentities($row['WritterId']); ?>"><?php echo htmlentities($row['Writter']); ?></option>
  <?php
 }
}

The expected results of this code is to show Writters name in a drop down.

What. No. This is for when you add a post and you select a category and then it gives you a list of writers from it — Neiko, Sep 08 '19 at 15:59

Nick · Accepted Answer · 2019-09-08T03:38:00.343

0

You're not actually preparing a query. You are assigning the result set of a query to $stmt. Instead, you should write:

$stmt = $con -> prepare('SELECT * FROM  tblwritter WHERE CategoryId=? and Is_Active=1');

Next, you need to use fetch to return data from a prepared statement. You can use it in a loop in the same way as you call mysqli_fetch_array. Remove your first call (as it will consume a row you want to output):

$stmt -> fetch();

and replace the loop starting with:

while($row=mysqli_fetch_array($stmt))

with

while ($stmt->fetch())
{
?>
  <option value="<?php echo htmlentities($WritterId); ?>"><?php echo htmlentities($Writter); ?></option>
<?php
}

edited Sep 08 '19 at 03:38

answered Sep 08 '19 at 02:58

Nick

138,499
22
57
95

Code still does not work. I replaced the while and it still does not work. Am i not Binding the right results for that? – Neiko Sep 08 '19 at 03:14
@Neiko sorry, my mistake, I forgot to use the bound variables. See my edit. – Nick Sep 08 '19 at 03:18
Yeah code is still not working. while($row=mysqli_fetch_array($stmt)) { ?> – Neiko Sep 08 '19 at 03:26
@Neiko You should remove that code and replace it with the code from my answer. – Nick Sep 08 '19 at 03:27
Ooops yeah sent you the wrong code. But i did your solution and it still did not work. fetch()) { ?> Do you think it is something with the query – Neiko Sep 08 '19 at 03:31
Just noticed you're not actually preparing a query. See my edit for how to write that line properly. You really should turn on PHP error reporting (see [this Q&A](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display)) and you will find errors a lot faster (you would have got an error from that line of code) – Nick Sep 08 '19 at 03:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/199130/discussion-between-neiko-and-nick). – Neiko Sep 08 '19 at 03:56

MYSQL prepared statment query not matching WRITTERID with WRITTER

1 Answers1