[SOLVED] MySQL syntax error in query: select price above and below

pescott · December 25, 2007

Hello people, Merry Christmas!

Hope someone can helm me with an issue I have got stuck on:

The following issue is what I am struggling with.

I have a db (MySQL 4.1.15), with a table named objects. There are a number of fields, amongst others 'id', 'city' and 'price'. I want the user to enter from a pulldown menu a city, a lower limit for price and an upper limit, and this is what I came up with:

$query = "SELECT * FROM objects WHERE city = $city AND price>=$price_min AND price<=$price_max ASC LIMIT $offset, $rowsPerPage";

There is also a bit of pagination in there. The upper and lower limit prices are chosen from pull down menu's, but the names ARE NOT names of table fields.

This renders an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND price>= AND price<= ASC LIMIT 0, 15' at line 1

???

Also, the rest of the code that is in the PHP file is not executed anymore, so the parsing actually ends after the SQL error.

For the life of me, I haveno clue what I am doing wrong. Please help!

Your assistance is much appreciated.

Chris

btherl · December 25, 2007

You probably need "WHERE city = '$city'", with quotes.

pescott · December 25, 2007

Thanks for the quick reply!

But the error points to a different location?

I have here the whole code, the table layout is still being worked on...

<?php
//Select the db structure.

include("db_connect.php");

// how many rows to show per page
$rowsPerPage = 15;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page'])) {
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
$id=1;
if (isset($_GET['id']))
   $id = $_GET['id'];
?>
<table id="maintable">
<form name="cb_q_display.php" target="_blank" id="search" method="get" action="">
<tr>
	<td>Woonplaats:</td>
</tr>
<tr>
    	<td><select name="woonplaats">
		<option value="none">- selecteer -</option>
		<option value="alle">Alle</option>
		<option value="Den Haag">Den Haag</option>
		<option value="Leiden">Leiden</option>
		<option value="Leidschendam">Leidschendam</option>
		<option value="Monster">Monster</option>
		<option value="Rijswijk">Rijswijk</option>
		<option value="Voorburg">Voorburg</option>
		<option value="Zoetermeer">Zoetermeer</option>
		<option value="anders">Anders</option>
		</select>
	</td>
    </tr>
<tr>
	<td>Bedrag vanaf:</td>
</tr>
<tr>
    	<td><select name="vraagprijs_min">
		<option value="none">- selecteer -</option>
		<optgroup label="Huur">
		<option value="0">€ 0 (/maand)</option>
		<option value="50">€ 50 (/maand)</option>
		<option value="100">€ 100 (/maand)</option>
		<option value="250">€ 250 (/maand)</option>
		<option value="500">€ 500 (/maand)</option>
		<option value="1000">€ 1.000 (/maand)</option>
		<option value="5000">€ 5.000 (/maand)</option>
		</optgroup>
		<optgroup label="Koop">
		<option value="10000">€ 10.000</option>
		<option value="25000">€ 25.000</option>
		<option value="50000">€ 50.000</option>
		<option value="75000">€ 75.000</option>
		<option value="100000">€ 100.000</option>
		<option value="125000">€ 125.000</option>
		<option value="150000">€ 150.000</option>
		<option value="175000">€ 175.000</option>
		<option value="200000">€ 200.000</option>
		<option value="225000">€ 225.000</option>
		<option value="250000">€ 250.000</option>
		<option value="275000">€ 275.000</option>
		<option value="300000">€ 300.000</option>
		<option value="325000">€ 325.000</option>
		<option value="350000">€ 350.000</option>
		<option value="375000">€ 375.000</option>
		<option value="400000">€ 400.000</option>
		<option value="425000">€ 425.000</option>
		<option value="450000">€ 450.000</option>
		<option value="475000">€ 475.000</option>
		<option value="500000">€ 500.000</option>
		<option value="525000">€ 525.000</option>
		<option value="550000">€ 550.000</option>
		<option value="575000">€ 575.000</option>
		<option value="600000">€ 600.000</option>
		<option value="625000">€ 625.000</option>
		<option value="650000">€ 650.000</option>
		<option value="675000">€ 675.000</option>
		<option value="700000">€ 700.000</option>
		<option value="725000">€ 725.000</option>
		<option value="750000">€ 750.000</option>
		<option value="775000">€ 775.000</option>
		<option value="800000">€ 800.000</option>
		<option value="825000">€ 825.000</option>
		<option value="850000">€ 850.000</option>
		<option value="875000">€ 875.000</option>
		<option value="900000">€ 900.000</option>
		<option value="925000">€ 925.000</option>
		<option value="950000">€ 950.000</option>
		<option value="975000">€ 975.000</option>
		<option value="1000000">€ 1.000.000</option>
		</optgroup>
		</select>
	</td>
    </tr>
