Jump to content

Trying To Use Mysqli_stmt_bind_param For The First Time


Ilo
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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')

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...