udwo
July 9, 2014, 9:17pm
1
I am sort of new to php and have what may be simple question.
I have a table with id, userid, macAddress, IPaddress, date columns. there are multiple entries for each userid and i need to show them as follows:
userid1 total of records (3 f.e.)
userid2 total of records (2 f.e.)
userid3 total of records (3 f.e.)
and so on…
i cant seem to figure out how to repeat the sub rows. Any help is appreciated!
Is the date field just a date field or a date and time field?
What extension are you using for PHP to interact with MySQL?
There are a number of ways to go about it.clients as you didn’t mention the name.
<?php
$dbhost = "";
$dbuser = "";
$dbpass = "";
$dbname = "";
try
{
$dbconn = new PDO("mysql:host=$dbhost;dbname=$dbname", "$dbuser", "$dbpass");
} catch (PDOException $e) {
// print "Error!: " . $e->getMessage() . "<br/>";
print "an error occurred";
die();
}
/////////////////
/// Example 1 ///
/////////////////
$data = array();
$sql = "SELECT userid
, macAddress
, IPaddress
, date
FROM clients";
$query = $dbconn->prepare($sql);
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$data[$row['userid']]['macAddress'][] = $row['macAddress'];
$data[$row['userid']]['IPaddress'][] = $row['IPaddress'];
$data[$row['userid']]['date'][] = $row['date'];
}
//echo "<pre>";
//print_r($data);
//echo "</pre>";
// Format display as you like.
echo '<table>';
$users = array();
foreach($data as $userid => $arr):
$cnt = count($arr['macAddress']);
if(!in_array($userid,$users)):
$users[] = $userid;
echo '<tr>
<th colspan="3">Userid: ' . $userid . ' Total of records (' . $cnt . ' f.e.)</th>
</tr>'."\\r";
endif;
foreach($arr['macAddress'] as $key => $arr2):
$macAddress = $arr['macAddress'][$key];
$IPaddress = $arr['IPaddress'][$key];
$date = $arr['date'][$key];
echo '<tr>
<td>macAddress ' . $macAddress . '</td><td>IPaddress ' . $IPaddress . '</td><td> ' . $date . '</td>
</tr>'."\\r";
endforeach;
endforeach;
echo '</table>';
/////////////////
/// Example 2 ///
/////////////////
$data2 = array();
$sql2 = "SELECT userid
, macAddress
, IPaddress
, date
FROM clients
ORDER BY userid ASC";
$query2 = $dbconn->prepare($sql2);
$query2->execute();
while($row2 = $query2->fetch(PDO::FETCH_ASSOC)){
$data2[$row2['userid']][] = $row2;
}
//echo "<pre>";
//print_r($data2);
//echo "</pre>";
// Format display as you like.
echo '<table>';
foreach($data2 as $userid => $arr):
$cnt = count($arr);
echo '<tr>
<th colspan="3">Userid: ' . $userid . ' Total of records (' . $cnt . ' f.e.)</th>
</tr>'."\\r";
foreach($arr as $k2 => $arr2):
$macAddress = $arr[$k2]['macAddress'];
$IPaddress = $arr[$k2]['IPaddress'];
$date = $arr[$k2]['date'];
echo '<tr>
<td>macAddress ' . $macAddress . '</td><td>IPaddress ' . $IPaddress . '</td><td> ' . $date . '</td>
</tr>'."\\r";
endforeach;
endforeach;
echo '</table>';
?>
udwo
July 10, 2014, 3:36pm
4
thank you Drummin, got it going with little tweaks! i really appreciate your help. last question is… is there a way to sort it by the total number of the records? show userid with the most at the top?
Using a sort function you can sort the array by number of records. The first example I posted earlier works best in this case.
<?php
$dbhost = "";
$dbuser = "";
$dbpass = "";
$dbname = "";
function aasort (&$array, $key) {
$sorter=array();
$ret=array();
reset($array);
foreach ($array as $ii => $va) {
$sorter[$ii]=$va[$key];
}
arsort($sorter);
foreach ($sorter as $ii => $va) {
$ret[$ii]=$array[$ii];
}
$array=$ret;
}
try
{
$dbconn = new PDO("mysql:host=$dbhost;dbname=$dbname", "$dbuser", "$dbpass");
} catch (PDOException $e) {
// print "Error!: " . $e->getMessage() . "<br/>";
print "an error occurred";
die();
}
/////////////////
/// Example 1 ///
/////////////////
$data = array();
$sql = "SELECT userid
, macAddress
, IPaddress
, date
FROM clients";
$query = $dbconn->prepare($sql);
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$data[$row['userid']]['macAddress'][] = $row['macAddress'];
$data[$row['userid']]['IPaddress'][] = $row['IPaddress'];
$data[$row['userid']]['date'][] = $row['date'];
}
//Call sort function
aasort($data,'macAddress');
//echo "<pre>";
//print_r($data);
//echo "</pre>";
// Format display as you like.
echo '<table>';
$users = array();
foreach($data as $userid => $arr):
$cnt = count($arr['macAddress']);
if(!in_array($userid,$users)):
$users[] = $userid;
echo '<tr>
<th colspan="3">Userid: ' . $userid . ' Total of records (' . $cnt . ' f.e.)</th>
</tr>'."\\r";
endif;
foreach($arr['macAddress'] as $key => $arr2):
$macAddress = $arr['macAddress'][$key];
$IPaddress = $arr['IPaddress'][$key];
$date = $arr['date'][$key];
echo '<tr>
<td>macAddress ' . $macAddress . '</td><td>IPaddress ' . $IPaddress . '</td><td> ' . $date . '</td>
</tr>'."\\r";
endforeach;
endforeach;
echo '</table>';
?>
udwo
July 10, 2014, 4:56pm
6
thank you. it did not work. i did some reading and tired variations of the sort function but it didn’t work. i could get it sorted if i used rsort($data, $arr[‘macAddress’]); but then the userid did not sho printed, just the array position… like 0, 1, and so on. here is my code, maybe you can pooint out what i did wrong. i had to change it a bit b/c i could not get the connection going to the server when i used your example. and the column fields are actually what they really are, i just didn’t have them exact at the time of writing the post so i used what i thought they will be:
$data = array();
$result = mysql_query("SELECT * FROM client WHERE userid !=''");
while($row = mysql_fetch_array( $result )){
$data[$row['userid']]['mac'][] = $row['mac'];
$data[$row['userid']]['ip_last_known'][] = $row['ip_last_known'];
$data[$row['userid']]['last_activity'][] = $row['last_activity'];
}
arsort($data,"mac");
//echo "<pre>";
//print_r($query);
//echo "</pre>";
// Format display as you like.
echo '<table>';
$users = array();
foreach($data as $userid => $arr):
$cnt = count($arr['mac']);
if(!in_array($userid,$users)):
$users[] = $userid;
echo '<tr>
<th colspan="3">Userid: ' . $userid . ' Total of records (' . $cnt . ')</th>
</tr>'."\\r";
endif;
foreach($arr['mac'] as $key => $arr2):
$mac = $arr['mac'][$key];
$ip_last_known = $arr['ip_last_known'][$key];
$last_activity = $arr['last_activity'][$key];
echo '<tr>
<td>macAddress ' . $mac . '</td><td>IPaddress ' . $ip_last_known . '</td><td> ' . $last_activity . '</td>
</tr>'."\\r";
endforeach;
endforeach;
echo '</table>';
Did you include the function aasort() I had at the top of the example?
function aasort (&$array, $key) {
$sorter=array();
$ret=array();
reset($array);
foreach ($array as $ii => $va) {
$sorter[$ii]=$va[$key];
}
arsort($sorter);
foreach ($sorter as $ii => $va) {
$ret[$ii]=$array[$ii];
}
$array=$ret;
}
Then call it with this line after you build the data.
//Call sort function
aasort($data,'mac');
udwo
July 10, 2014, 6:10pm
8
i didn’t at first but now i did and it works like a charm! that tells me how little i know and how much i have to learn! thank you for your help!!! have a great day!
@udwo ; Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.
udwo
July 11, 2014, 9:05pm
10
last question i have… i hope. would this be easily customizable to include page where i can type number of records i am searching and submit and have this code only show users that have for example 3 different MAC addresses (assuming 3 was the number i submitted)?
You would just add a limit type IF condition just after the $cnt = count($arr[‘mac’]); line that wraps around display coding.
udwo
July 14, 2014, 8:18pm
12
got it!!! perfect, thank you!