<tr>
	<td>Bedrag tot:</td>
</tr>
<tr>
    	<td><select name="vraagprijs_max">
		<option value="none">- selecteer -</option>
		<optgroup label="Huur">
		<option value="0">€ 0 (/maand)</option>
		<option value="50">€ 50 (/maand)</option>
		<option value="100">€ 100 (/maand)</option>
		<option value="250">€ 250 (/maand)</option>
		<option value="500">€ 500 (/maand)</option>
		<option value="1000">€ 1.000 (/maand)</option>
		<option value="5000">€ 5.000 (/maand)</option>
		</optgroup>
		<optgroup label="Koop">
		<option value="10000">€ 10.000</option>
		<option value="25000">€ 25.000</option>
		<option value="50000">€ 50.000</option>
		<option value="75000">€ 75.000</option>
		<option value="100000">€ 100.000</option>
		<option value="125000">€ 125.000</option>
		<option value="150000">€ 150.000</option>
		<option value="175000">€ 175.000</option>
		<option value="200000">€ 200.000</option>
		<option value="225000">€ 225.000</option>
		<option value="250000">€ 250.000</option>
		<option value="275000">€ 275.000</option>
		<option value="300000">€ 300.000</option>
		<option value="325000">€ 325.000</option>
		<option value="350000">€ 350.000</option>
		<option value="375000">€ 375.000</option>
		<option value="400000">€ 400.000</option>
		<option value="425000">€ 425.000</option>
		<option value="450000">€ 450.000</option>
		<option value="475000">€ 475.000</option>
		<option value="500000">€ 500.000</option>
		<option value="525000">€ 525.000</option>
		<option value="550000">€ 550.000</option>
		<option value="575000">€ 575.000</option>
		<option value="600000">€ 600.000</option>
		<option value="625000">€ 625.000</option>
		<option value="650000">€ 650.000</option>
		<option value="675000">€ 675.000</option>
		<option value="700000">€ 700.000</option>
		<option value="725000">€ 725.000</option>
		<option value="750000">€ 750.000</option>
		<option value="775000">€ 775.000</option>
		<option value="800000">€ 800.000</option>
		<option value="825000">€ 825.000</option>
		<option value="850000">€ 850.000</option>
		<option value="875000">€ 875.000</option>
		<option value="900000">€ 900.000</option>
		<option value="925000">€ 925.000</option>
		<option value="950000">€ 950.000</option>
		<option value="975000">€ 975.000</option>
		<option value="1000000">€ 1.000.000</option>
		</optgroup>
		</select>
	</td>
    </tr>
<tr>
	<td><input type="submit" name="submitSearch" value="Zoek" /></td>
</tr>

</form>
</table>

<?php
//‘id’, ‘woonplaats’, ‘vraagprijs’

// how many rows we have in database
$query = "SELECT * FROM objects WHERE woonplaats='$woonplaats' AND vraagprijs>='$vraagprijs_min' AND vraagprijs<='$vraagprijs_max' ASC LIMIT $offset, $rowsPerPage";

// Retrieve all the data from the "objects" table
$result = mysql_query($query) or die(mysql_error());
$recordcount = mysql_num_rows($result);
if ($recordcount==0)
    echo "<center>Helaas, er zijn <strong>GEEN</strong> resultaten die aan deze zoekopdracht voldoen.</center>";
if ($recordcount==1)
    echo "<center>Er is <strong>$recordcount</strong> resultaat.</center>";
if ($recordcount > 1)
    echo "<center>Er zijn <strong>$recordcount</strong> resultaten.</center>";
    
// Define your colors for the alternating rows
$color1 = "#eeeeee";
$color2 = "#dcdcdc";
$row_count = 0;

// We are going to use the "$row" method for this query. This is just my preference.

