Problems passing multiple selected checkboxes from form to PHP/SQL "search engine"

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 ? '&nbsp;' : $row['cutting_text'],
    $row['tags'],
	$displayFullCuttingInfo ? '&nbsp;' : $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! :slight_smile:

I think yours is basically an sql question.

If the user has picked tags 2,3 and 4 to filter on (not really a search) then your query should probably have the line:


 ... AND cutting_tag.tag_id in (2,3,4) ...

Nail the sql queries first.

Hi Cups,

Thanks for your reply. I appreciate that I need to nail the SQL, but there are also elements of PHP causing me difficulties, as I’m not sure of the best way to pass the value of each individual checkbox which has been selected back with the form to populate the SQL query because I’ll never know how many there are going to be - in the full version of the database, there are up to 24 different tags (which will be added to in the future) and there can be any number of (and any combination of) check boxes checked. So it’s that bit that I’m stuck with too…

Thanks.

Well, did you manage to get put together the target sql statement, and get a result you expect by submitting that sql into your database?

If so, we can work on the PHP/HTML to put your statement together.

OK, I’ve figured out the SQL. This is what I’m aiming for (but with the (‘1’,‘2’) being populated by the values of the checked checkboxes):

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
AND tag.tag_id IN ('1','2')
GROUP BY cutting_tag.cutting_id

Turning that from the pure SQL into my PHP controller file, I’ve replaced lines 23-35 with the following code, so that that section now looks like this:


// 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 IN (\\'1\\',\\'2\\')';
//	}

I’ve had to comment out the $tags_returned mysqli_real_escape_string and if statement in order for it to work.

I get that I somehow need to replace the $tags_returned mysqli_real_escape_string with something to turn the $_GET[‘tags’] values into an array that I can put into the $where statement to replace (\‘1\’,\‘2\’) with an array, but this is where my confidence with PHP disappears!

Thanks.

your query is a bit malformed, and could possibly perform poorly (there’s an implicit cross join in there)

change this –

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
   AND tag.tag_id IN ('1','2')
GROUP 
    BY cutting_tag.cutting_id

to this –

SELECT cutting.cutting_id
     , cutting.cutting_text
     , GROUP_CONCAT(tag.tag_name) AS tags
  FROM cutting
INNER 
  JOIN cutting_tag 
    ON cutting_tag.cutting_id = cutting.cutting_id
INNER 
  JOIN tag
    ON tag.tag_id = cutting_tag.tag_id
   AND tag.tag_id IN ( 1 , 2 )
GROUP 
    BY cutting.cutting_id

Thanks Rudy! Much appreciated. My well-thumbed copy of your book (Simply SQL) is actually sat open on my desk as I type! :slight_smile:

<?php
// take a look at what PHP is passing around
if( isset($_POST) )
    var_dump( $_POST );

//tag_id is an array, just get the keys
$tag_ids = array_keys($_POST['tag_id']);

// returns false if a tag_id is not numeric
$verified = ( array_map('is_numeric', $tag_ids));

// found one, so halt, redirect, log out, whatever
if( in_array(false, $verified) ){ 
exit( 'Eek! a bad one' );
}

// construct the sql you need for your IN filter 
$sql_in_argument = '(' . implode(',', $tag_ids) . ')';

// have a butchers'
echo '<hr />' . $sql_in_argument . '<hr />';

// this array is pretending to be your mysql select result
// ie select tag_id, tag_name
$rows = array(
1 => 'Mentions CEO',
2 => 'Mentions Sponsor',
3 => 'Review',
4 => 'Great Quotes',
'test' => 'Ah nasty, someone tampering with your form',
);

echo "<form action = '' method= 'POST'>";

foreach($rows as $key=>$value)
echo "<input type=checkbox id=tag_id[$key] name=tag_id[$key]>$value <br />" . PHP_EOL ;

echo "<input type=submit></form>";

Have a play with that. Contains very rudimentary check that was is passed was indeed an integer - you should probably typecast it to an integer and check > 0 and less than a reasonable number.

Hi Cups,

Thanks for your reply. I pasted your code into my existing code to “have a play” with it, but there were so many notices and warnings that I just don’t know where to start!! I’m just so new to PHP that I haven’t got a clue about what most of the above means in order to understand where it’s all gone wrong!

I think I’m just going to have to accept defeat on this one and go back to basics with my books and try to work something out for myself.

Thanks for your help anyway - I just wish I had the knowledge to interpret it!

Did you put it in a single file? Call it test.php - I tested that on PHP5.2.6 on win32 and it worked fine.

You should just see a simple form with some checkboxes, and it assembles the part you need were missing for your sql statement.

