PHP MySQL - An attempt at recursive functions

Question

I'm trying out my first recursive function (at least I think I am!) and it only half works. First, the code:

function check_title($i,$title) {
    $q=mysql_query("SELECT Title FROM posts WHERE Title = '$title'");
    $num=mysql_num_rows($q);
    if($num==0) {
            return $title;
    }else {
            $title=$title.' ('.$i++.')';
            check_title($i,$title);
    }
}

What I'm doing is taking a string (title) and checking if that title exists in the db already. If it does, I want to append a number to the newer of the duplicates (e.g. 'I Am A Title' becomes 'I Am A Title-2'). I then need to run the function again to check this new version of my title, and increase the appended value as required ('I Am A Title-3'). Once no duplication is discovered, return the Title in its acceptable form.

It works when no duplication is found (the easy bit), but fails when duplication is found. Instead of appending a number, the entire title variable is emptied.

Any help would by greatly appreciated!

Answer 1

Use a loop instead...

$record_exists = true;
$title_base = "I Am A Title";
$title = $title_base;
$i = 0;
while($record_exists) {
    $q=mysql_query("SELECT Title FROM posts WHERE Title = '$title'");
    $num=mysql_num_rows($q);
    if($num==0) {
        $record_exists = false;
        // Exit the loop.
    }
    else {
        $i++;
        $title = $title_base . "-" . $i;
    }
}

echo $title; // last existing title

However, optimally you'd do more work with a single SQL query and iterate the result, saving a lot of trips to and from the database.

And just for fun...

$title_base = "I Am A Title";
$title = $title_base;
for ($i=1, $num=1; $num != 0; $i++)
{
    $q=mysql_query("SELECT Title FROM posts WHERE Title = '$title'");
    $num=mysql_num_rows($q);
    $title = $title_base . "-" . $i;
}

echo $title; // next title in sequence (doesn't yet exist in the db)

Answer 2

In order to minimise MySql interactions I'd recommend something similar to the following.

function checkTitle($title)
{
    /*Return all iterations of the title*/
    $res = mysql_query("SELECT COUNT(title) AS titleCount FROM posts 
                    WHERE SUBSTR(title, 1,". strlen($title) .") = '$title' ");
    /*Return the incremented title*/
    return $title. (mysql_result($res, 0, "titleCount")  + 1);

}

Example:

mysql> select title from posts;
+----------+
| title    |
+----------+
| firefox1 |
| firefox2 |
| shoe     |
| firefox3 |
+----------+
4 rows in set (0.00 sec)

mysql> SELECT COUNT(title) AS titleCount FROM posts WHERE SUBSTR(title, 1,7) = 'firefox' ;
+------------+
| titleCount |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)
mysql>

---- Follow up test

Test table structure.
mysql>SHOW COLUMNS FROM posts;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| title | varchar(12) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

/*Test code and output*/
function checkTitle($title)
{
    /*Return all iterations of the title*/
    $res = mysql_query("SELECT COUNT(title) AS titleCount FROM posts 
                    WHERE SUBSTR(title, 1,". strlen($title) .") = '$title' ");
    /*Return the incremented title*/
    return $title. (mysql_result($res, 0, "titleCount")  + 1);

}
mysql_connect("localhost","root", "password");
mysql_select_db("test");
echo checkTitle("firefox");

Output: firefox4

Answer 3

Your recursive function is fine except for 2 things:

1. The original title isn't maintained between recursive calls. Hence each time $title = $title . ' (' . $i++ . ')' runs, another parenthesis is appended to the title, like "abc", "abc (1)", "abc (1) (2)" and so on.

2. You are returning $title when no more matches are found but no title is returned in the ELSE. It is important to do so. When the execution reaches the IF, it returns the title but the returned title is not assigned anywhere and hence is lost.

Here is the revised code:

$orgTitle = 'I am a title';
function check_title($i, $title = '') {
    global $orgTitle;
    $q = mysql_query("SELECT Title FROM posts WHERE Title = '$title'");
    $num = mysql_num_rows($q);
    if ($num == 0) {
        return $title;
    } else {
        $title = $orgTitle . ' (' . ++$i .')';
        return check_title($i, $title);
    }
}
echo check_title(0, $orgTitle);

Note the addition of new variable $orgTitle. I've replaced it in the assignment statement inside the ELSE. This does the fix for point 1 above.

Also note the return added before check_title call in the ELSE. This solves point 2.

Hope it makes sense!

Add-on: Recursions are confusing, logically complex and tricky to debug. Also, recursive calls consume more memory (not in case of simple operations like your example) because the compiler/interpreter had to maintain the state variables for all steps in a recursion.

Answer 4

As Mchl stated, the empty title is due to a lack of return in the else branch.

However, there is a problem with the function as it does not do what you intend. Currently, your function is building $title as 'Title-1-2-3-4-etc' the way you currently append the number to the title and check again. Instead of passing a modified title on the recursed call you should just pass the base title. Then, for the query, modify the title.

function check_title($title, $i = 0) {
    $qtitle = $title . ($i == 0 ? '' : "-$i");
    $q=mysql_query("SELECT Title FROM posts WHERE Title = '$qtitle'");
    $num=mysql_num_rows($q);
    if($num==0) {
        return $title . ($i == 0 ? '' : "-$i");
    }else {
        return check_title(++$i,$title);
    }
}

PS, I also changed the order of parameters that way your initial call doesn't need to specify 0.

$title = check_title($title);

PPS, I should mention this is a solution to do it via recursion. However, a recursive solution is not the proper solution here as it needlessly makes return trips to the DB. Instead, you should use an sql query that selects all titles LIKE "$title%" Order by title asc. Then, iterate through each result and do a regex comparison with the title to see if it matches a pattern <title>|<title>-<#>. If it does you increment a duplicate counter. At the end you spit out the title with an appended counter value. I'll leave that solution as an exercise for the original poster.

Answer 5

1. You lack a return in else branch.
2. Recursion is not the best idea for this application. Hint: do a query like this SELECT MAX(Title) FROM posts WHERE Title LIKE '$title%');