www.webdeveloper.com
Results 1 to 9 of 9

Thread: Failed to prepared the statement!

Hybrid View

  1. #1
    Join Date
    Dec 2011
    Posts
    20

    Failed to prepared the statement! MySQL

    So I have a problem with prepare statement, at all everything works, but it won't work if I bind $table, any suggestions?
    execSQL("SELECT id FROM ? WHERE id=?", array('si', $table, $u_id), style::Q_NUM_ROWS)
    Code:
    const Q_GET_VALUE = 0;
    const Q_NUM_ROWS = 1;
    const Q_AFFECTED_ROWS = 2;
    
    function user_exists($u_id, $table)
    {
    	if ( $this->execSQL("SELECT id FROM ? WHERE id=?", array('si', $table, $u_id), style::Q_NUM_ROWS) != 0 )
    		return true;
    		
    	return false;
    }
    	function execSQL($sql, $params, $exec_type)
    	{
    		$stmt = $this->db_connect->prepare($sql) or die ("Failed to prepared the statement!");
            
    		if ( !empty($params) )
    			call_user_func_array(array($stmt, 'bind_param'), $this->refValues($params));
        
    		$stmt->execute();
                
    		if ( $exec_type == self::Q_GET_VALUE ) 
    		{
    			$parameters = array();
    		
    			$meta = $stmt->result_metadata();
                 
    			while ( $field = $meta->fetch_field() ) {
    				$parameters[] = &$row[$field->name];
    			}  
             
    			call_user_func_array(array($stmt, 'bind_result'), $this->refValues($parameters));
                    
    			$stmt->fetch();
    		
    			$result = $row;
    		}
    		else if( $exec_type == self::Q_NUM_ROWS)
    		{
    			$stmt->store_result();
    			$result = $stmt->num_rows;
    		} 
    		else
    		{
    			$result = $stmt->affected_rows;
    		}
                
    		$stmt->close();
    
    		return $result;
    	}
    function refValues($arr)
    	{
    		if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
    		{
    			$refs = array();
    			foreach($arr as $key => $value)
    				$refs[$key] = &$arr[$key];
    				
    			return $refs;
    		}
    		
    		return $arr;
    	}
    Last edited by xbatista; 10-25-2012 at 12:58 PM.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,616
    Not sure which DB extension you're using, but I know at least some do not allow bound parameters for table/column names, in which case you'd just have to embed that variable directly into the SQL string.
    "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

  3. #3
    Join Date
    Dec 2011
    Posts
    20
    MySQL 5.5, table engine MylSAM, so what I suppose to do?

  4. #4
    Join Date
    Oct 2010
    Location
    Ohio
    Posts
    233
    I think by engine he meant whether you're using mysqli, pdo, or something else all together. MySQLI and PDO do not support bound table/column names at all. See http://us3.php.net/manual/en/book.pdo.php#69304

  5. #5
    Join Date
    Dec 2011
    Posts
    20
    Mysqli.
    I'm know what you're saying, so what I suppose to do change 'FROM ?' to 'FROM $table' or how?
    Last edited by xbatista; 10-25-2012 at 03:11 PM.

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,616
    Yeah, you'll have to put the variable for the table name directly in the string:
    PHP Code:
    "SELECT * FROM `$table` WHERE . . ." 
    We do something similar all the time at work with PDO using PostgreSQL, where the schema name is dynamic:
    PHP Code:
    $sql "SELECT * FROM {$this->dbSchema}.table_name . . ." 
    "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

  7. #7
    Join Date
    Dec 2011
    Posts
    20
    execSQL("SELECT id FROM `$table` WHERE id=?", array('i', $u_id), style::Q_NUM_ROWS)
    So it won't break something if i put a variable name in prepare statement?

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,616
    Should be fine, with the exception that if the table name comes from user input or some other external source, then you'll need to sanitize it to make sure no SQL injection attacks/errors occur. With MySQLi, you can use the mysqli::real_escape_string() method.
    "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

  9. #9
    Join Date
    Dec 2011
    Posts
    20
    Thank you for the answers.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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