...

View Full Version : MySQL, show visitors



chrisvmarle
04-07-2003, 03:12 PM
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
04-07-2003, 05:34 PM
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
04-07-2003, 06:48 PM
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
04-07-2003, 08:51 PM
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
04-07-2003, 09:00 PM
hehe that's funny:D
change


$pageSql = "SELECT page FROM visitors";


to


$pageSql = "SELECT DISTINCT page FROM visitors";


and see if it works.

chrisvmarle
04-07-2003, 09:02 PM
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
04-07-2003, 10:25 PM
my pleasure:D

chrisvmarle
04-07-2003, 10:30 PM
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
04-07-2003, 10:40 PM
Never mind, I got it :D

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum