CODEWITHSUNDEEP
phpmysql
RepeaterCreeper
RepeaterCreeper

Reputation: 342

Getting info with same data in mysql

I can't really put it into words cause I'm not really good at english so I'll just give an example. And for that reason I can't really search how to do it. Hope you understand.

E.g: Say I have a table setup like this.

+-------------+---------+---------+----------+
| Category_ID | User_ID |  Info1  |  Info2   |
+-------------+---------+---------+----------+
|           1 |       1 | 'Info1' | 'Info2'  |
|           1 |       2 | 'Info3' | 'Info4'  |
|           1 |       3 | 'Info5' | 'Info6'  |
|           1 |       4 | 'Info7' | 'Info8'  |
|           1 |       5 | 'Info9' | 'Info10' |
+-------------+---------+---------+----------+

Something like that. So what I want is to get every user that is using/in category_id of 1. And then print out the information they have. Is that even possible? Just explain to me how to do it I already have a method for getting info in and out of mysql.

Note #1: The idea is to print out a div template for each of the user so it will be like a list kinda thing.
To be honest the only thing I have tried was 'SELECT * FROM table WHERE Category_ID =1 GROUP BY column_choice'.
Hopefully you guys understood what I said. Sorry I'm not really good at english.

Upvotes: 0

Views: 54

Answers (4)

mdh.heydari
mdh.heydari

Reputation: 540

You have three ways to do it.

First: Use cartesian product of your tables and filter it out:

SELECT t.User_ID, u.ID, u.username, u.email FROM table AS t, users AS u WHERE t.Category_ID = 1 AND t.User_ID = u.ID;

Second: Join two tables together and get your results:

SELECT t.User_ID, u.username, u.email FROM table AS t NATURAL JOIN users AS where t.Category_ID = 1;

Third: Use a nested query to get user ids in category #1 then query users table and filter it by the set in previous part.

SELECT username, email FROM users WHERE ID in (SELECT User_ID FROM table WHERE Category_ID=1;

Hope this helps.

Upvotes: 0

Logan Wayne
Logan Wayne

Reputation: 5991

If you want to fetch them all and put them in a div. You can try this:

if($stmt = $YourConnection->prepare("SELECT Information FROM yourTable WHERE Category_ID = 1")){
  $stmt->execute();
  $stmt->bind_result($information);
  while($stmt->fetch()){
    ?>
      <div>
        <?php echo $information; ?>
      </div>
    <?php
  } /* END OF WHILE LOOP */
  $stmt->close();
} /* END OF PREPARED STATEMENT */

I hope you know how to establish your connection and just replace the necessary table name and connection variable above.

Upvotes: 1

Chico3001
Chico3001

Reputation: 1963

SELECT Information FROM table_name WHERE Category_ID = 1

Upvotes: 0

user1717828
user1717828

Reputation: 7225

Use the SQL command:

SELECT * FROM table WHERE Category_ID=1;

Upvotes: 0

Related Questions