PHP SQL Query That Displays Certain Results Based On Values In Another Table

Question

Right now, I have two database tables. Table history_2014 has a column(member) that says whether or not a member is active (Y or N). I have another table called history_overall that contains all of the contact information for these members. I need a table that will only show the rows of history_overall for active members. Right now, I have this PHP:

<?php
$con=mysqli_connect("localhost","myusername","mypassword","mydatabase");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM history_overall");

echo "<table border='1'>
<tr>
<th>Address 1</th>
<th>Last Name</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['address1'] . "</td>";
  echo "<td>" . $row['last_name'] . "</td>";
  echo "</tr>";
}

echo "</table>";

mysqli_close($con);
?>

The current code will allow me to display all rows, but I need to be able to display certain rows based on the other table.

Thank you guys for removing my password. Forgot to do that :) — , Jul 03 '14 at 16:08

Will B. · Accepted Answer · 2014-07-03T17:13:29.560

what column ties the two tables together? A JOIN will accomplish this if there is a relationship.

Example:

SELECT history_overall.* FROM members
JOIN history_overall
ON history_overall.member_id = members.id
WHERE members.active = 'Y';

----Edit/Comment----

Since I'm not able to add the table structure layout in a comment I will comment here. It is more valuable when wanting to retrieve specific records from the database to provide us with the table schema as opposed to the actual table names. As well as an example of the resulting data you want as a result.

All you really provided to us with is table history_2014 with columns (member = Y|N), table history_overall with columns (address1, last_name) which doesn't allow us to build a relationship between the two (or more) tables.

Here is an example assuming both tables have a memberid column:

-----------------------           -------------------------------------
|      table1         |           |              table2               |
-----------------------           -------------------------------------
| member | memberid   |           | address1   | full_name | memberid |
-----------------------           -------------------------------------
| Y      | 1          |           | 123 street | jon doe   | 1        |
-----------------------           -------------------------------------
| N      | 2          |           | 789 court  | jane doe  | 2        |
-----------------------           -------------------------------------
| Y      | 3          |           | 456 road   | foo bar   | 3        |
-----------------------           -------------------------------------

Question: How can I retrieve records from table2 where the member in table1 is 'Y'?

This is my desired result of the records:

-------------------------------------
|             recordset             |
-------------------------------------
| address1   | full_name | memberid |
-------------------------------------
| 123 street |  jon doe   | 1       |
-------------------------------------
| 456 road   |  foo bar   | 3       |
-------------------------------------

Answer query:

SELECT table2.* FROM table1 JOIN table2 ON table1.memberid = table2.memberid WHERE table1.member = 'Y'

Explained:

Retrieve all columns in table2 that the memberid column in table1 is the same as the memberid column in table2 from the rows in table1 that the member column contains Y as a value with no ordering or limit on the amount of records returned.

I'm sorry, I'm kind of a noob at this. The table with the displayed information is called history_overall, and the table with the activity information is history_2014. The 2014 column is member. How would this be displayed? — , Jul 02 '14 at 21:11
@fishmr1 Updated the answer to help you in the future. Hope it helps. — Will B., Jul 03 '14 at 14:19

score 0 · Answer 2 · answered Jul 02 '14 at 17:17

0

I guess you have some kind of Id of the user in both tables, you could make a query creating a join between the two tables where id matches and selecting only the rows where member = 'Y'

answered Jul 02 '14 at 17:17

LK-CHF

11
1

score 0 · Answer 3 · answered Jul 02 '14 at 17:23

0

Simply use this method

select * from history_overall,members where members.status='active'

You will get all the active members results including history also.

answered Jul 02 '14 at 17:23

Dinesh G

244
1
13

score 0 · Answer 4 · answered Jul 02 '14 at 17:25

Use a table join (MySQL doc: http://dev.mysql.com/doc/refman/5.7/en/join.html).

In short, something like:

SELECT ho.*
FROM history_overall AS ho
    RIGHT JOIN history_2014 AS h ON ho.memberId = h.memberId
WHERE h.isActive = 'Y';

Obviously your field names are probably different...

PHP SQL Query That Displays Certain Results Based On Values In Another Table

4 Answers4