MySQL, show visitors

[chrisvmarle]

I have this MySQL table, lets say like this:
page | ip | date
--------------------
home| 1 | 11:00
home| 1 | 12:00
links | 1 | 12:05
home| 2 | 12:15
links | 3 | 15:00

How would I write a script that will create an output (based on the table) like this:
home:
1 11:00
1 12:00
2 12:15

links:
1 12:05
3 15:00

I hope you can help me
Mzzl, Chris

[weronpc]

Hey, I don't think this is the efficient way to code it, but I think this might answer your question.

use mysql_fetch_array()

<?php
$result = mysql_array("select * from your_table_name", $connection);

while ($row = mysql_fetch_array($result))
{
echo "Home <br />";
echo $row["home"];
}


while ($row = mysql_fetch_array($result))
{
echo "Links <br />";
echo $row["links"];
}
?>

Ok, I don't know it will work or not, but you get the idea, just test it and let me know if it work or not...

Mike

[ASAAKI]

i would do it like this:


// first get all the pages
//i'm assuming the table's called 'visitors'
$pageSql = "SELECT page FROM visitors";
$pageResult = mysql_query($pageSql);
// now hold all the pages in an array
for($i = 0; $i < mysql_num_rows($pageResult); $i++){
$pageRowArray=mysql_fetch_row($pageResult);
/*$page will be the array to hold all the pages just queried from
the table. the $row_array array only contains 'page', the one field fetched from the query, which is $pageRowArray[0] */
$page[$i] = $pageRowArray[0];
}

/*now that the $page array is populated, select the rest of the
fields according to the values in the array */

/*first loop thru the page array to print out the page and to make
the right query */
for($i = 0; $i < count($page); $i++){
echo("<b>".$page[$i].": </b><br />");//print out the current page
$sql = "SELECT ip, time FROM visitors WHERE page = '".$page[$i]."'"; // make the query to select ips and times according to current page
$result = mysql_query($sql);
//and now loop through the ips and times of that page
for($j = 0; $j < mysql_num_rows($result); $j++){
$iptimeRowArray = mysql_fetch_array($result);
//now print out the values.
echo($iptimeRowArray[0]." - <i>".$iptimeRowArray[1]."</i> ");
echo("<br />");
}
echo("<br />"); // just an extra line before the next 'page'
}



hope it helps:)

[chrisvmarle]

First of all,
Thanks for the script

Originally posted by ASAAKI
i would do it like this:

Code Skipped

hope it helps:)

I've tried it, but I get an output that looks like this:

home:
1 11:00
1 12:00
2 12:15

links:
1 12:05
3 15:00

home:
1 11:00
1 12:00
2 12:15

links:
1 12:05
3 15:00

But I can't explain why...

If I add
echo("$page[$i]<br>");
under
$page[$i] = $pageRowArray[0];

I get an output like this:
home
links
home
links

Please help
Mzzl, Chris


Been thinking, maybe the problem is solved if there is a function like "filter_double":
$page[$i] = $pageRowArray[0];
$page = filter_double($page);

[ASAAKI]

hehe that's funny:D
change

$pageSql = "SELECT page FROM visitors";


to

$pageSql = "SELECT DISTINCT page FROM visitors";


and see if it works.

[chrisvmarle]

Originally posted by ASAAKI
hehe that's funny:D
change

$pageSql = "SELECT page FROM visitors";


to

$pageSql = "SELECT DISTINCT page FROM visitors";


and see if it works.

Thanks, that solves the problem

Thank you for helping me
I think I can code the rest myself, if not, you'll see a new post :D

Mzzl, Chris

[ASAAKI]

my pleasure:D

[chrisvmarle]

1 Question though, on MySQL
Do you know how to remove all the rows where "page" starts with (let's say) "test"
So that "test_1", "testpage", "testing" will be removed?

(I'd like a MySQL statement for that, but a PHP code is fine too)

Mzzl, Chris

[chrisvmarle]

Never mind, I got it :D

DELTE FROM `database` WHRE page LIKE "test%"