rushi kulkarni rushi kulkarni - 23 days ago 6
MySQL Question

Selecting particulars in mysql

I am using a table in which I have different particulars and different quantities for same ID, now I want quantities of each particular separately for that same particular ID to be printed.

$mq="SELECT count( particulars ) AS 'n' FROM delivery_challan WHERE job_order_id =".$_GET['jo'];
$ha=mysqli_query($link,$mq);


$haha=mysqli_fetch_object($ha);
$n=$haha->n;
echo $n;
for($i=1;$i<=$n;$i++)
{
$nq="SELECT qty FROM `delivery_challan` WHERE job_order_id=".$_GET['jo'];

$r=mysqli_query($link,$nq);

//$i=1;
$rs=mysqli_fetch_object($r);
$qty=$rs->qty;
}


This is how I am trying to fetch the respective quantity in $qty variable. This variable is used below :

<td><input name="invqty[]" type="text" onchange="SetDefault(<?php echo $cnt; ?>, $(this).val());" onkeyup="this.onchange();" onpaste="this.onchange();" oninput="this.onchange();" class="form-control1" id="invqty<?php echo $cnt; ?>" size="5" value="<?php echo $qty; ?>"></td>


For example :

In my table 'delivery_challan' I have 3 particulars x, y & z with 3 different quantities 10,20 & 30 respectively. Now in the above mentioned I want all the three quantities to be printed. The is being printed 3 times correctly, but in all 3 's only 10 is printed where as 10 then 20 and in 3rd, 30 must be printed.
But only the first value is being printed.

I am not able find a solution. Please help me. Thanks.

Answer Source

Something like this should work. You don't need to fetch a count of the results first, instead you can just loop through the result set from the main query until you reach the end. This example uses prepared statements and parameters to protect your database from injection attacks:

if ($stmt = mysqli_prepare($link, "SELECT qty FROM `delivery_challan` WHERE job_order_id= ?")) 
{
  //bind the job order parameter
  mysqli_stmt_bind_param($stmt, 'i', $_GET['jo']);

  //execute the query
  mysqli_stmt_execute($stmt);

  //bind the columns in the result to variables for use in the next part
  mysqli_stmt_bind_result($stmt, $qty);

  //loop through all the rows returned, and print the output
  while (mysqli_stmt_fetch($stmt)) {
     echo '<td><input name="invqty[]" type="text" onchange="SetDefault(<?php echo $cnt; ?>, $(this).val());" onkeyup="this.onchange();" onpaste="this.onchange();" oninput="this.onchange();" class="form-control1" id="invqty<?php echo $cnt; ?>" size="5" value="'.$qty.'"></td>';
  }
  mysqli_stmt_close($stmt);
}
else
{
  echo "ERROR - failed to prepare SQL statement";
  //here you should log the result of running mysqli_error() to your application's error log. Don't show it to the user.
}

You can see more examples here, for instance: http://php.net/manual/en/mysqli-stmt.fetch.php