Best way to check if a SQL query has returned any results

In the process of learning both MySQL and PHP, I am trying to determine the most optimal way to test whether a query has a result or not. A search on the web show many different examples of how this can be done. While any of these ways would work, I just want to make so that I am using code that will not create any problems for me down the road.

Here is my current query and the way I plan to test:

    $sql="SELECT * FROM tableResources WHERE reso_set_id = ? AND reso_sw == 'on'";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$userSelection]);

    if ($stmt($result)!=0) {
         foreach ($stmt as $row) {
             // do stuff
         }
    }

Here is the test I plan to use for checking if there is a result.

if (mysql_num_rows($result)!=0)

I think this would be the best way to test if there is a result or not. Are there any concerns I should be aware testing this way?

Is this “if” test even needed? If there are no results from the foreach, then will anything in the “do stuff” area even be executed?

Why would you go from Mysqli to the obsolete mysql_* that has been removed from Php?

Because I don’t know that. As i said, I looked at different coding examples and thought I picked one that would work for my requirements.

I’m sure you have a wealth of knowledge to share Benanamen, but responses like that are adversarial at best and do not address my original question. It also feels like a slight… like saying “hey you idiot, why are you using an obsolete mysql reference”.

I will do better on my part in future posts to make sure people understand that I am a beginner coder and will ask for patience in helping me understand.

Here is an example of how it should be written.

<?php
 $sql = 'SELECT password FROM users WHERE user_id = ?';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$_SESSION['user_id']]);
    $row = $stmt->fetch();

    if($row){
        //do something.
    }

Thank you for your assistance.

:

Your welcome. Happy to help. Let me know if you have any other questions. :smiley:

* The example I showed is using PDO and is only getting one record so it uses fetch. To get multiple records with out doing a do while, you would use fetchAll.

I would highly reccomend you use PDO. Here is a tutorial to get you going.
https://phpdelusions.net/pdo

Out of curiosity, would the foreach method work as well? If there were no results found matching the WHERE conditions, then would the code within the foreach even be executed? I suspect not, but I just thought I should check before I code for it.

Since you are learning, I would say just try it so you can see for yourself exactly what happens. Make sure you have error reporting turned on as well.

You can also download my PDO Bumbstart Database that will show you how to do many basic things. https://github.com/benanamen/pdo_bumpstart_ver1.6

From my example, the foreach would go after the if($row) so that might answer your question.

    if($row){
        foreach($row as $value)
{
// Doing stuff
}
    }

I only want to use PDO, but I don’t always find that document to be informative given specific scenarios such as the one I mentioned. (And my days of reading the entire document and retaining all that info are well behind me). I have to now learn on a problem solving basis – identify what function I want to accomplish and then figure out the code needed for that. Then move on to the next function, and so on.

The tutorial I linked will cut straight to it and my download will give you a clean demo of how to do the basic things you will need to do.

I’m just in the middle of coding that to see what happens :slight_smile: I do appreciate having the “proper code” to use should my attempt fail.

A couple other tips for just starting out. If you are on Windows I would recommend Laragon over the other WAMPS out there. One thing it does the others don’t is automatically creates virtual hosts so you can use a url like myproject.test instead of localhost/myproject. Also, sooner than later you will want to learn how to use git and GitHub.

I’m glad you brought this up. I have currently been developing my site in the Cloud9 IDE. It has been really great to work with. Sadly, as of July 30 it is being deactivated. There is an option to transfer to AWS Cloud but the last time I tried it really didn’t go well.

I’m not sure if you are familiar with Cloud9, but from what I am now reading of GitHub, it sounds like the same sort of application?

While testing I would try both valid and invalid queries then display the returned function/method results:

<?php 
declare (strict_types=1); // fail fast
error_reporting(-1); //maximum errors
ini_set('display_errors,'true'); // only locally and not online


$sql = 'SELECT password FROM users WHERE user_id = ?';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$_SESSION['user_id']]);
    $row = $stmt->fetch();
    // Debug also check PHP manual for result types
    var_dump($row);

    if($row){
        //do something.
    }