should I use prepared statements for selecting from a database

rich_web_development · September 20, 2016

Hi,

I've been following the php prepared statements on the w3schools website http://www.w3schools.com/php/php_mysql_prepared_statements.asp

I have made amendments to my code (well, not really mine as most of it I have had help with from this forum :rofl: ) - on line 47 - to use prepared statements when INSERTING info from a user to MySQL, as follows:

<!DOCTYPE html>
<head>
</head>
<html>
<body>

<form action="PTestPreStmnt.php" method="POST"><pre>
Name          <input type="text" name="name">
Bedrooms      <input type="text" name="bedrooms">
Length        <input type="text" name="length">
Width         <input type="text" name="width">
Serial Number <input type="text" name="serialno">
<input type="submit" value="ADD RECORD">
</pre>
</form>
<br><br><br>

<?php
require_once 'login.php';
$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);

if (isset($_POST['delete'])&&
	isset($_POST['serialno']))
	{
		$serialno = get_post($conn, 'serialno');
		$query = "DELETE FROM holidayhomes WHERE serialno='$serialno'";
		$result = $conn->query($query);
		if (!$result) echo "DELETE failed: $query<br>" . $conn->error . "<br><br>";
		
	}

if (isset ($_POST['name']) &&
	isset ($_POST['bedrooms']) &&
	isset ($_POST['length']) &&
	isset ($_POST['width']) &&
	isset ($_POST['serialno']))


	{
		$name     = get_post ($conn, 'name');
		$bedrooms = get_post ($conn, 'bedrooms');
		$length   = get_post ($conn, 'length');
		$width    = get_post ($conn, 'width');
		$serialno = get_post ($conn, 'serialno');
		// $query    = "INSERT INTO holidayhomes (name, bedrooms, length, width, serialno) VALUES ('$name','$bedrooms', '$length', '$width', '$serialno')";
		//PREPARED STATEMENT
		$stmt = $conn->prepare("INSERT INTO holidayhomes (name, bedrooms, length, width, serialno)VALUES(?,?,?,?,?)");
		$stmt->bind_param("sssss", $name, $bedrooms, $length, $width, $serialno);
		
		$stmt->execute();

        $result = $stmt->get_result();

		//$result = $conn->query($query);
		//if (!$result) echo "INSERT failed: $query<br>" . $conn->error . "<br><br>";

    }
if(isset($_POST["submit"])){
$target_dir = "uploads/";
$target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
$uploadOk = 1;
$imageFileType = pathinfo($target_file, PATHINFO_EXTENSION);
}
if(isset($_POST["submit"])){
$check = getimagesize($_FILES["fileToUpload"]["tmp_name"]);
if($check !== false){
// echo "FIle is an image - " . $check["mime"] . "." ;
$uploadOk = 1;
} else {
// echo "File is not an image.";
$uploadOk = 0;
}
}
// if ($uploadOk == 0){
// // echo "Sorry, your file was not uploaded.";
// } else {
if (isset($_POST["submit"])){
(move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file));
}
// echo "The file " . basename( $_FILES["fileToUpload"]["name"]) . " has been uploaded.";
// else {
// echo "Hello!";
// }
// }


if(isset($_POST["submit"])){
$mainpic = basename( $_FILES["fileToUpload"]["name"]); 
$serialno = $_POST['serialno'];
$query = "UPDATE holidayhomes SET mainpic='$mainpic' WHERE serialno='$serialno'";
$result = $conn->query($query);
}
    //SHOULD I USE A PREPARED STATEMENT HERE?
    $query = "SELECT * FROM holidayhomes";
    $result = $conn->query($query);
    if (!$result) die ("Database access failed: " . $conn->error);

    $rows = $result->num_rows;
    for ($j = 0 ; $j < $rows ; ++$j)
    {
    	$result->data_seek($j);
    	$row = $result->fetch_array(MYSQLI_NUM);
    	
echo <<<_END
<pre>
name       $row[0]
Bedrooms   $row[1]
Length     $row[2]
Width      $row[3]
Serial No  $row[10]
MainPic    $row[4]
<img src="uploads/$row[4]" width=200 height=200>
</pre>

<pre>
<form action="PTestPreStmnt.php" method="POST">
<input type="hidden" name="delete" value="yes">
<input type="hidden" name="serialno" value="$row[10]">
<input type="submit" value="DELETE RECORD">
</form>
</pre>


<pre>
<form action="PTestPreStmnt.php" method="post" enctype="multipart/form-data">
Select main photo:
<input type="hidden" name="serialno" value="$row[10]">
<input type="file" name="fileToUpload" id="fileToUpload">
<input type="submit" value="Upload Image" name="submit">
</form>
</pre>
<br><br>
_END;

    }

    
