-2

I'm trying to get the number of rows that contain null or empty fields so that if this number of rows is more than or equal to one, the user is prompted with an alert that tells them to complete these record(s), however I'm getting the error mentioned in the title.

Code:

function incompleteTechLog() {
    
include 'config.php';

// Connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM tbl_flights WHERE pilot_initial_post IS NULL OR = '' AND is_deleted = '0'"; // program allows for soft deleting so this must be included in the query
$result = $conn->query($sql);
$rows = mysqli_num_rows($result);

if ($rows >= 1) {
    echo "<div class='alert alert-warning' role='alert'><strong>Minor Alert! </strong>" . $rows . " tech log(s) are incomplete. Please check all aircraft tech logs.</div>";
} else {
    $conn = null;
}

}
Dharman
  • 30,962
  • 25
  • 85
  • 135
sinesine
  • 183
  • 1
  • 11

2 Answers2

1

This is because $conn->query($sql); returned FALSE. Your call $conn->query($sql); was not successful.

You have an error in your syntax:

$sql = "SELECT * FROM tbl_flights WHERE pilot_initial_post IS NULL OR = '' AND is_deleted = '0'";

should be changed to

$sql = "SELECT * FROM tbl_flights WHERE (pilot_initial_post IS NULL OR pilot_initial_post = '') AND is_deleted = '0'";

The OR operator requires the field_name.

And for debugging add or die(mysqli_error($conn)); to your $conn->query($sql); call.

user3606329
  • 2,405
  • 1
  • 16
  • 28
  • I've gone over the code and the schema, and the tables and columns definitely exist, and there are no typos. Perhaps it is something to do with the IS NULL part of the query? – sinesine Dec 31 '16 at 04:11
  • Updated my answer. Now the query is correct and add mysqli_error behind $conn->query() to parse the error. – user3606329 Dec 31 '16 at 04:28
  • I copied your query above exactly and it still didn't work. I added the `or die(mysqli_error($conn));` and it produced: Unknown column 'pilot_initial_post' in 'where clause'. – sinesine Dec 31 '16 at 04:32
  • This is because pilot_initial_post does not exist in your table design. If you think it exists, look closely for typos. – user3606329 Dec 31 '16 at 04:33
  • I'm going to have a look at my database SQL and perhaps start from scratch with a test database, it's obviously something on my end. The query you've posted clearly works so I'll mark it as correct. Thanks all for your help. – sinesine Dec 31 '16 at 04:36
1

It is returning false because the query failed.

Try:

SELECT * FROM tbl_flights WHERE (pilot_initial_post IS NULL OR pilot_initial_post='') AND is_deleted='0'
Shaun Adams
  • 76
  • 1
  • 5
  • Thank you but this did not work. – sinesine Dec 31 '16 at 04:10
  • Please snap a picture of the database structure with an example of the rows you expect it to catch. – Shaun Adams Dec 31 '16 at 04:13
  • I'm not sure how to do this on PHPMyAdmin, but I'm certain that it is not the database that is the problem here, as I have near-identical functions that are working but do not include the IS NULL in their queries, leading me to believe that is what the problem is. – sinesine Dec 31 '16 at 04:18
  • I just created a test database using the same query I posted. I set up two fields to check against, and entered 3 rows to pull from. The query works fine as I wrote it. Open phpmyadmin, browse to your table, then cluck the structure tab, take a screen shot / snippet. Then click on browse and show an example of the data you're pulling from. – Shaun Adams Dec 31 '16 at 04:33
  • Then it's definitely not the query that is the issue. As said above I'm going to create a test database/start the SQL from scratch to see what's wrong on my end. Thanks for your help. – sinesine Dec 31 '16 at 04:37