Ideas on how to deal with empty set from mysql?

slightlyeskew

Hey guys,

I'm trying to properly handle a call to my database with an intentionally invalid search parameter. I want to be able to reprompt my user to enter a valid name when they enter an invalid name, however when I use an invalid name the program doesn't compile and I get this error:

Catchable fatal error: Object of class MDB2_BufferedResult_mysql could not be converted to string in /var/www/NovaDB/emptysettest.php on line 9

An example of my code:

<?php
//assume proper db connection
$test = $db->query("SELECT * FROM ShareholderNames WHERE First_Name = 'Shenabablebooblekazam'");
echo "...";
echo "$test";
echo "...";
?>

Assuming that 1)Shenablebooblekazam is not in the db and 2)that my First_Name field is big enough to hold that name if it was in the field (not sure if that matters).

I would appreciate any suggestions you might have on how to handle this 🙂

rincewind456

Why not expect failure and test for that instead like

if(!$test = $db->query("SELECT * FROM ShareholderNames WHERE First_Name = 'Shenabablebooblekazam'"){
//sort error here 
} else {
//rest of your stuff here
}

johanafm

The returned result of a db query is a result set resource, and not an actual integer, string or whatever is stored in the database. That is assuming $db is the result of something like mysqli::__construct().
If $db is a class you or someone else wrote, it's of course entirely possible that the returned value is an int, string or array, but since I've not seen any such code, I'm rolling with my first assumption.

$result = $db->query();

// $result is either a result set resource, or if something went wrong, boolean false
if ($result === false) {
	echo $db->errno() . ": " . $db->error();
	/* exit; if you want your script to stop running here. But you might want to let the script
		complete without this query.
}
else {
	// Get the next row from the result set, and advance its internal pointer
	/* If no rows where returned, this evaluates to false the first time, so you've
		allready dealt with an empty result set */
	while ($row = $result->fetch_assoc()) {
		echo $row['someFieldNameFromShareholderNames'];
	}
}

slightlyeskew

I never really resolved this issue. Both ideas seem logical, but I tried to implement them and neither appeared to work.

The code:

error_reporting(E_ALL);
require_once 'MDB2.php';
$db = MDB2::connect();
    if (MDB2::isError($db)) { die("Can't connect: " . $db->getMessage()); }

if (!($getBlah = $db->query("SELECT ShareholderID FROM ShareholderNames WHERE First_Name = 'blah' AND Last_Name = 'yada'")))

//This entry does NOT exist in my db

{
echo "works";
}

I believe this should print "works" but it does not.

Alternatively:

error_reporting(E_ALL);
require_once 'MDB2.php';
$db = MDB2::connect();
    if (MDB2::isError($db)) { die("Can't connect: " . $db->getMessage()); }

if $getBlah = $db->query("SELECT ShareholderID FROM ShareholderNames WHERE First_Name = 'blah' AND Last_Name = 'yada'");

if ($getBlah === false)
{
echo "works";
}

This also does not work. I'd appreciate any suggestions you have to remedy this. As always, thanks for your suggestions.

slightlyeskew

FYI, I also tried using

if (MDB2::isError($getBlah))
{
echo "works";
}

I didn't think this would work because I'm not receiving an error when I manually go into the MySQL database interface and try it, but the empty set.

I tried

//query from above
if (isset($getBlah) && ($getBlah == ''))
{
echo "works";
}

Since I thought that getBlah might be set to the empty set...this also did not work.

johanafm

if (!($getBlah = $db->query("SELECT ShareholderID FROM ShareholderNames WHERE First_Name = 'blah' AND Last_Name = 'yada'")))

//This entry does NOT exist in my db

However, $getBlah is a result set, and will not ever evaluate to false. See http://pear.php.net/manual/en/package.database.mdb2.intro-query.php

// Proceed with a query...
$res =& $mdb2->query('SELECT * FROM clients');

// Always check that result is not an error
if (PEAR::isError($res)) {
die($res->getMessage());
}

You need to perform some kind of fetch on the $getBlah resource, see http://pear.php.net/manual/en/package.database.mdb2.intro-fetch.php

The MDB2_Result_Common object provides two methods for fetching data from rows of a result set: fetchOne(), fetchRow(), fetchCol() and fetchAll().

You should also turn on error reporting so you don't miss simple syntax errors. Missing ( in this case.

if $getBlah = $db->query("SELECT ShareholderID FROM ShareholderNames WHERE First_Name = 'blah' AND Last_Name = 'yada'");

[/code]