Mysqli

[edbr]

i do not know how dream weaver approaches its php code now. i stopped using it a long time ago as it because as DW tries to be everything to everyman the code it produces is long and over complex or was at least .

i thought i would post some examples to help break away from DW and code directly. so i will give some examples using Mysqli, basically because soon mysql will be no more and its use of prepareed statements offer a security that is very nescersary today, perhaps more that ever before.

Any way first, a database to work with. paste the below code intom phpadmin (or other database manager you use) on your server.

It will create a database called employees with a table named Staff

CREATE DATABASE `employees`
    CHARACTER SET 'latin1'
    COLLATE 'latin1_swedish_ci';

USE `employees`;

#
# Structure for the `staff` table : 
#

CREATE TABLE `staff` (
  `id` INTEGER(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  `position` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  `department` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  `start_date` DATE NOT NULL,
  `telephone` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  `gender` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  `status` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  `email` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  PRIMARY KEY USING BTREE (`id`) COMMENT ''
)ENGINE=InnoDB
AUTO_INCREMENT=9 AVG_ROW_LENGTH=2048 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
COMMENT=''
;
INSERT INTO `staff` (`id`, `name`, `position`, `department`, `start_date`, `telephone`, `gender`, `status`, `email`) VALUES
  (1,'Maria','manager','sales','2013-05-01','222 333 4444','female','maried','maria@work.com'),
  (2,'Gina','staff','sales','2013-05-01','222 333 4445','female','Single','Gina@work.com'),
  (3,'Jacob','staff','sales','2013-05-01','222 333 4446','Male','maried','Jacob@work.com'),
  (4,'Bob','staff','sales','2013-05-01','222 333 4447','Male','Single','Bob@work.com'),
  (5,'Nobby','manager','marketing','2013-05-01','222 333 4444','Male','maried','nobby@work.com'),
  (6,'Tom','staff','marketing','2013-05-01','222 333 4448','Male','Single','Tom@work.com'),
  (7,'Dick','staff','marketing','2013-05-01','222 333 4449','Male','Single','Dick@work.com'),
  (8,'Harry','staff','marketing','2013-05-01','222 333 4450','Male','Single','Harry@work.com');
COMMIT;

[edbr]

Ok, now we need to connect to that database. create a file named conn.php. and insert the following. you will need to edits the vars for host, user , pass database should be employees so thats ok.

This will connect, if all the names and permissions are correct) and create a new connection object. if not correct you will receive the errors

<?php 
$host = 'localhost'; 
$user = 'root';
$pass = 'password';
$database = 'employees';

$mysqli = new mysqli($host,$user,$pass,$database);
   if(mysqli_connect_errno()) {
      echo "Connection Failed: " . mysqli_connect_errno();
      exit();
   }
?>

[edbr]

next create a file, name it what you will but in the same directory as conn.php in this case, as i will use the connection as an included file, or of course you can change the path to suit.

This will use a prepare and execute select command.
It will bind the parameters of the select statement, then it will bind the results
the advantage of this is where the security of mysqli comes in this will combat sql injections form ne'er do wells
at the beginning is $search var. i have used this as an examople it could however be a $_POST or $_GET value.

I an using a table to return the data and i am using <?= Svar ;?> this is the same as <?php echo ;?> I am just trying to emphasize the brevity of code need to return data. in this case a singe result

<?php 
include 'conn.php'; //the connection 
$search = "Maria";
//create a prepared statement
$query = "SELECT id, name, position, department   FROM staff WHERE name =?";
$statement = $mysqli->prepare($query);

$statement->bind_param('s', $search);//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)

$statement->execute();//execute query

//bind result variables
$statement->bind_result($id, $name, $position,$department);
$statement->fetch();?><!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>single result</title>
</head>
<body>
</body>
</html><br>
<table border="1">
<tr>
<td><?=   $id  ;?></td>
<td><?=   $name ;?></td>
<td><?=  $position  ;?></td>
<td><?=  $department  ;?></td>
</tr>
</table>

<hr>
<?php 
$statement->close();//close connection
?>

[edbr]

next a loop to return more that one result.
i am using a format similar to the one Dw used in the past that actually breaks the php loop with html. I am sure not everyone likes this but i like that it keeps the html separate so making maintenance easier.
the search term is set for sales so it will return all employess in the sales department

