If function in LIMIT?

[TOA]

Is there a way to use the mysql if function to determine limit? I've tried a few things to no avail.

 

What I'm trying to do:

determine the char_length of the field selected and if it's less than 90 characters, get another, otherwise just get the one.

 

Not sure if this is possible or not (probably is) but I can't get it right.

 

Hope I explained that clearly 

 

Any ideas?

[TOA]

Ok, here's the closest I've been able to come up with and this was just since my OP.

 

This sql statement:

 

SELECT Testimonial_Name, CASE WHEN CHAR_LENGTH(Testimonial_Text) < 90 THEN CONCAT(SUBSTRING(Testimonial_Text, 1, 87), '...') ELSE Testimonial_Text END AS Testimonial_Text FROM Testimonials WHERE Approved=1 ORDER BY RAND() LIMIT 1

 

will get one testimonial and if it's longer than 90 chars it appends '...'. So now, I think I just need to figure out the right way to embed another select in there and concat them together as one... am I completely off base or in the park? Any better ideas?

 

Will keep chugging...

 

Thanks for looking

[TOA]

*UPDATE*

 

Got it to work, but realized its not the structure I need.

 

The sql:

SELECT Testimonial_Name, CASE WHEN CHAR_LENGTH(Testimonial_Text) < 90 THEN CONCAT(Testimonial_Text, (SELECT Testimonial_Text FROM Testimonials WHERE Approved=1 ORDER BY RAND() LIMIT 1)) ELSE Testimonial_Text END AS Testimonial_Text FROM Testimonials WHERE Approved=1 ORDER BY RAND() LIMIT 1

 

The php:

echo "$row[Testimonial_Text]-$row[Testimonial_Name]<br />";

 

The result:

 

TEXTTEXT - 1st Testimonials name

 

So its doing what I wrote, but what I wrote is wrong. I need to get one text and name, concat, determine length and condition to execute. If short enough, get another name and text, concat and return both as limit 1.

 

Right?  Sounds good in theory

 

Love to hear any suggestions

[fenway]

That means you need to get both first.

[TOA]

That means you need to get both first.

 

Yep.

 

I'm 3/4 of the way there. Quite the beast...

 

This sql:

SELECT CASE WHEN CHAR_LENGTH(CONCAT(Testimonial_Text, ' - ', Testimonial_Name)) > 90 THEN CONCAT(Testimonial_Text, ' - ', Testimonial_Name) ELSE Testimonial_Text END AS Testimonial FROM Testimonials WHERE Approved=1 ORDER BY RAND() LIMIT 1

 

Will do the right thing so far: it gets the name and text, checks for length and for testing, if > 90 it includes the name, else it doesn't so I can recognize which part of the condition is executing.

 

Working on the < 90 part now.

 

Thanks for reading

[fenway]

That's just hard to read... so elt me know if you need more help.

[TOA]

That's just hard to read... so elt me know if you need more help.

 

Yes it is.

 

Thanks I will

[TOA]

SELECT CASE 
WHEN CHAR_LENGTH(CONCAT(Testimonial_Text, ' - ', Testimonial_Name)) > 90 
THEN CONCAT(Testimonial_Text, ' - ', Testimonial_Name) 
ELSE CONCAT(Testimonial_Text, ' - ', Testimonial_Name, '<br />', (SELECT CONCAT(Testimonial_Text, ' - ', Testimonial_Name) FROM Testimonials WHERE CHAR_LENGTH(Testimonial_Text) < 90 ORDER BY RAND() LIMIT 1)) 
END
AS Testimonial 
FROM Testimonials 
WHERE Approved=1 
ORDER BY RAND() 
LIMIT 1

 

This does it. If less than 90 chars (or any defined number) it will go back and get one that is also less than 90. If more than 90 chars, it just outputs.

 

The only things I've encountered are:

1. Need to replace RAND()

2. It might grab the same field, so I need to make sure that doesn't happen.

 

Anybody know of a better way, or see any flagrant problems?

 

This may be quite far from the title, but it's what I was trying to accomplish. I have yet to find a way to use an if in the LIMIT, so if anyone knows of one, let me know

 

Thanks

 

[fenway]

You could have used user variables to make that simpler to read...

[TOA]

You could have used user variables to make that simpler to read...

 

I don't follow... 

[fenway]

Just so that the same expression doesn't show up multiple times, is all.

[TOA]

