Page 1 of 1
Multiple "OR" functions within a SELECT command
Posted: Fri Jul 27, 2007 4:23 pm
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?
Posted: Fri Jul 27, 2007 4:34 pm
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')
Posted: Fri Jul 27, 2007 5:45 pm
by trassalg
feyd | Please use 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
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]
Posted: Sat Jul 28, 2007 8:24 am
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";
}