searching multiple fields

Question

charter 0 Newbie Poster

20 Years Ago

I have a form in my search.php page and want to know how to search on more than one field in my Mysql Database to give me the result.

This is my form for searching one field:

<form action="results.php" method="post">
Search: <select name="searchtype">
<option value="date">Date</option>
<option value="wk no.">Wk #</option>
<option value="year">Year</option>
<option value="artist">Artist</option>
<option value="title">Title</option>

</select>  <input name="searchterm" type="text"> 
<INPUT type=image src="search.gif" value="Search"></div>
</form>

The thing is I wish to search on both the wk no. field and theyear field to give the result I want.

The think is that the Year is on multiple records (about 50 records) the Wk No. is the same on only 10 records in the database then the Wk No. changes with the Year staying the same.

This means that if I can search on both the Year and Wk No. it would give me the 10 records that match both criteria.

If I search on just the Year it gives me all 50 records for the year regardless of Wk No.
Or if I search on Wk No. it gives me all the records with the same WK No. in all the different years.

So if anyone can please amend my form code to do this it would be very much appreciated.

php

2 Contributors
4 Replies
3K Views
1 Day Discussion Span
Latest Post 20 Years Ago Latest Post by Ragnarok

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Ragnarok 0 Junior Poster in Training · Answer 1 · 2004-06-20T20:38:09+00:00

The SQL would look something like this:

"SELECT * FROM table WHERE wkno LIKE '%".$_POST['searchterm']."%' OR year LIKE '%".$_POST['searchterm']."%'"

charter 0 Newbie Poster · Answer 2 · 2004-06-21T01:33:07+00:00

The SQL would look something like this:

"SELECT * FROM table WHERE wkno LIKE '%".$_POST['searchterm']."%' OR year LIKE '%".$_POST['searchterm']."%'"

Sorry but this isn't what I am looking for.

I want a page with 2 search boxes (the code I gave was for 1 box).

The data base includes 2 fields 1 for wk no and 1 for year.

I want to search in 1 box for the wk no and to search in the other box for the year.

Then for the result to match the wk no. and the year.

This means having 2 search box forms on one page but one button to search for the criteria

charter 0 Newbie Poster · Answer 3 · 2004-06-21T02:44:35+00:00

Thanks Ragnarok but i'm a newbie to PHP/MySQL

Here's my code for the results page:

This is the full code I have on the results.php page:

<?php
// create short variable names

if(ini_get('register_globals') != "1")
{
$post = (floatval(substr(phpversion(), 0, 3)) >= 4.1) ? $_POST : $HTTP_POST_VARS;

foreach($post as $key => $value)
{
$$key = $value;
}
}
$searchtype = addslashes(trim($searchtype));
$searchterm = addslashes(trim($searchterm));

if(!$searchtype || !$searchterm)
{
exit('You have not entered search details. Please go back and try again.');
}
$link_id = @mysql_connect("localhost", "root", "password");

if($link_id === false)
{
exit("Error, Could not connect to the system database. Sorry...");
}
mysql_select_db('charts50', $link_id);
$result = mysql_query("SELECT * FROM `uk` WHERE `{$searchtype}` LIKE '%{$searchterm}%'");
?>
</head>

<tr>
<td height="29" class="years-ago">50 YEARS AGO</td>
</tr>
<tr align="left">
<td height="29" colspan="2" class="years-ago">
<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr class="headings">
<tr>
<th class="headings" width="28">TW</th>
<th class="headings" width="53">LW</th>
<th class="headings" width="28">Wks</th>
<th class="headings" width="340">Title</th>
<th class="headings" width="307">Artist</th>

</tr>
<?php $i = 1; while($row = mysql_fetch_array($result)){

$item = array();
$item = stripslashes($row);
$item = stripslashes($row);

// now we can override that

$item = ($item=='new')
? "<img src=\"images/new2.gif\" alt=\"new!\" />"
: $item;

$item = stripslashes($row);
$item = strtoupper(stripslashes($row));
$item = stripslashes($row);
?>

What I want is a AND situation. I wish to enter data for 2 fields into 2 search boxes on the page (field 1 is wk no and field 2 is year) I want the result to find the record (rows) which match both wk no AND year

I also need to know how to set up the search terms on the forms.
Many Thanks

Ragnarok 0 Junior Poster in Training · Answer 4 · 2004-06-21T04:04:23+00:00

use exactly the same query put use an AND insted of OR