How to fetch data of specific column from MySQL?

Question

I have a problem when I want to display name from genres table...How to call this function and how to get name from genres...When I use echo I get undefined $data...

public function getGenreName(){
    $query = mysqli_query($this->con, "SELECT * FROM genres WHERE id='$this->genre'");
    $data = mysqli_fetch_array($query);
    return $data['name'];
  }

where is `$this->con` and `$this->genre` defined? Can you please show more relevant code of this class? — Jeff, Aug 22 '18 at 14:12
Your function indicates you only want a genrename by looking to the function name `getGenreName` you should use `SELECT name` instead off `SELECT *`... Also your code `'$this->genre'` look to be prone to SQL injection.. — Raymond Nijland, Aug 22 '18 at 14:26

RiggsFolly · Answer 1 · 2018-08-22T14:43:54.330

You are not checking for error and I think you have one in the query line

public function getGenreName(){
    $query = mysqli_query($this->con, "SELECT * FROM genres WHERE id='{$this->genre}'");

    if ( ! $query ) {
        echo $this->con->error;
        return false;
    }
    $data = mysqli_fetch_array($query);
    return $data['name'];
}

You could be a bit more efficient and just select name as thats all you appear to be interested in.

public function getGenreName(){
    $query = mysqli_query($this->con, "SELECT name FROM genres WHERE id='{$this->genre}'");

    if ( ! $query ) {
        echo $this->con->error;
        return false;
    }
    $data = mysqli_fetch_array($query);
    return $data['name'];
}

Althought this still contains the possibility of an SQL Injection Attack Even if you are escaping inputs, its not safe! Use prepared parameterized statements in either the MYSQLI_ or PDO API's

So you should really be be doing

public function getGenreName(){
    $stmt = $this->con->prepare("SELECT name 
                                FROM genres 
                                WHERE id=?");
    $stmt->bind_param('s', $this->genre );
    $query = $stmt->execute();

    if ( ! $query ) {
        echo $this->con->error;
        return false;
    }
    $result = $stmt->get_result();
    $result->fetch_array(MYSQLI_NUM);
    return $result[0];
}

@NemanjaDimtrijevic `SELECT *` can be beter changed into `SELECT name` you only use name annyway in this function. Also `'{$this->genre}'` looks to be prone to SQL injection you can use a simple `(int)` cast that should protect it just fine, better is to use a prepard statement. if the topicstarter is using PHP 5.4.0 the last two lines `$data = mysqli_fetch_array($query); return $data['name'];` can be rewritten as a one liner `return mysqli_fetch_array($query)['name'];` Because of `Array dereferencing` — Raymond Nijland, Aug 22 '18 at 14:34
@RaymondNijland Took on board the SQL Injection as that really did need fixing — RiggsFolly, Aug 22 '18 at 14:44

How to fetch data of specific column from MySQL?

1 Answers1