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 4 of 4
  1. #1
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question Need some more help with SQL

    Hey, this is probably a simple thing to do, but I didn't write this script so I'm a little confused.

    I have a page that lists all info from a table. The problem I have is that, when there's more than one value assigned to a certain userID (even if its a new insert, it's because I assign the values by user ID), instead of adding a new line on the table with the extra info, it simply says "Multiple addresses".

    Here's the full code:

    Code:
    <?php
    session_start();
    
    $username = $_SESSION['username'];
    $userID = $_SESSION['userID'];
    if (strlen($username) >= 1) {
    
    include("../account/data/db_connect.php");
    $view = $_GET["view"];
    if($view!="all" && $view!="record")
      $view = "all";
    if($view == "all")
    {
        $sql = "SELECT GROUP_CONCAT(DISTINCT ip_address) as ip_address_list,
                       COUNT(DISTINCT ip_address) as ip_total,
                       COUNT(visitor_id) as page_count,
                       MIN(timestamp) as start_time,
                       MAX(timestamp) as end_time
                FROM visitor_tracking
                WHERE visitor_id = $userID";
        $result = mysql_query($sql);
        if($result==false){
            $view = "error";
            $error = "Could not retrieve values";   
        }
    } else {
        //show pages for a specific visitor
        $visitor_id = $_SESSION['userID'];
        //run $visitor_id through filter_var to check it's not an invalid
        //value, or a hack attempt
        if(!filter_var($visitor_id, FILTER_VALIDATE_INT, 0)){
            $error = "Invalid ID specified";   
            $view = "error";
        } else {
            $sql = "SELECT timestamp, page_name, query_string, ip_address FROM
              visitor_tracking WHERE visitor_id = '$visitor_id'";
            $result = mysql_query($sql);
        }    
    }
    function display_date($time){
        return date("F j, Y, g:i a", $time);   
    }
    
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-US">
    
     <head>
        <meta charset="utf-8">
        <!-- <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> -->
    
        <title>IP Logs</title>
    
        <meta name="description" content="">
        <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no" />
    
        <link href='http://fonts.googleapis.com/css?family=Lato:300,400,700,300italic,400italic|Raleway:400,300,700' rel='stylesheet' type='text/css'>
        <link rel="stylesheet" href="../css/bootstrap.min.css">
        <link rel="stylesheet" href="../css/icomoon.css">
        <link rel="stylesheet" href="../css/main.css">
        <link rel="stylesheet" href="../css/colors/red.css">
        <link rel="stylesheet" href="../css/animate.min.css">
    
        <script src="../js/other.min.js"></script>
    </head>
     
    <?php
    	include '../nav2.html';
    ?>
    
    	    <!-- START HEADER BAR -->
        <div class="top-bar">
            <span class="nav-trigger" data-icon="&#xe005;">Menu</span>
            <div class="container">
                <div><b>Logs</b></div>
            </div>
        </div>
        <!-- END HEADER BAR -->
    	
        <!-- START HEADER -->
        <header class="main-head" id="header">
            <!-- START MAIN SlIDER -->
            <div class="sl-slider-wrapper main-slider">
    
            </div>
    	</header>
            <!-- END MAIN SlIDER -->
    
    <br/>
    <br/>
    <div class="main-head">
            <!-- END MAIN SlIDER -->
    		<br/>
    		<br/>
    		<br/>
    		<br/>
    
    		<br/>
    		<center>
    			<div id="ABOUT">
    				<div id="logsgrey">
    					<div id="aboutcontent">
    						These are the accumulative logs of visited ip addresses using your ip-tracking pages created with ipbite.
    					</div>
    						<div id="aboutred">
    							<div id="abouttitle">
    									<center>What are these logs<font size="4">?</font></center>
    							</div>
    						</div>
    				</div>
    			</div>
    			
    		<br/>
    
    <h1>IP Tracker Report</h1>
    <?php if($view=="all") {
        //display all of the results grouped by visitor
        if($row = mysql_fetch_array($result)){
        ?>
        <table>
          <tbody>
          <tr>
          <th><font color="f3f3f3">IP Address(es)</font></th>
          <th><font color="f3f3f3">Visit Time</font></th>
          <th><font color="f3f3f3">Pages visited</font></th>
          </tr>
        <?php
          do{
            if($row["ip_total"] > 1)
                $ip_list = "Multiple addresses";
            else
            $ip_list = $row["ip_address_list"];
            $start_time = strtotime($row["start_time"]);
            $end_time = strtotime($row["end_time"]);
            $start = display_date($start_time);
            $end = display_date($end_time);
            $duration = $end_time - $start_time;
            if($duration >= 60) {
                $duration = number_format($duration/60, 1)." minutes";
            }
            else {
                $duration = $duration." seconds";   
            }
            echo "<tr>";
            echo "<td>$ip_list</td>";
            echo "<td>$start</td>";
            echo "<td>{$row["page_count"]}</td>";
            echo "</tr>";
          } while ($row = mysql_fetch_array($result));
        ?>
         </tbody>
        </table>
        <?php } else { ?>
          <h3>No records in the table yet</h3>
        <?php } ?>
    <?php } elseif($view=="record"){ ?>
      <h3>Showing records for Visitor <?php echo $visitor_id; ?></h3>
      <p><a href="ip_report.php">back</a></p>
      <?php
        //show all pages for a single visitor
        if($row = mysql_fetch_array($result)){
        ?>
        <table>
          <tbody>
          <tr>
          <th>Page viewed</th>
          <th>Query string</th>
          <th>Time of view</th>
          </tr>
        <?php
          do{
            if($row["ip_total"] > 1)
                $ip_list = "More than 1";
            else
                $ip_list = $row["ip_address_list"];
            $time = display_date(strtotime($row["timestamp"]));
            echo "<tr>";
            echo "<td>{$row["page_name"]}</td>";
            echo "<td>{$row["query_string"]}</td>";
            echo "<td>$time</td>";
            echo "</tr>";
          } while ($row = mysql_fetch_array($result));
        ?>
          </tbody>
        </table>
        <?php } else { ?>
          <h3>No records for this visitor</h3>  
        <?php
        }
    } elseif($view=="error") { ?>
        <h3>There was an error</h3>
        <?php echo $error;
    }
    ?>		
    		
            <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>
    		<script src="../js/main.js"></script>
    
    </body>
    <?php
    }
    else {
    	header("location:../account/login.html?error=mustlogin");
    }
    ?>
    Any suggestions? :3

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    assigned to a certain userID
    Code:
    SELECT GROUP_CONCAT(DISTINCT ip_address) as ip_address_list,
                       COUNT(DISTINCT ip_address) as ip_total,
                       COUNT(visitor_id) as page_count,
                       MIN(timestamp) as start_time,
                       MAX(timestamp) as end_time
                FROM visitor_tracking
                WHERE visitor_id = $userID";
    Where is userID in your sql, should be at the end, like
    "group by userID"

    Mysql allows without that but then you don't know what it realy does.

    GROUP_CONCAT, GROUP BY does that. Merges (agregate) rows with same userID. That is the intent of your code.

    remove

    Code:
    WHERE visitor_id = $userID";
    from code, run, that will clarify things for you.

    EDIT: do yourself a favour, extract sql code and run it in some sql client.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Bubikol is wrong.

    In the specific SQL code you are using, there is no need for any GROUP BY.

    Bubikol: He is *LIMITING* the selected records to ONLY those for a certain userid thanks to his WHERE clause. He really *DOES* want all those aggregate functions for *ALL* records for that one userid.

    ***********

    weCode: You were *GIVEN* the answer to this in another thread!

    Just change this code:
    Code:
            if($row["ip_total"] > 1)
                $ip_list = "Multiple addresses";
            else
                $ip_list = $row["ip_address_list"];
    to *ONLY* this:
    Code:
            $ip_list = $row["ip_address_list"];
    And this has *NOTHING* to do with MySQL, per se. It's entirely the quirky way your PHP code is written.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Ahhh...I see your comment in the other thread:
    I've done that, what it does is it lists both values together, in one line. What I want it to do is not combine two sets of values, but to create a whole new line
    You *can* do that, but then you can not *ALSO* get the COUNT, COUNT, MIN, MAX values that you are after. You would need two queries.

    An easier way to do this: Leave the GROUP_CONCAT alone.

    And then, to get multiple output lines from that value, just use PHP's explode( ) function:
    Code:
         $ip_array = explode( ",", $row["ip_address_list"] );
    That will give you an *array* of IP addresses, and then you can just run a PHP loop to output them all. EXAMPLE ONLY:
    Code:
         $ip_array = explode( ",", $row["ip_address_list"] );
         for ( $i = 0; $i < count($ip_array); ++$i )
         {
             echo $ip_array[$i] . "<br/>\n";
         }
    How the heck you will fit that in to make sense in the rest of your display, I don't know. But in any case it's all a PHP problem.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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