Multiple "OR" functions within a SELECT command

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
trassalg
Forum Newbie
Posts: 23
Joined: Fri Jul 27, 2007 3:26 pm

Multiple "OR" functions within a SELECT command

Post by trassalg »

How would I modify the following so it enables me to search any or all of the 3 "search" fields?

Code: Select all

$query = "SELECT * FROM articleTable WHERE (articleTitle LIKE '$srch1' OR articleText LIKE '$srch1') AND (dateOfArticle BETWEEN '$fromDate' AND '$untilDate')";
This code is working, but I would like to add the possibility to search "$srch2" and "$srch3" in both columns "articleTitle" and "articleText" but without the obligation that they be filled in.

I've tried the following, but it doesn't seem to work:

Code: Select all

$query = "SELECT * FROM articleTable WHERE ((articleTitle LIKE '$srch1' OR '$srch2' OR '$srch3') OR (articleText LIKE '$srch1' OR '$srch2' OR '$srch3')) AND (dateOfArticle BETWEEN '$fromDate' AND '$untilDate')";
Any ideas?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Code: Select all

SELECT *
FROM articleTable
WHERE
(articleTitle LIKE '$srch1' OR articleTitle LIKE '$srch2' OR articleTitle LIKE '$src3'
OR articleText LIKE '$srch1' OR articleText LIKE '$srch2' OR articleText LIKE '$src3')
AND (dateOfArticle BETWEEN '$fromDate' AND '$untilDate')
trassalg
Forum Newbie
Posts: 23
Joined: Fri Jul 27, 2007 3:26 pm

Post by trassalg »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


For some reason that's not working for me.  Here's the full code to my 2 programs... maybe that helps to figure it out?  Using the SQL query you gave me (included in the posted code) it returns all the results the database holds unless I have 2 search criteria, in which case it works.  But if I enter 3 keywords it doesn't.  Not sure why.

[u][b]buildArticleList.php[/b][/u]

Code: Select all

<html>
<head>
<title>Baso de datos de Rodolfo</title>
<link href="styles/styles.css" rel="stylesheet" type="text/css">
<?php
	include("includes/menuBar.php");
	include("includes/misc.inc");
	echo "<h2>Search for articles</h2>";
?>
</head>
<body>

<?php
$ThisYear = date("Y");
setlocale(LC_TIME, 'es_ES');
?>
<?  if (isset($_POST['month']) && is_numeric($_POST['month']) &&
    ((int)$_POST['month'] >= 1 && (int)$_POST['month'] <= 12)) {
    $month = (int)$_POST['month'];
  } else {
    $month = date('n');
  }
  if (isset($_POST['year']) && is_numeric($_POST['year']) &&
    ((int)$_POST['year'] >= 2005 && (int)$_POST['year'] <= 2010)) {
    $year = (int)$_POST['year'];
  } else {
  $year = date('Y');
  }
?>
  <form method="post" action="search.php">
	<table width="90%" align="center">
  	<tr>
      <td width="25%">From date:</td>
      <td>
    <select name="day1"><?php
      $maxdays = date('t', mktime(12, 0, 0, $month, 1, $year));
      for ($i = 1; $i <= $maxdays; $i++) {
        if (isset($_POST['day']) && $_POST['day'] == $i) {
          $sel = ' selected';
        } elseif ($i == date('j')) {
          $sel = ' selected';
        } else {
          $sel = '';
        }
        echo "<option value=\"$i\"$sel>$i</option>\n";
      }
    ?></select>

 <select name="month1"><?php
      for ($i = 1; $i <= 12; $i++) {
        if ($month == $i) {
          $sel = ' selected';
        } else {
          $sel = '';
        }

        $monthname = strftime('%B', mktime(12, 0, 0, $i, 1, 2005));

        echo "<option value=\"$i\"$sel>$monthname</option>\n";
      }