$result->close();
$conn->close();

function get_post($conn, $var)
{
	return $conn->real_escape_string($_POST[$var]);
}
?>
</body>
</html>

I was just wondering if I should use a prepared statement on the part that selects all from the database to be displayed - line 94 - ? If I do need to use prepared statements when information from the database could someone be so kind as to advise me on how to go about achieving this. I have tried but just cannot work it out. I noticed on the next section on the w3schools website it has a bit about 'select data' and in that part there is a bit for select data using prepared statements but only for PDO http://www.w3schools.com/php/php_mysql_select.asp. If anyone could help I would be extremely grateful.

Oh yeah, love the new templates http://www.w3schools.com/w3css/w3css_templates.asp

Edited September 20, 2016 by rich_web_development

Ingolme · September 20, 2016

You don't need prepared statements unless there's user input.

You wouldn't need it for SELECT * FROM table, but you would need it for SELECT * FROM table WHERE field = $something

justsomeguy · September 20, 2016

Even so, it's good practice to always use them, it will help you get in the habit.

rich_web_development · September 21, 2016

Even so, it's good practice to always use them, it will help you get in the habit.

I have tried lots of different combinations to try and use prepared statements with the 'SELECT * FROM' part on line 95. I just cannot work out how to use prepared statements on this 'SELECT * FROM' part.

I have tried replacing from line 95 to 97 with the following:

$stmt = $conn->prepare("SELECT (name, bedrooms, length, width, mainpic, serialno) FROM holidayhomes VALUES (?,?,?,?,?,?)");
$stmt->bind_param("ssssss", $name, $bedrooms, $length, $width, $mainpic, $serialno);
		
$stmt->execute();

$result = $stmt->get_result();

When I replace line 95 to 97 with the above code I get the following error:

Fatal error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\PTestPreStmnt02.php on line *the line that the bind_param is on

If you could show me how to get the prepared statement working when using SELECT from the database I would be very grateful.

davej · September 21, 2016

Where do you see any parens in the below examples?

http://www.w3schools.com/sql/sql_select.asp

rich_web_development · September 21, 2016

Where do you see any parens in the below examples?

http://www.w3schools.com/sql/sql_select.asp

I still get the same error if I remove the parens

$stmt = $conn->prepare("SELECT name, bedrooms, length, width, mainpic, serialno FROM holidayhomes VALUES (?,?,?,?,?,?)");
$stmt->bind_param("ssssss", $name, $bedrooms, $length, $width, $mainpic, $serialno);
		
$stmt->execute();

$result = $stmt->get_result();

Ingolme · September 21, 2016

The VALUES() keyword is only used in INSERT queries. You don't need it for SELECT queries, just do this:

$stmt = $conn->prepare("SELECT name, bedrooms, length, width, mainpic, serialno FROM holidayhomes");
$stmt->execute();

This looks like MySQLi, so you might need to use bind_result() to get the data. I prefer PDO, which lets you retrieve results from a prepared statement in an associative array.

rich_web_development · September 29, 2016

The VALUES() keyword is only used in INSERT queries. You don't need it for SELECT queries, just do this:
$stmt = $conn->prepare("SELECT name, bedrooms, length, width, mainpic, serialno FROM holidayhomes");
$stmt->execute();
This looks like MySQLi, so you might need to use bind_result() to get the data. I prefer PDO, which lets you retrieve results from a prepared statement in an associative array.

Thank you very much. I think I have it now. Well, it works anyway. Although where should I place the '$result->close();

$conn->close();' within the below code?

I have changed the code as follows:

<?php
require_once 'login.php';
$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);

if (isset($_POST['delete'])&&
isset($_POST['serialno']))
{
$serialno = mysql_entities_fix_string($conn, 'serialno');
$query = "DELETE FROM holidayhomes WHERE serialno='$serialno'";
$result = $conn->query($query);
if (!$result) echo "DELETE failed: $query<br>" . $conn->error . "<br><br>";

}

if (isset ($_POST['name']) &&
isset ($_POST['bedrooms']) &&
isset ($_POST['length']) &&
isset ($_POST['width']) &&
isset ($_POST['serialno']))


