Prepared statement using PHP

Question

I'm new to PHP .. I get stuck on how to transfer my sql statement to prepared statement .. The error message that I got is that you " can't connect " ..

My code is as the following

$connection = mysql_connect($host,$username,$password) or die ("can't connect"); 


$select_database = mysql_select_db($db_name);
$id = mysql_real_escape_string ($_GET ['id']); 

$query = 'SELECT * from &tbl_name where id=?';
$stmt = $connection->prepare($query);
$stmt->bind_param("d", $id);
$stmt->execute();
$rows=mysql_fetch_array($stmt);
$stmt->close();

$connecton->prepare Typo: $connection, but youre also mix deprecated mysql functions with PDO or mysqli? — pce, Nov 13 '12 at 13:54
You have an invalid mix of `mysql_*()` and MySQLi. Read the [MySQLi documentation](http://php.net/manual/en/book.mysqli.php) for the correct connection and usage method. — Michael Berkowski, Nov 13 '12 at 13:55

newfurniturey · Answer 1 · 2012-11-13T14:00:01.663

You have two problems actually.

The first, and relevant to your question, is on this line:

$connection = mysql_connect($host,$username,$password) or die ("can't connect");

You say you're receiving the error can't connect. This means that the host, username and/or password for your database connection is invalid. Check that the connection information is correct and you should be able to fix the issue.

The second is that you're connecting to your database with mysql_ functions and then trying to use mysqli_ binding/executing functions. You can't mix and match.

Because you're attempting to bind/execute with OOP style, here's a re-coded sample that should help out:

$connection = new mysqli($host, $username, $password);
if ($connection->connect_error) {
    die("can't connect");
}
$query = 'SELECT * from tbl_name where id=?';
$stmt = $connecton->prepare($query);
$stmt->bind_param("d", $id);
$results = $stmt->execute();
$rows = $results->fetch_array();
$stmt->close();

`$stmt->execute()` returns a boolean, so you need to use `bind_result()` instead. — Boden Garman, Nov 10 '14 at 01:32
here,will the `$stmt->close()` automatically closes the mysql connection or do we need to close it manually? — Abhinav, Dec 28 '17 at 06:42

score 2 · Answer 2 · answered Nov 13 '12 at 13:56

2

You cannot do prepared statements with the legacy mysql_* set of functions. You need to use PDO or MySQLi:

$dbh = new PDO("mysql:dbname=testdb;host=127.0.0.1", "username", "password");

$query = "SELECT * FROM myTable WHERE id=?";
$stmt = $dbh->prepare($query);
$stmt->execute(array($my_id));

$result = $stmt->fetchAll();

answered Nov 13 '12 at 13:56

Jeremy Harris

24,318
13
79
133

But you can use with mysqli, should not force user to use PDO when he/she can use just the new mysqli driver. – StormByte Nov 13 '12 at 13:56
The OP _is_ using MySQLi, just incorrectly (by mixing with `mysql_*()`) – Michael Berkowski Nov 13 '12 at 13:56
What good is throwing mysqli functions in when your entire (broken) connection is based on mysql_*. – Jeremy Harris Nov 13 '12 at 13:58

score 1 · Answer 3 · answered Nov 13 '12 at 13:56

The "can't connect" you have comes from the die() function which will stop execution of your script if mysql_connect fails. This means that your code doesn't succeed creating a connection, check your $host, $username and $password parameters. If your host is remote, make sure that you have the rights to connect to it.

As pce stated, you also have a typo in $connecton which should be $connection

IEnumerable · Answer 4 · 2012-11-13T14:37:44.037

0

try

1.Define the Database const for later use in your project; (this code should only be executed once)

define( "DBN","foo");//where foo is the database name
define( "DB_USERNAME", "rootuser" ); //generally root
define( "DB_PASSWORD", "my_very_hard_password" ); //be more creative
define( "DB_DSN", "mysql:host=localhost;dbname=".DBN );

...

2.Create PDO Object and execute

$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "SELECT * from &tbl_name where id=:id";
$st = $conn->prepare( $sql );
$st->bindValue( ":id", $obj->id, PDO::PARAM_INT );  
// or $st->bindValue( ":id", 5, PDO::PARAM_INT );   
$st->execute();
return $st->fetchAll();

edited Nov 13 '12 at 14:37

answered Nov 13 '12 at 13:57

IEnumerable

3,610
14
49
78

I got this message when I tried the above code :Fatal error: Uncaught exception 'PDOException' with message 'invalid data source name' in F:\wwwroot\ultitecwww\login.php:35 Stack trace: #0 F:\wwwroot\ultitecwww\login.php(35): PDO->__construct('', NULL, 'password') #1 F:\wwwroot\ultitecwww\login.php(7): loginnow() #2 {main} thrown in F:\wwwroot\ultitecwww\login.php on line 35 – user1743710 Nov 13 '12 at 14:28
My bad, I think you passed blank to the database connection string, see my above edit. thanks – IEnumerable Nov 13 '12 at 14:32
Also as mentioned in above comments, "can't connect" is purely a invalid credentials or database location. double check your server (im assuming localhost). But use PDO, there is no need to use mysql_real_escape_string() when using PDO and bindValue – IEnumerable Nov 13 '12 at 14:48

Prepared statement using PHP

4 Answers4