PHP code example of jitsu / sqldb

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

jitsu / sqldb example snippets

use Jitsu\Sql\Database;
use Jitsu\Sql\MysqlDatabase;

$search_term = $_GET['query'];

// Connect to the database
$db = new MysqlDatabase('localhost', 'my_database',
                        'my_user', 'my_password');
// Run a query with named parameters
$stmt = $db->query(<<<SQL
  select `name`, `description`
  from `packages`
  where `description` like :pattern
  order by `description` = :term desc
SQL
  , [
    'pattern' => '%' . Database::escapeLike($search_term) . '%',
    'term' => $search_term
  ]);

// Iterate over results
foreach($stmt as $row) {
  echo $row->name, ': ', $row->description, "\n";
}

$user_id = $_SESSION['user_id'];

// Get a single record using a positional parameter
$user = $db->row(<<<SQL
  select `first_name`
  from `users`
  where `id` = ?
SQL
  , $user_id);
echo "Welcome back, ", $user->first_name, "\n";

// Get the first column of the first row
$exists = $db->evaluate(<<<SQL
  select exists(
    select 1
    from `bookmarks`
    join `packages` on `bookmarks`.`package_id` = `packages`.`id`
    where `bookmarks`.`user_id` = ?
    and `packages`.`name` = ?
  )
SQL
  , $user_id, $search_term);
if($exists) {
  echo "You have already bookmarked this package.\n"
} else {
  echo "You have not bookmarked this package.\n";
}

class MyApp extends \Jitsu\App\Application {
  use \Jitsu\App\Databases;
  public function initialize() {
    $this->database('database', [
      'driver'     => 'mysql',
      'host'       => 'localhost',
      'database'   => 'my_database',
      'user'       => 'my_user',
      'password'   => 'shhhhhhh',
      'persistent' => true
    ]);
    $this->get('count-users', function($data) {
      $count = $data->database->evaluate('select count(*) from `users`');
      echo "There are $count users. Honestly, that's $count more than I expected.\n";
    });
    $this->notFound(function($data) {
      $data->response->setStatusCode(404, 'Not Found');
      echo "Nothing to see here. No database connection made.\n";
    });
  }
}