Databases
StackOverflow2013
Postsdbo
POMySQL join not returning rows
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL join not returning rows
    primarykey
    data
    text
    <p>I'm attempting to create an anti-bruteforcer for the login page on a website. Unfortunately, my query is not working as expected. I would like to test how many times an IP address has attempted to login, and also return the ID of the user for my next step in the login process. </p> <p>However, I'm having a problem with the query... for one thing, this would only return rows if it was the same user as they had been trying to login to before. I need it to be any user. Secondly, regardless of whether I use LEFT JOIN, RIGHT JOIN, INNER JOIN or JOIN, it will not return the user's ID unless there is a row for the user in <code>login_attempts</code>.</p> <pre><code>SELECT COUNT(`la`.`id`), `u`.`id` FROM `users` AS `u` LEFT JOIN `login_attempts` AS `la` ON `u`.`id` = `la`.`user_id` WHERE `u`.`username` = 'admin' AND `la`.`ip_address` = '127.0.0.1' AND `la`.`timestamp` &gt;= '1' </code></pre> <p>Here's the output from <code>DESC login_attempts</code></p> <pre><code>Field Type Null Key Default Extra id int(10) unsigned NO PRI NULL auto_increment user_id int(10) unsigned NO MUL NULL ip_address varchar(15) NO MUL NULL timestamp int(10) NO NULL </code></pre> <p>This query does the same thing, except does not even select the ID if there is a row in login_attempts corresponding to it:</p> <pre><code>SELECT COUNT(`la`.`id`), `u`.`id` FROM `users` AS `u` LEFT OUTER JOIN `login_attempts` AS `la` ON `u`.`id` = `la`.`user_id` AND `la`.`ip_address` = '127.0.0.1' AND `la`.`timestamp` &gt;= '1' WHERE `u`.`username` = 'admin' </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJohn M.
    1. USJohn M.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL join not returning rows
      1. USJohn M.
      1. VTFavorite
    1. COWhy is `LOGIN_ATTEMPTS.timestamp` a string? Pls post the output from: `DESC LOGIN_ATTEMPTS`.
      singulars
      1. POMySQL join not returning rows
      1. USOMG Ponies
    2. COA string simply because I'm not sure of the syntax. However, the way I'm sending variables via the script sends it as a signed integer, which it is. One second and I'll post the describe.
      singulars
      1. POMySQL join not returning rows
      1. USJohn M.
    3. COHelpful link explaining joins if you haven't seen it: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
      singulars
      1. POMySQL join not returning rows
      1. USOMG Ponies
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload