PHP - Mysqli Prepare Not Inserting

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.

hey... found this website when googling for a php help forum... hopefully this is the right place.

I am about to start a major project for a new high profile company contract my company is presently bidding on. One of the requirements is a web interface to communicate between the 2 companies.

I recently read (and have been reading more into it) that MDB2 is (one of) the best methods to communicate with a database (using prepared statements).

It seems to be extremely popular, and not to difficult to use... however, for some reason, it just wont work for me.

Code: [Select]
<?php
require_once '/include/MDB2.php';

function connect(){
    $SQL = array(
        'driver' => 'mysql',
        'user' => '***',
        'pass' => '***',
        'host' => '127.0.0.1',
        'dbname' => '***',
    );

    $SQL['sql'] = $SQL['driver']."://".$SQL['user'].":".$SQL['pass']."@".$SQL['host']."/".$SQL['dbname'];

    $mdb2 = MDB2::connect($SQL['sql']);
    $mdb2->setOption('emulate_prepared',true);

    if(PEAR::isError($mdb2))die("Error while connecting : " . $mdb2->getMessage());

    return $mdb2;
}

$mdb2 = connect();
$statement = $mdb2->prepare("INSERT INTO `test` (id,name) VALUES (?, ?)", array('integer','text'), MDB2_PREPARE_MANIP);
$statement->execute(array(1,'someuser'));
$statement->free();
?>

For some reason, this is not working? However if I just execute a normal query, it works no problem:

Code: [Select]
<?php
$mdb2 = connect();
$statement = $mdb2->query("INSERT INTO `test` (id,name) VALUES (1,'someuser')");
?>

So, I am connecting properly, everything is good... but this prepared statement just hates me Any help please?

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 code produces a dropdown and when a selection is made and submitted produces
a table row with a link to the record but the link doesn't work.  suggestions?

---------------------------------------------------------------------------

<!DOCTYPE><html><head>
<title>lookup menu</title>
</head>
<body><center><b>
<form name="form" method="post" action="">

<?php
// error_reporting(0);
error_reporting(E_ALL ^ E_NOTICE);
include 'homedb-connect.php';

//This creates the drop down box
echo "<select name= 'target'>";
echo '<option value="">'.'--- Select account ---'.'</option>';
$query = mysqli_query($con,"SELECT target FROM lookuptbl");
$query_display = mysqli_query($con,"SELECT * FROM lookuptbl");
while($row=mysqli_fetch_array($query))
{echo "<option value='". $row['target']."'>".$row['target']
.'</option>';}
echo '</select>';
?>
<input type="submit" name="submit" value="Submit"/>
</form><center>

<?php
// error_reporting(0);
error_reporting(E_ALL ^ E_NOTICE);
include 'homedb-connect.php';

