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: 3662
  • 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

you can check it by many ways
1.
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.
User avatar

XainPro

  • Posts: 3662
  • 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

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 />';
   }
   

?>
User avatar

XainPro

  • Posts: 3662
  • 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

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

XainPro

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


+ Post a reply

1, 2