SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    testimonial characters more than

    hi

    there are more than 200 testimonials in database table.

    i want to show only those testimonials whose character count is more than 50 characters.

    But at a time only one testimonial should be displayed.

    PHP Code:
    <?php
        $qry
    ="select * from testimonials_table order by rand()";
        
    $result=mysql_query($qry);
        
    $row=mysql_fetch_array($result);
    ?>
    how can it be done ?

    vineet

  2. #2
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I tried it with word count

    But it shows white blank space when the words count is less

    PHP Code:
    <?
    $qry
    ="select * from testimonials order by rand() LIMIT 0,1";
    $result=mysql_query($qry);
    while(
    $row=mysql_fetch_array($result))
    {
    $testm $row['testimonial'];

        if(
    str_word_count($testm) > 7)
        {
        
    /* display testimionials here*/
        
    }
    }
    ?>
    vineet

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,827
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    You will want to use the MySQL LEN() function in a WHERE clause.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  4. #4
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    You will want to use the MySQL LEN() function in a WHERE clause.
    Won't this query result in the entire database having to be scanned each time?

    I'd do this by storing a count column in the database table (so the column is an int that holds the number of characters in the article), and then query according to that.

  5. #5
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    You will want to use the MySQL LEN() function in a WHERE clause.
    thanks cpradio

    it works for me

    vineet

  6. #6
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinpkl View Post
    thanks cpradio

    it works for me

    vineet
    You know this will now have to scan every single row in your database table?

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,827
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aaarrrggh View Post
    Won't this query result in the entire database having to be scanned each time?

    I'd do this by storing a count column in the database table (so the column is an int that holds the number of characters in the article), and then query according to that.
    Agreed, that would be the optimum way of handling it so a table scan doesn't occur. Or removing testimonials under 50 characters and removing the WHERE clause.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  8. #8
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if i create a count column in the table then my query will become

    Code:
    where count > 7
    am i correct ?

    If yes then how will it work faster then MySQL LEN() function.

    whats difference in both. I would like to know your views on this.

    vineet

  9. #9
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinpkl View Post
    if i create a count column in the table then my query will become

    Code:
    where count > 7
    am i correct ?

    If yes then how will it work faster then MySQL LEN() function.

    whats difference in both. I would like to know your views on this.

    vineet
    Hi Vineet,

    This has to do with how mysql will treat your query. See, when you do the LEN() query, it will work the way you want (in that it produces the expected results), but you see, mysql has no way of knowing how many characters are in each testimonial without first checking each row in the database to see how many characters are there. This means for every single row, it will read the entire thing and do a count on the number of characters, so if you had 1000 testimonials, each time you do this query, it will have to scan all 1000 rows.

    If instead of doing this, you cache the results of the number of characters and store this in a new column, mysql can tell by querying this number, which rows to return, without having to scan all of them. So say you have 1000 rows and say 50 of them should be turned, mysql will work out instantly which rows they are by querying this number, which has already done the work of the LEN() method in the original query. So in this instance, you would have 1000 rows, but mysql won't have to scan them all and will simply return 50 without going through every row.

    In order to do this, you will need to insert the cached number in the new column (call it something like number_of_characters or something like that) each time you insert a testimonial in the database. For the existing records you have, you will have to write a one-off query to go through each row, do a count and insert the value, but this will only need to be done once.

    Does that make a bit more sense?

  10. #10
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You mean to say

    query with MYSQL LEN() Function is doing 2 jobs >> counting + comparing

    But on the otherside if a "number_of_characters" column is added to table then

    query column_count > 7 will do only 1 job of comparing, thats why it will be fast.

    vineet

  11. #11
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinpkl View Post
    You mean to say

    query with MYSQL LEN() Function is doing 2 jobs >> counting + comparing

    But on the otherside if a "number_of_characters" column is added to table then

    query column_count > 7 will do only 1 job of comparing, thats why it will be fast.

    vineet
    Yeah, that's it...

    Basically the job of doing the count is already done with query count column, so mysql can use that data to do the comparison, whereas the LEN() method requires both, meaning each row has to be scanned every time the query runs.

  12. #12
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok one more question

    if the character_count_column already exists in the table from the starting.

    then both the queries
    Code:
    where mysql len() > 7
    or
    Code:
    where character_count_column > 7
    will work equally fast because both are doing only 1 job ?

    am i correct ?

    vineet

  13. #13
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    No, if you do the len() method in the query, it will do the full table scan again. If you just use the second query (WHERE character_count_column > 7), then you solve the problem.

  14. #14
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok thanks

    vineet

  15. #15
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi

    where do you check that it is doing full table scan again and again ?

    does mysql show any performance stats like this ?

    vineet

  16. #16
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,827
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinpkl View Post
    You mean to say

    query with MYSQL LEN() Function is doing 2 jobs >> counting + comparing

    But on the otherside if a "number_of_characters" column is added to table then

    query column_count > 7 will do only 1 job of comparing, thats why it will be fast.

    vineet
    Basically, you are correct, but just thought I would explain a table scan a bit more.

    Whenever you use a function around a column name in your WHERE clause, GROUP BY, or HAVING statements you will invoke a table scan. Depending on the rest of your WHERE clause, GROUP BY, or HAVING statements it may have to look at EVERY row in your table or it may have been able to filter it a bit further before scanning the rest of the rows.

    Since this would be the ONLY condition to your WHERE clause it would have to scan every row in your table, count the number of characters and then use that in the condition. The additional column will allow your table to use a more optimized routine for getting the data you want. As the count is no longer needing to be calculated, that part was already done. So now you can technically index the count column and the database will perform an INDEX SEEK instead of a TABLE SCAN which is much much faster.

    Granted with 200 records you won't see a huge performance loss going either way. Once you start approaching more records, then it will become more apparent.

    I hope that helps.
    Good suggestion @aaarrrggh ;
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  17. #17
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinpkl View Post
    hi

    where do you check that it is doing full table scan again and again ?

    does mysql show any performance stats like this ?

    vineet
    Check out the mysql EXPLAIN statement:

    http://weevilgenius.net/2010/09/mysq...ain-reference/


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •