CODEWITHSUNDEEP
phpmysql
user3742456
user3742456

Reputation: 13

foreach loop not getting next result

i am stuck in a task which will just carry a for-loop between two array'S counting the array and select query to fetch the result.i am only getting the first matched result echoed not the next record.what i am doing is

<?php 
//db cnnect
//fetch between 06:00:00 to 08:30:00   09:00:00 to 10:00:00
$date=array('06:00:00','09:00:00');
$date1=array('08:30:00','10:00:00');

$count=count($date);
for($i=0;$i<$count;$i++){

$sql="SELECT count(*) AS test FROM fo WHERE DATE_FORMAT(sys_time,'%H:%m:%i') BETWEEN DATE_FORMAT(sys_time,'$date[$i]') AND  DATE_FORMAT(sys_time,'$date1[$i]')";
$i=$i+1;
}
$query=mysql_query($sql);
if(!$query){
die('could not connect'.mysql_error());
}
echo'<html>
<head>
<title>Count User Info TimeWise</title>
</head>
<h1>Count User</h1>
<table border="3" cellspacing="2">
<tr>
<th>Count</th>';

while($row=mysql_fetch_array($query)) {
echo "<tr>";
$str=$row['test'];
$subcategory = explode(',', $str);
foreach($subcategory as $value) {
echo "<td>" . $value . "</td>"; 
}}
echo "</table>";      
echo "</html>";
?>

its fetching the 1st record between 6-8 correctly not the next from 9-10.what i am doing wrong.and how can i get it.thanks

Upvotes: 0

Views: 102

Answers (3)

mychalvlcek
mychalvlcek

Reputation: 4046

  • remove increment line in for loop because its already achieved in for statement
  • wrap all the code to the first for loop
$count=count($date);
for($i=0; $i < $count; $i++) {
    $sql="SELECT count(*) AS test FROM fo WHERE DATE_FORMAT(sys_time,'%H:%m:%i') BETWEEN DATE_FORMAT(sys_time,'$date[$i]') AND  DATE_FORMAT(sys_time,'$date1[$i]')";
    // $i = $i + 1; delete this line

    // rest of your handling code

    $query=mysql_query($sql);
    if(!$query){
        die('could not connect'.mysql_error());
    }
    echo'<html>
           <head>
             <title>Count User Info TimeWise</title>
           </head>
           <body>
             <table>
               <tr><th>Count</th></tr>';

    while($row=mysql_fetch_array($query)) {
        echo "<tr>";
        $subcategory = explode(',', $row['test']);
        foreach($subcategory as $value) {
            echo "<td>" . $value . "</td>";
        }
        echo "</tr>";
    }
    echo "</table>";      
    echo "</html>";
}

and do NOT use mysql extension anymore - it's deprecated. Use newer alternatives like: mysqli or PDO

Upvotes: 1

Think Different
Think Different

Reputation: 2815

Your structure should be like this:

<?php 
echo'<html>
       <head>
          <title>Count User Info TimeWise</title>
       </head>
       <body>
            <table>
               <tr>
                 <th>Count</th>
               </tr>';

//db cnnect
//fetch between 06:00:00 to 08:30:00   09:00:00 to 10:00:00
$date=array('06:00:00','09:00:00');
$date1=array('08:30:00','10:00:00');

$count=count($date);
for($i=0;$i<$count;$i++){

     $sql="SELECT count(*) AS test FROM fo WHERE DATE_FORMAT(sys_time,'%H:%m:%i')  BETWEEN DATE_FORMAT(sys_time,'$date[$i]') AND  DATE_FORMAT(sys_time,'$date1[$i]')";


     $query=mysql_query($sql);
     if(!$query){
          die('could not connect'.mysql_error());
     }


     while($row=mysql_fetch_array($query)) {
         echo "<tr>";
         $str=$row['test'];
         $subcategory = explode(',', $str);
         foreach($subcategory as $value) {
               echo "<td>" . $value . "</td>"; 
         }
         echo '</tr>';
     }
}
echo "</table></body>";      
echo "</html>";
?>

NOTE: mysql_* is deprecated use mysqli_* instead

Upvotes: 1

Cheruvian
Cheruvian

Reputation: 5867

Pretty sure you have an extra increment. Also if you want to run 2 seperate queries your curly braces are in the wrong spot. You should also fix your html so it is valid.

<?php 
//db cnnect
//fetch between 06:00:00 to 08:30:00   09:00:00 to 10:00:00
$date=array('06:00:00','09:00:00');
$date1=array('08:30:00','10:00:00');

echo'<html>
    <head>
        <title>Count User Info TimeWise</title>
    </head>
    <body>
        <table>
        <tr>
            <th>Count</th>
        </tr>';
$count=count($date);
for($i=0;$i<$count;$i++){

    $sql="SELECT count(*) AS test FROM fo WHERE DATE_FORMAT(sys_time,'%H:%m:%i') BETWEEN DATE_FORMAT(sys_time,'$date[$i]') AND  DATE_FORMAT(sys_time,'$date1[$i]')";
    //This is unneccessary since you increment it in your for loop
    //$i=$i+1;
    //Moving this down so it prints both query results
    //}
    $query=mysql_query($sql);
    if(!$query){
        die('could not connect'.mysql_error());
    }

    while($row=mysql_fetch_array($query)) 
    {
        echo "<tr>";
        $str=$row['test'];
        $subcategory = explode(',', $str);
        foreach($subcategory as $value) 
        {
            echo "<td>" . $value . "</td>";
        }
        echo "</tr>";
    } 
//New }
}
echo "</table>";      
echo "</html>";
?>

You should also look into upgrading to PDO as the mysql extension is considered in some ways insecure and is no longer supported.

Upvotes: 3

Related Questions