PHP Checking If Query To Remote Database Returned Data Using PDO

Question

The following code is meant to check if a certain query returns data. The query takes a piece of session data that is searched by the user on another page.

$whatwechecking = $_SESSION ['assignment_searched']; 


$FindAsigns = $connection->query("SELECT `NAME`,`DATE`,`GRADE` FROM grades 
WHERE `ASSIGNMENT` = '$whatwechecking' ORDER BY `ASSIGN_ID` DESC LIMIT 0,5");

if ($FindAsigns->fetchColumn() > 0) //this is the attempt at seeing if 
                                  //the query returned something
{

while($row = $FindAssigns->fetch()) //this loop outputs the data found in the 
                                    //query into a table
 {

    ...find data


    echo (...echo out data into table);


}

    }

    else
    {

        header('Location: NameNotFound.php'); //this is to redirect the user
                    to an error page that says data was not retreived in the query

    }

ideally I'd like to do this in PDO as the query is in the same standard. The fetch rows method I imagine is not the most ideal in this case, so is there a better way to see if the query returns nothing?

Wireblue · Accepted Answer · 2014-03-31T01:54:44.380

1

A few things. The current query is not SQL-safe. It's possible that $_SESSION['assignment_searched'] may contain a malicious value, so I'd recomend either using the PDO Quote function or using prepared statements. In the example below I've used prepared statements.

Once you've prepared and executed the query you can easily check how many rows were returned and loop through them.

There's plenty of helpful examples of PDO in action on the internet. A quick search in Google will help. The PHP manual on PDO is also very good and the community have contributed many examples.

https://www.google.com/search?q=PHP+PDO+MySQL+Examples

http://www.php.net/manual/en/book.pdo.php

// Create PDO Prepared Statement (leave placeholder for our variable)
$stmt = $connection->prepare("
    SELECT `NAME`, `DATE`, `GRADE` FROM grades
    WHERE `ASSIGNMENT` = :whatwechecking
    ORDER BY `ASSIGN_ID` DESC
    LIMIT 0,5
");

// Bind Data to Placeholder in Statement and Execute (SQL-safe)
$stmt->execute(array('whatwechecking' => $_SESSION['assignment_searched']));

// Check if Anything was returned
if ($stmt->rowCount() > 0) { 
    // YES! Fetch Items and Loop Through
    foreach ($stmt->fetchAll() as $item) {
        var_dump($item);
    }
}

edited Mar 31 '14 at 01:54

answered Mar 31 '14 at 01:49

Wireblue

1,329
1
14
24

Ok, so you are saying pass the Session value through a check kind of like an MSQLI real escape string? – Andrew L Mar 31 '14 at 03:51
Yep, the PDO Quote function and prepared statements are both modern alternatives to the `mysql_real_escape_string` function. – Wireblue Mar 31 '14 at 04:40
Cool. On the other page I pass the session variables through MSQI_real_escape_string. Is this as secure? I had some issues binding the variables in some other applications of the site and was wondering if real_escape_string is just as good (assuming of course that no system is perfectly secure) EDIT: I realize I had asked for PDO methods to which you delivered perfectly. I was merely wondering how secure MSQLI_R_E_S was compared to PDO binding. – Andrew L Apr 01 '14 at 03:50
This may help answer some of your questions. http://stackoverflow.com/questions/1314521/how-safe-are-pdo-prepared-statements In short, both methods operate slightly differently to achieve the same results (safety from SQL injection). – Wireblue Apr 02 '14 at 00:30
Perhaps consider marking the question as solved if you're happy with the original answer. If you have any further questions, I'd recommend browsing through the site. It's highly likely you'll find what you're after. =) – Wireblue Apr 02 '14 at 00:38

PHP Checking If Query To Remote Database Returned Data Using PDO

1 Answers1