Page 1 of 1

php search query help?

Posted: Tue Mar 19, 2013 12:16 pm
by casey9090
hi guys,

i have this html code :

Code: Select all

                                <label for="manufacturer"><strong>manufacturer:</strong></label>
                                <select id="manufacturer">
                                    <option selected="selected">All</option>
                                    <option value="option1">BF Goodrich</option>
                                    <option value="option2">Continental</option>
                                    <option value="option3">Michelin</option>
                                    <option value="option4">Pirelli</option>
                                </select>
                            </li>
                            <li>
                                <label for="model"><strong>tyretype:</strong></label>
                                <select id="model">
                                    <option selected="selected">All</option>
                                    <option value="option1">Summer tyre</option>
                                    <option value="option2">winter tyre</option>
                                    <option value="option3">winter tyre 2</option>
                                </select>
                            </li>
                            <li class="select-two">
                                <div>
                                    <label for="min-price"><strong>width:</strong></label>
                                    <select id="min-price">
                                        <option selected="selected">All</option>
                                        <option value="option1">145</option>
                                        <option value="option2">150</option>
                                        <option value="option3">155</option>
                                        <option value="option3">160</option>
                                    </select>
                                </div>
                                <div>
                                    <label for="max-price"><strong>Profil:</strong></label>
                                    <select id="max-price">
                                        <option selected="selected">All</option>
                                        <option value="option1">30</option>
                                        <option value="option2">35</option>
                                        <option value="option3">40</option>
                                        <option value="option3">45</option
                                    </select>                   
                                </div>
                            </li>
                            <li class="select-two">
                                <div>
                                    <label for="min-price"><strong>Diameter:</strong></label>
                                    <select id="min-price">
                                        <option selected="selected">All</option>
                                        <option value="option1">13"</option>
                                        <option value="option2">14"</option>
                                        <option value="option3">15"</option>
                                        <option value="option3">16"</option>
                                        <option value="option3">17"</option>
                                        <option value="option3">18"</option>
                                        <option value="option3">19"</option>
                                    </select>
                                </div>
                                <div>
                                    <label for="max-price"><strong>Maximum load:</strong></label>
                                    <select id="max-price">
                                        <option selected="selected">All</option>
                                        <option value="option1">75</option>
                                        <option value="option2">76</option>
                                        <option value="option3">77</option>
                                        <option value="option3">78</option>
                                        <option value="option3">79</option>
                                        <option value="option3">80</option>
                                        <option value="option3">81</option>
                                        <option value="option3">82</option>
                                        <option value="option3">83</option>
                                        <option value="option3">84</option>
                                    </select>                   
                                </div>
                            </li>

                            <li>
                                <label for="engine"><strong>Maximum speed:</strong></label>
                                <select id="engine">
                                    <option selected="selected">All</option>
                                    <option value="option1">Q = max 160 km/h</option>
                                    <option value="option2">R = max 170 km/h</option>
                                    <option value="option3">S = max 180 km/h</option>
                                    <option value="option3">T = max 190 km/h</option>
                                    <option value="option3">H = max 210 km/h</option>
                                </select>
                            </li>
so these are the search criteria for the customers to choose from, as you see there are the all option for each search criteria

anyway back in mysql database i have the following structure:

Code: Select all

a table called tyre which has the following coloumns
name
manufacturer
width
profil
diameter
load
speed
price
for the php query i used:

Code: Select all

$manufacturer = $_POST['manufacturer'];
$type = $_POST['type'];
$width = $_POST['width'];
$profil = $_POST['profil'];
$diameter = $_POST['diameter'];
$load = $_POST['load'];
$speed = $_POST['speed'];
$sql = array();
if (!empty($manufacturer)) {
    $sql[] = "Manufacturer='$manufacturer'";
}
if (!empty($type)) {
    $sql[] = "type='$type'";
}
if (!empty($width)) {
    $sql[] = "width='$width'";
}
if (!empty($profil)) {
    $sql[] = "profil='$profil'";
}
if (!empty($diameter)) {
    $sql[] = "diameter='$diameter'";
}
if (!empty($load)) {
    $sql[] = "load='$load'";
}
if (!empty($speed)) {
    $sql[] = "speed='$speed'";
}
$sql = implode(' AND ', $sql);
$sql = "SELECT * FROM tyre" . (!empty($sql)? " WHERE " . $sql: '');
the thing is, every criteria works fine only the maximum load option it wont work, and if i select specific number from the maximum load option then it wont show any result?? any idea which section of the code is wrong?? :banghead:

Re: php search query help?

Posted: Tue Mar 19, 2013 12:40 pm
by requinix
I don't know how any of that works because all the option values are "optionN".

Re: php search query help?

Posted: Tue Mar 19, 2013 2:08 pm
by casey9090
requinix wrote:I don't know how any of that works because all the option values are "optionN".
no no skip that part, i copied an older version of my editing, so i know the values are not true, but in my script i have changed them to right values

Re: php search query help?

Posted: Tue Mar 19, 2013 3:27 pm
by requinix
Then I don't know what your SQL is.

What is the exact value of $sql at the very end?

Re: php search query help?

Posted: Tue Mar 19, 2013 4:03 pm
by casey9090
requinix wrote:Then I don't know what your SQL is.

What is the exact value of $sql at the very end?
it will search the database and all the search options above is included by this query, like i want to search for a tyre and i would select all manufactures, but i want width of 150, profil of 55, and for example diameter of 20, and i will leave the others set as all selected (for the load and speed).

then the query should take my selections and search the mysql database for rows that fits with what i need.

hope u get the query now?

so everything is working good with this code, except the load option. if i select specific number from the maximum load dropdown menu then it will show no result but there are actually rows in mysql which has that number of maximum load but they wont show and i dont know the reason.

Re: php search query help?

Posted: Tue Mar 19, 2013 5:33 pm
by requinix
What is the exact value of $sql at the very end?

Re: php search query help?

Posted: Tue Mar 19, 2013 5:43 pm
by casey9090
requinix wrote:What is the exact value of $sql at the very end?
this is a search query, it wont give exact value, it will fetch rows that applies to what you selected in the search criteria?? dont u get what i mean?

Re: php search query help?

Posted: Tue Mar 19, 2013 5:52 pm
by requinix
I mean

Code: Select all

// ...
$sql = "SELECT * FROM tyre" . (!empty($sql)? " WHERE " . $sql: '');
echo $sql;
or thereabouts.