Foreach Loop Php Mysql retrieve

Question

i want to retrieve mysql data using foreach loop, as i had 10 employees records with expenses, i want to display each employee name and his expenses with a table in a loop. please suggest me how to call this function using foreach

$data = mysql_query("SELECT * FROM employees where dt >= '$from' AND dt <= '$to' ORDER by empname ASC");
while ($result = mysql_fetch_assoc($data))
{

$ta = $result["ta"];
$da = $result["da"];
$petrol = $result["petrol"];
}

I need report like this.

Empname:

Record1 Record2
-----------------
Empname:

Record1 Record2
---------------

Please help.

Please use mysqli or PDO, as mysql_* is officially deprecated and should not be used anymore. Also, small "i" should not be used in questions neither :-) — Frederik.L
– Frederik.L, Commented Jun 29, 2013 at 8:15
Please provide more details like what is Record1, Record2, $ta, $da, $petrol and how you are retrieving the value of Empname, Record1, Record2? — Debashis
– Debashis, Commented Jun 29, 2013 at 8:16
@BolemVeeru why do you want to use a foreach if you can do it all with your while ? — Prix
– Prix, Commented Jun 29, 2013 at 8:19
Don't forget to take the SO Tour it will guide you on how to best use SO — Prix
– Prix, Commented Jun 29, 2013 at 9:58

Prix · Accepted Answer · 2013-07-01 08:55:12Z

1

There is no more support for mysql_* functions, they are officially deprecated, no longer maintained and will be removed in the future. You should update your code with PDO or MySQLi to ensure the functionality of your project in the future.

Since you're using mysql_*, I hope you're using mysql_real_escape_string on your $to and $from variables if they are coming from user input.

I am still not sure why you want to use a foreach but what you want to do, can be done with while which is also a loop:

$data = mysql_query("SELECT * FROM employees where dt >= '$from' AND dt <= '$to' ORDER by empname ASC");
while($result = mysql_fetch_assoc($data))
{
    $rows[] = $result;
}

print_r($rows);

Here is a working example using MySQLi:

<?php
$from = $_POST['from'];
$to = $_POST['to'];

if (is_null($from) || is_null($to))
    die('You must fill the from and to fields...');

$db = new mysqli('localhost', 'root', '', 'test_database');
if($db->connect_error)
    die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

if (!$stmt = $db->prepare("SELECT empname, ta, da, petrol FROM employees WHERE dt >= ? AND dt <= ?"))
    die('Prepare Error: ' . $db->error);

if (!$stmt->bind_param('ss', $from, $to))
    die('Bind Parameters Error ' . $stmt->error);

if (!$stmt->execute())
    die('Select Query Error ' . $stmt->error);

$stmt->bind_result($empname, $ta, $da, $petrol);
$rows = array();
while($stmt->fetch())
{
    $rows[$empname][] = array('ta' => $ta, 'da' => $da, 'petrol' => $petrol);
}
$stmt->close();
$db->close();

foreach ($rows as $empname=>$data)
{
    echo $empname . ':<br /><br />';
    foreach ($data as $result)
    {
        echo $result['ta'] . ' ' . $result['da'] . '<br />';
        echo '-----------------<br />';
        $petrol = $result['petrol'];
    }
    echo '==========================<br />';
}

Output:

Jorge:<br /><br />
12 3<br />
-----------------
1 4<br />
-----------------
2 6<br />
-----------------
3 1<br />
-----------------
33 11<br />
-----------------
==========================<br />
John:<br /><br />
4 3<br />
-----------------
5 6<br />
-----------------
6 8<br />
-----------------
7 9<br />
-----------------
8 10<br />
-----------------
==========================<br />

edited Jul 1, 2013 at 8:55

answered Jun 29, 2013 at 8:30

Prix

19.6k16 gold badges79 silver badges136 bronze badges

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

4 Comments

Php Gemini Over a year ago

Sorry Guys for late. @prix i can retrieve data using while but i need records by employee name and important thing is i need all records with that employee name should display in table. example i have 10 records with two user names. they are not in sequence order in mysql but i need first user name should display 5 records of his.

Prix Over a year ago

@BolemVeeru I see, that was some very important information missed on the main question, I will update my answer and let u know in a few.

Php Gemini Over a year ago

Yes, But it is not working tried. it is printing all records but i need all records in row by user name.

Prix Over a year ago

@BolemVeeru $rows[$data['empname']][] = $result; see if this is what u want.

Collectives™ on Stack Overflow

Foreach Loop Php Mysql retrieve

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related