1

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?

5

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.

  • +1 for EntityFieldQuery. I would also suggest using db_select(). Drupal has some nice tools. Might as well use them. – sareed Sep 3 '15 at 15:21
  • Yes it has a variety of good tools but don't use them just because they're there, use them because they are the right tool for the task. EntityFieldQuery, db_select() and db_query() all have strengths and weaknesses and none of them are the best tool for all scenarios. – rooby Sep 6 '15 at 10:24
  • True but db_query is VERY rarely the correct tool. – sareed Sep 8 '15 at 16:03
  • Depends on what kind of custom modules you are writing and what the queries are for, but each to their own. – rooby Sep 8 '15 at 22:06
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...

  • You're making assumptions that the OP wants to get only published results and wants results ordered by newest first. – rooby Sep 3 '15 at 15:03
2

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.

  • While I agree with you on your listed benefits for EntityFieldQuery, they are benefits only if you need those features. I disagree that db_query() should always be a last resort. db_select() and EntityFieldQuery are more complex and slower than db_query(). EntityFieldQuery is also limited in support for more complex queries. If you want node access support though db_query() may not be the best option. The best approach is to be familiar with the pros and cons of the different approaches and choose the one that best fits the requirements. – rooby Sep 3 '15 at 15:07
  • Fair points, but I'd add that from a security perspective, if anyone reading this doesn't 100% understand the potential security implications with arbitrary queries and how to mitigate them, then they should use db_select of EFQ. – Paul Querol Sep 3 '15 at 16:52
0

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.