SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Problem with SQL Query & BETWEEN

    I have a page that searches a one-table database of books. It worked fine when the query was only for Title or Author name, but when I added the last segment (to search between two sets of two values) the query just returns 0 rows now. If I remove that entire section it will work. I have tried breaking the query into sections with parentheses but still no luck. I'll post the query below...

    Any idea what I'm doing wrong?
    Code:
    SELECT * FROM booklist WHERE 
    ('Author' LIKE '%$Author%') AND 
    ('Title' LIKE '%$Title%') AND 
    ('ReadingLevel' BETWEEN '$ReadingLow' AND '$ReadingHigh') AND 
    ('PointValue' BETWEEN '$PointLow' AND '$PointHigh') ORDER BY 'Title','Author'

  2. #2
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Below is the entire file:
    PHP Code:
    <html>
    <head>
    <title> AR Book List </title>
    <STYLE TYPE="text/css">
      BODY { font-size: 12pt; font-family: Arial, Helvetica; }
      TD { font-size: 12pt; font-family: Arial, Helvetica; }
    </STYLE>
    </head>
    <body>

    <?php

     
    // Connect to the database server
     
    $dbcnx = @mysql_connect('localhost''xxxxxxx''xxxxxxxxx');
     if (!
    $dbcnx) {
       die( 
    '<p>Unable to connect to the ' .
            
    'database server at this time.</p>' );
     }

     
    // Select the database
     
    if (! @mysql_select_db('xxxxxxxxxx') ) {
       die( 
    '<p>Unable to locate the ' .
            
    'database at this time.</p>' );
     }

    ?>

    <?php

    if(isset($_POST['Submit'])) {


    echo(
    "
    <div align=\"center\">
    <p><h1> Search Results </h1></p>
    <p>
    <table width=90% cellpadding=5 cellspacing=1 bgcolor=cccccc border=0>
    <tr>"
    );

            echo(
    "<td bgcolor=f5f5f5>TestNum</td>");
            echo(
    "<td bgcolor=f5f5f5>Title</td>");
            echo(
    "<td bgcolor=f5f5f5>Author</td>");
            echo(
    "<td bgcolor=f5f5f5>ReadingLevel</td>");
            echo(
    "<td bgcolor=f5f5f5>PointValue</td>");


                  
    // Default - sort books by test number



        
    $result mysql_query("SELECT * FROM booklist WHERE
        ('Author' LIKE '%
    $Author%') AND
        ('Title' LIKE '%
    $Title%') AND
        ('ReadingLevel' BETWEEN '
    $ReadingLow' AND '$ReadingHigh') AND
        ('PointValue' BETWEEN '
    $PointLow' AND '$PointHigh')
        ORDER BY 'Title','Author'"
    );
         if (!
    $result) {
           die(
    '<p>Error performing query: ' mysql_error() .
               
    '</p>');
         }

         
    $color1 "#F5F5F5";
        
    $color2 "#FFFFFF";
        
    $row_count 0;


    while (
    $row mysql_fetch_array($result)) {
        
    $testnum $row["TestNum"];
        
    $title $row["Title"];
        
    $author $row["Author"];
        
    $readinglevel $row["ReadingLevel"];
        
    $pointvalue $row["PointValue"];

        
    /* Now we do this small line which is basically going to tell
        PHP to alternate the colors between the two colors we defined above. */

        
    $row_color = ($row_count 2) ? $color1 $color2;

        
    // Echo your table row and table data that you want to be looped over and over here.

        
    echo "<tr>
        <tr bgcolor=\"
    $row_color\">
        <td>
    $testnum</td>
        <td>
    $title</td>
        <td>
    $author</td>
        <td>
    $readinglevel</td>
        <td>
    $pointvalue</td>
        </tr>"
    ;

        
    // Add 1 to the row count

        
    $row_count++;
        }


    }

    else {

    echo(
    "
    <div align=\"center\">
    <table border=0 cellspacing=3 cellpadding=3>
    <tr valign=middle><td>
    <p><h1> AR Book List Database </h1></p>
    </td><td align=right>
    <p><b> Search the Book List Database</b></p>
    <p><form action=" 
    $_SERVER['PHP_SELF'] . " method=\"post\">
    Search By Author: <input type=textarea name=\"Author\">
    <br />Search By Title: <input type=textarea name=\"Title\">
    <br />Search By Reading Level: Between <input type=textarea size=4 name=\"ReadingLow\"> and <input type=textarea size=4 name=\"ReadingHigh\">
    <br />Search By Point Value: Between <input type=textarea size=4 name=\"PointLow\"> and <input type=textarea size=4 name=\"PointHigh\">
    <br /><BUTTON name=\"Submit\" value=\"Submit\" type=\"submit\">Submit</BUTTON></form>
    <p>
    </td></tr>
    </table>
    <table width=90% cellpadding=5 cellspacing=1 bgcolor=cccccc border=0>
    <tr>"
    );

            echo(
    "<td bgcolor=f5f5f5><a href=\"" $_SERVER['PHP_SELF'] . "\">TestNum</td>");
            echo(
    "<td bgcolor=f5f5f5><a href=\"" $_SERVER['PHP_SELF'] . "?orderby=title\">" "Title</a></td>");
            echo(
    "<td bgcolor=f5f5f5><a href=\"" $_SERVER['PHP_SELF'] . "?orderby=author\">" "Author</a></td>");
            echo(
    "<td bgcolor=f5f5f5>ReadingLevel</td>");
            echo(
    "<td bgcolor=f5f5f5>PointValue</td>");

    if (
    $orderby == title) {

             
    // Sort books by title
         
    $result = @mysql_query('SELECT * FROM booklist ORDER BY Title');
         if (!
    $result) {
           die(
    '<p>Error performing query: ' mysql_error() .
               
    '</p>');
         }

    } elseif (
    $orderby == author) {

             
    // Sort books by author, then by title
         
    $result = @mysql_query('SELECT * FROM booklist ORDER BY Author,Title');
         if (!
    $result) {
           die(
    '<p>Error performing query: ' mysql_error() .
               
    '</p>');
         }

    } else {

             
    // Default - sort books by test number
         
    $result = @mysql_query('SELECT * FROM booklist ORDER BY TestNum');
         if (!
    $result) {
           die(
    '<p>Error performing query: ' mysql_error() .
               
    '</p>');
         }

    }

    $color1 "#F5F5F5";
    $color2 "#FFFFFF";
    $row_count 0;


    while (
    $row mysql_fetch_array($result)) {
        
    $testnum $row["TestNum"];
        
    $title $row["Title"];
        
    $author $row["Author"];
        
    $readinglevel $row["ReadingLevel"];
        
    $pointvalue $row["PointValue"];

        
    /* Now we do this small line which is basically going to tell
        PHP to alternate the colors between the two colors we defined above. */

        
    $row_color = ($row_count 2) ? $color1 $color2;

        
    // Echo your table row and table data that you want to be looped over and over here.

        
    echo "<tr>
        <tr bgcolor=\"
    $row_color\">
        <td>
    $testnum</td>
        <td>
    $title</td>
        <td>
    $author</td>
        <td>
    $readinglevel</td>
        <td>
    $pointvalue</td>
        </tr>"
    ;

        
    // Add 1 to the row count

        
    $row_count++;
        }

    }

    ?>

    </table>
    </div>
    </body>
    </html>

  3. #3
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I moved it into PHP as I think it may be a problem with my PHP script somewhere and not my query. Any help would be appreciated...

    You can see the actual script work at http://www.troup.k12.ga.us/booklist.php

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I've just tried it where I filled all the fields in and got a result.

    I think because you are using AND, the sql statement wants an author AND title AND etc ... if you want to search using just one of the fields try OR

  5. #5
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Wow, I didn't even notice that. I wonder if I use 'OR' will it work for multiple items?

    Any input on how I can get it to work for 1 or all fields?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the way i read your query, it isn't going to find any row ever

    if your column names are in quotes, not backticks, then they are literals, i.e. character strings, and not column names

    so the string 'Author' will never be like '%$Author%' unless $Author is a substring of the string 'Author'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try replacing your ANDs with ORs. What should happen is that it will search, for example, a in title OR x in author OR etc...
    at the moment if you enter these values (and 1 and 10 for the numbers) you will produce all records where there is an a in the title and where there is a x in the author (around 50 records), if you do the query with ORs hopefully, I havent checked this fully, any blank fields will be ignored and you should be able to retrieve the just records that you want.
    Try it and let me know what happens

  8. #8
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Okay, here is what I did... I left it at "AND" and just added a default value of 0 and 99 for the search criterion and it works great!

    BTW, works great with the single quotes r937 because of the %


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
  •