Link to home
Start Free TrialLog in
Avatar of Marthaj
MarthajFlag for United States of America

asked on

PHP7 + PDO select query not returning correct row

I am using  PHP 7.4 -  PDO, on windows platform - localhost with MS SQl Server.
I am new to PDO and am struggle with a simple select query. :(
It is to test if a row in  a table has a  match for the parameters.
I know I am making a connection to the databse.
I know the row absolutely does exist in the SQLtable.
 But my $strNbrOfRows  returns 0 rows and  it defaults to  "NOMATCH' in my if statement.
What am I doing wrong ?
Thank you in advance for any help.
$_SESSION['SQLConnect'] = new PDO('sqlsrv:Server=MYLAPTOP;Database=TESTDB', $_SESSION['uid'], $_SESSION['pwd']);

if(!$_SESSION['SQLConnect'])
{
   echo '<BR>FAILED TO CONNECT TO SQL SERVER';
}else{   
   echo '<BR>SUCCEED TO CONNECT TO SQL SERVER';
}

// original SQL STATEMENT from PHP 5.6 
//$sql = "SELECT SupplierId From Suppliers WHERE AgencyID ='$strAgencyId' AND Code //='$strSupplierCode'";

// PDO PHP 7.4

$strAgencyId = 'X23';
$strSupplierCode = 'CCL';

$stmt = $_SESSION['SQLConnect']->prepare('SELECT SupplierId FROM Suppliers WHERE AgencyID=:$strAgencyId AND Code =:$strSupplierCode');
$stmt->execute(['AgencyID' => $strAgencyId, 'Code' => $strSupplierCode]);
$result = $stmt->fetch();
$strNbrOfRows = $stmt->rowCount();
echo '<BR>$strNbrOfRows: ' .$strNbrOfRows;

if($result)
{
   echo '<BR>FOUND A MATCH: ' . $result;
   $strReturn = 'MATCH';
}else{   
    echo '<BR>NO MATCHES: ' . $result;
    $strReturn = 'NOMATCH';
}


Open in new window



Avatar of Bembi
Bembi
Flag of Germany image

Hi, why execute ?
See some examples here:
https://coursesweb.net/php-mysql/pdo-select-query-fetch

If you want to use your construction, I guess the syntax should be:

$stmt = $_SESSION['SQLConnect']->prepare('SELECT SupplierId FROM Suppliers WHERE AgencyID= :strAgencyId AND Code =:strSupplierCode'); 

$stmt->execute(array(':strAgencyId' => $strAgencyId, ':strSupplierCode' => $strSupplierCode));

Open in new window

See according to execute:
https://www.php.net/manual/de/pdostatement.execute.php

ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Marthaj

ASKER

Thank you Bembi for responding. Good link.
I changed my coding -it says it found a 'MATCH. but does not return the SupplieId and the row count is 0.
The vars - $sname and $dbnames are set to a $_$_SESSION var and I did not include them.
$connect = new PDO("sqlsrv:Server=$sname;Database=$dbname", $_SESSION['uid'], $_SESSION['pwd']);

if(!$connect)
{
   echo '<BR>FAILED TO CONNECT TO SQL SERVER';
}else{   
   echo '<BR>SUCCEED TO CONNECT TO SQL SERVER';
}
$strAgencyId = '1000';
$strSupplierCode = 'DL';
try
 {
    $connect->exec("SET CHARACTER SET utf8");     
    $sql = "SELECT SupplierId FROM Suppliers WHERE AgencyID='$strAgencyId' AND Code = '$strSupplierCode'";
    echo '<BR>$sql: ' . $sql;
   
    $result =  $connect->query($sql);
    $strNbrOfRows = 0;
    $strNbrOfRows = $result->rowCount();
  
    // If the SQL query is succesfully performed ($result not false)
    if($result !== false) 
    {
       echo '<BR>$strNbrOfRows: ' .$strNbrOfRows;
       $strReturn = 'MATCH';
       echo '<BR><BR>FOUND A MATCH: ' . $strReturn;
     
       foreach($result as $row)
       {
         echo 'SupplierId =' . $row['SupplierId'] . '<br />';
       }
    }else{
        echo '<BR>$strNbrOfRows: ' .$strNbrOfRows;
        $strReturn = 'NOMATCH';
        echo '<BR><BR>NOT A MATCH: ' . $strReturn;
    }
   
    $connect = null;        // Disconnect
 }
 
catch(PDOException $e)
 {
    echo $e->getMessage();
 }

Open in new window


Avatar of Marthaj

ASKER

Chris -Thank you for responding. In PHP 5.6, I defined variables with a $ preceding the name I gave them i.e.$AgencyId .that was defined as variable that contained a value (or not).
So, in the select statement using PDO - I could put anything after the colon, and then as long as I pass to that 'name after the colon' the actual value of the variable. IS that what you are saying ??  That it is 'simply a name' after the colon. And I need to assign a value to it i.e.$strAgencyId to it.
 And since I am only extracting one row of data from the table, the foreach loop is unnecessary.
I know there are several ways of doing this from what I have read about. Am I correct about that ??
I.E.
$stmt = $_SESSION['SQLConnect']->prepare('SELECT SupplierId FROM Suppliers WHERE AgencyID=:name AND Code =:code');
$stmt->execute(['$name' => $strAgencyId, '$code' => $strSupplierCode]);
$result = $stmt->fetch();
$strNbrOfRows = $stmt->rowCount();
echo '<BR>$strNbrOfRows: ' .$strNbrOfRows;
if($result)
{
   echo '<BR>FOUND A MATCH: ' . $result;
   $strReturn = 'MATCH';
   echo '<BR>SUPPLIERID: ' . $result['SupplierId'];
}else{   
    echo '<BR>NO MATCHES: ' . $result;
    $strReturn = 'NOMATCH';
}

Open in new window

I changed my coding as you suggest and it's working correctly, but building on this, I tried returning more than one value.
For example if my SQL statement was:
$stmt = $_SESSION['SQLConnect']->prepare('SELECT RecId, SupplierId, Code, FROM Suppliers WHERE AgencyID=:name AND Code =:code');

Open in new window

 I only received the SupplierId and it errored out on $result['SupplierCode'] .
But it was due to a coding error - see below. It needed to be $result['Code'] and not $result['SupplierCode'] .

if($result)
{
   $strReturn = 'MATCH';
   echo '<BR>FOUND A MATCH: ' . $strReturn;
   echo '<BR>SUPPLIERID: ' . $result['RecId']
   echo '<BR>SUPPLIERID: ' . $result['SupplierId'];
   echo '<BR>CODE: ' . $result['SupplierCode']; -> this was my error - I had $result['SupplierCode'] instead of          
                                            $result['Code'] Yipes !!
}else{   
    echo '<BR>NO MATCHES: ' . $result;
    $strReturn = 'NOMATCH';
}


Open in new window

Thank you for your help.
Next is the insert statement !! I think the easiest is the delete...
I still need to integrate my try-catch routine.
First one is always the rough one - at least for me. PDO is much better to work with.


Hey there.

Yep - all looking good. In PHP variables start with the $ symbol. When using named parameters in your SQL, your placeholders start with a colon. When you execute you then assign a value to those placeholders by setting up the array you pass in:

$someVar = "Test Value";

$stmt = $db->prepare("SELECT col1, col2, col3 FROM someTable WHERE col4 = :placeHolder1 AND col5 = :placeHolder2");
$stmt->execute([
    'placeHolder1' => $someVar,
    'placeHolder2' => 'Some Other Value',
]);

if ($row = $stmt->fetch()) {
    echo "Match";
    echo "Col1 has a value of " . $row->col1;
} else {
    echo "No Match";
}

Open in new window

Something else to bear in mind. As well as using named parameters in your query, you can ue the question mark instead (positional parameters). The principle is the same - it just means that when you execute() you have to make sure the values are in the correct order, as you don't pass in the keys.

Here's a quick demo of the INSERT statement

try {
    $stmt = $db->prepare("INSERT INTO myTable (col1, col2, col3) VALUES (?, ?, ?)";
    $stmt->execute([
        $someVariable,
        "Some Value",
        "Some Other Value",
    ]);
} catch (PDOException $e) {
    // something went wrong!
    die($e);
}

Open in new window

And one last point - trying to store the PDO connection in the session is got going to work. In your case, you're never trying to retrieve it, but there's absolutely no point in trying to store it there. Typically, you just use a $variable - people often use $db, $conn, $pdo or $dbh - just because it makes code clearer:

$db = new PDO(...);
Avatar of Marthaj

ASKER

Thank you both for responding. It has been easier than what I thought ! I just needed a simple clear explanation.
And learning that the 'placeholders' are just simply a name (or question mark) really brought it all home to me !