Select Single Value from mysql

Question

I'm trying to select a single value from mysql - but however it does not work:

public function doStuff($posted_name){    
    $result = mysql_query("SELECT id FROM people WHERE people_name = $posted_name");
    $singleValue = mysql_fetch_object($result);
}

posted_name, people_name is TEXT, id is INT

I am posting a $posted_name , this I let compare with column people_name from table people, and selecting the column "id" ...

this id i want to store as value , here I tried to store it in $singleValue... but however it does not work - is there any mistake?

Solved: see comments

EDIT:

I changed some code to mysqli for testing- but now I have some error:

my code looks like:

private $db;


    // constructor
    function __construct() {
        include_once './db_connect.php';
        // connecting to database
        $db = new DB_Connect();
          $this->db = $db->connect();
    }

. . . . .

$result = this->db->query("INSERT INTO.....

The error appears for the above line ... Parse error: syntax error, unexpected '->' (T_OBJECT_OPERATOR)...

what I am doing wrong

my db_connect.php

class DB_CONNECT {

    private $db;

    // constructor
    function __construct() {
        // connecting to database
        $this->connect();
    }


    /**
     * Function to connect with database
     */
    function connect() {
        // import database db variables
        require_once __DIR__ . '/db_config.php';

        // Connecting to mysql database
       $this->db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD);

        if (mysqli_connect_errno()) {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }
        // Selecing database

        // returing connection cursor
        return $this->db;
    }



}

Answer 1

You are not adding single quote against $posted_name, this way, any value of this variable will be considered as MySQL keyword/DB name/Table Name/Field Name.

Single quotes are necessary against values if the values are alphabetic ones.

Change

$result = mysql_query("SELECT id FROM people WHERE people_name = $posted_name");

To

$result = mysql_query("SELECT id FROM people WHERE people_name = '$posted_name'");

Also, please don't use mysql_* functions as they are deprecated for security reasons and will be removed in future versions.

Answer 2

I think it should look more like:

$result = mysql_query("SELECT id FROM people WHERE people_name = '$posted_name' LIMIT 1");

Notice the quote around the $post_name. But in this situation you should be careful of SQL Injection, an attacker can hack you site if you do not sanitize your inputs. So I would suggest escaping $posted_name before using it in an SQL query

Answer 3

You need to add single quote in $posted_name like this '$posted_name'. and you should know how mysql_fetch_object() works and what it return.

mysql_fetch_object() fetches the result row as an object.It returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead.

I have edited your code which looks like this ...

public function doStuff($posted_name){    
    $result = mysql_query("SELECT id FROM people WHERE people_name = '$posted_name'");
    if($result){
         while ($row = mysql_fetch_object($result)){
              $id =  $row->id;
              echo $id;
          }
     }
 }

Answer 4

I have edited your code which looks like this

 class INSERT{
    private $db;

    //constructor 
    function __construct(){
        include_once 'db_connect.php';

        $this->db = new DB_CONNECT();
        $this->db->connect();
    }



    function insert_data(){
        $sql = "INSERT INTO.....";
        $insert = $this->db->_insert_data($sql);
        if($insert){
            echo "data has been inserted";
        }
    }

}

$data = new INSERT();
$data->insert_data();

and db_config.php includes the DB_HOST=>host name, DB_USER=>database username, DB_PASSWORD=>database password,DB_NAME=>database name

 class DB_CONNECT {

        private $db;


        // constructor
        function __construct() {
            // import database db variables
           require_once __DIR__ . '/db_config.php';
        }


        /**
         * Function to connect with database
         */
        function connect() {

            // Connecting to mysql database and selecting the database
           $this->db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD,DB_NAME);
           $this->check_error();


        }

        /**
         * Function to check the mysqli error
         */
        function check_error(){
           if (mysqli_connect_errno()) {
                echo "Failed to connect to MySQL: " . mysqli_connect_error();
            } 
        }



         /**
         * Function to run the query
         */
        function _run_query($query){
            return mysqli_query( $this->db , $query);
        }



        /**
         * Function to insert data in the database
         */ 
        function _insert_data($statement){
            $result = $this->_run_query($statement);
            if(mysqli_affected_rows( $this->db ) == 1){
                return true;
            }else{
                return false;
            }
        }
   }