Hi,

OK, putting it in a single file still gave me the notice/warnings etc. (as shown below) but after playing about with it for a bit longer, I’ve realised that they’re only there on loading the file for the first time, and they disappear on submitting the form with some values checked, so I’m guessing that they’re caused by the values being null.

array(0) { }
Notice: Undefined index: tag_id in /Applications/MAMP/htdocs/test.php on line 7

Warning: array_keys() expects parameter 1 to be array, null given in /Applications/MAMP/htdocs/test.php on line 7

Warning: array_map() [function.array-map]: Argument #2 should be an array in /Applications/MAMP/htdocs/test.php on line 10

Warning: in_array() expects parameter 2 to be array, null given in /Applications/MAMP/htdocs/test.php on line 13

Warning: implode() [function.implode]: Invalid arguments passed in /Applications/MAMP/htdocs/test.php on line 18

A lot of the code you posted is a beyond my PHP knowledge at this point, so I’m still a bit unsure as to how to apply it to my form/controller/results pages, but now that I can see it working I’ll persevere with playing about with it and just go down the trial and error route…!

Once I (hopefully!!) get it working, I’ll come back to this thread and post my solution, in case it might help others with similar problem.

Thanks again for your help.

OK, I understand you now, yes, it will chuck out errors until the form is submitted - I was just trying to keep it simple - but sadly you find that too complex to use.

The essential bit in your case is:


// This is how you access the form elements:
//tag_id is an array, just get the keys
$tag_ids = array_keys($_POST['tag_id']);

// This is how you use them to create (1,2,3)
// construct the sql you need for your IN filter 
$sql_in_argument = '(' . implode(',', $tag_ids) . ')';

If you cannot grasp that yet, don’t worry - what are you learning from anyway?

TBH your original question was so well thought out and presented, I felt obliged to reply to you - so, keep asking well thought out questions like that and you’ll get loads of support. :wink:

I’m just learning from Kevin Yank’s Build Your Own Database Driven Website Using PHP & MySQL, 4th ed, and the corresponding sitepoint PHP Live course. Only really been doing PHP since the beginning of April, so I’ve only been at it for 6 or 7 weeks or so…!

I got a severe case of me-and-my-big-mouth saying “yes, I can do that” in my new job and chucked myself in at the deep in with this project without quite realising how complex the coding would be! Typical, but I guess the best way to learn is to chuck yourself in at the deep in and see if you can swim… but I must say, this forum is a great buoyancy aid, and I really do appreciate all the support! :wink:

I promised that I’d post the working version once I got it working, so in the hope that it helps others with a similar problem to mine, here it is:

In the php controller file I replaced this code:


$tags_returned = mysqli_real_escape_string($link, $_GET['tags']);
    if ($tags_returned != '') // A category is selected
    {
        $where .= " AND tag.tag_id='$tags_returned'";
    }

with this code:


$tags = array_keys($_GET['tags']);
$sql_in_argument = implode(', ', $tags);
	
	if ($tags != '') // A category is selected
	{	
		$where .= " AND tag.tag_id IN ($sql_in_argument)";
	}

And in the searchform.html.php I replaced this code:


<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> 

with this code:


<fieldset>
       <legend>By Article Tag:</legend>
       <label for="tags[]">
       <input type="checkbox" name="tags[tag.tag_id]" id="tags[tag.tag_id]" value="" checked>Any tags</label><br />
       <label for="tags[]">
   <?php foreach($tags as $tag): ?>
       <input type="checkbox" id="tags[<?php htmlout($tag['tag_id']); ?>]" name="tags[<?php htmlout($tag['tag_id']); ?>]"><?php htmlout($tag['tag_name']); ?><br></label>
   <?php endforeach;?>
</fieldset>

No changes required for the results.html.php page.

The bit of this code that I like the most is the line form the searchform.html.php:

<input type="checkbox" name="tags[tag.tag_id]" id="tags[tag.tag_id]" value="" checked>Any tags</label>

It simply allows users to do a search on any tags by passing ‘tag.tag_id’ as the value of the array so the $where variable becomes " AND tag.tag_id IN (tag.tag_id)" which will always be true for all values, instead of the usual " AND tag.tag_id IN (1, 2)" which is what is created when one of the actual $tags checkboxes has been checked.

Many thanks again to CUPS who helped me out a lot on this one!

:slight_smile:

Oh, good on you! Thanks for posting your result and mentioning me… I needed cheering up after a few poorly thought out answers I posted earlier.

Do hang around and help out any others when you feel inclined (ps, the big secret is that is how you really learn).