PHP - Mdb2 Prepare Statements Php + Mysql

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'd love to use anonymous placeholders on my ecommerce site project.  I am writing half with php and half with golang.  On the three examples below, when run, gives the following exception, " Error: Call to a member function execute() on string. "  I tried it with a decimal too.  Thanks in advance.
 

$stmt = $dbo->prepare = ("SELECT * FROM products WHERE ProductName = ?");
//this one calls exception
$stmt->execute();

$stmt = $dbo->prepare = ("SELECT * FROM products WHERE ProductName = ?");
//this one calls exception
$stmt->bindParam(1, $productID, PDO::PARAM_INT);
$stmt->execute();

$stmt = $dbo->prepare = ("SELECT * FROM products WHERE ProductName = ?");
//this one calls exception
$stmt->bindValue(1, $productID, PDO::PARAM_INT);
$stmt->execute();

Here is the rest of the code :

<?php
$filename = "";
$keyword1 = $_GET['keyword'];
$titleOfSelectedDropDown = $_GET['val1'];
$fileID = "";
$imageID = "a";
$displayID = "";
$keyword1 = "test";
$titleOfSelectedDropDown = "cc";

$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'ecommerce';

$options = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
);

$gKeyword1 = "";
$gKeyword2 = "";
$gKeyword3 = "";

$key1ID = "";
$key2ID = "";
$key3ID = "";

$string1 = "<center><h1><u>Search Results</u><h1></center></p>";

$dbo = new PDO("mysql:host=$host;dbname=$database", $user, $pass, $options);

$stmt = $dbo->prepare = ("SELECT * FROM products WHERE ProductName = ?");
$test = "1";
$stmt->execute([ $test ]);

 

Edited March 17 by JoshEir
mistake

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.

with php pdo, the bindParam is not working. The query outputs :words and instead of the $words value. How can i get this code working?

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

which code example is more safe? The code below now has $words instead of :words but is missing bindParam.

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

$words is passed through both php $_get and $_post

Hey, I am coding this forum and the following PDO prepare query calls the topic details. The prepare query works perfectly well, however when I add it into a function within another class and then call it, the script does not seem to work.

I get errors like the following:

Fatal error: Call to a member function fetch() on a non-object in C:\wamp\www\new\sources\forum\new.php on line 117

The prepare query fails to excute, this is because I do not know how to add it into a function.

This is what I tried:

Code: [Select]
class FUNC {
function userDetails($table, $column, $cVaulue, $oBy, $ASDSC)
{
global $dbh;

$sth = $dbh->prepare('SELECT * FROM `'.$table.'` WHERE `'.$column.'` = '.$cVaulue.' ORDER BY `'.$oBy.'` '.$ASDSC.'');
$sth->bindParam(':id', $id, PDO::PARAM_INT);
$sth->execute();

}
}


The FUNC class is called as the $load variable, and in my forum class I have decalred it in my global so the variable can be passed:

Code: [Select]
$id = $forum_data['id'];

$load->userDetails('db_topics', 't_forum_id', ':id', 't_whenlast', 'DESC');
$coll=$sth->fetch(PDO::FETCH_ASSOC);
$this->html->RightForum($coll);


The code is very silly, but the fact is I did not know how to do this.. Please help me put this query into a function because I need to make use of it in many other parts of the forum and I don't want to constantly declare this query.

Hello, I have this code

<?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();
?>

and i will not insert into my database. I have check the syntax and there is no error, can anyore help me   thanks

Hi,

I will be creating pdf in php using following library.

http://sourceforge.net/projects/pdf-php/

After creating pdf ,I want to send PDF data in byte[] to web service.

Any idea how to read pdf and prepare pdf data in byte[] array.

With Kind Regards,
Zohaib.

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

Edited February 4, 2019 by Adamhumbug

The below is constantly loading the password page no matter what is enter in the password input on the form (on another page). I have checked the $login variable by echoing it and it is carrying the value from the field.


<?php
$off="yes";
$login=$_POST['login'];
if($off=="yes" && (!isset($_SESSION['login']) || $_SESSION['login']!="pass")){
    if($login=="" || $_SESSION['login']!="pass"){
    header('Location: ./index2.html');
    exit();
    }
    elseif(!isset($_SESSION['login'])){
    session_start();
    $_SESSION['login']=$login;
    header('Location: ./index.php');
    exit();
    }
}
elseif($off=="no" || $_SESSION['login']=="pass"){
content goes here.
}
?> 

How to write 2 "if" statements together?

if($userResult==$myResult) and ($userResult<0)
{

I am trying to write an if statement that checks to make sure the information put in the text box is an imbedded file. Does anyone know how to do that?

Hello, I am learning PDO SQL statements, I have mananged to connect using PDO:


Code: [Select]
try
{
$this->link = $dbh = new PDO('mysql:host='.$this->dbhost.';dbname='.$this->dbname.'', $this->dbuser, $this->dbpass);
}
catch (PDOException $e)
{
echo 'Connection failed: ' . $e->getMessage();
}


I am not getting any error, so I guess that is a good start. I then tried the PDO SQL statement out. My old mySQL query is commented out in the


Code: [Select]
if (isset($_SESSION['id']) && isset($_SESSION['password']))
{
$_SESSION['id'] = ( isset( $_SESSION['id'] ) ) ? $_SESSION['id'] : FALSE;
$_SESSION['password'] = ( isset( $_SESSION['password'] ) ) ? $_SESSION['password'] : FALSE;


//$logged = mysql_query("SELECT * FROM `db_members` WHERE `id`='".$_SESSION['id']."' AND `password` = '".$_SESSION['password']."'");
//$logged = mysql_fetch_array( $logged );

// the new pdo statement
$ff = $dbh->prepare('SELECT * FROM db_members WHERE id = '.$_SESSION['id'].' AND password = '.$_SESSION['password'].'');

$ff->execute();
$logged = $ff->fetchAll();

echo $logged['username'];
}

I am trying to assign the session to logged variable. So all I am asking is go into db_members and check the id and password that is the same as session id and password and collect the rows data such as username.

My login script and everything works perfectly, even session id and password is valid when echo'd but I cannot assign it to the logged variable like my old sql statements.


Code: [Select]
$logged = mysql_query("SELECT * FROM `db_members` WHERE `id`='".$_SESSION['id']."' AND `password` = '".$_SESSION['password']."'");
$logged = mysql_fetch_array( $logged );

I used other PDO statements and it works perfectly but I just don't understand why this is not working.... can I please get some help if you have any solution to this?

Hey guys,

is there any way to have a LIKE condition on an IF statement?
Something like:
Code: [Select]
if $GET_['WTV'] LIKE %LOVE% {
$category=romance
Any clues?
Thanks in advance.

Anything wrong with this because I got a parse error ONLY after adding to create this.

<?php
                            $visible = array('Yes', 'No');
                            foreach($visible as $visible):
                            $visible2 = array('yes', 'no');
                            foreach($visible2 as $visible2):
                        ?>