How to get the rows of mySQL based on a visitors path?

EnexoOnoma asked
php mysql
via

In my database I save the browsed pages of my registered visitors and the referrers.

So each new browsed page has its own row. Also, each row has a column “username” where I save the username of each visitor.

What I want to achieve is to know which visitors had a journey that while browsing the website viewed the /pricing page, but also BEFORE that page, they had an entrance ref by Google.

Honestly I don’t have a clue how to make it, but I am trying like below which for now is wrong.

Any heads up please?

SELECT * FROM pages WHERE (ref='Google' or url='/pricing') group by username


Answer
via

You could use a subquery to do this.

SELECT DISTINCT username
  FROM pages AS ref_pages
  WHERE ref = 'Google' AND username IN(
      SELECT DISTINCT username FROM pages
      WHERE url = '/pricing' AND time > ref_pages.time
  ) 
Share This
Posted in: