SQL, PHP, sorting content of table by content of other table

Question

I have a bit of a problem with some sql. I am have to tables in the same mysql database, called total and dismissed. In both of there is a column called person_id. I am trying to get the every record from total, minus the ones in dismissed.

So if the table total has the values: 1,2,3,4,5,6,7

and dismissed has: 3,4,7

I want only 1,2,5,6 to be displayed from the table.

My sql looks like this at the moment:

$sql = "SELECT * FROM total WHERE NOT EXISTS (SELECT 1 FROM dismissed WHERE total.person_id = dismissed.person_id)";

And the rest of the code is:

$servername = "servername";
$username = "username";
$password = "password";
$dbname = "dbname";


$conn=mysqli_connect($servername, $username, $password, $dbname);

if (mysqli_connect_errno()) {
    echo 'Fail:' . mysqli_connect_error();
}

$sql = "SELECT * FROM total WHERE NOT EXISTS (SELECT 1 FROM dismissed WHERE total.person_id =  dismissed.person_id)";

//$sql = "SELECT * FROM total";
$result=mysqli_query($conn,$sql);

$row=mysqli_fetch_assoc($result);          //First error message

while($feed_varer=mysqli_fetch_assoc($result)) {                 //Second error message

//some code

}

I have not problem connecting to the database using when doing something like $sql = "SELECT * FROM total"; , and the rest of the code works in that case too.

The error message i gets is: Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in url-to-page.php (url shortened). The error message is given twice, the lines are marked in the code. Hope you can help me.

tried to test query like this: (Thanks Abdus Sattar Bhuiyan)

if ($result = $conn->query("SELECT * FROM total ORDER BY person_id")) {
    $row_cnt = $result->num_rows;
    printf($row_cnt);
    $result->close();
}

Result showed the correct amount of rows.

Have you tried searching your error message? – u_mulder Feb 02 '17 at 07:51 — u_mulder, Feb 02 '17 at 07:51

Abdus Sattar Bhuiyan · Answer 1 · 2017-02-02T08:10:43.127

3

This happens usually when the mysqli_query hasnt run properly. Check whether $query is executed properly or does it contains anything or not. The problem may be with the connection or it may be with the query or it may be with the tables. Be sure $query contains the results.

Try this

if (!$result) {
        echo 'MySQL Error: ' . mysqli_error($result);
        exit;
    }

Let us know what is the mysql error

edited Feb 02 '17 at 08:10

answered Feb 02 '17 at 07:56

Abdus Sattar Bhuiyan

3,016
4
38
72

Warning: mysqli_error() expects exactly 1 parameter, 0 given in – Rosemary_J Feb 02 '17 at 08:07

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

Ok, first problem I see is the second part of your SQL command
SELECT 1 FROM dismissed WHERE total.person_id = dismissed.person_id
Why are you selecting 1?

Secondly I would use NOT IN rather than NOT EXIST as this is more efficient (link)

Thirdly the WHERE function should include a column

edited May 23 '17 at 10:29

Community

1
1

answered Feb 02 '17 at 08:00

Sphinx

956
7
21

tried `$sql = "SELECT * FROM total WHERE person_id NOT IN (SELECT person_id FROM dismissed)";` but gave the same error – Rosemary_J Feb 02 '17 at 08:41

SQL, PHP, sorting content of table by content of other table

2 Answers2