0

I'm trying to check if an sql query brings back any results in PHP, I've tried using mysql_num_rows($res) but I keep getting an error saying that the function expected parameter to be a resource but it is instead getting an object.

I've attached the relevant code here

$dsn = "mysql://$username:$password@$host/$dbName"; 

require_once('MDB2.php');    

$db =& MDB2::connect($dsn);

if(PEAR::isError($db)){ 
    die($db->getMessage());
}

$sql=//sql query

$res =& $db->query($sql);
if(PEAR::isError($res)){
    die($res->getMessage());
}

$resultsFound = false; 

if (mysql_num_rows($res)>0){
while($row=$res->fetchRow()){
    //insert results here
}
} else {
     echo "<br><h2>Sorry, invalid input</h2>";
}

I'm sure the solution is fiendlishly simple but I'm new to php and sql and would really appreciate your help!

Jeremy
  • 11
  • 4
  • 3
    Looks to me like you're mixing different mysql apis here. – Funk Forty Niner May 09 '20 at 15:06
  • You should try to print_r or var_dump the result of your query when it's empty, and check for that instead. – syedmh May 09 '20 at 15:07
  • 3
    Yeah, [this library](https://pear.php.net/manual/en/package.database.mdb2.intro-connect.php) which I have never heard of, and the deprecated mysql_* library – Alon Eitan May 09 '20 at 15:08
  • What does `MDB2` do? Why not use standard `pdo` or `mysqli`? – user3783243 May 09 '20 at 15:10
  • 1
    You're dealing with really ancient stuff here. The first thing you need to do is to determine what database library your project is using because you can't mix them. If it isn't a legacy project but new code, you shouldn't be using any of those. – Álvaro González May 09 '20 at 15:19
  • Use PDO - See https://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons and https://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059 and https://conetix.com.au/blog/why-you-should-use-pdo-instead-mysqli/ and https://websitebeaver.com/php-pdo-vs-mysqli – Mawg says reinstate Monica May 09 '20 at 16:07

1 Answers1

-2

Maybe this one helps? https://pear.php.net/manual/en/package.database.mdb2.intro-fetch.php

You could use fetchAll() to fetch all results - count() on the response of fetchAll() should do the trick for you.

After you've built up your functionality, I suggest you migrate to some newer stuff, you should not use MDB2 anymore - it's deprecated stuff. https://www.php.net/manual/en/pdo.query.php may be the more modern way to use PHP with mysql.

Otherwise use https://www.php.net/manual/en/mysqli.query.php - you can also retrieve the number of datasets in there.

gth44
  • 77
  • 5
  • Depending on code/results `fetchAll` might be very inefficent. You will be committing every row to memory. – user3783243 May 09 '20 at 15:13
  • Yes, that's correct - it depends on what you're gonna do with the results. If you need them it's okay to fetch them all. But I guess, as this question looks on a Beginner base - it's okay to use it to keep the code simple and stupid. He can do more complex things later on. – gth44 May 09 '20 at 15:16
  • Thank you for this! However when I use if(count($res->fetchAll())>0) then I can't seem to use while($row->fetchRow()) I presume this is because all data has already been fetched? – Jeremy May 09 '20 at 15:47
  • Correct. You do something like: `$x = $res->fetchAll(); if (count($x) > 0) { foreach ($x as $y) ......`. In fact you could directly start with `foreach ($res->fetchAll() as $data)` - but I guess there is a point why you want to check if the count is bigger than zero. Long story short: Simply use the return of fetchAll() and foreach over that, if count is bigger than zero. – gth44 May 09 '20 at 15:54
  • Please use English links when referencing external resources. – Funk Forty Niner May 09 '20 at 16:54
  • Fixed to englisch. Sorry, did not see this one. – gth44 May 30 '20 at 07:48