0

A while back diafol helped me get a searching script working. It works very well the only problem I am having now is getting it to return the searched values. Here's some context to walk you through the process.

I have a search page, image attached as help1.png, which I am trying to find a record that has the service county as "Johnson" and the License/Certificate as "Certified Social Worker" (NOTE both are arbitrary values that I picked because I specifically made a record to contain both values also see help2.png) the code can be found HERE please also note that the case statement is not done yet, only look at case 2 for the license2 field. The old code was everything but the case statements using the code in the default area as it's primary code. This worked and would return if I was searching "Certified Social Worker" but there was one problem which is what I am trying to fix. There are 5 fields (license1, license2, license3, license4, license5) which are all the same field but are only there in case someone has more than one license they can use the second field to specify and the 3rd if they have a 3rd and so on. The searching becomes an issue when I am looking for someone who has multiple fields and has listed the field I am searching for in a different license spot. Let's say I am searching Certified Social Worker and I put that in the License/Certificate 1 search area but there is someone who is also a registered nurse and put that before the Certified Social worker and put the Certified Social Worker in the License 2 spot. I wouldn't get back that record because I searched for Certified Social Worker in the License 1 spot. The case statements are my way of making it so that if they enter some value for a license field it will search not only the license field that the value was entered into but also the other 4. From what I see the stmtstring looks fine, I included it at the bottom, and the whereclause and bindarray also look fine, also included at the bottom. I am not getting an error I am just getting no result.
stmtString:

    SELECT * FROM VolunDB WHERE `johnson` = :johnson AND `license1` = :license21 AND `license2` = :license22 AND `license3` = :license23 AND `license4` = :license24 AND `license5` = :license25

WhereClause and BindArray using Var_Dump (in that order)

array(6) {
               [0]=>;
               string(24) "`johnson` = :johnson"
               [1]=>;
               string(23) "`license1` = :license21"
               [2]=>;
               string(23) "`license2` = :license22"
               [3]=>;
               string(23) "`license3` = :license23"
               [4]=>;
               string(23) "`license4` = :license24"
               [5]=>;
               string(23) "`license5` = :license25"
             }
             array(6) {
               [":johnson"]=>;
               string(9) "Johnson"
               [":license21"]=>;
               string(23) "Certified Social Worker"
               [":license22"]=>;
               string(23) "Certified Social Worker"
               [":license23"]=>;
               string(23) "Certified Social Worker"
               [":license24"]=>;
               string(23) "Certified Social Worker"
               [":license25"]=>;
               string(23) "Certified Social Worker"
             }

I also attached an image using Inspect element to show that the values are being sent to the page that is named help3 and help4 is just a small picture of the Value in the Database.
Please let me know if there is anything I am not making clear. There is a lot of information to type and I am not sure if I am missing something you want to see.
help5.png is a diagram that sort of shows what needs to happen.

Basic overview:

Need:
Search multiple database columns with 1 dropdown field.

Example:

Field1 = "dog" 
         Column1 = "bird"
         Column2 = "cat"
         Column3 = "dog"
         Run search:
         OUTPUT: 'Found Match' IF Column1 OR Column2 OR Column3 CONTAINS Field1
         bird =/= dog
         cat =/= dog
         dog = dog 
         'Found Match'

Edited by toxicandy

Attachments help4.PNG 8.79 KB help5.PNG 40.58 KB help1.PNG 62.52 KB help3.PNG 36.99 KB help2.PNG 64.13 KB
2
Contributors
3
Replies
19
Views
2 Years
Discussion Span
Last Post by toxicandy
0

the images are useless
someone probably can photoshop the images so they look right but that wont make your code work
to fix the code post the section in error for amendments or suggestions

0

Those are images that I took but yes the code that is isn't working is:

case "license2":
            if($san = filter_input(INPUT_POST, $field, FILTER_SANITIZE_STRING)){
            $whereClause[] = "`license1` = :license21";
            $bindArray[":license21"] = $san;  
            $whereClause[] = "`license2` = :license22";
            $bindArray[":license22"] = $san;
            $whereClause[] = "`license3` = :license23";
            $bindArray[":license23"] = $san;  
            $whereClause[] = "`license4` = :license24";
            $bindArray[":license24"] = $san;  
            $whereClause[] = "`license5` = :license25";
            $bindArray[":license25"] = $san;
            }
            break;

The default case which looks like:

default:
            if($san = filter_input(INPUT_POST, $field, FILTER_SANITIZE_STRING)){
                $whereClause[] = "`$field` = :$field";
                $bindArray[":$field"] =  $san;  
            }

works if I leave out the License/Certificate field. The whole point of this code is to search for a column containing the desired text from the drop down regardless of if it is in the License1, License2, License3, License4, License5 columns in the the database. The reason I am searching using the second drop down is because the text "Certified Social Worker" is in the License1 column on the database. I want to be able to pull that record because it has a license containing the desired search result. I want the 1, 2, 3, 4, 5 all to be arbitrary values but still needed as mere place holders for the possible licenses. Instead of having one column named License and then parsing it based on a camma delimited list we just made new columns where each column would be a separate item from the other possible way of doing it which is the CSV style list.

I tried making the code:

 case "license2":
                if($san = filter_input(INPUT_POST, $field, FILTER_SANITIZE_STRING)){
                $whereClause[] = "`license1` = :license2";
                $bindArray[":license2"] = $san;  
                $whereClause[] = "`license2` = :license2";
                $bindArray[":license2"] = $san;
                $whereClause[] = "`license3` = :license2";
                $bindArray[":license2"] = $san;  
                $whereClause[] = "`license4` = :license2";
                $bindArray[":license2"] = $san;  
                $whereClause[] = "`license5` = :license2";
                $bindArray[":license2"] = $san;
                }
                break;

but the result to that from the bind array was:

array(2) {
  [":johnson"]=>;
  string(6) "Johnson"
  [":license2"]=>;
  string(23) "Certified Social Worker"
}

which gave me an error for wrong number of assigned parameters. The errors were fixed when I redid the parameter system to the 2(n) where n is an integer that fits in 1<= n <=5

Edited by toxicandy

0

I think I found the problem. It is in the $stmtstring I have it logically set up to "AND" every time but since only 1 will return True it will logically be false because TRUE && FALSE = FALSE so instead I need to do TRUE OR FALSE to get an output. I think this is problem the problem.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.