Best Practice on PDO Query Using Not Equal

I have a query that works but it seems to me there should be a better way to look for something not equal.  Here is the code.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
	<title>View Records</title>
</head>
<body>
<h1 style="text-align:center;">Incompleted Chore List</h1>
 <link rel="stylesheet" href="style.css" type="text/css" />

<?php
/* 
	VIEW.PHP
	Displays all data from 'home' table
*/
error_reporting(E_ALL);
ini_set('display_errors', 1);


	//include database connection
include 'database.php';

// select all data
$query = "SELECT * FROM home WHERE View != 'H'  ORDER BY Month,  Chore_Location ASC";
$stmt = $con->prepare($query);
$stmt->execute();
 
		
	// display data in table
	echo "<p><b>View Incompleted Tasks</b> | <a href='view-paginated-pdo.php?page=1'>View Paginated</a> |  <a href='read-pdo.php'>View All</a></p>";
	
	echo "<table border='1' cellpadding='10'>";
	echo "<tr> <th>ID</th> <th>Chore Location</th> <th>Chore Name</th> <th>View</th><th>Quarter</th><th>Month</th><th>Frequency</th><th>Notes</th></tr>";

	// loop through results of database query, displaying them in the table
		
		while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
			extract($row);

		// echo out the contents of each row into a table
		echo "<tr>";
		echo '<td>' . $row['id'] . '</td>';
		echo '<td>' . $row['Chore_Location'] . '</td>';
		echo '<td>' . $row['Chore_Name'] . '</td>';
		echo '<td>' . $row['View'] . '</td>';
		echo '<td>' . $row['Quarter'] . '</td>';
		echo '<td>' . $row['Month'] . '</td>';
		echo '<td>' . $row['Frequency'] . '</td>';
		echo '<td>' . $row['Notes'] . '</td>';
		echo '<td><a href="edit.php-pdo?id=' . $row['id'] . '">Edit</a></td>';
		echo '<td><a href="delete-pdo.php?id=' . $row['id'] . '">Delete</a></td>';
		echo "</tr>"; 

	}
	// close table>
	echo "</table>";
	
	
?>
<p><a href="new-pdo.php">Add a new record</a></p>

</body>
</html>	

Open in new window


I have a list and one of the fields is View with a value of H or D.  H stands for hidden and D for display.  I need to print the list with all the H's not showing.  Thus the reason for my != 'H" in my query.

Is there a better way to represent this to not be prone to sql injections?  If so, what is the way?

Thanks,
sharingsunshineAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BradyPrincipal Data EngineerCommented:
Just change WHERE View != 'H' to WHERE View = 'D'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
... to not be prone to sql injections ...
SQL injection is a term of art.  You can learn about it by making a Google search for the exact term SQL injection.  In the code sample with this question, no SQL injection can occur because there is no external data that is injected into the SQL query string.

But that said, please take a moment to learn why extract($row); is an anti-practice.  That line of code would get you fired from a professional programming job, so don't ever do that unless you know, and can explain, why you're doing it!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html
sharingsunshineAuthor Commented:
I appreciate you giving the answer to my code but I am not sure what the alternative to to extract($row) is.  I read all of your link and most of the associated links off of the original article.

Looking on line they suggest on StackOverflow to use
extract($row, EXTR_SKIP);

Open in new window

to skip any existing variables.

Ray, Is that what  you had in mind?

Thanks,

Randal
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ray PaseurCommented:
PHP extract() is documented here.
http://php.net/manual/en/function.extract.php

Since extract() injects variables into the symbol table, you would want to know what variable names are being injected.  You might use the EXTR_PREFIX_ALL flag and a prefix of 'row' but please see the note here:
http://php.net/manual/en/function.extract.php#106374

I think a better practice is to retrieve the row with a "fetch_object" method.  Then you can address the data in the row directly with things like this:
$row->id
$row->Frequency

The PHP object notation is very easy to use in HEREDOC and other quoted strings that need variable substitution.  It does not contain any quotes or apostrophes, so there is nothing to escape.  With less fiddly punctuation, there is less chance to create parse errors.
Ray PaseurCommented:
Preparing a row with HEREDOC notation is done like this.  Heed, but do not be put off by the warning on the PHP man page.
http://php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc
// loop through results of database query, displaying them in the table
while ($row = $stmt->fetchObject())
{
    $tr = <<<ENDROW
<tr>
<td>$row->id</td>
<td>$row->Chore_Location</td>
<td>$row->Chore_Name</td>
<td>$row->View</td>
<td>$row->Quarter</td>
<td>$row->Month</td>
<td>$row->Frequency</td>
<td>$row->Notes</td>
<td><a href="edit.php-pdo?id=$row->id">Edit</a></td>
<td><a href="delete-pdo.php?id=$row->id">Delete</a></td>
</tr>
ENDROW;

    echo $tr;
}

Open in new window

sharingsunshineAuthor Commented:
Mark, thanks for the answer but I gave Ray points too because even with your answer I was wondering if that was a good practice.  Ray satisfied my concerns about sql injection and got me to quit using extract in the future.  These questions weren't asked but they were certainly in the back of my mind as to why I asked about the best practice in the first place.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.