Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    the Netherlands
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL, show visitors

    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
    My Website
    010100010011110101110100011011110111000001101000

  • #2
    Regular Coder
    Join Date
    Apr 2003
    Location
    Canada, Ontario, Mississauga
    Posts
    312
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hope this help

    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

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    This little Earth.
    Posts
    383
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i would do it like this:

    PHP Code:
    // 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
    'If you don't stand for something, you'll fall for anything.'

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    the Netherlands
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Edit:
    Been thinking, maybe the problem is solved if there is a function like "filter_double":
    $page[$i] = $pageRowArray[0];
    $page = filter_double($page);
    Last edited by chrisvmarle; 04-07-2003 at 10:00 PM.
    My Website
    010100010011110101110100011011110111000001101000

  • #5
    Regular Coder
    Join Date
    Jul 2002
    Location
    This little Earth.
    Posts
    383
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hehe that's funny
    change
    PHP Code:
    $pageSql "SELECT page FROM visitors"
    to
    PHP Code:
    $pageSql "SELECT DISTINCT page FROM visitors"
    and see if it works.
    'If you don't stand for something, you'll fall for anything.'

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    the Netherlands
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by ASAAKI
    hehe that's funny
    change
    PHP Code:
    $pageSql "SELECT page FROM visitors"
    to
    PHP Code:
    $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

    Mzzl, Chris
    My Website
    010100010011110101110100011011110111000001101000

  • #7
    Regular Coder
    Join Date
    Jul 2002
    Location
    This little Earth.
    Posts
    383
    Thanks
    0
    Thanked 0 Times in 0 Posts
    my pleasure
    'If you don't stand for something, you'll fall for anything.'

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    the Netherlands
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    My Website
    010100010011110101110100011011110111000001101000

  • #9
    Regular Coder
    Join Date
    Jun 2002
    Location
    the Netherlands
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Never mind, I got it

    DELTE FROM `database` WHRE page LIKE "test%"
    My Website
    010100010011110101110100011011110111000001101000


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •