Following Opentutorials WEB2 - PHP: Implementing CRUD with MySQL

php Opentutorials 2019.03.03

Following Opentutorials WEB2 - PHP _ Implementing CRUD with MySQL 

Database setup

Connect to MySQL 

cd /Applications/mampstack-7.1.26-2/mysql/bin

./mysql -uroot -p 

Create the database 

CREATE DATABASE opentutorials;

show databases;

use opentutorials;

Create the table 

create table topic(

    id int(11) not null auto_increment,

    title varchar(45) not null,

    description text,

    created datetime not null,

    primary key(id)

    )engine=InnoDB;

Principles 

php <-> MySQL server <-> MySQL Client( Monitor… )

(=MySQL Client)

php MySQL api

Recommended API   ->  mysqli or PDO_MySQL

* Recommendation: use PDO_MySQL objects — easy to swap in/out with Oracle, etc.

MySQL = MySQL Improved

Guide: http://php.net/manual/en/book.mysqli.php

Database control

Function style & object style: http://php.net/manual/en/mysqli.quickstart.dual-interface.php

Exercise 

mysqli_connect( );

http://php.net/manual/en/mysqli.construct.php

connect_test.php

<?php

$link = mysqli_connect('localhost','root','ever1227','opentutorials');

if (!$link) {

    die('Connect Error (' . mysqli_connect_errno() . ') '

            . mysqli_connect_error());

}

echo 'Success... ' . mysqli_get_host_info($link) . "\n";

mysqli_close($link);

  ?>

Check what's happening on the PHP server 

Search keyword: mysql general_log enable

mysqli_query()

$result = mysqli_query($conn,$sql);

var_dump($result);

var_dump($result ->num_rows);

mysqli_fetch_array()

$row = mysqli_fetch_array($result);

print_r($row);

// null if none

Associative array

echo '<h1>'.$row['title'].'</h1>';

while(boolean)

null = false

not null = true

Combining parameters into SQL 

$sql = "SELECT * FROM topic where id={ $_GET['id'] }";

Putting it into an array

$article = array(

  "title" => $row["title"],

  "description" => $row["description"]

);

Usage —>  $article['title']

Inspecting properties

print_r($article);

Anywhere that might be filled in by the user — apply security handling

For URLs

$filtered_id = mysqli_real_escape_string($conn, $_GET['id']);

For INPUT

mysqli_real_escape_string($conn, $_POST['title'])

Bits that personally confuse me

if(isset($_GET['id'])){ 

$sql = "SELECT * FROM topic where id={$_GET['id']}";

$update_link = "<a href=\"update.php?id=". $_GET['id'] ."\">update</a> "; }

<p><input type="text" name="title" placeholder="title" value="<?=$article['title'] ?>"></p>

<p><input type="text" name="title" placeholder="title" value="<?php echo $article['title'] ?>"></p>

Coercing the argument type to match the expected data type 

settype($_POST['id'], "integer");

Before sending to SQL, inspect the query in the browser alone 

die($sql);

Filtering: defending against cross-site scripting attacks — htmlspecialchars 

$escaped= array(

  'name' => '',

  'profile' => ''

);

if(isset($_GET['id'])){

//Type 1

  $filtered_id = mysqli_real_escape_string($conn, $_GET['id']);

  settype($filtered_id,"integer");

  $sql="select * from author where id={$filtered_id}";

  $result = mysqli_query($conn, $sql);

  $row = mysqli_fetch_array($result);

//Type 2

  $escaped['name']= htmlspecialchars($row['name']);

  $escaped['profile']= htmlspecialchars($row['profile']);

}

Redirect 

header('Location : author.php');

+ Extra lesson: applying it to a relational database 

Add one more table, 

and go through the CRUD process again with a table JOIN.

Example of the join code

  $sql = "SELECT * FROM topic LEFT JOIN author ON topic.author_id=author.id where topic.id={$filtered_id}";

Ugh... this issue came up...  <--   this part  isn't working..

  $table_form .= '

    <tr>

      <td>'.$filtered['id'].'</td>

      <td>'.$filtered['name'].'</td>

      <td>'.$filtered['profile'].'</td>

      <td><a href ="author.php?id='.$filtered['id'].'">update</a></td>

      <td>

        <form action="process_delete_author.php" method="post" onsubmit="conform(\'sure?\');">

          <input type="hidden" name="id" value="'.$filtered['id'].'">

          <input type="submit" value="delete">

        </form>

      </td>

    </tr>

  ';