mysql query does not display the right output

Question

I have this mysqli query that will display all the data that the crew_status is PENDING FOR LINEUP but with my query right now, it displays all the data that is in database

Here is my query:

<?php

include '../session.php';
require_once 'config.php';
include 'header.php';

    $master = 'MASTER';
    $chck = 'CHCK';
    $second_engineer = '2E';
    $second_mate = '2M';
    $third_engineer = '3E';
    $third_mate = '3M';
    $ce = 'CE';
    $bsn = 'BSN';
    $ab = 'AB';
    $olr = 'OLR';
    $dcdt = 'DCDT';
    $egdt = 'EGDT';
    $cook = 'COOK';
    $messman = 'MESSMN';
    $crew_status = 'PENDING FOR LINEUP';
    $query = "SELECT * FROM `crew_info` WHERE `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? OR `crew_rank` = ? AND `crew_status` = ?";
    $stmt = mysqli_prepare($conn, $query);
    mysqli_stmt_bind_param($stmt, 'sssssssssssssss', $crew_status, $master, $chck, $second_engineer, $second_mate, $third_engineer, $third_mate, $ce, $bsn, $ab, $olr, $dcdt, $egdt, $cook, $messman);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $id, $first_name, $middle_name, $last_name, $age, $month, $day, $year, $birth_place, $gender, $martial_status, $religion, $nationality, $email_address, $address_1, $address_2, $course, $school_graduated, $remarks, $note, $date_added, $crew_status, $crew_rank, $image_name, $updated_photo, $passport_registration, $passport_expiration);



?>

Can someone tell what is the problem?

    <?php

    while(mysqli_stmt_fetch($stmt)) {
        echo "<tr>";
        echo "<td>".sprintf("%s%s%s", $first_name, $middle_name, $last_name)."</td>";
        echo "<td>".sprintf("%s", $crew_rank)."</td>";
        echo "<td>".sprintf("%s", $crew_status)."</td>";
        echo '</tr>';
    }
    ?>

cover all OR conditions for crew_rank in () and check

user2338456
– user2338456

2016-07-27 05:15:25 +00:00
Commented Jul 27, 2016 at 5:15 — user2338456
– user2338456, Commented Jul 27, 2016 at 5:15

Blank · Accepted Answer · 2016-07-27 06:23:10Z

3

Wrap your or with (, ), try this:

SELECT *
FROM `crew_info`
WHERE (
    `crew_rank` = 'MASTER'
    OR `crew_rank` = 'CHCK'
    OR `crew_rank` = 'MESSMN'
    OR `crew_rank` = '2E'
    OR `crew_rank` = '2M'
    OR `crew_rank` = 'COOK'
    OR `crew_rank` = 'OLR'
    OR `crew_rank` = 'AB'
    OR `crew_rank` = '3E'
    OR `crew_rank` = '3M')
AND `crew_status` = 'PENDING FOR LINEUP'

Without (), if one of these ORs matches, then condition left will not be computed.

See this:

true || false && false = true 
(true || false) && false = false

And I think you'd better use IN instead of OR like :

SELECT *
FROM `crew_info`
WHERE 
    `crew_rank` IN ('MASTER','CHCK','MESSMN','2E','2M','COOK','OLR','AB','3E','3M')
AND `crew_status` = 'PENDING FOR LINEUP'

Edited:

SELECT *
FROM `crew_info`
WHERE (crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ?)
AND crew_status = ?

edited Jul 27, 2016 at 6:23

answered Jul 27, 2016 at 5:07

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

aron pascua Over a year ago

can you explain me this one? why my query does not display the right output and yours displays the right output?

aron pascua Over a year ago

how to do this in mysqli prepared ?

aron pascua Over a year ago

SELECT * FROM crew_info` WHERE crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? OR crew_rank = ? AND crew_status = ?` how can I do that in my code?

Blank Over a year ago

OK, here you just give us sql query, but no php code. I can't do more than this. @aronpascua

Blank Over a year ago

No, code like so does not make sense. You should post more about this query.

|

Noman · Accepted Answer · 2016-07-27 05:08:06Z

1

Some changes in your query:

SELECT * FROM `crew_info` WHERE (`crew_rank` = 'MASTER' OR `crew_rank` = 'CHCK' OR `crew_rank` = 'MESSMN' OR `crew_rank` = '2E' OR `crew_rank` = '2M' OR `crew_rank` = 'COOK' OR `crew_rank` = 'OLR' OR `crew_rank` = 'AB' OR `crew_rank` = '3E' OR `crew_rank` = '3M') AND (`crew_status` = 'PENDING FOR LINEUP')

Hope it will help you.

answered Jul 27, 2016 at 5:08

Noman

1,4872 gold badges20 silver badges39 bronze badges

Comments

Wrokar · Accepted Answer · 2016-07-27 05:10:51Z

0

The ANDs and ORs are ambiguous. It's unclear if you want something like:

(A AND B) OR C

or

A AND (B OR C)

Add some parenthesis to clear things up:

SELECT * FROM `crew_info` WHERE (`crew_rank` = 'MASTER' OR `crew_rank` = 'CHCK' OR `crew_rank` = 'MESSMN' OR `crew_rank` = '2E' OR `crew_rank` = '2M' OR `crew_rank` = 'COOK' OR `crew_rank` = 'OLR' OR `crew_rank` = 'AB' OR `crew_rank` = '3E' OR `crew_rank` = '3M') AND `crew_status` = 'PENDING FOR LINEUP'

answered Jul 27, 2016 at 5:10

Wrokar

9737 silver badges17 bronze badges

Comments

shakyl mansuri · Accepted Answer · 2016-07-27 05:15:56Z

0

SELECT * FROM crew_info WHERE crew_rank IS NOT NULL AND crew_status = 'PENDING FOR LINEUP' I hope this would be worked.

answered Jul 27, 2016 at 5:15

shakyl mansuri

19113 bronze badges

Collectives™ on Stack Overflow

mysql query does not display the right output

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related