CODEWITHSUNDEEP
phpmysql
Kiel Lamar
Kiel Lamar

Reputation: 13

MySQL limit characters in multi-select query

Sorry if there is a simple answer but I'm fairly new to MySQL and can't figure out how to explain this one:

I have a number of queries that select random words and concatenate them together. I'm trying to limit the query to only return results if they are under a specific character limit. So, for example, here's one query: 

SELECT CONCAT((SELECT Word FROM Adjectives ORDER BY Rand() LIMIT 1),' ',
              (SELECT Word2 FROM Verbs ORDER BY Rand() LIMIT 1),' ',
              (SELECT Word FROM Nouns ORDER BY Rand() LIMIT 1))

It returns a result like "Adjective Verb Noun"

What I want to do is have the query check if the full concatenated result is 30 characters or less; if it is, return it, and if not, try it again until one with 30 or fewer characters is presented. Thanks!

UPDATE: This is for a fantasy football team name generator, that pulls "random" words from various tables and strings them together. So there are literally millions of possible combinations.... if the user specifies a character limit, I want SQL to re-try in the background until it finds and answer that fits, then present it. I don't want the user getting "please try again" kind of messages.

Upvotes: 1

Views: 806

Answers (3)

Sarath Chandra
Sarath Chandra

Reputation: 1888

Another approach would be to let SQL handle the decision making and finally spit out a string you want. I used an SQL procedure here. 

DELIMITER $$
CREATE OR REPLACE PROCEDURE `FETCH_STRING`(IN LEN INT)
    BEGIN

        declare TEMP varchar(100);
        declare isValid boolean;
        declare maxLength int;
        declare minLength int;

        set maxLength = LEN;
        set isValid = false;

        set minLength = (SELECT MIN(LENGTH(n.word))+MIN(LENGTH(a.word))+MIN(LENGTH(v.word))+2 FROM NOUNS n, ADJECTIVES a, VERBS v);
        #That +2 in the end is compensating for two spaces in between
        if maxLength < minLength THEN
            set temp = "";
            set isValid = true;
        END IF; 

        WHILE isValid = false DO
            set temp = (SELECT CONCAT(
                    (SELECT Word FROM Adjectives ORDER BY Rand() LIMIT 1) ,' ',
                    (SELECT Word FROM Verbs ORDER BY Rand() LIMIT 1),' ',
                    (SELECT Word FROM Nouns ORDER BY Rand() LIMIT 1)));
            IF LENGTH(temp) <= maxLength THEN
                set isValid = true;
            END IF;
        END WHILE;            
    select temp;
    END$$
DELIMITER ;

This should do it. You can call it and always get a string matching your requirement. No need to process it again.

Is this what you were looking for ?

EDIT: Added input field for length desired. And added condition to check if the given length is greater than the smallest possible concat and if so, return an empty string. This is to avoid infinite loop in the procedure.

Upvotes: 1

nomistic
nomistic

Reputation: 2962

You could create an alias of your concatenated variables, such as words in your query, and then use php to check it

$query = "SELECT CONCAT((SELECT Word FROM Adjectives ORDER BY Rand() LIMIT 1),' ',
              (SELECT Word2 FROM Verbs ORDER BY Rand() LIMIT 1),' ',
              (SELECT Word FROM Nouns ORDER BY Rand() LIMIT 1)) as words"

$row = $query->fetch_assoc();
$words = $row['words'];

and then you could use something like strlen() to check the length

if (strlen($words)) > 30 {
 echo 'too many words';

and then spit out the form again. you could embed it in here, this way you'd get the error, and then the form again, OR you could just have a display indicator like this:

     $display_form = 1;
}

else {
   echo $words;
  $display_form= 0;
}

===

Edit - alternate answer to new clarification

You can just do something like this:

SELECT words from
(SELECT CONCAT((SELECT Word FROM Adjectives ORDER BY Rand() limit 1 ),' ',
              (SELECT Word2 FROM Verbs ORDER BY Rand() limit 1),' ',
              (SELECT Word FROM Nouns ORDER BY Rand() limit)) as words) as temptable
where length(words) < 30

Upvotes: 0

EQrxJx
EQrxJx

Reputation: 135

You may use strlen() to know how many characters contain the string:

$query_result = "MYSQL QUERY";

if(strlen($query_result) <= 30){
    echo $query_result;
}

Upvotes: 0

Related Questions