SELECT * FROM table_1 WHERE id = id in table_2

Question

Been working on this for a while; Need to select data from a table where the ID column matches the ID from another table.

My code so far:

<?php
$sql = "SELECT * FROM input";
$sql2 = "SELECT * FROM output WHERE question_id =".$row["id"];
$result = $conn->query($sql);
$result2 = $conn->query($sql2);

if ($result->num_rows > 0) {
  $index = 0;
  while($row = $result->fetch_assoc()) {
    $index++;
    ?>

    <?php
    echo '<input type="hidden" name="questionid" value="'. $row['id'].'"/>';
    ?>

    <?php

    if ($result2->num_rows > 0) {
      while($row2 = $result2->fetch_assoc()) {
      }
    } else {
      echo "0 results";
    }
  }
} else {
  echo "0 results";
}
$conn->close();
?>

I know I m missing something important here. Any suggestions are appreciated.

Naresh Kumar P · Answer 1 · 2016-09-05T07:50:10.617

In order to achieve this thing you can use the JOIN query which is available in mysql.

Ex: Consider two tables user and course

User Table:

id  name         course
1   Alice        1
2   Bob          1
3   Caroline     2
4   David        5
5   Emma        (NULL)

Course Table:

id  name         
1   HTML5
2   CSS3
3   JS
4   PHP
5   WORDPRESS

INNER JOIN (or just JOIN)

The most frequently used clause is INNER JOIN. This produces a set of records which match in both the user and course tables, i.e. all users who are enrolled on a course:

SELECT user.name, course.name FROM `user` INNER JOIN `course` on user.course = course.id;

LEFT JOIN

What if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course):

SELECT user.name, course.name FROM `user` LEFT JOIN `course` on user.course = course.id;

RIGHT JOIN

Perhaps we require a list all courses and students even if no one has been enrolled? A RIGHT JOIN produces a set of records which matches every entry in the right table (course) regardless of any matching entry in the left table (user):

SELECT user.name, course.name FROM `user` RIGHT JOIN `course` on user.course = course.id;

score 1 · Accepted Answer · edited May 23 '17 at 12:17

1

You missed fetching the row items to extract the ID you want to use as CONSTRAINT in the next query

$sql = "SELECT * FROM input";

$row = mysqli_fetch_array($sql);

    $sql2 = "SELECT * FROM output WHERE question_id =".$row["id"];
    $result = $conn->query($sql);
    $result2 = $conn->query($sql2);

You can also consider JOIN SELECT of the two tables like this;

$sql = SELECT input.* , output.* FROM input JOIN output (id) WHERE input.id = "".

check out MySql Join three tables

edited May 23 '17 at 12:17

Community

1
1

answered Sep 05 '16 at 07:32

Asuquo12

827
17
26

how do you expect that to run the second query with an unavailable constraint when its needed? – Asuquo12 Sep 05 '16 at 07:38
@Asuquol12 hey, i was wondering how can i add `ORDER BY date DESC` line to the second table.. i tried adding it but the $row['id'] is making some problem here... can you help me on this one mate.. – Sep 05 '16 at 08:29
You to be mindful of the quotes. You can use this $sql2 = "SELECT * FROM output WHERE question_id = ' $row["id"] ' ORDER BY date DESC " ; – Asuquo12 Sep 05 '16 at 08:42
tried it.. it said error like `Parse error: syntax error, unexpected '"', expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)` – Sep 05 '16 at 08:46
make this a variable like $myid = $row["id"] ; then replace $row["id"] with $myid in the query. – Asuquo12 Sep 05 '16 at 08:49
Can you take a look at this one mate..http://stackoverflow.com/questions/39330887/slide-up-slide-down-is-not-working-for-selected-table-rows-from-db?noredirect=1#comment65993670_39330887 – Sep 05 '16 at 13:20

score 0 · Answer 3 · answered Sep 05 '16 at 07:24

0

Not sure if this is what you want so let me know in the comments before downvoting, try this:

SELECT * FROM output, input WHERE output.question_id = input.id

answered Sep 05 '16 at 07:24

Marco Santarossa

4,058
1
29
49

score 0 · Answer 4 · answered Sep 05 '16 at 07:24

0

You can achieve this using SQL INNER JOIN Keyword

SQL

SELECT * FROM input INNER JOIN output on input.id = output.question_id

answered Sep 05 '16 at 07:24

Sofiene Djebali

4,398
1
21
27

tried inner join it said some error like `Parse error: syntax error, unexpected '$result' (T_VARIABLE) in C:\xampp\htdocs\the_database\answer.php on line 816` – Sep 05 '16 at 07:30

score 0 · Answer 5 · answered Sep 05 '16 at 07:24

0

Use this inner join to get row from both table where the ids are match

SELECT *
FROM input
INNER JOIN output
ON input.id=output.question_id;

answered Sep 05 '16 at 07:24

FullStack

198
7
16

score 0 · Answer 6 · answered Sep 05 '16 at 07:26

0

You should rather join both tables with an INNER JOIN, which will discard rows from tables if the id is not present in the other table. Try something like:

SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id;

answered Sep 05 '16 at 07:26

Thomas Baruchel

7,236
2
27
46

tried inner join it said some error like `Parse error: syntax error, unexpected '$result' (T_VARIABLE) in C:\xampp\htdocs\the_database\answer.php on line 816` – Sep 05 '16 at 07:28

SELECT * FROM table_1 WHERE id = id in table_2

6 Answers6