Mick Redman: database

Showing posts with label database. Show all posts

Wednesday, 5 March 2025

Delete, add and edit columns using Laravel 12 migrations

The following code example show how to delete, add and edit columns using Laravel 12 migrations. In all cases, when the migration has been completed, you run the command:
php artisan migrate

Delete example

php artisan make:migration delete_column_name_to_table_name --table=table_name

public function up(): void
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->dropColumn('column_name');
        });
    }

    public function down(): void
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->string('column_name'); // Define the column type to restore it
        });
    }

Add example

php artisan make:migration add_column_name_to_table_name --table=table_name

public function up(): void
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->string('column_name')->nullable();
        });
    }

    public function down(): void
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->dropColumn('column_name');
        });
    }

Edit example

php artisan make:migration edit_column_name_to_table_name --table=table_name

public function up(): void
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->string('column_name', 100)->nullable()->change();
        });
    }

    public function down(): void
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->string('column_name', 255)->notNullable()->change();
        });
    }

Monday, 15 August 2016

PHP resize image before base64 encoding

It took me a while to work this out, but it was worth it. base64 encoding can be really useful in presenting and storing images on the web. However you don't want to add a 2000 pixel width base64 encoded image to your database. Here's how to resize the image before it goes in.

<?php
$file = 'mypic.jpg';
$image = imagecreatefromjpeg($file);
$image = imagescale($image , 100);
ob_start();
imagejpeg($image);
$contents = ob_get_contents();
ob_end_clean();
$dataUri = "data:image/jpeg;base64,".base64_encode($contents);
echo '<img src="'.$dataUri.'" />';
?>

Tuesday, 28 August 2012

Saving and retrieving images using MySQL through PHP

I really should have looked into this a long time ago, but still. The example below deliberately doesn't use jQuery. If you want to POST files through jQuery, you'll need to use an ajax form plugin.

Right. Now we've got that out of the way, to my example. I have created a database in MySQL with a table called pictures, and I have 2 fields:
id : int(8)

picture : longblob
I have used the database class which I developed earlier to handle the requests.
At this stage, I'm just handling JPEG images.

<?php
require_once 'database.class.php';
$db = new database;
if($_POST)
{
$image=$_FILES['uploadfile']['tmp_name'];
$fp = fopen($image, 'r');
$content = fread($fp, filesize($image));
$content = addslashes($content);
fclose($fp);
$sql="insert into pictures (picture) values ('$content')";
$results = $db->query($sql);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>File upload</title>
</head>
<body>
<form enctype="multipart/form-data" method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" name="MAX_FILE_SIZE" value="10000000" />
<input name="uploadfile" id="uploadfile" type="file" />
<input type="submit" value="Submit" />
</form>
<div id="results">
<?php
$arr = $db->query('SELECT * FROM pictures');
if($arr)
{
foreach($arr as $row)
{
echo $row['id'];
echo '<img src="data:image/jpeg;base64,'.base64_encode($row['picture']).'" /><br />';
}
}
?>
</div>
</body>
</html>

Enjoy!

Thursday, 19 July 2012

Simple PDO Class

For some reason, maybe it was my first (negative) introduction to OOP through C++, I've always hated those (::) double colons. So, when I started to look at PHP Data Objects (PDO), although it seemed like a good idea, I couldn't get over the fact that I was opening myself up to using static class and methods with all the syntax which goes with it.

So I started to create a simple database class which made use of PDO, which would be a little friendlier to call. Humble beginnings these. First I created an ini file for all the settings, which looks something like this:

DB_TYPE = mysql
DB_HOST = localhost
DB_USERNAME = jimmy
DB_PASSWORD = password
DB_NAME = testdb

Then, I created the database class which called the ini file thus:

<?php
class database
{
private $config;
private $connection;
private $pdoString;
function __construct()
{
$this->config = (object) parse_ini_file('config.ini', true);
$this->pdoString = $this->config->DB_TYPE;
$this->pdoString .= ':dbname='.$this->config->DB_NAME;
$this->pdoString .= ';host='.$this->config->DB_HOST;
$this->connection = new PDO($this->pdoString, $this->config->DB_USERNAME, $this->config->DB_PASSWORD);
}

public function query($q)
{
return $this->connection->query($q);
}

function __destruct()
{
$this->connection = NULL;
}
}
?>
Finally, I created a calling page to see how it ran:

<?php
require_once 'database.class.php';
$db = new database;
$arr = $db->query('SELECT * FROM users');
foreach($arr as $row)
{
echo $row['username'].'<br />';
}
?>
It's a start.