Problem with SQL query, please help!

Codewalkers Forums Sponsor:

AT&T devCentral & BlackBerry(r) Webcast Series: BlackBerry and GPS -Build Location Awareness into your BlackBerry Applications, July 10th-1:00PM EST. Register Today!

October 15th, 2003, 12:07 PM

beley

Join Date: Apr 2007

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Problem with SQL query, please help!

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'

October 15th, 2003, 04:04 PM

CodeKadiya

Join Date: Apr 2007

Location: Colombo,Sri Lanka

Posts: 2,313

Time spent in forums: < 1 sec
Reputation Power: 4

RE: Problem with SQL query, please help!

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'

October 15th, 2003, 04:14 PM

beley

Join Date: Apr 2007

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

RE: Problem with SQL query, please help!

THanks for the suggestion but that's what I tried first, then tried to separate it out into parentheses to see if that would help. Any other suggestions? Below is the entire page:

php Code:

Original - 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

//Colors to Alternate Rows

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

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

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

<?php

// If the Search form has already been submitted

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

//Print the table header
	echo("
	<div align="center">
	<p><h1> Search Results </h1></p>
	<p>
	<table width=90% cellpadding=5 cellspacing=1 bgcolor=cccccc border=0>
	<tr>
    <td bgcolor=f5f5f5>TestNum</td>
    <td bgcolor=f5f5f5>Title</td>
    <td bgcolor=f5f5f5>Author</td>
    <td bgcolor=f5f5f5>ReadingLevel</td>
    <td bgcolor=f5f5f5>PointValue</td>");

//MySQL SELECT Query to sort out form data from search

$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>');
	 }

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++;
		}

}

// If the Form was not submitted, display all the books below

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>

<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=text size=4 name="ReadingLow"> and <input type=text size=4 name="ReadingHigh">
	<br />Search By Point Value: Between <input type=text size=4 name="PointLow"> and <input type=text size=4 name="PointHigh">
	<br /><input type="submit" value="Submit" name="Submit"></form>

</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>');
		}

}

/* 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>

<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
 
    //Colors to Alternate Rows
 
    $color1 = "#F5F5F5";
    $color2 = "#FFFFFF";
    $row_count = 0;
 
 
    // Connect to the database server
    $dbcnx = @mysql_connect('localhost', 'xxxxx', 'xxxxx');
    if (!$dbcnx) {
        die( '<p>Unable to connect to the ' .
            'database server at this time.</p>' );
 }
 
    // Select the database
    if (! @mysql_select_db('xxxxx') ) {
        die( '<p>Unable to locate the ' .
            'database at this time.</p>' );
 }
 
?>
 
<?php
 
// If the Search form has already been submitted
 
if(isset($_POST['Submit'])) {
 
    //Print the table header
    echo("
    <div align="center">
    <p><h1> Search Results </h1></p>
    <p>
    <table width=90% cellpadding=5 cellspacing=1 bgcolor=cccccc border=0>
    <tr>
    <td bgcolor=f5f5f5>TestNum</td>
    <td bgcolor=f5f5f5>Title</td>
    <td bgcolor=f5f5f5>Author</td>
    <td bgcolor=f5f5f5>ReadingLevel</td>
    <td bgcolor=f5f5f5>PointValue</td>");
 
 
    //MySQL SELECT Query to sort out form data from search
 
    $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>');
     }
 
 
    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++;
        }
 
 
}
 
// If the Form was not submitted, display all the books below
 
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>
    <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=text size=4 name="ReadingLow"> and <input type=text size=4 name="ReadingHigh">
    <br />Search By Point Value: Between <input type=text size=4 name="PointLow"> and <input type=text size=4 name="PointHigh">
    <br /><input type="submit" value="Submit" name="Submit"></form>
    </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>');
        }
 
    }
 
 
        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>

October 15th, 2003, 04:27 PM

CodeKadiya

Join Date: Apr 2007

Location: Colombo,Sri Lanka

Posts: 2,313

Time spent in forums: < 1 sec
Reputation Power: 4

RE: Problem with SQL query, please help!

Change your sql statement like this

php Code:

Original - php Code
$result = mysql_query("SELECT * FROM booklist WHERE Author LIKE '%$Author%' AND Title LIKE '%$Title%' AND '$ReadingLow'<=ReadingLevel AND ReadingLevel<='$ReadingHigh' AND '$PointLow'<=PointValue AND PointValue<='$PointHigh' ORDER BY Title,Author");

 
$result = mysql_query("SELECT * FROM booklist WHERE
    Author LIKE '%$Author%' AND
    Title LIKE '%$Title%' AND
    '$ReadingLow'<=ReadingLevel AND ReadingLevel<='$ReadingHigh' AND
    '$PointLow'<=PointValue AND PointValue<='$PointHigh'
    ORDER BY Title,Author");