PHP & MySql check if table is empty

Question

I'm a bit of a noob- and I'm having a hard time...

I need a bit of of code that searches a db table to find the row that matches the $id variable. There's a field in that table 'description' that I need to grab. If it's null, I need to show one message, if not another. Here's the code I have (I know I need to add the mysqli escape string, just doing this real quick from memory):

$query = "SELECT description FROM posts WHERE id = $id";
$result = mysqli_query($dbc, $query);

$row = mysqli_fetch_array($result, MYSQLI_ASSOC) ;

if(!$row){
echo "<p>'No description'</p>";
} else {
echo '<p>' . $row['description'] . '</p>';
}

Oh - here's what it's doing. It returns the description either way, regardless of data in db. — user517593
– user517593, Commented Jan 31, 2011 at 17:43
if ( $row <= 0 ) echo 'empty'; else echo $row['description']; It works so well to me. Or try to use mysql_num_row() function to control your results. — devasia2112
– devasia2112, Commented Jan 31, 2011 at 17:48
Question title is not correct, it's not about only checking for emptyness. — Eluny
– Eluny, Commented Apr 2, 2019 at 14:29

Perception · Accepted Answer · 2012-03-31 13:11:19Z

10

mysqli_fetch_array will fetch a row regardless of if the columns in that row are null. You want to be checking if $row['description'] is set instead of if $row is set:

$query = "SELECT description FROM posts WHERE id = $id";
$result = mysqli_query($dbc, $query);

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

if(isset($row['description'])) {
    echo "<p>No description</p>";
} else {
    echo '<p>' . $row['description'] . '</p>';
}

EDIT: Or, as an alternative, you can not fetch rows from the database where description is NULL:

$query = "SELECT description FROM posts WHERE id = $id AND description IS NOT NULL LIMIT 1";
$result = mysqli_query($dbc, $query);

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

if(! $row) {
    echo "<p>No description</p>";
} else {
    echo '<p>' . $row['description'] . '</p>';
}

Now you'd check to see if you were able to grab a row or not.

edited Mar 31, 2012 at 13:11

Perception

80.8k19 gold badges190 silver badges197 bronze badges

answered Jan 31, 2011 at 17:45

Josh

11.1k11 gold badges67 silver badges112 bronze badges

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

3 Comments

Phoenix Over a year ago

Don't forget to LIMIT 1 the query too, don't want to go through the whole table once it finds the row with the id, assuming that id is unique of course.

Mchl Over a year ago

If id is marked as PRIMARY or UNIQUE index, MySQL will now by itself not to scan entire table once the only row has been found.

Josh Over a year ago

@user517593, you can mark an answer the correct one by using the check mark icon next to it.

Marc · Accepted Answer · 2011-01-31 17:47:06Z

2

The !$row will only occur if no record is found. If the field description is really null, you have to check it this way:

if(is_null($row['description'])){

but I recommend you to check if the value is empty (or 0 or null):

if(empty($row['description'])){

answered Jan 31, 2011 at 17:47

Marc

6,7919 gold badges51 silver badges79 bronze badges

Comments

Sarfraz · Accepted Answer · 2011-01-31 17:49:43Z

1

BTW, you can do the check from within your query using COALESCE:

$query = "SELECT COALESCE(description, 'No description') FROM posts WHERE id = $id";
$result = mysqli_query($dbc, $query);

$row = mysqli_fetch_array($result, MYSQLI_ASSOC) ;
echo $row['description'];

This way, when there is a value for the description field, it will be shown otherwise No description will be output. So that way, you can do away with the if condition of PHP.

answered Jan 31, 2011 at 17:49

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

3 Comments

davogotland Over a year ago

plus one for concise code. although, if "description is null" has more consequences than changing the output, then this is bad.

Sarfraz Over a year ago

@dav: COALESCE will stop NULLs poping in with user friendly message, it is essentially the same as what OP was doing with PHP :)

davogotland Over a year ago

exactly. so as long as that is the only consequence, this is fine. if there are other consequences, then it is not fine. let's say something needs to be set in the session, or whatever, when a description does or doesn't exist. then it's not possible to find out if this extra behavior should be executed in the application. or there's a security risk where setting your description to "No description" would cause a behavior that's not in line with the original concept of the application.

Josh · Accepted Answer · 2011-01-31 18:10:11Z

1

How about:

SELECT COUNT(*) FROM table WHERE `description` IS NOT NULL

edited Jan 31, 2011 at 18:10

Josh

11.1k11 gold badges67 silver badges112 bronze badges

answered Jan 31, 2011 at 17:44

Michael B

1,7634 gold badges21 silver badges35 bronze badges

1 Comment

user517593 Over a year ago

But it will give me a count either way. I tried a mysqli_num_rows and doing a statement like "if == 0, do this... if == 1, do this" but it's not checking what's in the field, just that there is one so each one came back a false positive.

Collectives™ on Stack Overflow

PHP & MySql check if table is empty

4 Answers 4

3 Comments

Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related