SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems with LIMIT (was please help/....)

    why is it my limit is not working here?

    PHP Code:
    $descriptionQuery="SELECT * 
                FROM news 
                WHERE ( 
           subject like '%"
    .$keyword[$i]."%' 
        or content like '%"
    .$keyword[$i]."%' 
           )  "

    and

    PHP Code:
    $descriptionQueryli="SELECT * 
                FROM news 
                WHERE ( 
           subject like '%"
    .$keyword[$i]."%' 
        or content like '%"
    .$keyword[$i]."%' 
           )  limit 
    $s,$limit"
    they both return the same number of result....

    please take note that they above queries are inside a for loop.

    the full code is :

    PHP Code:
    $limit 10;
    for (
    $i 0$i <= $noskeyword$i++) { 


    $descriptionQuery="SELECT * 
                FROM news
                WHERE (
           subject like '%"
    .$keyword[$i]."%'
        or content like '%"
    .$keyword[$i]."%'
           )  "



    $descriptionQueryli="SELECT * 
                FROM news
                WHERE (
           subject like '%"
    .$keyword[$i]."%'
        or content like '%"
    .$keyword[$i]."%'
           ) limit 
    $s,$limit"


        
    $descriptionResult mysql_query($descriptionQuery) or die(mysql_error()); 
        
    $descriptionResultli mysql_query($descriptionQueryli) or die(mysql_error());


        while (
    $rowDescription mysql_fetch_array($descriptionResult,2)) { 
            
    $resultOut[] = $rowDescription
       } 
           while (
    $rowDescriptionli mysql_fetch_array($descriptionResultli,2)) { 
            
    $resultOutli[] = $rowDescriptionli
       } 


    $z count($resultOut);   // OUTPUT 19
    $x count($resultOutli); // OUTPUT 19

      
    $num_results_nolim mysql_num_rows($descriptionResult); // OUTPUT 10
        
    $num_resultslim mysql_num_rows($descriptionResultli); // OUTPUT 10 
    why is count() giving me 19 and mysql_num_rows giving me 10?

  2. #2
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thread edited. Please use descriptive titles

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please run your queries outside of php and report the totals again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don know how to run the queries in phpmyadmin because the keywords is more than one.

    i don know how to write a for loop in sql.

    ..........


    i am using more than 1 keyword...

  5. #5
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks seanf for the mod to the title.


  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please give a few sample rows of data (abbreviate if necessary, no need to paste an entire news article)

    and please show how you want to use more than one keyword -- should they all be found, or any of them (i.e. ANDs or ORs)?

    in other words, show how multiple keywords should find the example articles that you show
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok...


    the page we are dealing with index.php

    user would login, then register a session.

    so index.php would have a query to the database to request for the keywords that the user have.

    let's say : machine and computer.
    the query for that is as follow
    PHP Code:
    $keywordsQuery "SELECT keyword 
                      FROM keyword 
                      WHERE username = '
    $valid_user'"

    // create a resultset by executing keywords query 
    $keywordResult mysql_query($keywordsQuery) or die(mysql_error()); 
    while (
    $rowKeywords mysql_fetch_array($keywordResult,1)) { 
        
    $keyword[] = $rowKeywords['keyword']; 


    then after that, the keywords are in $keyword[]

    the next query is to actually request the news containing the keywords.

    PHP Code:
    $limit 10
    for (
    $i 0$i <= $noskeyword$i++) { 


    $descriptionQuery="SELECT * 
                FROM news 
                WHERE ( 
           subject like '%"
    .$keyword[$i]."%' 
        or content like '%"
    .$keyword[$i]."%' 
           )  "



    $descriptionQueryli="SELECT * 
                FROM news 
                WHERE ( 
           subject like '%"
    .$keyword[$i]."%' 
        or content like '%"
    .$keyword[$i]."%' 
           ) limit 
    $s,$limit"


        
    $descriptionResult mysql_query($descriptionQuery) or die(mysql_error()); 
        
    $descriptionResultli mysql_query($descriptionQueryli) or die(mysql_error()); 


        while (
    $rowDescription mysql_fetch_array($descriptionResult,2)) { 
            
    $resultOut[] = $rowDescription
       } 
           while (
    $rowDescriptionli mysql_fetch_array($descriptionResultli,2)) { 
            
    $resultOutli[] = $rowDescriptionli
       } 


    $z count($resultOut);   // OUTPUT 19 
    $x count($resultOutli); // OUTPUT 19 

      
    $num_results_nolim mysql_num_rows($descriptionResult); // OUTPUT 10 
        
    $num_resultslim mysql_num_rows($descriptionResultli); // OUTPUT 10 

    the query would search the news database for those whose subject OR content containing the keywords....

    apologies if this is more of php ques?


    thanks.......

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how about showing your rows of data and your how your tables are related. you are doing multiple sql queries and you should most likely be doing a join and dropping the loop altogether.

  9. #9
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok.

    rows of data.

    id | username | subject | type | content | date |
    1 | defiance | Water found on mars | science | Water found on mars | 27-2-2005

    yea...
    that is one of the example.............

    join?

    aha..................

    yea.......
    you are right........

    but..
    i am a newb.
    i will look into doing join.

    right now, i really need to get this stuff done first........

    pleaase..........

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, a join, like this --
    Code:
    select news.* 
      from keywords
    inner
      join news
        on ( 
           subject like concat('%', keyword, '%') 
        or content like concat('%', keyword, '%')
           )  
     where username = '$valid_user'
    any time you find yourself doing a query in a loop, it is definitely inefficient and can almost always be revised to do a single query with a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    thanks..

    but things doesnt seem to be working.

    FYI, my keywords are stored in a different database for some reason.

    so,

    i have 3 databases; keywords, field=username, keyword
    member = details about member
    news = news

    please...............

  12. #12
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don quite get you.

    i mean,

    how else can i be doing this query;
    i ve got an array of my keyword. i don see any other way on how to do that without a loop.

    may be we are not understanding each other correctly here.

    i am trying to achieve this;

    1. select news which has keyword, machine in the news
    2. select news which has keyword , computer in the news

    and the keywords for each user would vary. more than 2 is expected.

    please kindly straightened my thinking here if i am wrong.

    Thank you very much for your help.


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
  •