PHP - Pulling Exact Data From A Database?

Question

So, below is my code I am using to pull account expiry data from the string ?term=[username], which then will show the expiry of the account in unix timestamp, however, it will only pull data from one username with similar characters, for example, I try to get the account expiration data for the username demo123, I use demo.net/api.php?term=demo123 to do that and I get the results, but when I try to grab account expiration data for demo124 ( Similar characters, but not exact ), I don't get any results.. ? Is there something wrong with the code, or? Any help is appreciated! I want the data to only show and output the exact results of the search string.

    // Database Connection String
    $con = mysql_connect($db_hostname,$db_termname,$db_password);
    if (!$con)  {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db($db_database, $con);

    if (!empty($_REQUEST['term'])) {

        $term = mysql_real_escape_string($_REQUEST['term']);     

        $sql = "SELECT * FROM users WHERE username LIKE '%".$term."%'"; 
        $r_query = mysql_query($sql); 

        while ($row = mysql_fetch_array($r_query)){  
            echo '' .$row['expire'];  
        }  

    }
    ?>

`'%".$term."'";` remove one of the `%` or use `WHERE col='$term'` for exact search. — Funk Forty Niner, Nov 26 '15 at 22:31
`LIKE` checks whether the search term occurs, not whether something resembling the search term is present, so `demo123` and `demo124` will never match using like. Could it be that you are looking for what's commonly referred to as a "fuzzy search"? — fvu, Nov 26 '15 at 22:35
How would I integrate WHERE col='$term' -- I already have a WHERE statement? The % removal didn't work. And, fvu, I am trying to only find the exact user in the database, nothing more than the exact, so I guess 'fuzzy search' is a good term to describe what I am wanting. — Comradeo, Nov 26 '15 at 22:40
$sql = "SELECT * FROM users WHERE username='$term'"; Like that? I've tried that, and I am still getting similar results o-o — Comradeo, Nov 26 '15 at 22:51
I probably misunderstood what you're looking for, sorry for that. This is odd. Does the query `SELECT * FROM users WHERE username='demo124'` return a result when you execute it in eg workbench or whatever database utility you commonly use? — fvu, Nov 26 '15 at 22:58
No, no. FVU, you are right, it is Fuzzy Search. So what do I use instead of LIKE? I am trying to pull data from the exact string when I use my API to show expiry data. You weren't misunderstanding at all. — Comradeo, Nov 26 '15 at 22:59
yep, however if `demo124` doesn't exist, then your query failed. You need to show us your db schema. Also, if `demo124` was entered in your db with a space at the end, then that could be the reason why it failed. Or, that the URL itself contains a space (use `trim()` if that is the case). There isn't anything else I can say or do here, sorry. — Funk Forty Niner, Nov 26 '15 at 23:00
The exact string is demo124, and there was no space entered in at the end, demo124 is in the username column, the url also does not contain a space. I appreciate your attempted assistance, Fred! — Comradeo, Nov 26 '15 at 23:01
I'm probably less sure that I understood you than you are, apparently :) But ok, fuzzy search is more complicated than that - see http://stackoverflow.com/questions/369755/how-do-i-do-a-fuzzy-match-of-company-names-in-mysql-with-php-for-auto-complete to get a feel for the techniques involved. — fvu, Nov 26 '15 at 23:36
FVU, is there anyway I can contact you directly, do you have a Skype? — Comradeo, Nov 26 '15 at 23:38
Sorry, no. But if SO would be so nice to show the "move to chat" option we could have a short q&a to clear up a couple of concepts, because I'm still not convinced we're converging here... — fvu, Nov 27 '15 at 00:04
Let me formulate it in clear text. Suppose the database contains 'demo123' and you search for 'demo124', would you like to have the db respond 1) no, there's no demo124 here OR 2) I have no exact match but demo123 is pretty close, as is dimo124 and so on (probably sorted by "distance", ie a measure of similarity?) 2) is a fuzzy search, it will show close matches and a measure of "closeness" — fvu, Nov 27 '15 at 00:07
I want the database to respond with 1.); There's no demo1234 here. How do I do it? — Comradeo, Nov 27 '15 at 01:09
Fvu, I want the data to only show and output the exact results of the search string. In the database, demo123 and demo124 exist. When I search demo123 it gives its result of demo123, but when I search demo124, it gives me a blank result, but it's existent in the database, Idk why. I want what's in the database to pull exactly as is from the search of the API. — Comradeo, Nov 27 '15 at 02:06
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/96307/discussion-between-comradeo-and-fvu). — Comradeo, Nov 27 '15 at 03:18

hanshenrik · Accepted Answer · 2015-11-27T02:55:27.003

1: do not use mysql_ , it hasn't been maintained for years, is deprecated, and removed as of php7. use PDO or mysqli. who knows, maybe you're seeing a bug? which won't be fixed either way

2: don't use empty() here, use array_key_exist() or isset() instead.. else you wont be able to search for username "0"

3: make sure you're running with error_reporting(E_ALL) when trying to figure out why your code doesn't work

4: make sure you're connecting with the right character set, and make sure that characterset is UTF8 (or "utf8mb4" in the MySQL world). -- http://utf8everywhere.org/

5: don't use LIKE in this SQL, just use =

try something like this:

<?php
error_reporting(E_ALL);
    $con = new PDO('mysql:host='.$db_hostname.';dbname='.$db_database.';charset=utf8mb4', $db_termname,$db_password, array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
        if(array_key_exists('term',$_REQUEST)){
            $stm=$con->prepare('SELECT * FROM users WHERE username = ?');
            $stm->execute(array($_REQUEST['term']));
            while($row = $stm->fetch(PDO::FETCH_ASSOC)) {
                echo $row['expire'];  
        }   
    }

and a hint, var_dump() is awesome for debugging, if something doesn't work.. ^^

and a great place to learn about PDO, if you're used to mysql_, is here: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

edit: fixed typo in (untested) code x.x

PHP - Pulling Exact Data From A Database?

1 Answers1