PHP - Prepare Pdf Data In Byte[] Array.

I'm trying to clean up all my functions with any queries which take dynamic parameters using PDO prepared statements. I originally thought I was using prepared statements and was told later I wasn't so it's been on my to-do list to go and clean them up. I have cleaned up a lot of them and tested them and they are working fine. This one is giving me a problem though..

        /*fetch production data*/
	if( isset( $field ) && isset( $type ) && isset( $value ) ) {

		$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date 
				FROM production_data
				WHERE :field :type :value 
				ORDER BY enterprise, job_number, line_item LIMIT :offset, :records_per_page';
		
		$stmt = $pdo->prepare($sql);
		$stmt->execute( [ 'field' => $field, 'type' => $type, 'value' => $value, 'offset' => $offset, 'records_per_page' => $records_per_page ] );
		
	} else {
		$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date 
				FROM production_data
				ORDER BY enterprise, job_number, line_item LIMIT '. $offset . ', '. $records_per_page;
		
	}

It takes values from some drop downs (I'm using tabulator to generate a table and this part is related to it's pagination functions)...here is the HTML for those elements.
 

                 <div class="table-controls">
			<div class="form-row">
				<div class="col">
                  
					<label for="filter-field" class="col-form-label-sm">Field: </label>
					<select id="filter-field" class="form-control form-control-sm">
					  <option></option>
					  <option value="Job Number">Job Number</option>
					  <option value="Enterprise">Enterprise</option>
					</select>
				</div>
              
				<div class="col">
					<label for="filter-type" class="col-form-label-sm">Type: </label>
					<select id="filter-type" class="form-control form-control-sm">
					  <option value="like" selected="selected">Like</option>
					  <option value="=">Equal to</option>
					</select>
				</div>
              
				<div class="col">
					<label for="filter-value" class="col-form-label-sm">Value: </label>
					<input id="filter-value" class="form-control form-control-sm" style="float: left;" type="text" placeholder="Value to filter...">
				</div>
              
				<div class="col d-flex align-content-end flex-wrap">
					<button id="filter-clear" class="btn btn-primary btn-sm rounded-0">Clear Filters & Sorting</button>
				</div>
			</div>
		</div>

After attempting to put adjust the query to a prepared statement, I get the following error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? ? ORDER BY enterprise, job_number, line_item LIMIT ?, ?' at line 3 in C:\wamp64\www\test\scripts\order_status.php on line 125

I have this at the top of my index.php:

<?php
session_start();
// start of script every time.

//  setup a path for all of your canned php scripts
$php_scripts = '/home/larry/web/test/php/'; // a folder above the web accessible tree
//  load the pdo connection module  
require $php_scripts . 'PDO_Connection_Select.php';
require $php_scripts . 'GetUserIpAddr.php';

//*******************************
//   Begin the script here
$ip = GetUserIpAddr();
if (!$pdo = PDOConnect("foxclone")):
{
	echo "Failed to connect to database" ;
    exit;
}
else:
{
    $stmt = $pdo->prepare("INSERT INTO 'download' ('IP_ADDRESS', 'FILENAME') VALUES (?, ?");
    $stmt->bindParam(1, $ip);
    $stmt->bindParam(2, $filename);  
    $stmt->execute();

}

endif;
//exit();
?>

