logo-dw

Go Back   Dreamweaver Club Forums > Hand Coders Forum > PHP
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 05-12-2015, 03:48 AM   #1
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default Mysqli

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

Code:
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;
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas

Last edited by edbr; 05-12-2015 at 09:16 AM..
edbr is offline   Reply With Quote
Old 05-12-2015, 03:55 AM   #2
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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

Code:
<?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();
   }
?>
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 05-12-2015, 04:13 AM   #3
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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

Code:
<?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
?>
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 05-12-2015, 04:22 AM   #4
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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
Code:
<?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>
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 05-13-2015, 03:08 AM   #5
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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

Code:
(name, position, department, start_date, telephone,gender, status,email)
=
$name, $position, $department, $start , $telephone,$gender,$status,$email
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas

Last edited by edbr; 05-13-2015 at 05:40 AM..
edbr is offline   Reply With Quote
Old 05-13-2015, 03:21 AM   #6
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

here is the whole thing on one page
the form (i added a few datalists to make testing a bit easier)
Code:
<?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>
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 05-15-2015, 07:49 AM   #7
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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.

Code:
<?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
?>
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 05-15-2015, 07:51 AM   #8
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

the action is set for update.php
update.php
Quote:
<?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
?>
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 04-08-2017, 10:52 AM   #9
Bill Goldberg
 
Join Date: Jan 2017
Posts: 39
Default

Thank you very much for the information.
Bill Goldberg is offline   Reply With Quote
Old 04-11-2017, 03:04 AM   #10
makza01
Banned
 
Join Date: Mar 2017
Posts: 3
Default Mysqli

Asking for advice about Mysqli with













maxbet mobile
makza01 is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 06:06 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com