pescott Posted December 25, 2007 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/83133-solved-mysql-syntax-error-in-query-select-price-above-and-below/ Share on other sites More sharing options...
btherl Posted December 25, 2007 Share Posted December 25, 2007 You probably need "WHERE city = '$city'", with quotes. Quote Link to comment https://forums.phpfreaks.com/topic/83133-solved-mysql-syntax-error-in-query-select-price-above-and-below/#findComment-422872 Share on other sites More sharing options...
pescott Posted December 25, 2007 Author Share Posted 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! Quote Link to comment https://forums.phpfreaks.com/topic/83133-solved-mysql-syntax-error-in-query-select-price-above-and-below/#findComment-422875 Share on other sites More sharing options...
redarrow Posted December 25, 2007 Share Posted December 25, 2007 you sure form needs get not post? Quote Link to comment https://forums.phpfreaks.com/topic/83133-solved-mysql-syntax-error-in-query-select-price-above-and-below/#findComment-422876 Share on other sites More sharing options...
pescott Posted December 25, 2007 Author Share Posted 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... Quote Link to comment https://forums.phpfreaks.com/topic/83133-solved-mysql-syntax-error-in-query-select-price-above-and-below/#findComment-422879 Share on other sites More sharing options...
lmktech Posted December 25, 2007 Share Posted 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"; Quote Link to comment https://forums.phpfreaks.com/topic/83133-solved-mysql-syntax-error-in-query-select-price-above-and-below/#findComment-422884 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.