Searching with a checkbox and textbox in php??

General PHP Help
#1

I can’t debug this and I’m making more errors. Can someone help me? I need to make sure this checkbox and textbox search works correctly.

[php]

<?php require_once 'mysql.php'; try{ // connect to MySQL $db=new MySQL(array ('host'=>'localhost','user'=>'','password'=>'', 'database'=>'')); $searchterm = $db->escapeString($_POST['searchterm']); $check = $_POST['check_list']; if(isset($_POST['check_list']) && is_array($_POST['check_list'])){ foreach($_POST['check_list'] as $check) { $workerquery = "SELECT firstname, lastname, asbestos FROM users WHERE ('searchterm > '' AND (firstname,lastname)=:searchterm) OR (:check > '' AND asbestos=:check ); $result=$db->$workerquery; if(!$result->countRows()){ echo '

No results were found. Go back and try a new search.

'.""; } else{ // display search results echo '

Your search criteria returned '.$result->countRows().' results.

'.""; while($row=$result->fetchRow()){ echo '

First Name:' .$row['firstname']. 'Last Name:' .$row['lastname']. 'Asbestos:' .$row['asbestos'].

'.""; } } } echo '
'; } } catch(Exception $e){ echo $e->getMessage(); exit(); } ?>

[/php]

#2

Are you sure you’re using that mysql class correctly?

[php] $workerquery = "SELECT firstname, lastname, asbestos
FROM
users
WHERE
('searchterm > ‘’ AND (firstname,lastname)=:searchterm) OR
(:check > ‘’ AND asbestos=:check );

$result=$db->$workerquery;[/php]

Here you’re seemingly performing a query, but you aren’t sending in any data to match the placeholders in the query. Also the $class->$variable notation is strange, and it should probably be a function call…

#3

I guess I can’t write the php then because combining the textbox and checkbox is too difficult. Here’s the form with all the checkboxes and the textbox. I was trying to just validate one checkbox and then add the others, but it won’t work.

[php]

Search Engine

Search Engine

Search by Name

Search by License, Certification, or Training

Aerial Work Platforms Asbestos Awareness
Crane Operator
First Aid
Forklift Operator
Lead Awareness
OSHA 10
OSHA 30
Personal Protection Equipment Training MUST 18 Modules 
</div>
    <center>
    <input type="submit" class="searchbutton" value=" Search">

[/php]

#4

Well, I didn’t completely fix your form, but it’s much closer. Someone with more experience in PDO will be able to help with the rest. There’s definitely something wrong with the SELECT query.

Anyways, here’s your html file:
(Fixed the indentations and other junk)

[code]

Search Engine

Search Engine

Search by Name

Search by License, Certification, or Training

Aerial Work Platforms Asbestos Awareness Crane Operator First Aid Forklift Operator Lead Awareness OSHA 10 OSHA 30 Personal Protection Equipment Training MUST 18 Modules
[/code]

Here’s your PHP:
[php]<?php
require_once (‘mysql.php’);
try{
// connect to MySQL
$db=new MySQL(array(‘host’=>‘localhost’,‘user’=>’’,‘password’=>’’,‘database’=>’’));
if(isset($_POST[‘searchterm’]) { $searchterm = $db->escapeString($_POST[‘searchterm’]); }
if(isset($_POST[‘check_list’]) && is_array($_POST[‘check_list’])){ $check = $_POST[‘check_list’]; }
foreach($_POST[‘check_list’] as $check) {
$workerquery = “SELECT firstname, lastname, asbestos FROM users WHERE (”.$searchterm." > ‘’ AND (firstname,lastname)=:searchterm) OR (:check > ‘’ AND asbestos=:check)";
$result=$db->$workerquery;
if(!$result->countRows()){
echo ‘

No results were found. Go back and try a new search.

’;
} else {
echo ‘

Your search criteria returned ‘.$result->countRows().’ results.

’;
while($row=$result->fetchRow()){
echo ‘
’
‘First Name:’.$row[‘firstname’].
‘Last Name:’.$row[‘lastname’].
‘Asbestos:’.$row[‘asbestos’].
‘
’;
}
echo ‘
’;
}
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
?>[/php]

Your foreach line is messed up as well:
[php]foreach($_POST[‘check_list’] as $check) {[/php]
$check = $_POST[‘check_list’] as defined earlier in your code. This line won’t work.

Also, I changed how $check is created. You created $check first then did an isset statement. It should be the other way around. So, I changed it to this:
[php]if(isset($_POST[‘check_list’]) && is_array($_POST[‘check_list’])){ $check = $_POST[‘check_list’]; }[/php]
Now, $check is being properly “checked”

Someone else with more knowledge of PDO will need to finish it. I fixed what I saw, but the rest is beyond me.

#5

Thanks for your help. I know there’s an error in the select but I’m not sure how to write it.

#6

The form won’t run. The select isn’t correct and the error is:

[php] Parse error: syntax error, unexpected ‘{’ in processform.php on line 10
[/php]

#7

Add a closing bracket on the last line, but before the closing PHP tag. That should fix the that error, but you’ve still got bad code.

Here’s some small revisions I made to the foreach and query:
[php]foreach($check as $checked) {
$workerquery = “SELECT firstname, lastname, asbestos FROM users WHERE (”.$searchterm." > ‘’ AND (firstname,lastname)=:searchterm) OR (:checked > ‘’ AND asbestos=:checked)";[/php]

Tell me, is the purpose of
[php]".$searchterm." > ‘’[/php]
and
[php]:checked > ‘’[/php]
To check if the user inputted anything? Because if so, you shouldn’t be doing that within the query. You should check the variable for that prior to putting the variable in the query.

#8

Yes, but I’m not sure how to write it.

#9

As I said earlier, I’m not that experienced with PDO, but I’ll give it a shot.

Try changing this:
[php]“SELECT firstname, lastname, asbestos FROM users WHERE (”.$searchterm." > ‘’ AND (firstname,lastname)=:searchterm) OR (:checked > ‘’ AND asbestos=:checked)";[/php]
To this:
[php]“SELECT firstname, lastname, asbestos FROM users WHERE firstname = :searchterm OR lastname = :searchterm OR asbestos = :checked”;[/php]
Still not entirely certain when you use colons instead of $ in front of variables, so try this as well:
[php]“SELECT firstname, lastname, asbestos FROM users WHERE firstname = “.$searchterm.” OR lastname = “.$searchterm.” OR asbestos = “.$checked.””;[/php]

#10

In PDO you can (and should) use named or unnamed placeholders.

Unnamed: SELECT * FROM users WHERE id IN (?, ?, ?)

Named: SELECT * FROM users WHERE id IN (:id1, :id2, :id3)

You should never enter user submitted data directly into a query. Sending the data in as bounded parameters protects your application from SQL injection.

#11

It still doesn’t like those brackets.

Parse error: syntax error, unexpected ‘{’ in C:\wamp\www\namesearch\backup\processform.php on line 7

#12

Can you re-post your current code?

I may be useless in regards to PDO, but I can help with this =P

#13

This is the current code.

[php]

<?php

require_once (‘mysql.php’);
try{
// connect to MySQL
$db=new MySQL(array(‘host’=>‘localhost’,‘user’=>’’,‘password’=>’’,‘database’=>’’));
if(isset($_POST[‘searchterm’]) { $searchterm = $db->escapeString($_POST[‘searchterm’]); }
if(isset($_POST[‘check_list’]) && is_array($_POST[‘check_list’])){ $check = $_POST[‘check_list’]; }
foreach($_POST[‘check_list’] as $check) {

$workerquery = “SELECT firstname, lastname, asbestos FROM users WHERE firstname = :searchterm OR lastname = :searchterm OR asbestos = :checked”;

$result=$db->$workerquery;

if(!$result->countRows()){

echo ‘

No results were found. Go back and try a new search.

’;

} else {

echo ‘

Your search criteria returned ‘.$result->countRows().’ results.

’;

while($row=$result->fetchRow()){

echo ‘

’

‘First Name:’.$row[‘firstname’].

‘Last Name:’.$row[‘lastname’].

‘Asbestos:’.$row[‘asbestos’].

‘

’;

}

echo ‘

’;

}
}
catch(Exception $e){

echo $e->getMessage();

exit();
}
?>

[/php]

#14

You never added the closing bracket at the end of the script I told you about.

Here’s your code with the bracket included:
[php]<?php
require_once (‘mysql.php’);
try{
// connect to MySQL
$db=new MySQL(array(‘host’=>‘localhost’,‘user’=>’’,‘password’=>’’,‘database’=>’’));
if(isset($_POST[‘searchterm’]) { $searchterm = $db->escapeString($_POST[‘searchterm’]); }
if(isset($_POST[‘check_list’]) && is_array($_POST[‘check_list’])) { $check = $_POST[‘check_list’]; }
foreach($_POST[‘check_list’] as $check) {
$workerquery = “SELECT firstname, lastname, asbestos FROM users WHERE firstname = :searchterm OR lastname = :searchterm OR asbestos = :checked”;
$result=$db->$workerquery;
if(!$result->countRows()){
echo ‘

No results were found. Go back and try a new search.

’;
} else {
echo ‘

Your search criteria returned ‘.$result->countRows().’ results.

’;
while($row=$result->fetchRow()){
echo ‘
’
‘First Name:’.$row[‘firstname’].
‘Last Name:’.$row[‘lastname’].
‘Asbestos:’.$row[‘asbestos’].
‘
’;
}
echo ‘
’;
}
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
}
?>[/php]

@JimL - I want to learn PDO. I read your thread on it. It was excellent. Could you expand a bit on what the “IN” in “WHERE id IN (:id1, :id2, :id3)” does? Is it used for the multiple values (eg: id1, id2, id3) or what? I’m genuinely curious. Thanks.

#15

It’s normal SQL

SELECT * FROM users WHERE id IN (1, 2, 3)

is the same as

SELECT * FROM users WHERE id = 1 OR id = 2 OR id = 3

No problems using it with either unnamed or named placeholders.

#16

@JimL - Thanks, brah

Does this code look accurate?
[php]$workerquery = “SELECT firstname, lastname, asbestos FROM users WHERE firstname = :searchterm OR lastname = :searchterm OR asbestos = :checked”;
$result=$db->$workerquery;
$result->execute(array(’:searchterm’ => $searchterm));
$result->execute(array(’:checked’ => $checked));[/php]

#17

You can find another tutorial for it here

As you can see in my tutorial I like to use a wrapper class for it so that I can do this

[php]$users = $db->query(’
SELECT firstname, lastname, asbestos FROM users
WHERE firstname = ?
OR lastname = ?
OR asbestos = ?’,
[$searchterm, $searchterm, $checked]
);[/php]

#18

@JimL - Genius. Well, if that didn’t help out OP, it certainly helped me. Thank you.

PS: Since you’re calling the variables within the query I suppose “$result->execute” afterwords isn’t required?

Sponsor our Newsletter | Privacy Policy | Terms of Service