Can't insert all data in MySQL using PHP

Question

The title is not disclosing the whole thing for sure. Right now I'm working on a Lab Software. Here, all the individual test names & their details are in a table (say name 'test_info') & all the bills generated for those tests are in another table (say name 'bill_info') & when it's time to insert the test reports I'm storing it in another table (say name 'rpt_info').

There are a few group tests which are a collection of tests. I made a column named 'group' if the value is null then they are under no group test & when their is a value (which is name of a test) means this test is under that group test.

Now, take a look at what forced me to ask a question here. When its time to enter the value of test report, I just call them like-

           <?php
          $gtsql = "SELECT * FROM `bill_info` where `test_id` = '" . $pid."' and `group`='y'";
        $reslt = mysql_query($gtsql);
        while ($data = mysql_fetch_assoc($reslt))
        {
            $test_inf=  "SELECT * FROM `test_info` where `group` = '". $data["pr_name"]."'";
            $test_qr=  mysql_query($test_inf);
             $k = 1;
             while ($test = mysql_fetch_assoc($test_qr)) {

            echo '<tr><td width="80"></td><td align="left"> <input name="name" id="name" style="border:none; background: transparent;" value="' .  $test["name"] . '" readonly/></td><td width="144" align="left"> <INPUT name="result" id="result" /></td><td width="125" align="left"><input name="unit" id="unit" style="border:none; background: transparent;" value="' . $test["unit"] . '" readonly/></td><td align="left">'; 
$ref= $test["ref_range"];
$ref2= $test["ref_range2"];
$ref3= $test["ref_range3"];
if($ref=!"")
{ echo '<br/><input name="ref_range" id="ref_range" style="border:none; background: transparent;" value="'.$test["ref_range"].'" readonly/>';} 
if ($ref2=!"")
{ echo '<br/><input name="ref_range2" id="ref_range2" style="border:none; background: transparent;" value="'.$test["ref_range2"].'" readonly/>';} else { echo "";}
if ($ref3=!"")
{ echo '<br/><input name="ref_range3" id="ref_range3" style="border:none; background: transparent;" value="'.$test["ref_range3"].'" readonly/>';} else { echo "";}; 

'
</td></tr>
';
            $k++;
             }
    }


         ?>

This is working fine & showing all the tests & their info perfectly on the screen to the user. But when I try to insert these data in 'rpt_info' table then the problem appears. I've tried 'while', 'for' but can't insert the value to the table. Here is the insert code that I am running right now. One more thing to share to you friends, to get the perfect number of tests I made a row in another table (name 'sell') where I put individual test (bill) records. the row named 'num'. I calculate the 'num' value to find out how many times I want to run the code. the $num comes perfect when I do echo that one. but every time I got only one row being inserted.

 <?php
  if(isset($_POST['posted']))
  {

$prsql = "SELECT SUM(num) FROM `sell` where `test_id`='".$_POST['test_id']."'";
$prrs = mysql_query($prsql);
$datas = mysql_fetch_assoc($prrs);
$num= $datas['SUM(num)'];
$i=1;
while ($i < $num)
  {
$str_tst = "INSERT INTO `rpt_info` (`pt_id`,`test_id`,`result`,`name`,`unit`,`ref_range`, `ref_range2`, `ref_range3`, `rcv_date`, `dlv_date`, `age`, `test_cat`)VALUES ( '" . $_POST['pt_id'] . "', '" . $_POST['test_id'] . "', '" . $_POST['result'] . "', '" . $_POST['name'] . "', '" . $_POST['unit'] . "', '" . $_POST['ref_range'] . "', '" . $_POST['ref_range2'] . "', '" . $_POST['ref_range3'] . "', '" . $_POST['rcv_date']. "', '" . $_POST['dlv_date'] . "', '" . $_POST['pt_age'] . "', '" .  $_POST['test_cat']. "')";

 $i++;

  }
?>

<?php
   if(mysql_query($str_tst))
     {
        echo '................';
}
?>

Is there anyone who can help me with this?

This is my first question & may be I can't clear this to all of you. If you want to be more clear about the problem then feel free please to ask me questions.

Post the part that is not working, not the part that is working — Gerald Schneider
– Gerald Schneider, Commented Aug 20, 2015 at 7:23
Apart from the fact that you should not use mysql_* functions any more (use mysqli or PDO, you are creating an insert query, but you are not executing it. — Burki
– Burki, Commented Aug 20, 2015 at 7:40

Hearner · Accepted Answer · 2015-08-20 08:16:02Z

1

1) Your $num= $datas['SUM(num)']; doesn't exist. Try

$prsql = "SELECT SUM(num) as SumNum FROM `sell` where `test_id`='".$_POST['test_id']."'";

And you can get it using $num= $datas['SumNum'];

2) $str_tst is a simple string, you don't tell it to execute the query. You execute it using mysql_query($str_tst) or die mysql_error();. Use

while ($i < $num)
  {
$str_tst = "INSERT INTO `rpt_info` (`pt_id`,`test_id`,`result`,`name`,`unit`,`ref_range`, `ref_range2`, `ref_range3`, `rcv_date`, `dlv_date`, `age`, `test_cat`)VALUES ( '" . $_POST['pt_id'] . "', '" . $_POST['test_id'] . "', '" . $_POST['result'] . "', '" . $_POST['name'] . "', '" . $_POST['unit'] . "', '" . $_POST['ref_range'] . "', '" . $_POST['ref_range2'] . "', '" . $_POST['ref_range3'] . "', '" . $_POST['rcv_date']. "', '" . $_POST['dlv_date'] . "', '" . $_POST['pt_age'] . "', '" .  $_POST['test_cat']. "')";
mysql_query($str_tst) or die mysql_error(); // Each loop execute the query
 $i++;

  }

3) Your while loop isn't correct. I think you're missing one insert. You begin to 1 and you go, for example, to 10, so you are inserting 9 rows BECAUSE you're using < instead of <=

Advice : Stop using mysql_. Please change to mysqli_ or PDO

edited Aug 20, 2015 at 8:16

answered Aug 20, 2015 at 7:44

Hearner

2,7053 gold badges19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

corvusmile Over a year ago

1) but when I do echo $num it is showing the right value :/ Ok, I'm trying your way now

Hearner Over a year ago

The most important is that you are not executing your insert query, so this shouldn't work... And be careful, you are missing one insert. $i should begins to 0 or put <= in your loop

Hearner Over a year ago

Use ` or die mysql_error()` at the end of your query to see what erroor you have

corvusmile Over a year ago

3) I got that thought,too. But sadly every time it is inserting only 1 row, whether it is 5 or 6...

Hearner Over a year ago

Look your loop. You get a string, then $i++, then THE SAME VAR is edited with the new query, then it is edeting it again ang again 5-6 times. I said you sould pu the insert IN the loop, not outside

|

Collectives™ on Stack Overflow

Can't insert all data in MySQL using PHP

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related