Hi folks!
I’ve built a press cuttings database which allows users to categorise press cuttings with various select-one-option attributes (i.e. journalist, publication etc.) and also tag them with multiple communication theme tags (mentions sponsor, mentions CEO, review, good quotes etc.) (many to many relationship).
I’ve got the add new cutting/edit existing cutting front-end forms all under control, and the search functionality is all working perfectly, apart from the tags. If you select multiple checkboxes for multiple tags in the search form (e.g. tags=2 and tags=3), it only searches on the last one selected (e.g. tags=3) and ignores cuttings with which are tagged with tags=2.
I’m still a bit of a newbie to all this - however, my limited knowledge is telling me that even though I can see both tag values are being sent through the $_GET form (since I can see them both in the URL) the first value assigned to the variable (tags=2) is being overwritten by the second value (tags=3) therefore it only searches on the second value. Therefore I think that means I need to do one of two things:
(1) turn the selected checkboxes being submitted into an array, but I’m not sure whether it is best to do that at the form end or at the back end.
or (2) alter the SQL somehow to select cuttings based on an ‘equal-to-any-of-the-following’ list of values returned…
or a combination of both!!
I’ve stripped back a lot of the code/database so it just shows the stuff relevant to what I’m talking about…
Here’s the SQL for a stripped back version of the database:
CREATE DATABASE press;
USE press;
CREATE TABLE cutting (
cutting_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cutting_text TEXT
) DEFAULT CHARACTER SET utf8;
CREATE TABLE tag (
tag_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
tag_name VARCHAR(255)
) DEFAULT CHARACTER SET utf8;
CREATE TABLE cutting_tag (
cutting_id INT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY (cutting_id, tag_id)
) DEFAULT CHARACTER SET utf8;
INSERT INTO cutting (cutting_id, cutting_text) VALUES
(1, 'News Article Example Headline'),
(2, 'Press Review Example Headline');
INSERT INTO tag (tag_id, tag_name) VALUES
(1, 'Mentions CEO'),
(2, 'Mentions Sponsor'),
(3, 'Review'),
(4, 'Great Quotes');
INSERT INTO cutting_tag (cutting_id, tag_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 3),
(2, 4);
Here’s the stripped back version of the PHP controller file (I should also say I created privileges for a user with username ‘username’ and password ‘password’ just to keep things simple).
<?php
if (isset($_GET['action']) and $_GET['action'] == 'search')
{
$link = mysqli_connect('localhost', 'username', 'password');
if (!$link)
{
echo $error = 'Unable to connect to the database server.';
exit();
}
if (!mysqli_set_charset($link, 'utf8'))
{
echo $error = 'Unable to set database connection encoding.';
exit();
}
if (!mysqli_select_db($link, 'press'))
{
echo $error = 'Unable to locate the database.';
exit();
}
// The basic SELECT statement
$select = 'SELECT cutting.cutting_id, cutting_text, GROUP_CONCAT(tag.tag_name) AS tags
FROM cutting
INNER JOIN tag
INNER JOIN cutting_tag ON cutting.cutting_id = cutting_tag.cutting_id AND cutting_tag.tag_id = tag.tag_id';
$where = ' '; //reason for this is there are multiple search criteria, but I'm only showing the tags here, so you're not having to wade through masses of code
$group = ' GROUP BY cutting_tag.cutting_id';
$tags_returned = mysqli_real_escape_string($link, $_GET['tags']);
if ($tags_returned != '') // A category is selected
{
$where .= " AND tag.tag_id='$tags_returned'";
}
$result = mysqli_query($link, $select . $where . $group);
if (!$result)
{
echo $error = 'Error fetching cuttings: ' . mysqli_error($link);
echo $error;;
exit();
}
if (mysqli_num_rows($result) == 0)
{
echo 'The search criteria you entered did not yield any results this time.';
exit();
}
else
{
include 'results.html.php';
exit();
}
}
// Display search form
$link = mysqli_connect('localhost', 'username', 'password');
if (!$link)
{
echo $error = 'Unable to connect to the database server.';
exit();
}
if (!mysqli_set_charset($link, 'utf8'))
{
echo $error = 'Unable to set database connection encoding.';
exit();
}
if (!mysqli_select_db($link, 'press'))
{
echo $error = 'Unable to locate the database.';
exit();
}
// Build the list of article tags
$sql = "SELECT tag_id, tag_name FROM tag GROUP BY tag_name";
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error fetching list of article tags. ' . mysqli_error($link);
echo $error;;
exit();
}
while ($row = mysqli_fetch_array($result))
{
$tags[] = array(
'tag_id' => $row['tag_id'],
'tag_name' => $row['tag_name'],
'selected' => FALSE);
}
include 'searchform.html.php';
?>
and here’s the front-end search form ‘searchform.html.php’
<?php include_once $_SERVER['DOCUMENT_ROOT'] .
'/includes/helpers.inc.php'; ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Search Press Cuttings</title>
</head>
<body>
<h1>Search Press Cuttings</h1>
<form action="" method="get">
<p>Search the press cuttings database using the following criteria:</p>
<div>
<fieldset>
<legend>By Article Tag:</legend>
<label for="tags">
<input type="checkbox" name="tags" id="" value="" checked>Any tags</label>
<label for="tags">
<?php foreach ($tags as $tag): ?>
<div>
<input type="checkbox" name="tags" id="tags<?php htmlout($tag['tag_id']); ?>" value="<?php htmlout($tag['tag_id']); ?>"><?php htmlout($tag['tag_name']); ?></label></div>
<?php endforeach; ?>
</fieldset>
</div>
<div>
<input type="hidden" name="action" value="search">
<input type="submit" value="Search">
</div>
</form>
<p><a href="..">Return to Press Cutting Database home</a></p>
</body>
</html>
and the results page ‘results.html.php’:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Press Cuttings Database: Your Search Results</title>
</head>
<body>
<p>Your search of the press cutting database returned <?php echo mysqli_num_rows($result);?> result(s).
<p>Your search results:</p>
<table width="1200" border="1" cellspacing="1" cellpadding="1">
<thead>
<tr>
<th>Cutting Title</th>
<th>Tags</th>
<th>Edit?</th>
</tr>
</thead>
<tbody>
<?php
$last = null;
while ($row = mysqli_fetch_assoc($result))
{
$displayFullCuttingInfo = true;
if($last !== $row['cutting_id'])
{
$last = $row['cutting_id'];
$displayFullCuttingInfo = false;
}
printf(
'<tr>
<td>%s</td>
<td>%s</td>
<td><form action="?" method="get">
<div>
<input type="hidden" name="cutting_id" value="%s">
<input type="submit" name="action" value="Edit">
</div>
</form></td>
</tr>',
$displayFullCuttingInfo ? ' ' : $row['cutting_text'],
$row['tags'],
$displayFullCuttingInfo ? ' ' : $row['cutting_id']
);
}
?>
</tbody>
</table>
<p><a href="?">Click here to perform a new search</a><br />
<a href="..">Return to Press Cuttings Database home</a></p>
</body>
</html>
Any suggestions/help would be gratefully appreciated!
Thanks! 