{
$name     = mysql_entities_fix_string ($conn, 'name');
$bedrooms = mysql_entities_fix_string ($conn, 'bedrooms');
$length   = mysql_entities_fix_string ($conn, 'length');
$width    = mysql_entities_fix_string ($conn, 'width');
$serialno = mysql_entities_fix_string ($conn, 'serialno');
// $query    = "INSERT INTO holidayhomes (name, bedrooms, length, width, serialno) VALUES ('$name','$bedrooms', '$length', '$width', '$serialno')";

$stmt = $conn->prepare("INSERT INTO holidayhomes (name, bedrooms, length, width, serialno)VALUES(?,?,?,?,?)");
$stmt->bind_param("sssss", $name, $bedrooms, $length, $width, $serialno);

$stmt->execute();

$result = $stmt->get_result();

//$result = $conn->query($query);
//if (!$result) echo "INSERT failed: $query<br>" . $conn->error . "<br><br>";

}
if(isset($_POST["submit"])){
$target_dir = "uploads/";
$target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
$uploadOk = 1;
$imageFileType = pathinfo($target_file, PATHINFO_EXTENSION);
}
if(isset($_POST["submit"])){
$check = getimagesize($_FILES["fileToUpload"]["tmp_name"]);
if($check !== false){
// echo "FIle is an image - " . $check["mime"] . "." ;
$uploadOk = 1;
} else {
// echo "File is not an image.";
$uploadOk = 0;
}
}
// if ($uploadOk == 0){
// // echo "Sorry, your file was not uploaded.";
// } else {
if (isset($_POST["submit"])){
(move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file));
}
// echo "The file " . basename( $_FILES["fileToUpload"]["name"]) . " has been uploaded.";
// else {
// echo "Hello!";
// }
// }


if(isset($_POST["submit"])){
$mainpic = basename( $_FILES["fileToUpload"]["name"]); 
$serialno = $_POST['serialno'];
$query = "UPDATE holidayhomes SET mainpic='$mainpic' WHERE serialno='$serialno'";
$result = $conn->query($query);
}

// $query = "SELECT * FROM holidayhomes";
// $result = $conn->query($query);
// if (!$result) die ("Database access failed: " . $conn->error);

// $rows = $result->num_rows;
// for ($j = 0 ; $j < $rows ; ++$j)
// {
// $result->data_seek($j);
// $row = $result->fetch_array(MYSQLI_NUM);
// }

// $result->close();
// $conn->close();

// function get_post($conn, $var)
// {
// 	return $conn->real_escape_string($_POST[$var]);
// }
function mysql_entities_fix_string($conn, $var){
return htmlentities(mysql_fix_string($conn, $var));
}
function mysql_fix_string($conn, $var){
if (get_magic_quotes_gpc()) $var = stripslashes($var);
return $conn->real_escape_string($_POST[$var]);
}
?>

<!DOCTYPE html>
<head>
</head>
<html>
<body>

<form action="PTestPreStmnt05.php" method="POST"><pre>
Name          <input type="text" name="name">
Bedrooms      <input type="text" name="bedrooms">
Length        <input type="text" name="length">
Width         <input type="text" name="width">
Serial Number <input type="text" name="serialno">
<input type="submit" value="ADD RECORD">
</pre>
</form>
<br><br><br>



    
<?php 
// $query = "SELECT * FROM holidayhomes";
// $result = $conn->query($query);
// if (!$result) die ("Database access failed: " . $conn->error); 
$stmt = $conn->prepare("SELECT name, bedrooms, length, width, mainpic, serialno FROM holidayhomes");
$stmt->execute();
$stmt->bind_result($name, $bedrooms, $length, $width, $mainpic, $serialno);

$result = $stmt->get_result();  	
$rows = $result->num_rows;
for ($j = 0 ; $j < $rows ; ++$j)
{
$result->data_seek($j);
$row = $result->fetch_array(MYSQLI_NUM);
echo <<<_END
<pre>
Name:       $row[0]
Bedrooms:   $row[1]
Length:     $row[2]
Width:      $row[3]
Serial No:  $row[5]
MainPic:    $row[4]
<img src="uploads/$row[4]" width=200 height=200>
</pre>
_END;
}
$result->close();
$conn->close();
?>

<pre>
<form action="PTestPreStmnt05.php" method="POST">
<input type="hidden" name="delete" value="yes">
<input type="hidden" name="serialno" value="<?php echo $row[5]?>">
<input type="submit" value="DELETE RECORD">
</form>
</pre>


<pre>
<form action="PTestPreStmnt05.php" method="post" enctype="multipart/form-data">
Select main photo:
<input type="hidden" name="serialno" value="<?php echo $row[5]?>">
<input type="file" name="fileToUpload" id="fileToUpload">
<input type="submit" value="Upload Image" name="submit">
</form>
</pre>
<br><br>



</body>
</html>

Edited September 29, 2016 by rich_web_development

Sign In

should I use prepared statements for selecting from a database

Recommended Posts

rich_web_development

Link to comment

Share on other sites

Ingolme

Link to comment

Share on other sites

justsomeguy

Link to comment

Share on other sites

rich_web_development

Link to comment

Share on other sites

davej

Link to comment

Share on other sites

rich_web_development

Link to comment

Share on other sites

Ingolme

Link to comment

Share on other sites

rich_web_development

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Browse

Activity