MySQL fetching results nothing but query works fine in Mysql workbench

Question

MySQL fetching results nothing but query works fine in Mysql workbench:

$sql1="set @num := 0, @type := '';
select sum(t1.amount)as total_bal from (select cat1.customer_account_num, cat1.amount, @num := if(@type = cat1.customer_account_num, @num + 1, 1) as row_number, @type := cat1.customer_account_num as dummy,cat1.date_of_transaction from customer_account_transaction AS cat1, customer_account AS a, plan AS p where cat1.customer_account_num=a.customer_account_num and a.planid=p.planid and p.plantype=1 and a.executive='AV/E105' order by cat1.customer_account_num, cat1.date_of_transaction asc) as t1 where t1.row_number>1 and t1.date_of_transaction BETWEEN '2014-04-06' AND '2014-05-10';";

$result1=mysqli_query($GLOBALS['con'],$sql1);
    $daily_bal='';  
    if($result1)
    {   
        $row1=mysqli_fetch_array($result1);
        $daily_bal=$row1['total_bal'];
        echo $daily_bal;
    }

change it to mysql_query(...) or die(mysql_error()); so you get an error. — WizKid
– WizKid, Commented May 13, 2014 at 6:43
have a look at mysqli_multi_query() since your query is multi-statment — user2009750
– user2009750, Commented May 13, 2014 at 6:45
You can't run two queries with mysqli_query ... look into mysqli_multi_query — Mark Miller
– Mark Miller, Commented May 13, 2014 at 6:45

user2009750 · Accepted Answer · 2014-05-13 06:47:55Z

2

You can't execute multiple queries this way have a look at this. You need to use:

mysqli_multi_query()

This snippet from php.net should help you.

if (!$mysqli->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());

answered May 13, 2014 at 6:47

user2009750

3,1976 gold badges37 silver badges58 bronze badges

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

Comments

Umesh M Gowda · Accepted Answer · 2014-05-13 07:22:06Z

Thank you ɢʜʘʂʈ ʀɛɔʘɴ I changed my query as below and it worked.

$sql1="set @num := 0, @type := '';
select sum(t1.amount)as total_bal from (select cat1.customer_account_num, cat1.amount, @num := if(@type = cat1.customer_account_num, @num + 1, 1) as row_number, @type := cat1.customer_account_num as dummy,cat1.date_of_transaction from customer_account_transaction AS cat1, customer_account AS a, plan AS p where cat1.customer_account_num=a.customer_account_num and a.planid=p.planid and p.plantype=1 and a.executive='AV/E105' order by cat1.customer_account_num, cat1.date_of_transaction asc) as t1 where t1.row_number>1 and t1.date_of_transaction BETWEEN '2014-04-06' AND '2014-05-10';";

if (!mysqli_multi_query($GLOBALS['con'],$sql1)) {
    echo "Multi query failed: (" . mysqli_errno() . ") " . mysqli_error();
}
    $daily_bal='';  
    do {
        if ($result1 = mysqli_store_result($GLOBALS['con'])) {
            //var_dump(mysqli_fetch_all($result1,MYSQLI_ASSOC));
            $row1=mysqli_fetch_array($result1);
            $daily_bal=$row1['total_bal'];

            mysqli_free_result($result1);
        }
    } while (mysqli_more_results($GLOBALS['con']) && mysqli_next_result($GLOBALS['con']));

Collectives™ on Stack Overflow

MySQL fetching results nothing but query works fine in Mysql workbench

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related