Advanced Search

Results 1 to 6 of 6

Thread: where in where

  1. #1
    Join Date
    Jun 2010
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default where in where

    PHP Code:
    <?php
    /*foreach ($_POST as $a=>$b){
            echo $a.":".$b."<br/>";
        }*/
        
    include("config.php");
        
        
    $from     $_POST["from"];
        
    $to     $_POST["to"] ;
        
    $sel     $_POST["select"];
        
    $dur    $_POST["dur"];
        
        
    $sql "
                SELECT billsec, clid , src , lastapp , duration,disposition,SUM(billsec),COUNT(billsec)
                FROM cdr
                WHERE   calldate > '
    $from'
                    AND calldate < '
    $to'
                    AND duration > '
    $dur'
                    AND src = '
    $sel'
                    AND lastapp     != 'busy'
                    AND disposition !='FAILED'
                    AND disposition !='NO ANSWER'
                    AND disposition !='BUSY'
                    
            "
    ;
            
    $result mysql_query($sql);

    if (
    mysql_num_rows($result) == 0) {
            echo 
    "No rows found, nothing to print so am exiting";
        exit;
    }

    ?>
    <table  class="tablo1" border="1">
      <tr>
            <td><div>No :             </div></td>
            <td><div>Dahili :         </div></td>
            <td><div>Kaynak :         </div></td>
            <td><div>Durum :         </div></td>
            <td><div><?php echo $dur ?>sn .T.A</div></td>
            <td><div><?php echo $dur ?>sn .T.S</div></td>

      </tr>

    <?php
    $i
    =0;
    while (
    $row mysql_fetch_array($result)) {   
    $i++;
        echo
    '
            <tr>
                <td><div>'
    .$i                        .' </div></td>
                <td><div>'
    .$row["clid"]                .'</div></td>
                <td><div>'
    .$row["src"]                .'</div></td>
                <td><div>'
    .$row["lastapp"]            .'</div></td>
                <td><div>'
    .$row["COUNT(billsec)"]    .'</div></td>
                <td><div>'
    .$row["SUM(billsec)"]        .'</div></td>
        
            </tr>
            '
    ;
    }

    ?>
    </table>
    i can count and sum billsec values but how can i count and sum bilsec vlaue when disposition value is not busy or anything at the same time


    eg: like SUM(billsec) for disposition ='FAILED'
    Last edited by zodehala; 03-28-2013 at 05:25 PM.

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,622
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Two things:

    1) Do you mean that you want to SUM the `billsec` columns, even if the `disposition` column is (for example) "busy", or "failed"?

    Simply remove the constraints in your query that prevent that. Alternatively, run a second query that specifically SUMs only those records:
    Code:
    SELECT billsec, clid , src , lastapp , duration,disposition,SUM(billsec),COUNT(billsec)
                FROM cdr
                WHERE   calldate > '$from'
                    AND calldate < '$to'
                    AND duration > '$dur'
                    AND src = '$sel'
                    AND lastapp = 'busy' --or 'FAILED', etc., whichever you want to check
    If that's not what you are asking, please explain further.


    2) you should NEVER PLACE USER INPUT DIRECTLY IN AN SQL QUERY.
    Your code is vulnerable to SQL injection attacks and/or simple SQL errors.

    You must always validate and sanitize everything that a user provides to your script to make sure it is what it is supposed to be.
    You should also be using the appropriate escape function (in this case, mysql_real_escape_string(); but see 2a below) to make sure MySQL treats your strings as strings, and won't confuse them with commands.

    2a) # If at all possible, you should avoid using the mysql_* functions. #
    Existing code should be updated to avoid performance and security problems.
    Quote Originally Posted by php.net (official PHP website)
    Warning
    This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future.
    Instead, either the mysqli or PDO_MySQL extension should be used.
    See also the MySQL API Overview for further help while choosing a MySQL API.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  3. #3
    Join Date
    Jun 2010
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default



    this is my db

    how can i list following datas in one table at the same time

    datas between two dates which are ( for example
    src 110 and duration which is bigger than X and its count ands its sum where lastapp value is dial

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,622
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by zodehala View Post


    this is my db

    how can i list following datas in one table at the same time

    datas between two dates which are ( for example
    src 110 and duration which is bigger than X and its count ands its sum where lastapp value is dial
    This seems entirely unrelated to the question in your first post. Is this the same problem, or a different one?

    **********

    I don't understand the example you gave above:
    • Neither src nor duration appear to be DATE columns.
    • By "datas," do you mean records ("rows")?
    • What do you mean by "in one table"? and "at the same time"?


    Please describe which columns you wish to retrieve, and under what conditions.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  5. #5
    Join Date
    Jun 2010
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by traq View Post

    Please describe which columns you wish to retrieve, and under what conditions.
    when lasapp is dial and src = 110 and all billsec SUM and COUNT

    lastapp is hungup and src = 110 and all billsec SUM and COUNT
    lastapp is wait and src = 110 and all billsec SUM and COUNT

  6. #6
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,622
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Please try to answer each question specifically. It is very difficult to offer help without complete information.

    Best guess:
    Code:
    SELECT calldate,src,lastapp,duration,billsec,disposition
        ,COUNT( duration ) count_duration
        ,SUM( duration ) sum_duration
        FROM your_table_name
        WHERE lastapp = 'dial' -- change to 'hungup', etc. as desired
        AND src = $src
        AND duration > $min_duration
        AND (calldate BETWEEN $start_date AND $end_date)
    Please note; this assumes:
    -- $min_duration is an INT value
    -- $src is an INT value (it would need to be quoted otherwise)
    -- calldate is a DATETIME value
    -- $start_date and $end_date are also properly formatted as MySQL DATETIME values
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

Bookmarks

Posting Permissions

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