// Haal de resultaten uit de database
while($row = mysql_fetch_array($result)) {
    $id = $row["id"];
    $achternaam = $row["achternaam"];

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

// Begin your table outside of the array
echo ("<table id=\"adrestable\">
<tr>
        <td width=\"105\"> </td>
        <td width=\"400\"> </td>
    </tr>
<tr>
	<td bgcolor=\"$row_color\" nowrap valign=\"middle\" align=\"center\"><img src=\"".$row["afbeelding"]."\" width=\"90\" height=\"120\"></td>
	<td bgcolor=\"$row_color\" nowrap>
		<table>
			<tr>
        			<td id=\"adres\" width=\"110\">Naam:</td>
				<td id=\"adres\" align=\"left\" valign=\"top\">".$row["voorletters"]." ".$row["voorvoegsel"]." ".$row["achternaam"]." ".$row["meisjesnaam"]." "."(".$row["geslacht"].")</td>
    			</tr>
			<tr>
        			<td id=\"adres\" width=\"110\">Voornaam:</td>
                	<td id=\"adres\" align=\"left\" valign=\"top\">".$row["voornaam"]."</td>
    			</tr>
        		<tr>
        			<td id=\"adres\" width=\110\">Woonplaats:</td>
                	<td id=\"adres\" align=\"left\" valign=\"top\">".$row["woonplaats"]."</td>
    			</tr>
        		<tr>
        			<td id=\"adres\" width=\"110\">E-mailadres:</td>
                	<td id=\"adres\" align=\"left\" valign=\"top\"><a href=\"mailto: ".$row["emailadres"]."\">".$row["emailadres"]."</a></td>
    			</tr>
        		<tr>
        			<td id=\"adres\" width=\"110\">Studierichting:</td>
                	<td id=\"adres\" align=\"left\" valign=\"top\">".$row["studierichting"]."</td>
    			</tr>
		</table>
	</td>
</tr>");

// Add 1 to the row count

    $row_count++;
}
echo " </table> ";

// how many rows we have in database
$query = "SELECT COUNT(id) AS numrows FROM objects";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
        if ($page == $pageNum) {
                $nav .= " $page "; // no need to create a link to current page
}
        else {
                $nav .= " <a href=\"$self?page=$page\">$page</a> ";
}
}


        if ($pageNum > 1) {
                $page = $pageNum - 1;
                $prev = " <a href=\"$self?page=$page\">vorige</a> ";

                $first = " <a href=\"$self?page=1\">eerste</a> ";
}
        else {
                $prev = ' '; // we're on page one, don't print previous link
                $first = ' '; // nor the first page link
}

        if ($pageNum < $maxPage) {
                $page = $pageNum + 1;
                $next = " <a href=\"$self?page=$page\">volgende</a> ";

                $last = " <a href=\"$self?page=$maxPage\">laatste</a> ";
}
        else {
                $next = ' '; // we're on the last page, don't print next link
                $last = ' '; // nor the last page link
}

?>

As you see, I have some translations in there (to Dutch), but that should not change the suntax errors...

The quotes did not fix the error unfortunately...

If you have other suggestions I would love to hear them!

Thanks!

redarrow · December 25, 2007

you sure form needs get not post?

pescott · December 25, 2007

That seemed to work when I wasn't selecting data between values from pulldown menus, just posting all the entries or finding a specific entry using 'selectSearch' (exact entries). It's just a problem setting upper and lower limits for database entries to find...

lmktech · December 25, 2007

</form>

</table>

I assume you are starting the cb_q_display.php file here. And need to declare the vars $woonplaats, $vraagprijs etc... before they can go into your query.

<?php

//‘id’, ‘woonplaats’, ‘vraagprijs’

// example

$woonplaats = $_GET["woonplaats"];

$vraagprijs_min = $_GET["vraagprijs_min"];

etc...

// how many rows we have in database

$query = "SELECT * FROM objects WHERE woonplaats='$woonplaats' AND vraagprijs>='$vraagprijs_min' AND vraagprijs<='$vraagprijs_max' ASC LIMIT $offset, $rowsPerPage";

Sign In

[SOLVED] MySQL syntax error in query: select price above and below

Recommended Posts

pescott

Link to comment

Share on other sites

btherl

Link to comment

Share on other sites

pescott

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

pescott

Link to comment

Share on other sites

lmktech

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information