title: SQL Join in Codeigniter on two tables?
body: <p>I am building a url shortening / redirect service and I'm a bit confused as to how I can best get the data from two tables.</p>
  
  <p>I have two tables like so:</p>
  
  <p><strong>redirects</strong></p>
  
  <pre><code>id
  datetime
  ip_address
  browser_agent
  url_string
  </code></pre>
  
  <p><strong>links</strong></p>
  
  <pre><code>id
  alias
  url
  created
  user_id
  </code></pre>
  
  <p><strong><em>Links</em></strong> stores the link details and every time there is a redirect it's logged in the <strong><em>redirects</em></strong> table as a single row for each redirect.</p>
  
  <p><code>alias</code> in <strong><em>links</em></strong> and <code>url_string</code> in <strong><em>redirects</em></strong> are the same. e.g. <code>Dw4</code> so the domain would be <code>example.com/Dw4</code> - redirects to the <code>url</code> field in <strong><em>links</em></strong>.</p>
  
  <p>What I'm trying to achieve is to select (and count) all redirects from the <strong><em>redirects</em></strong> table where the <code>url_string</code> is same as <code>alias</code> and then where <code>user_id</code> is the same as the logged-in user.</p>
  
  <p>I am using Codeigniter.</p>
  
  <p>So far I'm trying to go for something like this: </p>
  
  <pre><code>$query=$this-&gt;db-&gt;query("SELECT * " . "FROM links, redirects " . "WHERE links.alias = redirects.url_string");
  </code></pre>
  
  <p>But I'm having no luck.</p>