More php and MySQL fun

[Meestor_X]

Still learning mySQL and boning up on php. To see if any rows are returned from a query, many tutorials and sites suggest this kind of code:

$sql = "SELECT * FROM $table WHERE authtoken='$token' AND user='$user'";
if ($result = $db->query($sql)) {
  // do something with the returned rows
}

To do something if any rows are returned. However, in some cases, the sql returns no rows and yet $result is not false. I’ve changed this to:

$sql = "SELECT * FROM $table WHERE authtoken='$token' AND user='$user'";
$result = $db->query($sql);
if ($result->num_rows > 0) {
  // do something with the returned rows
}

Which works, of course, but lots of tutorials show the first method as correct. Is it just a case of lots of examples simply being wrong?

[DaveS]

@Meestor_X

$sql = "SELECT COUNT(*) as CNT FROM $table WHERE authtoken='$token' AND user='$user'";

then query if CNT is >0 … it will ALWAYS return a value

[Meestor_X]

Thanks, @DaveS …
Not sure I follow. I’m trying to find out why the first method is wrong, even though it’s purported as the way to normally do a query…
e.g.
https://www.w3schools.com/php/func_mysqli_query.asp

[DaveS]

been a long time since I’ve done mySQL, but I’d guess the 1st example returns NULL/Nil and therefore isn’t comparable. I was taught to always use a count method, that way you didn’t need to worry about the underlying database engine, as that method always worked.

[jjr]

A result of zero rows is not an error if the query is valid:
SELECT * FROM people WHERE firstname="Edgar";
The query may be valid, but there may be no rows in people with a firstname="Edgar".

[npalardy]

if the “do something with the rows” iterates across all the rows, but there are none (the recordset exists but is empty) then processing code will do nothing

[Meestor_X]

Understood. So I was misunderstanding the first example. It’s merely testing that the query was performed without error. “No Rows returned” is not an error.
Probably my php should be:

$sql = "SELECT * FROM $table WHERE authtoken='$token' AND user='$user'";
$result = $db->query($sql);
if ($result && $result->num_rows > 0) {
  // do something with the returned rows
}

To keep from producing an error warning if the Query fails. (not that php shows errors on screen by default, but still…)

[npalardy]

In Xojo if you did

 dim r as recordset = db.sqlselect ( query_that_runs_ but_returns_no_rows )
 while r.eof <> true
       // do stuff with each row
       r.movenext
 wend

and there are no rows then the loop will not execute
if there are then it would

I’m sure php would do the same when the query runs but there are no results

[thorstenstueker]

Yes it will do the same when nimrods is not > 0 then the if condition is not reached

[Meestor_X]

I sure do miss ?. (null conditionals) from .net!
if (result?.num_rows > 0) then

[thorstenstueker]

For what you miss it?

[Meestor_X]

@thorstenstueker

For what you miss it?

I just like less verbosity.

[npalardy]

less verbosity is sometimes also less clarity
its a tricky balance

[thorstenstueker]

Only chance to get if a variable is null is the function is_null() which gives a Boolean back. There is nothing else comparable to it.

 Person firstPerson = null;
var newAddress = new Address();

if (firstPerson != null)
{
    firstPerson.Relocate(newAddress);
}

which would be applicable also with if is_null(). There is no leak, that’s what I want to say. If you want to check it, you may check it and react to it. Where is the need for more in PHP?

[HalGumbert]

@Meestor_X

Still learning mySQL and boning up on php. To see if any rows are returned from a query, many tutorials and sites suggest this kind of code

This is the code that I use when using PDO SQL calls. This is part of a class, so everywhere you see “$this->” it’s setting a var in the class.

Where you see “if ( $sqlIsSelect )” is where I get the row count. For Selects, it sets the number of returned rows, but for Inserts, Updates, Deletes, it set the number of row affected.

