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 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. 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! 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? 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... 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"; 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
Archived
This topic is now archived and is closed to further replies.