Sql tooling

Hi,

I'm more less in a similar situation here is what I was thinking to do.

1. Import DB to new DB and give it correct namings and prefixes etc.

2. Do not import DB and reflect incorrect namings in your models.

Obviously option 1 is the better one and you can add more imports as you build up you new app.

Hope it helps!

@aurawindsurfing

Option 1 is pretty much what I'm trying to do, I guess I don't have a choice. I will suffer for this one tho...

Thanks for your reply!

What I also did was make migration script part of my migrations. Then I created another table with migration and import all in one step. So each time I run migration I rerun the import. That way you could work it up gradually model by model, table by table.

Hope it helps!

@aurawindsurfing I don't know why I did not think about that before, but I just enabled mysql query logs, the file created gives me everything I need.. Thanks for your replies!

There you go ;-)

@AURAWINDSURFING - Hi sir, on the same codebase, which is php 5.6, the php current() function gives me a response that is a float, but it gives me the number i need, however in php7.3 the same function gives me float(the-number-i-need) I mean the index, instead of the value of this index, do you know how to fix this behavior? I’ve read the docs but it wasn’t really helpful.. Thank you!

Looks like here is your problem:

http://php.net/manual/en/function.current.php

Changelog ¶ Version Description 7.0.0 array is now always passed by value. Prior to this version, it was passed by reference if possible, and by value otherwise.

Looks like you need to update your codebase to 7.3 and do not come back or change that function behaviour.

You forget laravel has the pdo instance, so you could take that instance and build a class to run queries.

I actually piggybacked an old framework through laravel for a while during the upgrade to laravel process, some examples, shouldn't need explanation.

A class with the instance:

namespace App\Helpers;

use Illuminate\Support\Facades\DB;
use PDO;

class DbPdo
{

    protected static $dbh = null;

    protected function __construct()
    {
        
    }

    protected function __clone()
    {
        
    }

    public static function dbh()
    {

        try {
            $dbh = DB::connection()->getPdo();
        } catch (PDOException $e) {
            throw new pdoDbException($e);
        }
        return $dbh;
    }

    /**
     * run raw sql queries
     * @param  string $sql sql command
     * @return return query
     */
    public static function raw($sql)
    {
        return self::dbh()->query($sql);
    }

    /**
     * method for selecting records from a database
     * @param  string $sql       sql query
     * @param  array  $array     named params
     * @param  object $fetchMode
     * @param  string $class     class name
     * @return array            returns an array of records
     */
    public static function select($sql, $array = array(), $fetchMode = PDO::FETCH_OBJ, $class = '')
    {
        $stmt = self::dbh()->prepare($sql);

        foreach ($array as $key => $value) {
            if (is_int($value)) {
                $stmt->bindValue("$key", $value, PDO::PARAM_INT);
            } else {
                $stmt->bindValue("$key", $value);
            }
        }

        $stmt->execute();

        if ($fetchMode === PDO::FETCH_CLASS) {
            return $stmt->fetchAll($fetchMode, $class);
        } else {
            return $stmt->fetchAll($fetchMode);
        }
    }
/////more methods

Model method example that calls above:

    public function getDogs($offset = "", $rowsperpage = "", $dogsearch = "")
    {
        $pagingQuery = "LIMIT {$offset}, {$rowsperpage}";
        $dogsearch = $dogsearch . "%"; // not used here
        return DBO::select("SELECT * FROM " . "dc_" . "dogs " . $pagingQuery);
       
    }

Controller method calling it:

$dogs = DBS::getDogs($pages->getLimit2(), $pages->getPerpage(), $dogsearch);

And to make calls static:

<?php

namespace App\Helpers;

class DBS
{

    public static function __callStatic($method, $params)
    {
        $class = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2)[1]['class'];  //learned from Taylor
        $slh = strrpos($class, "\") + 1;
        $model = "\App\Models\SMVC" . "\" . substr($class, $slh, -10) . "Model";
        $instance = new $model();
        return $instance->$method(...array_values($params));
   }


}

So old queries can be in a temp name spaced model, call as you so desire, yet all of the nice view, blade, controller techniques, routing, etc are available till you or while you convert to laravel in another namespaced section.

Look Taylor made laravel easy, and flexible. And it's a php framework, so ....

An old app with all kinds of queries could be up and running in laravel in an evening, but that's once you have learned the basics, not day one. The docs are ok, but dig into the API. And if stumped, show code and ask here.

Above is no longer used, all laravel now. Just example of the flexibility of laravel. And how you can keep an app going while building new right in the same framework, thanks to different namespacing.

At one time this would not be as easy to accomplish.

To add, you can use getPdo or the db facade and use whatever query, as example a report:

    public function veryTemp()
    {
        $page = Request::input('page', '1');
        // other request as needed
        $bsdate = Request::input('begindate');
        $esdate = Request::input('enddate');
        $t1 = "b"; // thrown in to demo, report isn't paginated.
        $perpage = "5";
        $offset = ($page - 1) * $perpage;
        $krows = DB::select(); /// Put your count query here
        // above line is a query where you get total count////
        $numrows = $krows[0]->count;
        $pagingQuery = "LIMIT {$offset}, {$perpage}";
        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = `accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = `transactions`.`AccountID`))) where (`transactions`.`TransactionDate` Between :bdate and  :edate) group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName`";

        // a space and  " . $pagingQuery; would be added when paginating:
        //like long query ....  AccountName` " . $pagingQuery;

        $sth = DB::getPdo()->prepare($sql);
        $params = ['bdate' => $bdate, 'edate' => $edate];
        $sth->execute($params);
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);
        $report = new LengthAwarePaginator($quy, $numrows, $perpage);
        $pagelinks = ['bdate' => $bdate, 'edate' => $edate, 'page' => $page]; // report has none, for demo only
        // here $pagelinks are appends to query string

        $title = 'Monthly Report';
        $view = 'acct/mreport';
        $layout = ViewLayout::getLayout('acct/reporttp');
        $content = View::make($view)
                ->with('report', $report)
                ->with('pagelinks', $pagelinks); // if needed
        return view($layout)->with('content', $content)->with('title', $title);
    }

A join group by:

Which I simply iterate through for a nice report, see image:

In case no image shown: https://i.imgur.com/RlzeSEp.jpg

Just saying there's ways to work with laravel with old while developing new.

Thank you!