Just so that the same expression doesn't show up multiple times, is all.

 

Ahhhh ... I get you. Good point.

 

On that, my intention here was to do it in one query, instead of two. Would setting a user variable prior to running this affect performance and defeat the purpose of sending one query? I tend to doubt it...

 

Or would using a (PHP) constant instead be to the same effect?

 

Thanks for the help

[fenway]

I mean inline, in the same query.

[TOA]

I mean inline, in the same query.

 

Looking into it...

 

Thanks

[TOA]

Like this? (Not sure this is what you meant or not)

 

SET @a = CONCAT(Testimonial_Text, ' - ', Testimonial_Name);
SELECT CASE 
WHEN CHAR_LENGTH(@a) > 95 
THEN @a 
ELSE CONCAT(@a, '<br /><br />', (SELECT @a FROM Testimonials WHERE CHAR_LENGTH(Testimonial_Text) < 90 ORDER BY RAND() LIMIT 1)) 
END
AS Testimonial 
FROM Testimonials 
WHERE Approved=1 
ORDER BY RAND() 
LIMIT 1

 

Strange thing (at least to me) it works in my script, but when I went into phpmyadmin to test the speed, it won't run it. Any ideas?

Nevermind, figured that part out

[TOA]

Actually, that's not working..going to keep trying

[TOA]

Ok, I'm trying to get up to speed with this. I see no reason the previous statement isn't working, but I havn't given up yet. I have a parallel question:

 

I found this here (right after they talk about using CAST(), about 1/4 of the way down):

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.

Does this contradict you or am I taking it out of context?

 

So here's the question resulting from finding that:

Would setting the variable first in it's own query affect performance if the table gets really large and has alot of users?

[fenway]

Something similar... but you can make that variable in the WHERE clause, and thus it's a single statement.

[TOA]

Something similar... but you can make that variable in the WHERE clause, and thus it's a single statement.

 

I'm not sure I get it. Can you elaborate?

 

Thanks

[fenway]

If you set your variable with ( SELECT @yourVar := 'yourValue' ) in a derived table, it's easier.

[TOA]

If you set your variable with ( SELECT @yourVar := 'yourValue' ) in a derived table, it's easier.

 

Don't know much about derived tables (anything really).

 

Guess I have another thing to look into. Turned into quite a learning task 

[TOA]

So far, I'm not getting anywhere.

 

If you set your variable with ( SELECT @yourVar := 'yourValue' ) in a derived table, it's easier.

 

Is that all I would have to do is just move that from the beginning to the WHERE clause?

 

I read up on it and I think I get the use, but I can't get the sql right

 

Thanks

[fenway]

If you'll post your CREATE TABLE statments, and some sample data as INSERTs, then I can give you a working query.

[TOA]

If you'll post your CREATE TABLE statments, and some sample data as INSERTs, then I can give you a working query.

 

Thanks, I really appreciate it. I just can't seem to get this right

 

CREATE TABLE IF NOT EXISTS `Testimonials` (
  `Testimonial_ID` int(11) NOT NULL auto_increment,
  `Testimonial_Name` text collate utf8_unicode_ci,
  `Testimonial_Text` text collate utf8_unicode_ci NOT NULL,
  `Approved` int(11) NOT NULL,
  PRIMARY KEY  (`Testimonial_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27 ;

 

INSERT INTO `Testimonials` (`Testimonial_ID`, `Testimonial_Name`, `Testimonial_Text`, `Approved`) VALUES
(1, 'AS', 'This is a great program! Not only have I got my health under control, I finally got my finances back in order after my divorce. The classes and resources were extremely helpful. Thanks!', 1),
(2, 'B.K', 'I''ve dropped 53 pounds since our company took on this program! I can''t believe it! All I needed was the knowledge..', 1),
(7, 'Jill', 'I can not believe there is a place that offers this much help. Fantastic', 1),
(9, 'Anonymous', 'Fantastic!', 1),
(11, 'Anonymous', 'Can''t tell you how much this rocks!', 1),
(12, 'GT', 'This has even helped my husband get control of his work environment!', 1),
(20, 'Andrea', 'Wow fantastic I never thought improving my life would be so easy', 1),
(21, 'Anonymous', 'Superb', 1),
(25, 'Jennifer', 'Awesome program! I''ve got my friends using the tactics I learned also. This is great', 1);

 

[fenway]

Sorry, I didn't forget, I'm just swamped.