<?php 
include 'conn.php';
$search = "sales";
//create a prepared statement
$query = "SELECT id, name, position, department   FROM staff WHERE department =?";
$statement = $mysqli->prepare($query);
//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
$statement->bind_param('s', $search);
//execute query
$statement->execute();
//bind result variables
$statement->bind_result($id, $name, $position,$department);
?>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>loop results</title>
</head>
<body>
<table border="1">
<?php
while($statement->fetch()) {  ?>
<tr>
<td><?=   $id  ;?></td>
<td><?=   $name ;?></td>
<td><?=  $position  ;?></td>
<td><?=  $department  ;?></td></tr>
<?php }   ?>
</table>
<hr>
<?php 
//close connection
$statement->close();
?>
</body>
</html>

[edbr]

Insert records using prepared statements.

the following will only include the php provided the form has been submitted and in this case that a naem has been given. that is the purpose of the if condition at the start ans it encloses all the php

PHP Code:

  if (isset($_POST['Submit']) && $_POST['name'] != "") {...........

} //end if 


the security of this lies in prepare and execute\

PHP Code:

$statement = $mysqli->prepare($query);
$statement->execute() 


$query is the insert statement. it contains a number of ?'s. this is called binding . it will use vars that you specify in your bind statement that follows

PHP Code:

$statement->bind_param('ssssssss', $name, $position, $department, $start , $telephone,$gender,$status,$email); 


these have been defined as the corresponding $_post values, makes for cleaner looking code

