INSERT into MySQL DB using prepared statements

PHP
1

Hi,
I’m having difficulty inserting data into database using prepared statement the OOP way. I can’t figure out what the error is about. the code is as follows:


//connection.
$conn = new mysqli($host,$user,$pass,$db);
        //prepare SQL
	    $sql = "INSERT INTO event (organiserID,roomID,event_title,date,start_time,end_time,total_hours, description)
		VALUES (?,?,?,?,?,?,?,?)";
		
		// Create statement object
		$stmt = $conn->stmt_init();
		
		// Create a prepared statement
		$stmt->prepare($sql);
			
		// Bind variables to replace the ?s
		$stmt->bind_param('isssssis', $organiserID,$room,$event_title,$event_date,$start_time,$end_time,$total_hours,$description);
		
		// Execute query
		$stmt->execute();
		
                //check affected rows
		if($stmt->num_rows){
			echo 'Event successfully created.';
		} else {
			echo 'problem';
		}
//close resource
$stmt->close();

And I’m getting this warning:

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: invalid object or resource mysqli_stmt in /Users/me/Sites/workspace/event.php on line 62

Warning: mysqli_stmt::execute() [mysqli-stmt.execute]: invalid object or resource mysqli_stmt in /Users/me/Sites/workspace/event.php on line 65

Warning: main() [function.main]: Property access is not allowed yet in /Users/me/Sites/workspace/event.php on line 67
problem
Warning: mysqli_stmt::close() [mysqli-stmt.close]: invalid object or resource mysqli_stmt in /Users/me/Sites/workspace/event.php on line 74

thanks

2

Why even use bind?


 
[COLOR=#0000bb]$sql [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#dd0000]"INSERT INTO event (organiserID,roomID,event_title,date,start_time,end_time,total_hours, description) [/COLOR]
[COLOR=#dd0000]      VALUES ([COLOR=#0000bb]$organiserID[/COLOR][COLOR=#007700],[/COLOR][COLOR=#0000bb]$room[/COLOR][COLOR=#007700],'[/COLOR][COLOR=#0000bb]$event_title'[/COLOR][COLOR=#007700],'[/COLOR][COLOR=#0000bb]$event_date'[/COLOR][COLOR=#007700],'[/COLOR][COLOR=#0000bb]$start_time'[/COLOR][COLOR=#007700],'[/COLOR][COLOR=#0000bb]$end_time'[/COLOR][COLOR=#007700],[/COLOR][COLOR=#0000bb]$total_hours[/COLOR][COLOR=#007700],'[/COLOR][COLOR=#0000bb]$description'[/COLOR])"[/COLOR][COLOR=#007700]; [/COLOR]
3

Ok, I tried that but still no success:

//prepare SQL
$sql = “INSERT INTO event (organiserID,roomID,event_title,date,start_time,end_time,total_hours, description)
VALUES (‘$organiserID’,‘$room’,‘$event_title’,‘$event_date’,‘$start_time’,‘$end_time’,‘$total_hours’,‘$description’)”;

	// Create statement object
	$stmt = $conn->stmt_init();
	
	// Create a prepared statement
	$stmt->prepare($sql);
			
	// Execute query
	$stmt->execute();
	
	if($stmt->num_rows){
		echo 'Event successfully created.';
	} else {
		echo 'problem';
	}
	
	// Close statement object
	$stmt->close();

and these are errors

Warning: mysqli_stmt::execute() [mysqli-stmt.execute]: invalid object or resource mysqli_stmt in event.php on line 62

Warning: main() [function.main]: Property access is not allowed yet in event.php on line 64

Warning: mysqli_stmt::close() [mysqli-stmt.close]: invalid object or resource mysqli_stmt in event.php on line 71

4

Sincerely, learn about the advantages of prepared statements before replying this kind of things.
Here is one of the reason to not create a query strings like we were still in the 90s : http://en.wikipedia.org/wiki/SQL_injection#Parameterized_statements

To the OP : put a

if(!$stmt->prepare($sql)){
  die($conn->error);
}

To check if you don’t have an SQL error.

5

try adding


$mysqli = new mysqli($host,$user,$pass,$db_name);
 if($mysqli->connect_error){
    die('Connection error: ('.$mysqli->connect_errno.'): '.$mysqli->connect_error);
        }

to see if you are connecting even