I'm getting the following error at the $pdo->prepare line:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''download' ('IP_ADDRESS','FILENAME') VALUES (?, ?' at line 1 in /home/larry/web/test/public_html/index2.php:23 
Stack trace: #0 /home/larry/web/test/public_html/index2.php(23): 
PDO->prepare('INSERT INTO 'do...') #1 {main} thrown in /home/larry/web/test/public_html/index2.php on line 23

I  verified the format of the statement at https://www.w3schools.com/php/php_mysql_prepared_statements.asp but am unsure if it needs to be in the PDO_Connection_Select.php, or it belongs where I have it since the db is already connected.

<?php
 $stmt = $db->prepare("INSERT INTO `members` (`playername`,`player_login_name`,`player_login_pass`,`my_email`,
                                           `my_gender`,`signup_IP`,`signup_time`)
                    VALUES(?,?,?,?,?,?,?)");
$stmt->bind_param("ssssssi",$_POST['email'],$_POST['email'],md5($_POST['pass']),$_POST['email'],$_POST['gender'],$_POST['REMOTE_ADDR'],time());
$stmt->execute();
$stmt->close();
?>

$sql = "SELECT count(*) FROM posts WHERE MATCH (comments) AGAINST (':words' IN BOOLEAN MODE)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':words', $words);
$stmt->execute();

$stmt = $db->prepare(SELECT count(*) FROM posts WHERE MATCH (comments) AGAINST ('$words' IN BOOLEAN MODE)");
$stmt->execute();

Excerpts of code:

        function addUser() {
	    $username = $_POST['username'];
	    $password = password_hash($_POST['password'], PASSWORD_DEFAULT);
	    $bio = $_POST['bio'];
	    $email = $_POST['email'];
	    $c_status = 0;
            //$avatar = 

	    //$username_query = $pdo->prepare("SELECT * from profiles001 WHERE username=':username'");
            //$username_query->bindValue(':username', $username);
            //$username_query->execute();

            $query = $pdo->prepare("INSERT into profiles001 (username, password, email, c_status, bio) VALUES (:username, :password, :email, :cstat, :bio)");

            $query->bindValue(':username', $username);
            $query->bindValue(':password', $password);
            $query->bindValue(':email', $email);
            $query->bindValue(':cstat', $c_status);
            $query->bindValue(':bio', $bio);

            $query->execute();

            setAvatar();
        }

        function setAvatar() {
            // check if avatar is set, if not give default avatar
            if (isset($file) && $fileError === UPLOAD_ERR_OK) {
                $file = $_FILES['userfile'];
                $fileName = $file['name'];
                $fileTmpName = $file['tmp_name'];
                $fileSize = $file['size'];
                $fileError = $file['error'];
                $fileType = $file['type'];
                $fileExt = explode('.', $fileName);
                $fileActualExt = strtolower(end($fileExt));

                $allowedExtensions = array('jpg', 'jpeg', 'png');
            }

           

            // if user has not assigned avatar, assign the default.
            if (empty($file)) {
                $avatar = "assets/soap.jpg";
                
                $query = $pdo->prepare("INSERT INTO profiles001 (avatar) VALUES (:avatar)");

                $query->bindValue(':avatar', $avatar);
                $query->execute();
            }
        }

       addUser(); 
}

From the database file:

<?php
$host   = "localhost";
$database = "soapbox";
$username = "drb";
$password = "m1n3craft";

// Create connection
 		$pdo = new PDO('mysql:host=localhost;dbname=soapbox;', $username, $password);

/*

Print error message and or code to the screen if there is an error.

*/

?>

NOTE: I also require dbcon.php at the top of the confirmation.php file which is NOT included in the excerpt at the top.

Making pdo a global variable would probably fix it, but from what I heard globals are frowned upon.

Hi All,

I have the following code which works fine:

$mysqli = new mysqli("server", "user", "pass", "database");
if($mysqli->connect_error) {
exit('Could not connect');
}



$sql = "SELECT customer_contactname, customer_companyname, customer_phonenumber, customer_emailaddress, customer_address1, customer_address2, customer_city, customer_postcode
FROM ssm_customer WHERE customer_id = ?";

$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $_GET['q']);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($ccontname, $ccompname, $cphoneno, $cemail, $cad1, $cad2, $ccity, $cpost);
$stmt->fetch();
$stmt->close();

however, when i use my dbconn.php as an include rather than the top section of this code, i get the following error: function prepare() on null

The error references the following

$stmt = $mysqli->prepare($sql);

In my database connection file, i am using the following:

$conn = mysqli_connect($db_servername, $db_username, $db_password, $db_name);

This seems to be the issue and i believe i am mixing procedural and object based but not sure why there should be a difference here?

when i used my database connection file, i do change $mysqli->prepare to $conn->prepare

Kind Regards

Hi All,

Just wondering whether it is possible to resize a jpg image to a specific size in bytes (while preserving height/width ratio).

Thanks for any clues!