Multiple tests in a SQL search
Multiple tests in a SQL search
I am new to PHP and SQL so forgive me if this has been asked before or seems simple, but I can't find it.Basically what I'm trying to do, is have a form with 5 fields and a submit button. Each field forms a search so whtever all 5 fields contain form the search criteria eg Field A AND Field B AND Field C AND Field D AND Field E. The results need to check through all of the fields to get the relevant results.
The html script I have so far is:
<div style="position:absolute; left:165px; top:160px; width:950px; height:20px;">
<form action="snowboards.php" method="get">
<fieldset>
<label for="brand" class="formlabel">Brand</label>
<select id="brand" name = "brand">
<option value = "">Select</option>
<option value = "Academy">Academy</option>
<option value = "Apo">Apo</option>
<br/>
</select>
<label for="year" class="formlabel">Year</label>
<select id="year" name = "year">
<option value = "">Select</option>
<option value = "2013">2013</option>
<option value = "2012">2012</option>
<option value = "2011">2011</option>
<hr/>
</select>
<label for="model" class="formlabel">Model</model>
<input id = "model" name = "model" type="text">
<label for="riding_style" class="formlabel">Riding style</label>
<select id="riding_style" name = "riding_style">
<option value = "">Select</option>
<option value = "all mountain">All mountain</option>
<option value = "freeride">Freeride</option>
<option value = "freestyle">Freesyle</option>
<br/>
</select>
<label for="base_type" class="formlabel">Base type</label>
<select id="base_type" name = "base_type">
<option value = "">Select</option>
<option value = "extruded">Extruded</option>
<option value = "sintered">Sintered</option>
<br/>
</select>
<input type="submit" class="submitbutton" value = "Search">
</fieldset>
</Form>
</div>
The PHP I have so far is:
<?php
session_start();
$result='';
$snowboards='';
$brand = $_POST['brand'];
$year = $_POST['year'];
$ridingstyle = $_POST['riding_style'];
$model = $_POST['model'];
$basetype = $_POST['base_type'];
$connect = mysql_connect("127.0.0.1","salts408_admin","xxxxxxxx") or die ("Could not connect to database");
$selected = mysql_select_db("salts408_Snowboards",$connect) or die ("Could not find database");
$query = mysql_query("SELECT * FROM snowboards WHERE brand='$brand' ORDER BY brand, year, model; ASC");
$result_obj = '';
$result_obj = $connect->query($query);
while($result = $result_obj->fetch_array(MYSQLI_ASSOC))
{
$snowboards[]=$result;
}
print_r ($snowboards);
?>
<html>
<div style="position:absolute; left:241px; top:168px; width:777px; height:42px;">
<div class="Artistic-Body-P">
<span class="Artistic-Body-C5"><?php echo $snowboards; ?></span></div>
</div>
</html>
Like I said I don't really have a clue what I'm doing. I understand it, but not quite there so if anyone could point me in the right direction or let me know a better way I would be very grateful.
Thanks,
Sarah
Re: Multiple tests in a SQL search
you just have to correct your query and make multiple quires basis on each field combinationand control them with if else
like this
$brand = $_POST['brand'];
$year = $_POST['year'];
$ridingstyle = $_POST['riding_style'];
$model = $_POST['model'];
$basetype = $_POST['base_type'];
if only brand is submited
$query = mysql_query("SELECT * FROM snowboards WHERE
brand='$brand' ORDER BY brand ASC");
//if only year is submited
$query = mysql_query("SELECT * FROM snowboards WHERE
year='$year'");
if all things submited
$query = mysql_query("SELECT * FROM snowboards WHERE
brand='$brand' AND
year='$year' AND
ridingstyle='$ridingstyle' AND
AND model like '%$model%'
AND basetype = '$basetype' ORDER BY brand ASC");
etc etc
Re: Multiple tests in a SQL search
Thank you I will try that.How can I determine if a search field is '' or blank when doing the if statement?
Re: Multiple tests in a SQL search
you can check it by many ways1.
- Code: Select all
if($field['name'] == '') {
//code goes here
}
2.
- Code: Select all
if(isset($field['name']) {
//code goes here
}
3.
- Code: Select all
if(empty(isset($field['name'])) {
//code goes here
}
etc,etc
it depends on you which method best suite your needs.
Re: Multiple tests in a SQL search
OK, I'll give it a go. Again, thanks for your help, you're a starRe: Multiple tests in a SQL search
I'm getting error "Fatal error: Call to a member function query() on a non-object in /home/salts408/public_html/snowboards.php on line 18" which is the line "$result_obj = $connection->query($query);" on this coding:<?php
session_start();
$result='';
$snowboards='';
$brand = $_POST['brand'];
$year = $_POST['year'];
$ridingstyle = $_POST['riding_style'];
$model = $_POST['model'];
$basetype = $_POST['base_type'];
$connection = mysql_connect("127.0.0.1","salts408_admin","*********") or die ("Could not connect to database");
$selected = mysql_select_db("salts408_Snowboards",$connection) or die ("Could not find database");
$query = mysql_query("SELECT * FROM snowboards WHERE brand='$brand' ORDER BY brand ASC");
$result_obj = '';
$result_obj = $connection->query($query);
while($result = $result_obj->fetch_array(MYSQLI_ASSOC))
{
print_r ($result);
echo '<br />';
}
?>
Last edited by SarahLouLeeming on Thu Jun 14, 2012 12:21 pm, edited 1 time in total.
Re: Multiple tests in a SQL search
i did not understand your code beyound this point$result_obj = '';
$result_obj = $connection->query($query);
while($result = $result_obj->fetch_array(MYSQLI_ASSOC))
{
print_r ($result);
echo '<br />';
}
?>
you are using it as object and you did not included any class ! are you forgot to include class and iniciate an object or this code is just being copy paste from any other place any how replace your this code with mine and i hope my code gona work for you here it is
- Code: Select all
<?php
session_start();
$result='';
$snowboards='';
$brand = $_POST['brand'];
$year = $_POST['year'];
$ridingstyle = $_POST['riding_style'];
$model = $_POST['model'];
$basetype = $_POST['base_type'];
$connection = mysql_connect("127.0.0.1","salts408_admin","!2£4QwEr") or die ("Could not connect to database");
$selected = mysql_select_db("salts408_Snowboards",$connection) or die ("Could not find database");
$query = mysql_query("SELECT * FROM snowboards WHERE brand='$brand' ORDER BY brand ASC");
while($result = mysql_fetch_array($query);)
{
print_r ($result);
echo '<br />';
}
?>
Re: Multiple tests in a SQL search
OK, tried that and get an issue at lin 19 which is the while statement:Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/salts408/public_html/snowboards.php on line 19
Re: Multiple tests in a SQL search
Now I have the coding below. For some reason the sql query i not working, it seems to be blank. Any ideas?<?php
session_start();
$query='';
$result='';
$brand = $_POST['brand'];
$year = $_POST['year'];
$ridingstyle = $_POST['riding_style'];
$model = $_POST['model'];
$basetype = $_POST['base_type'];
$connection = mysql_connect("127.0.0.1","salts408_admin","********") or die ("Could not connect to database");
$selected = mysql_select_db("salts408_Snowboards",$connection) or die ("Could not find database");
$query = mysql_query("SELECT 'brand' FROM 'Snowboards' WHERE 'brand'=$brand") or die("Data not selected");;
while($result = mysql_fetch_array($query))
{
echo "Brand:".$result{'brand'}."Model:".$result{'model'}."Year:".$result{'year'}."<br>";
}
?>
Re: Multiple tests in a SQL search
Remove quotes In RED From Query Add quotes in Green<?php
session_start();
$query='';
$result='';
$brand = $_POST['brand'];
$year = $_POST['year'];
$ridingstyle = $_POST['riding_style'];
$model = $_POST['model'];
$basetype = $_POST['base_type'];
$connection = mysql_connect("127.0.0.1","salts408_admin","********") or die ("Could not connect to database");
$selected = mysql_select_db("salts408_Snowboards",$connection) or die ("Could not find database");
$query = mysql_query("SELECT 'brand' FROM 'Snowboards' WHERE 'brand'='$brand'") or die("Data not selected");
while($result = mysql_fetch_array($query))
{
echo "Brand:".$result{'brand'}."Model:".$result{'model'}."Year:".$result{'year'}."<br>";
}
?>
in short replace this line
- Code: Select all
$query = mysql_query("SELECT brand FROM Snowboards WHERE brand='$brand'") or die("Data not selected");