-1

I have to write a php script with mysql queries to insert and update some tables in database. I should use transactions to make sure all the queries are run. The problem is I have to used first query data as the input values of the second query inside a while loop. I write a sample script, but it doesn't work. No data insert or update. Please help..!

Here's the php script.

    $processes = array();

    $select_qty = "SELECT * FROM stock_master WHERE itemid='$itemId'";
    $result_qty = mysql_query($select_qty);

    while($row = mysql_fetch_assoc($result_qty)){

        $id = $row['id'];
        $stokqty = $row['availableqty'];


        $processes[] = "UPDATE stock_master SET availableqty = '$stokqty' WHERE itemid = '$id'";
        $processes[] = "INSERT INTO nista_bill_master (billno, billdate, icode, iname, warehouse, quantity) VALUES ('$id', 'xx', 'xx', 'xx','xx', '$stokqty')";

    }

    mysql_query("SET AUTOCOMMIT=0");
    mysql_query("START TRANSACTION");  

    $count_processes = count($processes);
    for($i=0; $i < $count_processes; $i++){
        try {
            $q = mysql_query($processes[$i]);
            if (!$q) throw new Exception(mysql_error());
        }
        catch (Exception $e) {
            mysql_query("ROLLBACK");
            break;
        }
    }
    mysql_query("COMMIT");
    mysql_query("SET AUTOCOMMIT=1");
Hirantha
  • 101
  • 6
  • The `mysql_*` functions in PHP are deprecated and shouldn't be used. Please read [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for information on why and what to replace them with. – Matt Raines Apr 07 '16 at 11:49
  • I am editing an existing program. It used mysql (not mysqli). So what do I do..? – Hirantha Apr 07 '16 at 11:52
  • Replacing mysql with mysqli is pretty straightforward. Read the [docs](http://php.net/manual/en/book.mysqli.php) and you'll find most of the function calls have similar names with slightly different parameters. You'll want to look into [using prepared statements](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) too. – Matt Raines Apr 07 '16 at 11:56

1 Answers1

0

Hey this will be the issue. In this statement you have put "," in last

$processes[] = "INSERT INTO nista_bill_master (billno, billdate, icode, iname, warehouse, quantity) VALUES ('$id', 'xx', 'xx', 'xx','xx', '$stokqty',)";

Remove "," form the statement like below:

$processes[] = "INSERT INTO nista_bill_master (billno, billdate, icode, iname, warehouse, quantity) VALUES ('$id', 'xx', 'xx', 'xx','xx', '$stokqty')";
vishu
  • 231
  • 1
  • 9
  • Data is not inserted. The commit part is not working. – Hirantha Apr 07 '16 at 11:50
  • can u print the process array in which the statement stored and see the statement once a while, there is any problem – vishu Apr 07 '16 at 12:05
  • Yes of course, I print and check all the queries. Once array containing 16 queries with no errors. mysql doesn't support for transactions. Is it true..? Or should I convert entire program into mysqli ..? – Hirantha Apr 08 '16 at 03:34
  • It is solved..I did not echo the error which is caught by exception class. It shows an error with in a query. Now it was fixed. Thanks...! – Hirantha Apr 08 '16 at 05:43