bind_param('ssssssss', these must equal in number the list of vars that follow AND the list of field names you selected in the insert statement ie

(name, position, department, start_date, telephone,gender, status,email)

as must the number of ?'s to the fields and the binding parameters

(name, position, department, start_date, telephone,gender, status,email) =8
bind_param('ssssssss' =8
$name, $position, $department, $start , $telephone,$gender,$status,$email = 8

in addition the binding parameters must be in the same order as the select staement

(name, position, department, start_date, telephone,gender, status,email)
=
$name, $position, $department, $start , $telephone,$gender,$status,$email

[edbr]

here is the whole thing on one page
the form (i added a few datalists to make testing a bit easier)

<?php
   if (isset($_POST['Submit']) && $_POST['name'] != "") {
include 'conn.php';

//values to be inserted in database table from $_post
		$name = $_POST['name'];
		$position = $_POST['position'];
		$department = $_POST['department'];
		$start= $_POST['start'];
		$telephone= $_POST['telephone'];
		$gender= $_POST['gender'];
		$status= $_POST['status'];
		$email= $_POST['email'];    
  
$query = "INSERT INTO staff (name, position, department, start_date, telephone,gender, status,email) VALUES(?, ?,?,?,?,?, ?,?)";
$statement = $mysqli->prepare($query);
//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
$statement->bind_param('ssssssss', $name, $position, $department, $start , $telephone,$gender,$status,$email);
if($statement->execute()){
    print 'Success! ID of last inserted record is : ' .$statement->id .'<br />';
}else{
    die('Error : ('. $mysqli->errno .') '. $mysqli->error);
}
$statement->close();
   }//end if
?><!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>
<style>
#form{ position:relative; width: 500px; margin:0 auto;}
 label {     float:left;    clear:left;}
 input{	 float:left;    color: #555 ;    width: 70%;    border: 1px solid #e5e5e5;    background: #fbfbfb;    height: 25px;}</style>
</head>

<body>
<div id="form">
        <form action="" method="post">
       <label>name</label> <input name="name" type="text" placeholder="Name" list="name" >  
      				 <datalist id="name">    <option value="Jimi Hendrix">   <option value="Julius">   <option value="Eric Clapton">  </datalist> 
       <label>start</label>   <input name="start" type="text"  placeholder="2013-05-01"  list="date" > <datalist id="date">   <option value="2013-05-01">   </datalist>
        <label>position</label> <input name="position" type="text"  placeholder="manager" list="position" >
       				 <datalist id="position">   <option value="manager">   <option value="staff">  </datalist>
         <label>department</label><input name="department" type="text" placeholder="production" list="dept" >
         			<datalist id="dept">   <option value="production">   <option value="sales"> <option value="hrd"> </option></datalist>
        <label>telefone</label> <input name="telephone" type="text" placeholder="telephone">
         <label>gender</label><input name="gender" type="text" placeholder="gender" list="gender" > 
         			<datalist id="gender">    <option value="male">  <option value="female">  </datalist>
        <label>status</label> <input name="status" type="text" placeholder="status"  list="status"> 
        			<datalist id="status">   <option value="married">  <option value="single">  </datalist>
         <label>email</label><input name="email" type="text" placeholder="julius@work.com">
        <input name="Submit" type="submit"   >
        </form><br>
</div>
</body>
</html>

[edbr]

Updating

To update , first you will update a specific record. this will be a unique field usually the id primary fiels. but for the purpose of demonstration here and the data base i gave we can use a name.

First to return the data from the database into a form for editing your updates. a one field form to select. ten a form to recieve the values once the first has been submitted (protected by a similar if condition i used before.

<?php 
include 'conn.php'; //the connection 
$search = $_POST['name_p'];
 ?><!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>single result</title>
<style>
#search{ width:300px; position:relative; margin: 0px auto;}
#form{ position:relative; width: 500px; margin:0 auto;}
  label {     float:left;    clear:left;}
input{ float:left;    color: #555 ;    width: 70%;    border: 1px solid #e5e5e5;    background: #fbfbfb;   height: 25px;}

.submit { 
    width: auto; 
    padding: 9px 15px; 
    background: #617798; 
  
    color: #FFFFFF; 
    }
</style>
</head>
<body>
<div id="search">
<form action="" method="post">    <input name="name_p" type="text">
    <button name="Submit" type="submit" class="submit"  ></form>
</div>
<br>
<br>

<?php
   if (isset($_POST['name_p'] )) {

//create a prepared statement
$query = "SELECT name, position, department, start_date, telephone,gender, status,email FROM staff WHERE name =?";
$statement = $mysqli->prepare($query);

$statement->bind_param('s', $search);//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)

$statement->execute();//execute query

//bind result variables
$statement->bind_result($name, $position,$department,$start,$telephone , $gender, $status,$email);
$statement->fetch()

?>

    
<div id="form">
        <form action="update.php" method="post">
       <label>name</label> <input name="name" type="text" value="<?=   $name ;?>" >  
      
       <label>start</label>   <input name="start" type="text"  value="<?=   $start ;?> "  >
        <label>position</label> <input name="position" type="text" value="<?=   $position ;?> " >
       				 
         <label>department</label><input name="department" type="text" value="<?=   $department ;?> "  >
         		
        <label>telefone</label> <input name="telephone" type="text" value="<?=   $telephone ;?> ">
         <label>gender</label><input name="gender" type="text" value="<?=   $gender ;?>"> 
         		
        <label>status</label> <input name="status" type="text" value="<?=   $status;?>" > 
        		
         <label>email</label><input name="email" type="text" value="<?=   $email;?>">
        <input name="Submit" type="submit"   >
        </form><br>
</div>
<?php
; } /* end if  */ ?>
</body>
<?php 
$statement->close();//close connection
?>

[edbr]

the action is set for update.php
update.php

<?php
if (isset($_POST['Submit']) && $_POST['name'] != "") {
include 'conn.php';

//values to be inserted in database table from $_post
$name = $_POST['name'];
$position = $_POST['position'];
$department = $_POST['department'];
$start= $_POST['start'];
$telephone= $_POST['telephone'];
$gender= $_POST['gender'];
$status= $_POST['status'];
$email= $_POST['email'];

$query = "UPDATE staff SET position=?, department=? , start_date=? , telephone=? , gender=? , status=? , email=? WHERE name=?";
$statement = $mysqli->prepare($query);

//bind parameters for markers, where (s = string, i = integer, d = double, b = blob)
$statement->bind_param('ssssssss', $position, $department, $start , $telephone,$gender,$status,$email,$name );


if($statement->execute()){
print 'Success! record updated ';
}else{
die('Error : ('. $mysqli->errno .') '. $mysqli->error);
}
}//end if
?>

[Bill Goldberg]

Thank you very much for the information.

[makza01]

Asking for advice about Mysqli with













maxbet mobile