Can i select data from mysqli database using SUBSTR to refine the query

Question

I am trying the use refine tools for a search on my website. The bit i'm stuck with is search by start letter. For example i could use a wildcard '%X%' but his would return anything that contained the letter 'x'. I read on few sites that SUBSTRING can be used in mysql queries

http://dev.mysql.com/

http://www.kirupa.com/

https://stackoverflow.com/questions/6302027

This is what I have so far but returns nothing. There is data in the database that should return with the query.

public function refineUsersFollowers($user_id,$q){
    if($this->databaseConnection()){
        // get the users followers
        $state = array(1,2);
        $stmt = $this->db_connection->prepare("SELECT * FROM friends WHERE id_2 = :1 AND Friend_Request_State = :2 OR id_2 = :3 AND Friend_Request_State = :4");
        $stmt->bindParam(':1',  $user_id);
        $stmt->bindParam(':2',  $state[0]);
        $stmt->bindParam(':3',  $user_id);
        $stmt->bindParam(':4',  $state[1]);
        $stmt->execute();
        // format the SQL OR statements
        $sql = '';
        $ids = [];
        while($rows = $stmt->fetch(\PDO::FETCH_ASSOC)){
            array_push($ids,$rows['id_1']);
        }

        for($x = 0; $x < count($ids); $x++){
            if(count($ids) == 1){
                //if there is one result
                $sql.= ' user_id = :'.$x." AND SUBSTRING('first_name',0,1) = :".$x.$x;
            }else if($x == (count($ids) - 1)){
                // last entry
                $sql.= ' user_id = :'.$x." AND SUBSTRING('first_name',0,1) = :".$x.$x;
            }else{
                //continue loop
                $sql.= ' user_id = :'.$x." AND SUBSTRING('first_name',0,1) = :".$x.$x." OR";
            }
        }
        $stmt = $this->db_connection->prepare("SELECT * FROM account WHERE ".$sql);
        for($x = 0; $x < count($ids); $x++){
            $stmt->bindParam(':'.$x,$ids[$x]);
            $insert = $x.$x.'';
            $stmt->bindParam(':'.$insert,$q);
        }
        $stmt->execute();
        $results = $stmt->fetch(\PDO::FETCH_ASSOC);
        print_r($results);
        // check for followers that start with letter
    }
}

The first part of the function is fine, this gets an array of id's which is then placed together as an SQL string. Is the SQL not returning results because SUBSTRING is not supported in this way? If so is there a way of producing a query like this or would it be easier to pull every result from the database then check them in a different function?

Yes it does, did not know that though. Thats gonna make my code a lot simpler — Paul Ledger, Sep 30 '14 at 11:39

Gordon Linoff · Answer 1 · 2014-09-30T12:16:59.647

2

You have two issues with this expression:

SUBSTRING('first_name', 0, 1) = :".$x.$x;

First, substr() in SQL (in general) starts counting with 1 and not 0. So, the first argument should be 1.

Second, you have the first argument in single quotes. So, at best, this would return the letter 'f'. Here is a simple rule: Only use single quotes for string and date constants. Never use single quotes to refer to column names.

There are several way to write what you want. Here are three:

SUBSTRING(first_name, 1, 1) = $x
LEFT(first_name, 1) = $x
first_name like '$x%'

edited Sep 30 '14 at 12:16

answered Sep 30 '14 at 11:44

Gordon Linoff

1,242,037
58
646
786

can you correct last substring to use 1 as first argument as explained in the beginning – radar Sep 30 '14 at 11:54

score 0 · Answer 2 · answered Sep 30 '14 at 11:42

0

You query can be greatly simplified with the LIKE operator. This:

"AND SUBSTRING('first_name',0,1) = :".$x.$x;

can become this:

"AND first_name LIKE '".$x.$x."%'";

I'm not sure what the $x.$x is for, so I just left it in for illustrative purposes.

answered Sep 30 '14 at 11:42

wavemode

2,076
1
19
24

Can i select data from mysqli database using SUBSTR to refine the query

2 Answers2