Why is my Query not returning a result

Question

Background

I have a large table with lots of values and want to check if any of the names are repeating and only display those results. I have built a view and cannot get the SQL query to return the result I want.

Code

Here is the code as it stands, I've done loads of variations of it but cant get the result and think that there must be a core problem with my logic.

So I have built a view and selected a couple of column and now I want a column that will print out the name of the title the number of times it appears in the database. Or just count the number and print that.

<?php
  $value = $row->title;
  $query= "SELECT node.title, node.type 
           FROM {node} node 
           WHERE  (node.type IN ('institution') 
           AND node.title  =".$value.")";
  $result= db_query($query);
  $wat = $result->fetchAll(PDO::FETCH_COLUMN, 0);
  foreach($wat as $res){echo $res;}
?>

So as you can see I get the value of title at the row I'm on and compare it to every value for title in the database, then return only results that match. After that I loop through them and get the value printed.

I have managed to get all the titles out and print them but as soon as i bring $value into it it breaks. I have checked the value of $value and it returns the expected result. I just cant figure out what is wrong with my SQL query.

Question

What is wrong with my Query? is there something that stops me using the $row->title in drupal that I don't know about?

Answer 1

Without access to your error logs etc. I'm going to assume the problem is that you are not putting quotes around your title string, so it is trying to use the title as a database column and it fails.

It should be like this:

AND node.title  = '" . $value . "')";

That is not a great solution either though because it opens you up to possible SQL injection attacks so instead, use Drupal's built in sanitisation like this:

$query = "SELECT title, type 
            FROM {node}
           WHERE type = 'institution'
             AND title = :title";
$result = db_query($query, array(':title' => $value));

When you do it like that Drupal will sanitize the $value and it will also know that it is a string and wrap it in quotes. For more information see the documentation for Drupal's database API - Static queries.

In the above example I also changed the type "IN" operator to "=" because you only had one value, removed the redundant brackets around the where clause and removed the table alias to make the query as simple as possible.

On the topic of EntityFieldQuery, there are multiple different ways to run a query in Drupal and the best solution depends on the particular use case. If you want optimal performance and don't need to restrict results based on node access then db_query() is likely the best method. Without knowing your requirements I couldn't advise on the best approach for your current query.

Answer 2

i have always used entity query for select records.

   $value = $row->title;

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'institution')                  
  ->propertyCondition('status', 1, '=')
  ->propertyCondition('title',$value , '=')  
  ->propertyOrderBy('created','DESC');
     $results = $query->execute();
   if (isset($results['node'])) {
      $nodes = node_load_multiple(array_keys($results['node']));

      foreach ($nodes as $row) {
        // your stuff here
     }
  }

hope this helps you...

Answer 3

You should follow @Kamal Oberoi's advice and use EntityFieldQuery (EFQ).

The reason that changing to using EFQ is a good idea (apart from possibly fixing your issue) is that it will make your code more robust, transferable, extendable and secure.

• Robust: The underlying data structure might change with a module update one day; Your query would stop working while EFQ would not.
• Transferable: If you want to migrate your site to another data storage system (not MySQL), EFQ would still work.
• Extendable: e.g. adding a pager to your table is as simple as adding ->pager()to your EFQ definition and theme('pager') to the end of your rendered output.
• Secure: It's pretty much impossible for any SQL injection attacks to be introduced as a result of using an EFQ. In your example above, if $value is not sufficiently sanitised then it could introduce a major security hole.

n.b. For queries for which EFQ is not suited (i.e. ones that don't return a list of entities), use db_select, which has many of the same advantages. db_query is best left as the last resort.

Answer 4

I'd echo this would be a good place for EntityFieldQuery.

But to answer what's wrong with your actual code (besides the SQL injection vulnerability), it's that $value isn't wrapped in quotes, so it's not being treated as a string.

So if your $row->title is 'Foo Bar', this is how your clause is being prepared/executed:

WHERE node.title = Foo Bar

This won't work. The clause needs to look like this:

WHERE node.title = 'Foo Bar'

That's what's immediately wrong. The more complete answer is that you should use argument placeholders, which has consistent syntax regardless of variable type and protects against SQL injection:

$query= "SELECT node.title, node.type 
           FROM {node} node 
           WHERE  (node.type IN ('institution') 
           AND node.title = :title)";
$placeholders = array(':title' => $row->title);
$result= db_query($query, $placeholders);