SQL Join Codeigniter on Two Tables?

Question

I am creating a url reduction / redirection service, and I'm a bit confused about how best to get data from two tables.

I have two tables:

redirects

id datetime ip_address browser_agent url_string 

link

 id alias url created user_id 

Links store link data and each time a table is redirected , redirects as a single row for each redirect.

alias in links and url_string in redirects are the same. for example, Dw4 , so the domain will be example.com/Dw4 - redirected to the url field in the links .

What I'm trying to achieve is to select (and count) all the redirects from the redirect table, where url_string matches alias , and then where user_id is the same as the registered user.

I am using Codeigniter.

While I'm trying to find something like this:

 $query=$this->db->query("SELECT * " . "FROM links, redirects " . "WHERE links.alias = redirects.url_string"); 

But I’m out of luck.

Answer 1

JOIN two tables, for example:

 SELECT r.* FROM redirects r INNER JOIN links l ON r.url_string = l.alias; 

---

Update: Try the following:

 SELECT l.*, r.browser_agent, rCounts.redirectsCount FROM links l INNER JOIN redirects r ON r.url_string = l.alias INNER JOIN ( SELECT url_string, COUNT(*) redirectsCount FROM redirects GROUP BY url_string ) rCounts ON rrCounts.url_string = l.alias; 

Answer 2

You can use the join to this as:

 $this->db->select('*'); $this->db->from('links'); $this->db->join('redirects', 'redirects.url_string = links.alias'); $query = $this->db->get(); return $query->result; 

and in view, use foreach syntax to display the contents of db as your requirement.

Answer 3

As you said, user_id should be the same for the registered user, you should use the connection with user_id to also get specific user_id data, so your request should look like this:

 SELECT redirects.id,count(*) FROM redirects inner join links on redirects.url_string=links.alias and redirects.id=links.user_id group by redirects.id