if(isset($_POST['target']))
{
$name = $_POST['target'];
$fetch="SELECT target, purpose, user, password, email, visits, date,
 saved FROM lookuptbl WHERE target = '".$name."'";
$result = mysqli_query($con,$fetch);
if(!$result)
{echo "Error:".(mysqli_error($con));}

//display the table
        echo '<table border="1"><tr><td bgcolor="#ccffff" align="center">lookup menu</td></tr>
        <tr><td>
        <table border="1">
        <tr>
        <td> Target </td>
        <td> Purpose </td>
        <td> User </td>
        <td> Password </td>
        <td> Email </td>
        <td> Visits </td>
        <td> Date </td>
        <td> Saved </td>
        </tr>';

        while($data=mysqli_fetch_row($result))                              
          {
$url= "http://localhost/home/crud-link.php?target=". $data[0];
$link= '<a href="'.$url.'">'. $data[0]. '</a>';

echo ("<tr><td> $link </td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td>
<td>$data[4]</td><td>$data[5]</td><td>$data[6]</td><td>$data[7]</td></tr>");
}     
        echo '</table>
        </td></tr></table>';
  }
?>
</body></html>

 

When running the following code i get the error:

Call to undefined method mysqli::errno() 

the code:
$conn = new mysqli(HOST, USER, PASSWORD, DATABASE);
if ($conn->errno() !== 0)
{
$msg = $conn->error();
throw new connErrorException($msg, 'Connect');
}

I am fairly new to classes but as i understand it this should be correct. I am using mysql 5.1 so mysqli is on by default. I have even checked the php ini and everything looks fine there in respect to this. Any advice?

I am using mysqli, OO, to connect to MySQL.

I have only today started looking at this and am used to:
Code: [Select]
<?php
$con = mysql_connect();//etc
mysql_close($connection);
?>

Am I right that with mysqli (OO) that I don't need to set a connection variable wither when connecting or closing??
Code: [Select]
<?php
mysqli::connect();//etc
mysqli::close();
?>

What about with multiple databases, does mysqli keep track for me, as I am used to this:
Code: [Select]
<?php
$con1 = mysql_connect();//db1
$con2 = mysql_connect();//db2
?>
//etc

I dont know whether the statement is correct.....i just tried it.....and it didn't work.

   $stmt->bind_param('ssiiiss',$_POST['name'],$_POST['email'],$_POST['d'],$_POST['m'],$_POST['y'],$_POST['add'],$_POST['phone']);

here my first two values are strings and next 2 tiny int's next is int and last 2 again strings.

Hi, The following code is what I want in that it creates a menu and I can select and display a table row. I still need to use that selection to update the "lastused". I really appreciate your help.

 

<!DOCTYPE><html><head><title>email menu</title></head>     
    <body><center>
    <form name="form" method="post" action="">
    <?php
    $con=mysqli_connect("localhost","root","cookie","homedb");
    //============== check connection
    if(mysqli_errno($con))
    {echo "Can't Connect to mySQL:".mysqli_connect_error();}
    else
    {echo "Connected to mySQL</br>";}
       //This creates the drop down box
    echo "<select name= 'target'>";
    echo '<option value="">'.'--- Select email account ---'.'</option>';
    $query = mysqli_query($con,"SELECT target FROM emailtbl");
    $query_display = mysqli_query($con,"SELECT * FROM emailtbl");
    while($row=mysqli_fetch_array($query))
    {echo "<option value='". $row['target']."'>".$row['target']
    .'</option>';}
    echo '</select>';
    ?>
    <input type="submit" name="submit" value="Submit"/><!-- update "lastused" using selected "target"-->
    </form></body></html>

<!DOCTYPE><html><head><title>email menu</title></head>    
    <body><center>
    <?php
    $con=mysqli_connect("localhost","root","cookie","homedb");
    if(mysqli_errno($con))
    {echo "Can't Connect to mySQL:".mysqli_connect_error();}
        if(isset($_POST['target']))
      {
    $name = $_POST['target'];
    $fetch="SELECT target,username,password,emailused,lastused, purpose, saved FROM emailtbl WHERE target = '".$name."'";
    $result = mysqli_query($con,$fetch);
    if(!$result)
    {echo "Error:".(mysqli_error($con));}
   $lastused = "CURDATE()"; // update "lastused" using selected "target"
    //display the table
    echo '<table border="1">'.'<tr>'.'<td bgcolor="#ccffff align="center">'. 'Email menu'. '</td>'.'</tr>';
    echo '<tr>'.'<td>'.'<table border="1">'.'<tr>'.'<td bgcolor="#ccffff align="center">'.'target'.'</td>'.'<td bgcolor="#ccffff align="center">'.'username'.'</td>'.'<td bgcolor="#ccffff align="center">'.'password'.'</td>'.'<td bgcolor="#ccffff align="center">'.'emailused'.'</td>'.'<td bgcolor="#ccffff align="center">'.'lastused'.'</td>'.'<td bgcolor="#ccffff align="center">'.'purpose'. '</td>'.'<td bgcolor="#ccffff align="center">'. 'saved' .'</td>'.'</tr>';
    while($data=mysqli_fetch_row($result))
    {echo ("<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td><td>$data[4]</td><td>$data[5]</td><td>$data[6]</td></tr>");}
    echo '</table>'.'</td>'.'</tr>'.'</table>';
      }
    ?>
       </body></html>



 

Hello everyone,

For two weeks now, I'm trying to get this database connection in my query. Can someone give me a solution and tell me what I've done wrong? Am I overlooking something?

<?php

class Mysql{
public function connect(){
$mysqli = new mysqli('localhost','root','','login');
}
}

class Query extends Mysql{
public function runQuery(){
$this->result = parent::connect()->query("select bla bla from bla bla");
}
}

$query = new Query;
$query->runQuery();

?>

I have just started using MySQLi and am clueless it is giving me the follow errors in which i do not understand Warning: mysqli_select_db() expects exactly 2 parameters, 1 given in C:\xampp\htdocs\Login\connect.php on line 23

Notice: Trying to get property of non-object in C:\xampp\htdocs\Login\connect.php on line 25

Notice: Use of undefined constant mysqli - assumed 'mysqli' in C:\xampp\htdocs\Login\connect.php on line 32

Warning: mysqli_query() expects parameter 1 to be mysqli, string given in C:\xampp\htdocs\Login\connect.php on line 32

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, null given in C:\xampp\htdocs\Login\connect.php on line 33    can someone please explain to me why i am getting these?   and my code is

$mysqli_db = mysqli_select_db("$db_name");

if($mysqli_db->connect_errno) {

	printf("Database not found: %s\n", $mysql->connect_error);
	exit();
}

$sql = "SELECT * FROM $tbl_name WHERE username='$username' AND password='$password'";
$result = mysqli_query($sql);
$row = mysqli_fetch_assoc($result);

I just got rid off most the errors the only ones left are  Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\xampp\htdocs\Login\connect.php on line 32

Fatal error: Call to undefined function mysqli_result() in C:\xampp\htdocs\Login\connect.php on line 33   Code Updated: 

$mysqli_db = mysqli_select_db($mysqli_connect, $db_name);

if(!$mysqli_db) {

	printf("Database not found: %s\n", $mysqli->connect_error);
	exit();
}

$sql = "SELECT * FROM $tbl_name WHERE username='$username' AND password='$password'";
$query = mysqli_query($sql);
$result = mysqli_result($query);
$row = mysqli_fetch_assoc($result);

Edited by Tom8001, 30 November 2014 - 12:43 PM.

Ok I am trying to use mysqli instead of the usual mysql.  Mysql would be outdated.
With mysqli, sgl-injection is impossible if you use the "?" in those codes.

I would normally use a function but  I've made a simple script to find the error.

I use $parameters and $sql because these are the data I need to give as parameters to the function, so I used it here too
but without the function actually.

Code: [Select]
ini_set('display_errors',1); // 1 == aan , 0 == uit
error_reporting(E_ALL | E_STRICT);
# sql debug
define('DEBUG_MODE',true);  // true == aan, false == uit


$userid = 11;
$lang = 1;
$newLink = "testing123";

$db_host = "localhost";
$db_gebruiker = "root";
$db_wachtwoord = '';
$db_naam = "projecteasywebsite";

$sql= "INSERT tbl_link(userid,linkcat,linksubid,linklang,linkactive,linktitle)  VALUES(?, ?, ?, ?, ?, ?)";
$parameters = '"iiisis", $userid, 1, 0, $lang, 1, $newLink';

echo $parameters;

$mysqli = new mysqli($db_host, $db_gebruiker, $db_wachtwoord, $db_naam);
$stmt = $mysqli->prepare($sql);

$stmt->bind_param($parameters); 

$stmt->execute();

echo "<br><br>". mysqli_connect_errno();
echo "<br><br>". mysqli_report(MYSQLI_REPORT_ERROR);

$stmt->close();
$mysqli->close();


I got Wrong parameter count for mysqli_stmt::bind_param() 

So naturally a problem when we execute : Warning: mysqli_stmt::execute() [mysqli-stmt.execute]: (HY000/2031): No data supplied for parameters in prepared statement    ($stmt->execute()

Is someone using mysqli too ?

hello , I'm starting to use mysqli and i have few questions.

is there a guide for mysqli?

and how do i use this functions at mysqli ?
mysql_num_rows
mysql_query
mysql_fetch_assoc
mysql_fetch_array

thanks , Mor.

I have converted my mysql to mysqli and am working on converting some functions.

My question - Can I not just convert the mysql to mysqli?

This is the full code:

Code: [Select]
class zipcode_class {

   var $last_error = "";            // last error message set by this class
   var $last_time = 0;              // last function execution time (debug info)
   var $units = _UNIT_MILES;        // miles or kilometers
   var $decimals = 2;               // decimal places for returned distance

  function get_zip_point($zip) {

     // This function pulls just the lattitude and longitude from the
     // database for a given zip code.

     $sql = "SELECT lat, lon from zip_code WHERE zip_code='$zip'";
     $r = $mysqli->query($sql);
     if (!$r) {
        $this->last_error = mysql_error();
        return false;
     } else {
        $row = mysql_fetch_array($r);
        mysql_free_result($r);
        return $row;
     }
  }

   function calculate_mileage($lat1, $lat2, $lon1, $lon2) {
     // used internally, this function actually performs that calculation to
     // determine the mileage between 2 points defined by lattitude and
     // longitude coordinates.  This calculation is based on the code found
     // at http://www.cryptnet.net/fsp/zipdy/

     // Convert lattitude/longitude (degrees) to radians for calculations
     $lat1 = deg2rad($lat1);
     $lon1 = deg2rad($lon1);
     $lat2 = deg2rad($lat2);
     $lon2 = deg2rad($lon2);

     // Find the deltas
     $delta_lat = $lat2 - $lat1;
     $delta_lon = $lon2 - $lon1;

     // Find the Great Circle distance
    $temp = pow(sin($delta_lat/2.0),2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0),2);
    $distance = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp));
      return $distance;
  }

  function get_zips_in_range($zip, $range, $sort=1, $include_base) {
      // returns an array of the zip codes within $range of $zip. Returns
     // an array with keys as zip codes and values as the distance from
     // the zipcode defined in $zip.

     $this->chronometer();                     // start the clock

     $details = $this->get_zip_point($zip);  // base zip details
     if ($details == false) return false;

     // This portion of the routine  calculates the minimum and maximum lat and
     // long within a given range.  This portion of the code was written
     // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
     // the time it takes to execute a query.  My demo took 3.2 seconds in
     // v1.0.0 and now executes in 0.4 seconds!  Greate job Jeff!

     // Find Max - Min Lat / Long for Radius and zero point and query
     // only zips in that range.
     $lat_range = $range/69.172;
     $lon_range = abs($range/(cos($details[0]) * 69.172));
     $min_lat = number_format($details[0] - $lat_range, "4", ".", "");
     $max_lat = number_format($details[0] + $lat_range, "4", ".", "");
     $min_lon = number_format($details[1] - $lon_range, "4", ".", "");
     $max_lon = number_format($details[1] + $lon_range, "4", ".", "");

     $return = array();    // declared here for scope

     $sql = "SELECT zip_code, lat, lon FROM zip_code ";
     if (!$include_base) $sql .= "WHERE zip_code <> '$zip' AND ";
     else $sql .= "WHERE ";
     $sql .= "lat BETWEEN '$min_lat' AND '$max_lat'
              AND lon BETWEEN '$min_lon' AND '$max_lon'";

      $r = mysql_query($sql);

     if (!$r) {    // sql error
      $this->last_error = mysql_error();
      return false;
     } else {
      while ($row = mysql_fetch_row($r)) {

        // loop through all 40 some thousand zip codes and determine whether
        // or not it's within the specified range.

        $dist = $this->calculate_mileage($details[0],$row[1],$details[1],$row[2]);
        if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
        if ($dist <= $range) {
          $return[str_pad($row[0], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);
        }
      }
      mysql_free_result($r);
     }

     // sort array
     switch($sort)
     {
      case _ZIPS_SORT_BY_DISTANCE_ASC:
        asort($return);
        break;

      case _ZIPS_SORT_BY_DISTANCE_DESC:
        arsort($return);
        break;

      case _ZIPS_SORT_BY_ZIP_ASC:
         ksort($return);
         break;

      case _ZIPS_SORT_BY_ZIP_DESC:
        krsort($return);
        break;
      }

      $this->last_time = $this->chronometer();

      if (empty($return)) return false;
      return $return;
   }
}