[SOLVED] new and confused, php search script for use with mysql db

[gwydionwaters]

hello, i am new to php. just started attempting to understand it a few hours ago. i have made a mysql db already and have made the form i would idealy like to be able to use for my search page. i have tried to find pre built scripts and tutorials online but none seem to allow the use of multiple search queries of specific columns, ei. search for XX in col1 and YY in col3 but nothing this time in col2. the form i would like to use is as follows;

<form name="form" action="gearsearch.php" method="get">
   <table align="center" width="60%" border="0">
    <tr>
     <td align="center">By Name<BR><input type="text" name="name"></td><td align="center">Author<BR><input type="text" name="author"></td><td align="center">Keyword<BR><input type="text" name="key" /></td>
    </tr>
    <tr>
     <td align="center">By Type<BR><select name="Type"><option value="" selected="selected">No Type</option><option value="Knife - Fixed">Knife - Fixed</option><option value="Knife - Folding">Knife - Folding</option><option value="Stove - Canister">Stove - Canister</option></select></td><td align="center">By Use<BR><select name="Use"><option value="" selected="selected">No Use</option><option value="Backpacking">Backpacking</option><option value="Multi-Purpose">Multi-Purpose</option></select></td><td align="center">By Cost<BR><select name="Cost"><option value="" selected="selected">No Cost</option><option value="=< $10">$10 or less</option><option value="=< $20">$20 or less</option><option value="=< $30">$30 or less</option><option value="=< $40">$40 or less</option></select></td>
    </tr>
    <tr>
     <td colspan="3" align="center">By Rating   <select name="Rating"><option value="" selected="selected">No Rating</option><option value="10/10">10/10</option><option value="9/10">9/10</option><option value="8/10">8/10</option><option value="7/10">7/10</option><option value="6/10">6/10</option><option value="5/10">5/10</option><option value="low">< 10</option></select></td>
    </tr>
    <th colspan="3"><input type="submit" name="Submit" value="Search" /></th>
   </table>
  </form>

thanks for reading everyone

[mmarif4u]

select * from table where id='test' and name='alpha'

 

OR

 

select * from table where id='test' OR name='alpha'

[gwydionwaters]

where do test and alpha come from?

is this the correct way to search some or all of the columns in a table?

i have not written anything in php before, forgive all the questions

[mmarif4u]

id and name are your table fields, and test and alpha is coming from your form.

There are  alot of ways to search mysql tables.

you can use some thing like:

select * from table where name like '%$auther%';

select * from table where name like '%$auther';

select * from table where name like '$auther%';

 

$auther will be your variable coming from the form.

Many other ways to do search, depends on your search script. that what you want to search and how to search.

 

[gwydionwaters]

oh ok, so is there a way to make it like

select * from table where id='test' AND OR name='alpha'

i'm not sure what AND OR would actually be

[mmarif4u]

You can use it, but not like that.

some how like this:

select * from table where id='test' AND name='alpha' OR fullname='beta'

[gwydionwaters]

hmmm, well i have tried modifiying a pre built script

<?php

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10; 

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","root","root"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("test") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select * from Gear where Name='Name' OR Type='Type' OR Use='Use' OR Author='Author' OR Cost='Cost' OR Rating='Rating'
  order by Type"; // EDIT HERE and specify your table and field names for the SQL query

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";

// begin to show results set
echo "Results";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["Name"];

  echo "$count.) $title" ;
  $count++ ;
  }

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp ";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>

but when i try to pass the string from my form to the script page it does not work

[mmarif4u]

Pass the string from the variable to the query.

[gwydionwaters]

i'm sorry i don't understand, how do i do that? does the script look like it should work ok?

[gwydionwaters]

ok, so i have done a lot of reading and have learnt some basics. now i have a theoreticaly working search script but all types of searches display no results. my form is as follows

<form name="form" action="gearsearch.php" method="post">
   <table align="center" width="60%" border="0">
    <tr>
     <td align="center">By Name<BR><input type="text" name="name"></td><td align="center">Author<BR><input type="text" name="author"></td><td align="center">Keyword<BR><input type="text" name="key"></td>
    </tr>
    <tr>
     <td align="center">By Type<BR><select name="Type"><option value="" selected="selected">No Type</option><option value="Knife - Fixed">Knife - Fixed</option><option value="Knife - Folding">Knife - Folding</option><option value="Stove - Canister">Stove - Canister</option></select></td><td align="center">By Use<BR><select name="Use"><option value="" selected="selected">No Use</option><option value="Backpacking">Backpacking</option><option value="Multi-Purpose">Multi-Purpose</option></select></td><td align="center">By Cost<BR><select name="Cost"><option value="" selected="selected">No Cost</option><option value="=< $10">$10 or less</option><option value="=< $20">$20 or less</option><option value="=< $30">$30 or less</option><option value="=< $40">$40 or less</option></select></td>
    </tr>
    <tr>
     <td colspan="3" align="center">By Rating   <select name="Rating"><option value="" selected="selected">No Rating</option><option value="10/10">10/10</option><option value="9/10">9/10</option><option value="8/10">8/10</option><option value="7/10">7/10</option><option value="6/10">6/10</option><option value="5/10">5/10</option><option value="< 5/10">< 5</option></select></td>
    </tr>
    <th colspan="3"><input type="submit" name="Submit" value="Search" /></th>
   </table>
  </form>

and my php script is this

<html>
<head>
  <title>Untitled Document</title>
  </head>
<body>
<?
$username="root";
$password="root";
$database="test";

$Name=$_POST['Name'];
$Author=$_POST['Author'];
$Type=$_POST['Type'];
$Use=$_POST['Use'];
$Cost=$_POST['Cost'];
$Rating=$_POST['Rating'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM Gear WHERE Use='$Use' OR Type='$Type' OR Cost='$Cost' OR Rating='$Rating' OR Author='%$Author%' OR Name='%$Name%' ORDER BY Type";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Gear Database Search Results</center></b><br><br>";

$i=0;
while ($i < $num) {

$Name=mysql_result($result,$i,"Name");
$Author=mysql_result($result,$i,"Author");
$Type=mysql_result($result,$i,"Type");
$Use=mysql_result($result,$i,"Use");
$Cost=mysql_result($result,$i,"Cost");
$Rating=mysql_result($result,$i,"Rating");

echo "$Name $Author $Type $Use $Cost $Rating";

$i++;
}

?>
</body>
</html>

 

maybe someone knows where my error lies, thanks for reading