Search not working on custom table

#1

I have a custom table in a Wordpress install that has 3 fields in it

ID - auto increment
cencode - varchar 3 characters in the form of 000-999
place - varchar 80 characters

I have a custom template that allows the input of 3 characters and then a search of the database takes place and the results are returned.

If the cencode entry in the input form is 099 or less no result is returned even though there is data for that code. If it is 100 or greater then a correct result is returned. The input field is set as text and with a breakpoint set in my code a correct 3 digits is displayed as part of my query.

This is the appropriate piece of my code
[php]

<?php if ($_POST['srch'] == "Search"){ echo '
'; $s_tbl = "ts14_occupations"; $t_name = sanitize_text_field($_POST['source']); $cen_code = sanitize_text_field($_POST['cencode']); if ($t_name == "birthplace") { $s_tbl = "ts14_birthplace"; } $query = " SELECT * FROM " . $s_tbl . " WHERE cencode LIKE " . $cen_code; //echo $query; $results = $wpdb->get_results($query, OBJECT); //print_r($results); if ($results) { ?>
            <br>
                <div>
                    <?php
                    foreach ($results as $result) {
                        //print_r($results);
                        if ($s_tbl == "ts14_occupations") {
                            echo "Code: " . $cen_code . " occupation is: " . $result->occupation;
                        } else {
                            echo "Code: " . $cen_code . " birthplace is: " . $result->place;
                        }
                    }

                    echo "</div>";
                    }
                    else {
                        echo 'No record of this code found in the 1911 Census codes.<br>Please check that you have entered a valid code.<br></div>';
                    }

[/php]

Anyone got any bright ideas? I suspect it is something with the query but can’t sort out what.

#2

I suspect your losing the zero when you sanitize. What is the output of $cen_code when you use 099?

#3

It is still 099 so it is not being altered by sanitise. Thanks for the suggestion

#4

Print the query and kill the script to see what is actually sent to the database first.

#5

It looks fine to me

SELECT * FROM ts14_occupations WHERE cencode LIKE 010
#6

I have resolved this. A bit odd but it works

[php]$query = "
SELECT *
FROM " . $s_tbl . "
WHERE cencode LIKE ‘" . $cen_code."’";[/php]