Error in executing while loop for fetching records from database

Question

I have an error in executing a while loop in php. actually in php code I tried to fetch records from database for that i establish connection at the beginning of code.In code my outer loop executed well but inner loop executed only one time and terminated.Then i tried to establish mysql connection once again within while loop then it executed.If i comment this it not work well.Tell me to run while loop there is need to establish connection once again or there is another way to solve problem.

Here is my code:

$con    = mysqli_connect("localhost", "root", "root", "scheduler");
$sql    = "select * from jobschedule";
$result = mysqli_query($con,$sql);
$sql2   = "SET GLOBAL event_scheduler = 1";

mysqli_query($con,$sql2);
while($row = mysqli_fetch_array($result))
{
    // $con = mysqli_connect("localhost", "root", "root", "scheduler");

    $dbname  = $row['DBName'];
    $jobname = $row['JobName'];

    $sql1    = "call $dbname.$jobname";
    $result1 = mysqli_query($con,$sql1);

    while($row1 = mysqli_fetch_array($result1))
    {
        echo '<tr>';
           . '<td>' . $row1['Name'] . '</td>';
           . '<td>' . $row1['CountryCode'] . '</td>'
           . '<td>' . $row1['District'] . '</td>'
           . '<td>' . $row1['Population'] . '</td>'
           . '</tr>';
        }    
    }

If the inner loop terminates after one time, it just means that the query only returned one row. Are you sure it's supposed to return more? — Barmar
– Barmar, Commented Sep 12, 2013 at 8:10

Barmar · Accepted Answer · 2013-09-12 08:11:34Z

1

Try this

//Connection
$con = mysqli_connect("localhost", "root", "root", "dbname") or die("Error " . mysqli_error($con));

//Query 

$query = "SELECT * FROM tablename" ;

//execute the query.

$result = $con->query($query) or die("Error in the Query.." . mysqli_error($con));

//display information:

while($row = mysqli_fetch_array($result)) {
  echo $row["field_name"] . "<br>";
}

edited Sep 12, 2013 at 8:11

Barmar

789k57 gold badges554 silver badges669 bronze badges

answered Sep 12, 2013 at 7:52

Reshil

4813 silver badges9 bronze badges

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

2 Comments

dialogik Over a year ago

I like that OR die(...) - that's a great way to see where exactly the error occurs. Even better would be try { ... } catch(Exception $e) { throw new Exception() }.

Barmar Over a year ago

In the question he said the loop is executing one time. That means the query was successful, so adding this error checking, while a good idea, probably won't solve the problem.

Alex · Accepted Answer · 2013-09-12 07:40:56Z

0

You are trying to acces normal array's values with keys

    $dbname = $row['DBName'];
    $jobname = $row['JobName'];

To do this you must have associative arrays. Try changing

$row = mysqli_fetch_array

to

$row = mysqli_fetch_assoc

answered Sep 12, 2013 at 7:40

Alex

1,30411 silver badges20 bronze badges

2 Comments

Barmar Over a year ago

By default, mysqli_fetch_array() returns an array with both numeric and named keys, so his array accesses should work.

rohini jadhav Over a year ago

I tried both solution for my problem but it not work. it giving me error like: "Commands out of sync; you can't run this command now" when inner while loop run

Collectives™ on Stack Overflow

Error in executing while loop for fetching records from database

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related