public function serverConnect() {
        try {
            $servername = DBS_SERVERNAME;
            $serverport = DBS_SERVERPORT;
            $username = DBS_USERNAME;
            $password = DBS_PASSWORD;
            $dbname = DBS_DBNAME;
            $optDB = array(
                \PDO::MYSQL_ATTR_FOUND_ROWS => TRUE,
                \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
            );
            $newDB = new \PDO( "mysql:host=$servername;port=$serverport;dbname=$dbname", $username, $password, $optDB );
            return $newDB;
        } catch ( \PDOException $e ) {
            logEventToFile( 'Catch', 'DBConnect', $e->getMessage(), paramEncode( $_SERVER[ 'PHP_SELF' ] ), $_SESSION[ 'recUsersCURRENT' ][ 'EmailAddress' ] ?? 'No', $_SESSION[ 'recUsersCURRENT' ][ UUIDUSERS ] ?? 'No' );
            return 'Error: ' . $e->getMessage();
        }
    }

	public function query( ) {
	    // Init
	    $this->errorB = false;
        $this->rowsD = [];
        $this->rowCount = 0;
        $this->messageSQL = '';
        $this->messageExtra = '';
        try {
            // Connect
            if ( !isset( $GLOBALS[ 'db' ] ) ) {
                $GLOBALS[ 'db' ] = $this->serverConnect();
            }
           
            // Prepared Statement
            $ps = $GLOBALS[ 'db' ]->prepare( $this->querySQL );
            
            // Bind
            if ( !empty( $this->queryBindValuesA ) ) {
                $bindValuesArrayCount = count( $this->queryBindValuesA );
                for ( $bindIndex = 0; $bindIndex <= ( $bindValuesArrayCount - 1 ); $bindIndex++ ) {
                    
                    $thisColValue = $this->queryBindValuesA[ $bindIndex ];

                    // Bind as NULL if value is 'NULL'
                    if ( strval( $thisColValue ) === 'NULL' ) {
                        $ps->bindValue( $bindIndex + 1, NULL, \PDO::PARAM_NULL );
                    } else {
                        $ps->bindValue( $bindIndex + 1, $thisColValue, \PDO::PARAM_STR );
                    }
                    
                }
            }
            
            // Execute
            $ps->execute();
            
            // Check the Statement
            $sqlWords = explode( ' ', $this->querySQL );
            $sqlIsSelect = ( strtoupper( $sqlWords[ 0 ] ) === 'SELECT' ? true : false );
            // $sqlIsInsert = ( strtoupper( $sqlWords[ 0 ] ) === 'INSERT' ? true : false );
            // $sqlIsUpdate = ( strtoupper( $sqlWords[ 0 ] ) === 'UPDATE' ? true : false );
            
            // Process
            if ( $sqlIsSelect ) {
                // Process SELECT
                $this->errorB = false;
                $this->rowsD = $ps->fetchAll( \PDO::FETCH_ASSOC );
                $this->rowCount = count( $this->rowsD );
            } else {
                // Process NOT SELECT
                $this->errorB = false;
                $this->rowsD = [];
                $this->rowCount = $ps->rowCount();
            }
        } catch ( \PDOException $e ) {
            // Log Error
            logEventToFile( 'Catch', 'DBSelect: ' . $this->querySQL, $e->getMessage(), paramEncode( $_SERVER[ 'PHP_SELF' ] ), $_SESSION[ 'recUsersCURRENT' ][ 'EmailAddress' ] ?? 'No', $_SESSION[ 'recUsersCURRENT' ][ UUIDUSERS ] ?? 'No' );
            $this->errorB = true;
            $this->rowsD = [];
            $this->rowCount = 0;
            $this->messageSQL = $e->getMessage();
            return;
        }
        
        // Update Message
        if ( $this->rowCount < 1 ){
            $this->messageSQL = 'Found 0';
        } else {
            $this->messageSQL = 'Found ' . $this->rowCount;
        }
        
    }

[Meestor_X]

@thorstenstueker

Person firstPerson = null;
var newAddress = new Address();

if (firstPerson != null)
{
    firstPerson.Relocate(newAddress);
}

In vb this could be shortened to:

Dim firstPerson As Address = nothing;
Dim newAddress = New Address;
firstPerson?.Relocate(newAddress);

[npalardy]

Personally the first is clearer - even though I dont write PHP

terse ≠ clear in a lot of languages

[Meestor_X]

I like to have a choice. I got no time for verbosity!

[npalardy]

< Old guy mode >
Lets just say write the code as if someone else, someone new, someone NOT you, is going to need to read it 6 months from now
You will appreciate the clarity in your code
</ Old guy mode >

I worked for one company then nearly 7 years later contracted back to them to review & fix my own code
I trkly appreciated having adopted that philosophy as it made code review (for Y2K) trivial
But they paid me well anyway

[Meestor_X]

Clarity for me comes from well thought out modules/classes, proper naming and good commenting. I can see how if you aren’t familiar with .net you’d not understand ?.
But if you do understand it, it’s plenty obvious what’s going on.

I also really came to appreciate this in javascript:

myVar= someVar || 'default value'

Which some people might find not clear, but to me it’s just as clear and much less verbose than Xojo’s :

myVar = if (someVar <> Nil,  someVar, 'default value')