Login | Register | FAQ
Anonymous

Multiple tests in a SQL search

+ Post a reply

17 posts 1, 2


Multiple tests in a SQL search

by SarahLouLeeming » Fri Jun 08, 2012 3:37 pm

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
User avatar

SarahLouLeeming

  • Posts: 14
  • Joined: Fri Jun 01, 2012 12:07 pm

Re: Multiple tests in a SQL search

by XainPro » Sat Jun 09, 2012 6:07 am

you just have to correct your query and make multiple quires basis on each field combination

and 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
User avatar

XainPro

  • Posts: 3933
  • Joined: Fri Feb 17, 2012 8:10 pm

Re: Multiple tests in a SQL search

by SarahLouLeeming » Sun Jun 10, 2012 12:47 pm

Thank you I will try that.

How can I determine if a search field is '' or blank when doing the if statement?
User avatar

SarahLouLeeming

  • Posts: 14
  • Joined: Fri Jun 01, 2012 12:07 pm

Re: Multiple tests in a SQL search

by XainPro » Mon Jun 11, 2012 12:19 pm

User avatar

XainPro

  • Posts: 3933
  • Joined: Fri Feb 17, 2012 8:10 pm

Re: Multiple tests in a SQL search

by SarahLouLeeming » Wed Jun 13, 2012 10:40 am

OK, I'll give it a go. Again, thanks for your help, you're a star
User avatar

SarahLouLeeming

  • Posts: 14
  • Joined: Fri Jun 01, 2012 12:07 pm

Re: Multiple tests in a SQL search

by SarahLouLeeming » Wed Jun 13, 2012 11:52 am

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.

User avatar

SarahLouLeeming

  • Posts: 14
  • Joined: Fri Jun 01, 2012 12:07 pm

Re: Multiple tests in a SQL search

by XainPro » Thu Jun 14, 2012 11:25 am

User avatar

XainPro

  • Posts: 3933
  • Joined: Fri Feb 17, 2012 8:10 pm

Re: Multiple tests in a SQL search

by SarahLouLeeming » Thu Jun 14, 2012 12:23 pm

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
User avatar

SarahLouLeeming

  • Posts: 14
  • Joined: Fri Jun 01, 2012 12:07 pm

Re: Multiple tests in a SQL search

by SarahLouLeeming » Thu Jun 14, 2012 2:47 pm

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>";
}
?>
User avatar

SarahLouLeeming

  • Posts: 14
  • Joined: Fri Jun 01, 2012 12:07 pm

Re: Multiple tests in a SQL search

by XainPro » Fri Jun 15, 2012 3:59 am

User avatar

XainPro

  • Posts: 3933
  • Joined: Fri Feb 17, 2012 8:10 pm


+ Post a reply

1, 2