SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Add up values of several mysql rows?

    I want to select some rows from a database... one of the columns will have numbers in them...

    Like:
    admin | 5 | 532.135.32.192
    jabird | 3 | 231.632.64.322

    ( that is for a script that tracks the number of times a certain IP failed to login as a certain user (obviously the IP's are fake ))

    I am wanting to add the middle rows together. and return that number to the PHP script, possible?

    I know of "COUNT(*)" but from reading about it in Kevin Yank's book, it seems like it does the same thing as mysql_num_rows...
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  2. #2
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes thats possible..

    select sum(whatever_column_holds_the_count) from whatever_table_keeps_track;

    next time if you give us whats in each column, let us know what the column names are.. so we can write the query easier

  3. #3
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry about that...

    so I would do something like:

    mysql_query("SELECT SUM(times) FROM failed_logins WHERE username='$username'");

    ?
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  4. #4
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no i don't see why you would want to get the sum from a username. arent you already keeping track of how many times the username failed to login in your failed_logins table? For isntance, each time a user fails to login, you should increment the times field.. thus you already have the total for that. If you want the sum of all times people have failed to login, just SELECT SUM(times) FROM failed_logins;
    and that will get you the total.

  5. #5
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to count the TOTAL number of times someone failed logging into a particular username, so when I am in the admin section, it shows them all... I hope you understand what I mean.

    Say its like this:
    IP -- times_failed -- username
    192.532.642.2 -- 4 -- admin
    314.235.421.532 -- 10 -- admin

    when i do the query, it would show that logging in as admin failed 14 times total...
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  6. #6
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, the query you asked about should be correct then. I forgot that you were using new rows for each distinct ip/username combo

  7. #7
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query didn't work... I tried to fetch the array, and echo the array like:

    failedarray['times']... that didn't work. I tried echoing the query, and I got:
    Resource id #25

    is there a special way to echo the sum?
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  8. #8
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how about this:
    $res = mysql_query("SELECT SUM(times) AS failures FROM failed_logins WHERE username='$username'");

    while ($row=mysql_fetch_array($res, MYSQL_ASSOC)) {
    echo $row['failures'];
    }

    Another way around that would have been to not use the AS failures part, but then use a numeric array (MYSQL_NUM) and reference $row[0]
    However, its good to use the AS keyword because if say you want to put this in an html table, you can fetch the field names returned, and use those for the column headers.. and you can make it whatever name you want to refer to it as. If you want it to be more than one word, like sum failures, write it as `sum failures` with the ticks (top left hand corner of your keyboard)

  9. #9
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could I perhaps get rid of the while by making:
    $row=mysql_fetch_array($res, MYSQL_ASSOC)? I am wanting to put it in an HTML table as you stated, and I have that in a while script already...
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  10. #10
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Does the initial query that sets up the existing while loop contain the data from this table?
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  11. #11
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, the existing query is:

    $admins = mysql_query("SELECT * FROM users WHERE user_level=2");
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  12. #12
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well you seem to want to display information for more than one user.. so you must put mysql_fetch_array in a loop, otherwise you will only get the first result row from the query.. I have no idea what your parent loop is doing, but i don't see how you could put it there.. i'd have to see the code

  13. #13
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The entire code is:

    PHP Code:
         function usersManage()
         {
             
    $admins mysql_query("SELECT * FROM users WHERE user_level=2");
             echo(
    "<table border='0'>
                 <tr>
                     <th width='10' scope='col' align='left' bgcolor='#CCCCCC'>ID</th>
                     <th width='100' scope='col' align='left' bgcolor='#999999'>Username</th>
                     <th width='100' scope='col' align='left' bgcolor='#CCCCCC'>E-Mail</th>
                     <th width='100' scope='col' align='left' bgcolor='#999999'>Name</th>
                     <th scope='col' align='left' bgcolor='#CCCCCC'>Failed Logins</th>
                     <th width='10' align='left' bgcolor='#999999'>Delete</th>
                 </tr>"
    );
             while(
    $adminrow mysql_fetch_array($admins)) {
                 
    $username $adminrow['username'];
                 
    $totalfailed mysql_query("SELECT SUM(times) FROM failed_logins");
                 
    $failedarray mysql_fetch_array($totalfailed);
                 echo(
    "<tr>
                     <th bgcolor='#CCCCCC' align='left'>"
    .$adminrow['id']."</th>
                     <th bgcolor='#999999' align='left'>"
    .$adminrow['username']."</th>
                     <th bgcolor='#CCCCCC' align='left'>"
    .$adminrow['email']."</th>
                     <th bgcolor='#999999' align='left'>"
    .$adminrow['firstname']." ".$adminrow['lastname']."</th>
                     <th bgcolor='#CCCCCC' align='left'>"
    .$totalfailed."</th>");
                 
    $id $adminrow['id'];
                 if(!
    $id == 1) {
                     echo(
    "<th bgcolor='#999999' align='left'><input type='checkbox' name='delete' /></th></tr>");
                 } else {
                     echo(
    "<th bgcolor='#999999' align='left'></th></tr>");
                 }
                 echo(
    "</table>");
             }
         } 
    ...

    Thanks for all the help and patience
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  14. #14
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think you need to clean up your database a bit.. this is what i think your tables should look something like the following (basically, not necessarily exactly)

    table users
    id (primary key) | username (unique) | user_level |then columsn for other basic info like email/etc

    table failed logins
    id (pk) | user_id | ip | times

    you could also put a unique key across user_id and ip in the failed_logins table.. so that you can never get duplicates for that combination.. use phpmyadmin do to that.

    Now if you have it like that, and you use the id from table_users for your user_id in failed logins, you can easily join the two and get all the information you need in one query.. If you can't change your database.. just join on usernames.. however its much better practice to make the id the foreign key rather than the username.. because the id will never change, whereas the username could possibly, and then you'd have to change it in two places.

  15. #15
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so when creating the users table (I don't have PHPMyAdmin, I do it from an install script I wrote for my CMS)... I need to create the username:

    username TEXT UNIQUE?

    and what is (pk)?
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  16. #16
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    acronym for primary key

    I would set username not to text but varchar.. with a length of maybe 16 characters or so.. text fields con contain like whole pages of information.. i dont remember the syntax for making fields unique.. you'll have to look it up, i always use phpmyadmin.. if you have the ability to put phpmyadmin, do it.. it takes like 5 minutes to setup typically.. literally extract it somewhere on your server, turn on authentication in the config file, and then type in the address.. It shoudl pop up with a username/password dialog box, and you can login and access your databases. You can easily make backups, run queries, optimize, and do tons of other php related functions with it..

  17. #17
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I'll install myadmin... can it extract the query used to set up the database for me? Like I said, I currently have an install script, so others can just run the script and it populates the database for them...
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  18. #18
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it will import sql just fine..
    it won't import a script, you'll have to run that on your own.. if you have a table dump though that is simple to insert with mysql..

  19. #19
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I meant export the commands to create the query... and I found it...

    Thats nice, I can set up the database exactly how I want it, export the MySQL, insert it in my script, modify it where it needs to be modified... goody...

    Thanks, gonna go redesign my database now...
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  20. #20
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, what is a good field type for a md5'd password? I've been using text, but I'm not sure if thats the best...
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  21. #21
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    varchar length 32

  22. #22
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  23. #23
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I changed it, and it all works great now...

    now, for that query =\ I have no idea of how to go about creating a query, that selects the times failed from another table based on the id on one table...
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  24. #24
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    alrighty i said i'd help you so i will.. but first.. if you know how, connect to mysql on a command line or terminal... usually just type mysql -u your_username -p
    then type your password
    now type connect db_name_here
    you'll be connected to the database.. now type describe table_name_here
    for each table.. then copy and paste the results in this thread. this way i can see exactly what columns we are using and write the appropriate query ..

    Then tell me what you want the output to look like.. that is, the function you have above, what is the table supposed to look like (breifly)

    Just tell me what columns you want for headers... You do want a single table right.. i'm guessing something like this:
    user id, user name, real name, ip, failed login attempts, email
    the reason why i ask this is because you used
    $admins = mysql_query("SELECT * FROM users WHERE user_level=2");
    which is a bad practice.. For testing purposes, go ahead and use * for what columns you want to select.. but for actual projects and what not it makes things a lot easier to actually write out each column you are selecting.. it especially we who help you out because we can get a lot of information from what you've already done.. select * tells us nothing.. and most of the time you'll find you don't even need all the columns, not to mention it slows down the query..


    Also, looking at your above code, i'm curious as to the structure of your table..You are supposed to use <th></th> for headers. the data cells should be in <td></td> tags.. td tags are auto left aligned you could remove the align='left' off of them.. Also, you could easy make an id or class for this table like so
    <table class="users">

    in your stylesheet, or in a style block in your header section put this
    Code:
    table.users {
    width:95%; /*or whatever you want*/
    border:0;
    }
    table.users td {
    background-color:#999;
    }
    table.users th {
    background-color:#CCC;
    }
    now you can cut out all the inline styles you have (well unless you have a column or cell that wants something different, you'll have to inline it for that).

  25. #25
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    alright try this.. assuming
    your users table has these columns
    id, username, name, email

    and your failed_logins table has
    id, user_id, ip, times_failed

    here is the query ( i think)
    Code:
    SELECT u.id
         , u.username
         , u.name
         , u.email
         , f.ip
         , f.times_failed FROM users
        AS u
         LEFT JOIN failed_logins AS f
         ON u.id = f.user_id
         WHERE u.user_level=2;
    i think thats what you want..


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
  •