www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: MySQLi problem when passing a variable as query

  1. #1
    Join Date
    Oct 2013
    Posts
    25

    MySQLi problem when passing a variable as query

    I have been having a weird problem since noon today,

    Code:
    <?php
        class db {
    	
    		public $con = null;
    		
    		private $server = '127.0.0.1';
    		private $user = 'root';
    		private $pass = '';
    		private $database = 'sites';
    
    		public function __construct(){
    			$this->con = new MySQLi($this->server, $this->user, $this->pass, $this->database);
    				if ($this->con->connect_errno > 0){
    					die('Error: Unable to connect to database ['.$this->con->connect_error.']');
    				}
    		}
    		
    		public function __distruct(){
    			$this->con->close();
    		}
    
    		public function search($query){
    			
    			$query = $this->con->query($query);
    			
    
    			$result = array();
    				while ($row = $query->fetch_assoc()){
    					$result[] = $row;
    				}
    			return $result;
    		}
    	}
        
    ?>
    I have been trying to call a query like this but returning 0 rows when i use a variable as the query.

    Code:
    SELECT * FROM `realtyimages` WHERE `rname` = 'pacific Monarch Resorts' AND `rcity` = 'Las Vegas' AND `rstate` = 'NV';
    If i place that string into $this->con->query(SELECT * FROM `realtyimages` WHERE `rname` = 'pacific Monarch Resorts' AND `rcity` = 'Las Vegas' AND `rstate` = 'NV';"); it returns the rows.

    However when i place that string into lets say $query, it returns 0 rows.

    I had to write this a bit fast as work is almost over, but why am I having this problem? I cannot think of anything causing this to be a problem.

    I will be rewriting this thread as soon as i get home. Thanks

  2. #2
    Join Date
    Jan 2014
    Posts
    27
    I am guessing your meant to declare the variable $query, so for example your code would be

    $query =SELECT * FROM `realtyimages` WHERE `rname` = 'pacific Monarch Resorts' AND `rcity` = 'Las Vegas' AND `rstate` = 'NV';
    As I looked throught your code I found that you have not declared the variable $query. It should work once you declare the $query variable and you could declare it around here somewhere

    lass db {

    public $con = null;

    private $server = '127.0.0.1';
    private $user = 'root';
    private $pass = '';
    private $database = 'sites';

    public function __construct(){
    $this->con = new MySQLi($this->server, $this->user, $this->pass, $this->database);
    if ($this->con->connect_errno > 0){
    die('Error: Unable to connect to database ['.$this->con->connect_error.']');
    }
    }

  3. #3
    Join Date
    Oct 2013
    Posts
    25
    I do not think that is the problem. I am calling this class from another file.

    Here is my Database connection class:
    Code:
    <?php
        class db {
    	
    		public $con = null;
    		
    		private $server = '127.0.0.1';
    		private $user = 'root';
    		private $pass = '';
    		private $database = 'sites';
    
    		public function __construct(){
    			$this->con = new MySQLi($this->server, $this->user, $this->pass, $this->database);
    				if ($this->con->connect_errno > 0){
    					die('Error: Unable to connect to database ['.$this->con->connect_error.']');
    				}
    		}
    		
    		public function __distruct(){
    			$this->con->close();
    		}
    
    		public function search($value){
    			$query = $value;
    			
    			$query = $this->con->query($query);
    			
    
    			$result = array();
    				while ($row = $query->fetch_assoc()){
    					$result[] = $row;
    				}
    			return $result;
    		}
    	}
    ?>
    Then inside another class file, I have a function which creates the query string.

    Code:
    private function getQuery($data){
    		// Takes raw data and creats image(s) query to search for listing resort...
    			$listing = $data['listing'];
    			$rname = $data['rname'];
    			$rcity = $data['rcity'];
    			$rstate = $data['rstate'];
    				$query = "SELECT * FROM `realtyimages` WHERE rname = '$rname' AND rcity = '$rcity' AND rstate = '$rstate';";
    			return $query;
    	}
    Then I can the database query via the __construct of this listing class which is in a different file.

    Code:
    public function __construct($data){
    		$listing = $data['listing'];
    		$rname = $data['rname'];
    		$rcity = $data['rcity'];
    		$rstate = $data['rstate'];
    		$Used = 0;
    		$UsedTimes = 0;
    		$con = new db;
    		$result = $con->search($this->getQuery($data));
    		
    		print_r(count($result));
    }
    I can pass the query to db->search and it echos correctly with no sql syntax errors, If i place it directly in as a string without passing it as a variable it works fine.

  4. #4
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    752
    Can we see the example with the use of the var?

  5. #5
    Join Date
    Oct 2013
    Posts
    25
    I believe that is the example I just posted? in the __construct I call the query by $con->search($this->getQuery($data)); it gets the query from $this->getQuery() and then sends it to the search function in the db class.

    I do not fully understand what you are wanting to see as an example?

  6. #6
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    752
    Could the error in this statement be the issue?
    PHP Code:
    $query "SELECT * FROM `realtyimages` WHERE rname = '$rname' AND rcity = '$rcity' AND rstate = '$rstate';"

  7. #7
    Join Date
    Oct 2013
    Posts
    25
    No doesn't seem to be the problem. I echoed that statement, then I copied and pasted it into the database. It worked correctly and returned 58 rows.

    That $query statement echoes

    PHP Code:
    SELECT FROM `realtyimagesWHERE rname 'Pacific Monarch Resorts' AND rcity 'Las Vegas' AND rstate 'NV'
    If I use that string when calling the query like

    PHP Code:
        $result $con->search("SELECT * FROM `realtyimages` WHERE rname = 'Pacific Monarch Resorts' AND rcity = 'Las Vegas' AND rstate = 'NV';"); 
    It works correctly and returns 58 rows.

    Now if I do lets say

    PHP Code:
    $query "SELECT * FROM `realtyimages` WHERE rname = 'Pacific Monarch Resorts' AND rcity = 'Las Vegas' AND rstate = 'NV';";
    $result $con->search($query); 
    it also works, and returns 58 rows.

    Now if i do this...

    PHP Code:
    $query $this->getQuery($data);
    $result $con->search($query); 
    It does not work.

    Now what i did was

    PHP Code:
    $test $this->getQuery($data);
    $test2 "SELECT * FROM `realtyimages` WHERE rname = 'Pacific Monarch Resorts' AND rcity = 'Las Vegas' AND rstate = 'NV';";
    echo 
    $test "<br>" $test2
    the results was

    Code:
    SELECT * FROM `realtyimages` WHERE rname = 'Pacific Monarch Resorts' AND rcity = 'Las Vegas' AND rstate = 'NV';
    SELECT * FROM `realtyimages` WHERE rname = 'Pacific Monarch Resorts' AND rcity = 'Las Vegas' AND rstate = 'NV';
    Both are the same sql string, and both work. I cannot understand why wont it allow the string returned by the getQuery function to be used in the query... I know I am missing something just need help figuring it out.

  8. #8
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    752
    And you get no error codes? No exception? Do you have error reporting turned on and do you check each function call?

  9. #9
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    752
    PS - It appears you declared your own destructor function but you misspelled it. If it matters.

  10. #10
    Join Date
    Oct 2013
    Posts
    25
    I fixed the spelling on the destruct function. It didn't change anything but thanks for pointing that out!!!

    Error reporting is turned on and not coming up with anything, no other errors and all functions seem to be working correctly like they should.

    could it be a problem with how the getQuery() is returning the query?

  11. #11
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    752
    When in doubt echo it out

    BTW - seems like an awful lot of obfuscation to produce a query and get results.

  12. #12
    Join Date
    Oct 2013
    Posts
    25
    The reason for it, is I am running a script that gets emails and extracts the listing number, resort name, resort city, resort state. It then pulls the information from the database and automatically assigns the resort image(s) to be listed onto our website. So the query must be dynamic for each listing.

    I tried removing the getQuery() function and just make it a simple string like:

    PHP Code:
    $listing $data['listing'];
            
    $rname $data['rname'];
            
    $rcity $data['rcity'];
            
    $rstate $data['rstate'];
            
    $Used 0;
            
    $UsedTimes 0;
            
    $con = new db;

            
    $query "SELECT * FROM `realtyimages` WHERE rname = '" $rname "' AND rcity = '" $rcity "' AND rstate = '" $rstate "';";

            
    $result $con->search($query);
            
            
    print_r(count($result)); 
    yes it still the query returns 0 rows. I am going to start rewriting a few things and see if that could fix the problem.

    I even tried passing the $rname, $rcity, $rstate to the search function and then create the query but no luck. It seems to be when I try to make a sql query with variables. Since I am about to go to lunch, when i get back and change the database class to use MySQLi prepared statement and see if that helps at all.

  13. #13
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    752
    Do you ever check the results of the line that calls query($query)?
    PHP Code:
    public function search($value)
    {
        
    $query $value;
        
    $query $this->con->query($query);    // how do you know this ran???
        
    $result = array();
        while (
    $row $query->fetch_assoc())
        {
            
    $result[] = $row;
        }
        return 
    $result;


  14. #14
    Join Date
    Oct 2013
    Posts
    25
    Checked it and checked it again. It is running the query, it is just returning 0 results for it. If there is a variable in the query it returns 0 results, if i hard write the values into the sql string it returns 58 results. So it is a variable issue in the sql string. I gave PDO a try and same thing is happening... I have been stuck on this for a few days now and it is slowly eating me alive lol.

    I am running out of ideas... :/

  15. #15
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,361
    I would tweak the search() method to something like this, so you can see if the query is really going in as you think it is:
    PHP Code:
        // note change of input parameter just to help me keep names separated
        
    public function search($sql){        
            
    $query $this->con->query($sql);
            if(
    $query == false) {
                throw new 
    Exception("Query failed:".PHP_EOL.$this->con->error.PHP_EOL.$sql);
            }
            if(
    $query->numRows == 0) {
                
    // need E_NOTICE errors enabled to see this,
                // on screen if display_errors is on, else in PHP error log
                
    trigger_error("Query returned 0 rows:".PHP_EOL.$sql);
            }
            
    $result = array();
                while (
    $row $query->fetch_assoc()){
                    
    $result[] = $row;
                }
            return 
    $result;
        } 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles