Why does this always give same result, even when table is empty?

[CGTroll]

I am trying to show two different options, depending on if the table is empty or not. It's a form to post images of artists. The user should be able to add images to existing artist, but if not artist has yet been added, then the option to add a new should be showed instead. I haven't added any artist to the DB yet, and therefor is empty, yet this code always shows the empty dropdown menu and never the text box. What is the best way to check if a query is empty or not to use in an if-statement?

$query = "SELECT artist_id, artist_name FROM content_artists ORDER BY artist_name ASC";
$result = $mysqli->query($query);
if ($result)
	{
		echo "
			<td>Pick from existing artits:</td>
  			<td>";
		echo "<select name='ac_artist_id'>";
		while ($row = $result->fetch_array(MYSQLI_ASSOC)) 
			{
				echo "<option value='" . $row['artist_id'] . "'>" . $row['artist_name'] . "</option>";
			}
		echo "</select></td>";
	}
else
	{
		echo "
			<td>Name of artist:</td>
			<td><input type=\"text\" name=\"artist_name\"></td>
		";
	}

Edited September 1, 2013 by CGTroll

[Psycho]

The result of a query will be false if the query failed - i.e. there was an error. An empty result set is still a valid result.

[Psycho]

$query = "SELECT artist_id, artist_name FROM content_artists ORDER BY artist_name ASC";

$result = $mysqli->query($query);


if(!$result)

{

    //There was an error executing the query

    echo "There was a problem getting the list of artists";

}

elseif(!mysqli_num_rows($result))

{

    //There were no results

    echo "<td>Name of artist:</td>\n";

    echo "<td><input type=\"text\" name=\"artist_name\"></td>    ";

}

else

{

    //There were results

    echo "<td>Pick from existing artits:</td>\n";

    echo "<td><select name='ac_artist_id'>\n";

    while ($row = $result->fetch_array(MYSQLI_ASSOC))

    {

        echo "<option value='{$row['artist_id']}'>{$row['artist_name']}</option>\n";

    }

    echo "</select></td>\n";

}

Edited September 1, 2013 by Psycho

[CGTroll]

$query = "SELECT artist_id, artist_name FROM content_artists ORDER BY artist_name ASC";
$result = $mysqli->query($query);

if(!$result)
{
    //There was an error executing the query
    echo "There was a problem getting the list of artists";
}
elseif(!mysqli_num_rows($result))
{
    //There were no results
    echo "<td>Name of artist:</td>\n";
    echo "<td><input type=\"text\" name=\"artist_name\"></td>    ";
}
else
{
    //There were results
    echo "<td>Pick from existing artits:</td>\n";
    echo "<td><select name='ac_artist_id'>\n";
    while ($row = $result->fetch_array(MYSQLI_ASSOC))
    {
        echo "<option value='{$row['artist_id']}'>{$row['artist_name']}</option>\n";
    }
    echo "</select></td>\n";
}

Thanks a lot Psycho! !mysqli_num_rows($result), Didn't know of that one. That will solve/simplify a lot of other cases too!

[objnoob]

Make a habit of checking for mysqli_query errors using the === operator..

 

$rs = mysqli->query($sql);

if($rs === false) { /* error */ }

[jazzman1]

Make a habit of checking for mysqli_query errors using the === operator..

 

$rs = mysqli->query($sql);

if($rs === false) { /* error */ }

 

Why? The "!" NOT operator is enough! It will returned true (error) if this SQL Statement is not true.

Edited September 2, 2013 by jazzman1

[objnoob]

simple.

$sql = "UPDATE .... ";
$cnt = mysqli->query($sql);
if( ! $cnt ) {
     # false positive when 0 records are affected
}

[jazzman1]

The php mysql_query function just send this sql statement to mysql database. The database check wether this statement is true and return the result back to php. 

 

EDIT: Zero or NULL will be correct results too!

Edited September 2, 2013 by jazzman1

[objnoob]

i'm not sure what you mean.

[mac_gyver]

@objnoob

 

the mysqli query method doesn't return the number of affected rows. you must be thinking of the pdo exec() method.

[objnoob]

i stand corrected, thanks. has it always been like that? it's been awhile since i've last used mysqli extension but i can remember the manual warning about using == false to check for execution errors like it was yesterday.

[Psycho]

i stand corrected, thanks. has it always been like that? it's been awhile since i've last used mysqli extension but i can remember the manual warning about using == false to check for execution errors like it was yesterday.

Yes, it has always been like that.

http://www.php.net/manual/en/mysqli.query.php

 

 

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

 

However, there are plenty of other functions that may return a value of 0 that is not false and you must use the type comparison ===. For example, strpos() return the position in which a search string is found. Since 0 is a valid location for the position found and does not mean there was an error.