0

Following are the tables:

Table User:

User_Id = 1,2,3;
User_Name: A,B,C;

Table Business:

Business_Id = 1,2,3;
Business_Name: A,B,C;
Business_Detail: Details_A, Details_b, Details_c;

Table Visits:

Visit_Id = 1,2,3,4,5,6:
User_Id = 1,1,1,2,1,1;
Business_Id = 1,1,1,2,2,3;

I need to create a function that return a list of the visits and the information about the business that the user visited. So far I have got the list of the store the user has visited but don't know where to go from there. 

 function visit_count($user_id=1){
     global $database;
     $sql = "SELECT * FROM visits WHERE user_id ='{$user_id}' LIMIT 0 , 30";
     $result_set = $database->query($sql);
     $visits = mysql_fetch_array($result_set);

     //Get the unique ids of the business

    //Run another query that has the business information

    //combing both queries.

 }

Thanks for the quick response guys. It's pretty much what I am looking for I think I am looking for the query to return an object as following:

Object:
  - Business:
     - Business_id;
     - Business_name;
     - Visit_counts;
  - Business:
     - Business_id;
     - Business_name;
     - Visit_counts;

So basically the object will have the business information and the no of times that the user has visited the store.

Thanks much for all the help

rex
  • 985
  • 5
  • 28
  • 48
  • 1
    Seeing as there are already a number of answers here, just going to give you this [link to a Q&A that I wrote](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) which I think will help you greatly. It is purely about SQL and goes from basics through to intermediate. It is filled with examples and contains all the code needed to build the tables etc so you can actually run and experiment yourself. Do yourself a favour and have a read :) – Fluffeh Sep 26 '13 at 22:26

4 Answers4

0

You need Joins:

SELECT v.*, b.Business_Name, b.Business_Detail FROM visits as v 
JOIN Business as b on b.Business_Id = v.Business_Id
WHERE v.user_id ='{$user_id}' LIMIT 0 , 30

Also - use mysqli and ensure you are sanitizing your inputs!

Edit

Follow @KHMKShore's advice on using prepared statements.

Matthew
  • 9,851
  • 4
  • 46
  • 77
0

Well, first off you should look into prepared statements, it is the current best practice for working with sql in PHP.

What it sounds like you need are joins.

$sql = "SELECT * FROM visits v
        JOIN business b ON b.Business_Id = v.Business_Id
        JOIN user u ON u.Business_Id = v.Business_Id
        WHERE v.user_id ='{$user_id}' LIMIT 0 , 30";
Kenny
  • 1,543
  • 9
  • 16
0
SELECT Visits.* , Business.Business_Name, Business.Business_Details
FROM Visits 
LEFT JOIN Business on Business.Business_Id  = Visits.Business_Id 
WHERE Visits.User_Id =1

try this SQL for each user

btlr.com
  • 139
  • 5
0

Try following SQL code:

SELECT business.business_id, business.business_name, COUNT(business.business_id) AS visit_counts
FROM business 
  LEFT JOIN visits
  ON business.business_id = visits.business_id
GROUP BY business_id, user_id

Result:

business_id |business_name |visit_counts
1            A              3
2            B              1
2            B              1
3            C              1
user2577405
  • 85
  • 5