Getting a folder ID in as few SQL statements as possible
<p>Consider the database table: folders</p>

<pre>
folders
id   parent_id    name
1    0            a  
2    1            b  
3    2            c
</pre>

<p>'b' is a folder inside 'a' and thus it's parent_id is the id of 'a'.<br>
Folders with a parent_id of 0 simply means it's in the root folder.</p>

<p>I wrote a recursive function in php that helps me get the ID of a path that I'm interested in.</p>

<p>For example:</p>

<pre><code>echo get_folder_id('a/b/c'); // 3 (3 SQL queries)  
echo get_folder_id('a'); // 1 (1 SQL query)  
echo get_folder_id('a/b'); // 2 (2 SQL queries)  
echo get_folder_id('a/c'); // false (2 SQL queries)
</code></pre>

<p><b>
Problem: For each folder in the path, I had to make a SQL query to the DB server.</p>

<p>Question: Is there a way I can reduce the number of queries if the path is 'a/b/c'?
</b></p>

<p>Here's my current working solution for reference.</p>

<pre><code>function get_folder_id($path, $parent_id=0) {
    $path = explode('/', $path);
    $id = 0;

    //if there's only 1 folder in the path, query the database for the ID
    if (count($path) == 1) {
        $rs = $this-&gt;db-&gt;select('id')
                -&gt;from('folders')
                -&gt;where('name', $path[0])
                -&gt;where('parent_id', $parent_id)
                -&gt;limit(1)
                -&gt;get();

        if ($rs-&gt;num_rows() == 0) return FALSE;

        $result = $rs-&gt;row_array();
        return $result['id'];
    }

    foreach($path AS $i=&gt;$p) {
        if ($i==0 &amp;&amp; $p=='') continue; //if a path starts with / move on to the next item
        $id = $this-&gt;get_folder_id($p, $id);
    }

    return $id;
}
</code></pre>

<p>Note: I'm using CodeIgniter framework but this isn't related to my question. Just for folks wondering why I used $this->get_folder_id() and $this->db</p>
