Trying To Use Mysqli_stmt_bind_param For The First Time

[Ilo]

HelloI have the following script that I am trying to improve:

<?PHP	$firstname=$_POST['firstname'];	$lastname=$_POST['lastname'];	$email=$_POST['email'];	$location=$_POST['location'];	$phone=$_POST['phone'];	date_default_timezone_set('GMT');	$date=date("d/m/Y");	$time=date("H:i:s");	$ip=$_SERVER['REMOTE_ADDR'];$host = '';$user = '';$password = '';$dbName = '';$conn = mysql_connect($host, $user, $password) or die(mysql_error());$db = mysql_select_db($dbName, $conn) or die(mysql_error());				$query = "SELECT air_email FROM irecords WHERE air_email = '$email'";			$result = mysql_query($query);						if(mysql_num_rows($result) > 0)			{				echo ("The email address you have submitted is already in the database. <a href=\"index.php\">index</a>");			} 			else			{				mysql_query("INSERT INTO irecords (						air_firstname,						air_lastname,						air_email,						air_location,						air_phone,						air_date,						air_time,						air_ip					) VALUES (						"$firstname",						"$lastname",						"$email",						"$location",						"$phone",						"$date",						"$time",						"$ip"					)") or die(mysql_error());				echo ("Your information has been successfully added to the database. <a href=\"index.php\">index</a>");			}?>

Now the above works ok with a normal html form, etc... except for the fact it is easy to hack and does not work with names that have accents.Anyhow a friend recommended I look at:http://uk.php.net/manual/en/mysqli.prepare.phphttp://uk.php.net/manual/en/mysqli-stmt.bind-param.phpThe only problem is i have no idea of how to get that to work!I did the following:

<?php$mysqli = new mysqli('localhost', '', '', '');/* check connection */if (mysqli_connect_errno()) {	printf("Connect failed: %s\n", mysqli_connect_error());	exit();}	$firstname="Tom";	$lastname="Test";	$email="test@test.com";	$location="Cork";	$phone="12345";	date_default_timezone_set('GMT');	$date=date("d/m/Y");	$time=date("H:i:s");	$ip=$_SERVER['REMOTE_ADDR'];$stmt = $mysqli->prepare("INSERT INTO irecords VALUES (?, ?, ?, ?, ?, ?, ?, ?)");$stmt->bind_param('ssssssss', $firstname, $lastname, $email, $location, $phone, $date, $time, $ip);/* execute prepared statement */$stmt->execute();printf("%d Row inserted.\n", $stmt->affected_rows);echo("%d Row inserted.\n", $stmt->affected_rows);/* close statement and connection */$stmt->close();/* close connection */$mysqli->close();?>

tested but i can't get it to add anything to the database, i am quite sure I am doing something wrong.Could someone help??Thank in advance

[Ilo]

I have also tried the following:

<?php$host="localhost"; // Host name$username=""; // Mysql username$password=""; // Mysql password$db_name="irishalt"; // Database name$tbl_name="ilocation"; // Table name// Connect to server and select databse.mysqli_connect("$host", "$username", "$password")or die("cannot connect");mysqli_select_db("$db_name")or die("cannot select DB");?>

and the error message is : cannot select DBthese setting work fine with the original script so i don't understand why it's a problem with this setup.

[Ilo]

have done some changes to the script, i know the part for listing the information works (have it working somewhere else)however I am still unable to upload to the database

<?php$mysqli = new mysqli("localhost", "user", "pass", "db");/* check connection */if (mysqli_connect_errno()) {	printf("Connect failed: %s\n", mysqli_connect_error());	exit();}$stmt = $mysqli->prepare("INSERT INTO table VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");$stmt->bind_param('sssssssss', $interest, $firstname, $lastname, $email, $location, $phone, $date, $time, $ip);	$interest = "";	$firstname = "";	$lastname = "";	$email = "";	$location = "";	$phone = "";	$date = "";	$time = "";	$ip = "";/* execute prepared statement */$stmt->execute();printf("%d Row inserted.\n", $stmt->affected_rows);/* close statement and connection */$stmt->close();/* prepare statement */if ($stmt = $mysqli->prepare("SELECT * FROM table")) {	$stmt->execute();	/* bind variables to prepared statement */	$stmt->bind_result($air_firstname, $air_interst);	/* fetch values */	while ($stmt->fetch()) {		printf("%s is %s\n", $air_firstname, $air_interst);	}	/* close statement */	$stmt->close();}/* close connection */$mysqli->close();?>

I would normally have some default values set just to see what happens however at the moment it does nothing, does not display the affected rows or the database.

[justsomeguy]

Is the lack of default values the problem? Does your database have required fields? Check if the return value of execute is false and print the mysql error message if so.

[Ilo]

There are default values sorry i just removed them before posting

$stmt = $mysqli->prepare("INSERT INTO irecords VALUES (air_interest, air_firstname, air_lastname, air_email, air_location, air_phone, air_date, air_time, air_ip)");$stmt->bind_param('sssssssss', $interest, $firstname, $lastname, $email, $location, $phone, $date, $time, $ip);	$interest = "looking";	$firstname = "Test";	$lastname = "test";	$email = "test@test.com";	$location = "test";	$phone = "12345";	$date = "test";	$time = "test";	$ip = "test";

Change the question marks to the actual field names, I also have an air_id field that is not there but has an auto-increment on it so i don't bother adding it usually, guess it may be an issue in this caseDon't get an error message at all just a blank page, is there any way I could change it to generate one?Thanks

[justsomeguy]

MySQL does not automatically show errors, you have to check for them. The execute method returns false if the query failed, and there's another function to print the error.Leave the question marks there, that's where they go. You need to list the field names though, you can't just leave out a column if you're not giving the list of columns. You need to tell it which ones you're giving and which you aren't. e.g.:INSERT INTO table (field1, field2, field3) VALUES ('value1', 'value2', 'value3')

[Ilo]

I have changed it to this

$stmt = $mysqli->prepare("INSERT INTO irecords VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");$stmt->bind_param('ssssssssss', $id, $interest, $firstname, $lastname, $email, $location, $phone, $date, $time, $ip);	$id = "";	$interest = "test";	$firstname = "test";	$lastname = "test";	$email = "test@test.com";	$location = "Cork";	$phone = "12345";	$date = "test";	$time = "test";	$ip = "test";

Following what you said i specified the id field, added one question mark, added the ID and the last pieceone question I have is what do the sssssss stand for, I saw a brief explanation but i don't really get it. I'm assuming you change them depending on the field? Some of my entries are going to be only numbers so should i change?Thanks for the help

[justsomeguy]

The format string is explained here:http://www.php.net/manual/en/mysqli-stmt.bind-param.phpIf your ID is an autonumber, you should not use it in the insert statement. Instead just list the columns you're using like I showed.