?>
</select>
    <select name="year1"><?php
      for ($i = 2000; $i <= $ThisYear; $i++) {
        if ($year == $i) {
          $sel = ' selected';
        } else {
          $sel = '';
        }
        echo "<option value=\"$i\"$sel>$i</option>\n";
      }
    ?>
  	</select>
			</td>
    </tr>
  	<tr>
      <td>Until date:</td>
      <td>
    <select name="day2"><?php
      $maxdays = date('t', mktime(12, 0, 0, $month, 1, $year));
      for ($i = 1; $i <= $maxdays; $i++) {
        if (isset($_POST['day']) && $_POST['day'] == $i) {
          $sel = ' selected';
        } elseif ($i == date('j')) {
          $sel = ' selected';
        } else {
          $sel = '';
        }
        echo "<option value=\"$i\"$sel>$i</option>\n";
      }
    ?></select>

 <select name="month2"><?php
      for ($i = 1; $i <= 12; $i++) {
        if ($month == $i) {
          $sel = ' selected';
        } else {
          $sel = '';
        }

        $monthname = strftime('%B', mktime(12, 0, 0, $i, 1, 2005));

        echo "<option value=\"$i\"$sel>$monthname</option>\n";
      }
?>
</select>
    <select name="year2"><?php
      for ($i = 2000; $i <= $ThisYear; $i++) {
        if ($year == $i) {
          $sel = ' selected';
        } else {
          $sel = '';
        }
        echo "<option value=\"$i\"$sel>$i</option>\n";
      }
    ?>
  	</select>
			</td>
    </tr>
      <td>Search text:</td>
      <td><input type="text" name="search1" size=20 maxlength=20></td>
    </tr>
    </tr>
      <td></td>
      <td><input type="text" name="search2" size=20 maxlength=20></td>
    </tr>
    </tr>
      <td></td>
      <td><input type="text" name="search3" size=20 maxlength=20></td>
    </tr>
      <td></td>
      <td><input type="submit"></td>
    </tr>
  </table>
  </form>
</body>
</html>

search.php

Code: Select all

<html>
<head>
<title>Baso de datos de Rodolfo</title>
<link href="styles/styles.css" rel="stylesheet" type="text/css">
<?
include("includes/misc.inc");
include("includes/menuBar.php");
include("includes/connection.inc");

if (!"")
	{
$srch1 = "%".$search1."%";
$srch2 = "%".$search2."%";
$srch3 = "%".$search3."%";
$fromDate = "$year1-$month1-$day1";
$untilDate = "$year2-$month2-$day2";

$query = "SELECT *
FROM articleTable
WHERE
(articleTitle LIKE '$srch1' OR articleTitle LIKE '$srch2' OR articleTitle LIKE '$src3'
OR articleText LIKE '$srch1' OR articleText LIKE '$srch2' OR articleText LIKE '$src3')
AND (dateOfArticle BETWEEN '$fromDate' AND '$untilDate')";

$result = mysql_query($query);}
	if ($result)
		{
	echo "Here are the results:";
	$table_content = "";
	$table_content .= "<table border='1'>";
	while ($row = mysql_fetch_array($result)) {
		$table_content .= "<tr>
		<td>".$row['articleIDNumber']."</td>
		<td>".$row['dateOfArticle']."</td>
		<td>".$row['categories']."</td>
		<td>".$row['articleTitle']."</td>
		<td>".$row['articleText']."</td>
		</td>
		</tr>";
		}
	$table_content .= "</table>";
	echo $table_content;
		}
else {
	echo '<div align="center"><strong>No elegiste nada para buscar!!!</strong></div><br>';
	}

mysql_close();

?>
</body>
</html>

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
trassalg
Forum Newbie
Posts: 23
Joined: Fri Jul 27, 2007 3:26 pm

Post by trassalg »

Figured it out.

Added a default value of "" to each form field.

MySQL query is:

Code: Select all

$query = "SELECT * 
FROM articleTable 
WHERE ((articleTitle LIKE \"%$search1%\" 
OR articleText LIKE \"%$search1%\" 
OR articleTitle LIKE \"%$search2%\" 
OR articleText LIKE \"%$search2%\" 
OR articleTitle LIKE \"%$search3%\" 
OR articleText LIKE \"%$search3%\")) 
AND dateOfArticle 
BETWEEN '$fromDate' AND '$untilDate'";
And before the SQL query, the following:

Code: Select all

if ($search1 == "") {
	$search1 = "Text_String_That_Will_Never_Be_Found";
	}
if ($search2 == "") {
	$search2 = "Text_String_That_Will_Never_Be_Found";
	}
if ($search3 == "") {
	$search3 = "Text_String_That_Will_Never_Be_Found";
	}
Post Reply