Jump to content

should I use prepared statements for selecting from a database


Recommended Posts

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 by rich_web_development
Link to post
Share on other sites

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

Link to post
Share on other sites

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.

Link to post
Share on other sites

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();
Link to post
Share on other sites

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.

Link to post
Share on other sites

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 by rich_web_development
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...