Author: CairoCoder

Fast Export of Large MySQL Data to Excel using Laravel and Python

30th September 2023 Uncategorised No comments

Introduction:
Handling large datasets can be a challenge. When tasked with exporting thousands of rows from a MySQL database using Laravel, I found traditional methods lacking. This article showcases a solution that marries Laravel, Python, and Pandas for efficient data export.

Why Python with Pandas?
Python’s Pandas module is a powerhouse for data manipulation. Its prowess in swiftly exporting large datasets to Excel makes it an attractive choice for this challenge.

Integrating Symfony/Process in Laravel:
The symfony/process extension in PHP is our bridge to the Python world. It facilitates the execution of shell commands, in this case, running our Python script.

Laravel Export Function:
This function is the core of our solution. Here’s a breakdown:

$result = DB::table('full_report');
// ... [Database querying based on request parameters]
$sql = $result->toSql();
$bindings = $result->getBindings();
$filledSql = vsprintf(str_replace('?', "'%s'", $sql), $bindings);
$filename = 'FullReport-' . Carbon::now()->timestamp . '.xlsx';
$process = new Process(['/opt/homebrew/bin/python3', 'export.py', $filledSql, $filename]);
$process->run();
if (!$process->isSuccessful()) {
    throw new ProcessFailedException($process);
}
return response()->json(['success' => 'Export started successfully!']);

Abstract:
The function begins by querying the database based on request parameters. It then generates the SQL query and its bindings. Finally, using the Symfony Process, it runs the Python script, passing the SQL query and filename as arguments.

AJAX Request:
The AJAX request serves as the trigger for our export function.

$.ajax({
    // ... [AJAX setup and data parameters]
    success: function(response) {
        if (response.success) {
            console.log("Success:", response.success);
        }
    },
    error: function(error) {
        console.error("Error:", error);
    }
});

Abstract:
This asynchronous request sends data parameters to the server. Upon successful data export, it provides feedback to the user. In case of errors, they are logged for debugging.

Python Script (export.py):
This script is responsible for fetching and exporting the data.

import pandas as pd
import mysql.connector
import sys
import os
# ... [Database connection setup]
query = sys.argv[1]
filename = sys.argv[2]
df = pd.read_sql(query, cnx);
storage_path = os.path.join(os.path.dirname(__file__), '..', 'storage', 'app', 'public');
os.makedirs(storage_path, exist_ok=True);
df.to_excel(os.path.join(storage_path, filename), index=False);
cnx.close();

Abstract:
The script connects to the MySQL database using mysql.connector. It then fetches the data using Pandas based on the SQL query passed from Laravel. The data is saved as an Excel file in Laravel’s storage directory.

Conclusion:
By combining Laravel, Python, and Pandas, we achieve:

  • Speed: Rapid export of large datasets.
  • Flexibility: Harnessing the strengths of both PHP and Python.
  • Background Processing: Ensuring system responsiveness.

In web development, such integrative solutions can lead to enhanced efficiency and user satisfaction.

Teaser Slider

22nd March 2018 JavaScript and jQuery 6 comments

I have been looking for a while for a teaser slide the could do the following:

  1. Simple slider functionality.
  2. An option to tease for the next slide by small image within each slide.

All I found are normal slides that display a part of next slide. But what if I need to just promote with a small image, that not a must to be a part of next slide, while it’s like an Ad for it only.

That’s why I made this slider from scratch.

Here’s the Git Repository:

Teaser Slider Repository

Here’s the one page code, and your images should be inside ‘slides’ folder or whatever you want šŸ˜‰

<!DOCTYPE html>
<html>
<head>
    <title>CairoCode Slider</title>
    <style>
        /*-CairoCoder Slide-*/
        .cc-wrapper {
            width: 960px;
            max-height: 495px;
            overflow: hidden;
            position: relative;
        }
        .cc-slide {
            display: none;
        }
        .cc-slide.cc-active {
            display: block;
        }
        .slide {
            width: 100%;
            float: left;
            height: auto;
        }
        .teaser {
            float: left;
            max-height: 100%;
            position: absolute;
            top: 0;
            right: -15%;
            opacity: 0.8;
            cursor: pointer;
            border-radius: 5px 0 0 5px;
            transition-timing-function: ease-in;
            transition: 0.25s;
        }
        .teaser:hover {
            right: 0;
            opacity: 1;
            transition-timing-function: ease-out;
            transition: 0.25s;
        }
    </style>
</head>
<body>
<div class="cc-slider">
    <ul class="cc-wrapper">
        <li class="cc-slide cc-active">
            <img class="slide" src="slides/slide1.png" alt="slide1">
            <img class="teaser" src="slides/teaser1.png" alt="teaser1">
        </li>
        <li class="cc-slide">
            <img class="slide" src="slides/slide2.png" alt="slide2">
            <img class="teaser" src="slides/teaser2.png" alt="teaser2">
        </li>
    </ul>
</div>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"
        integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8="
        crossorigin="anonymous"></script>
<script>
    $('.teaser').click(function () {
        var next = $(this).parent().next('.cc-slide');
        $(this).parent().hide();
        if (next.length > 0) {
            next.show();
        } else {
            $('.cc-slide').hide().first().show();
        }
    });
</script>
</body>
</html>

How to shorten (terminal) command line (bash) prompt (hide computer name and user name) for Mac users and Linux

10th April 2017 Uncategorised No comments

Mac’s display name and username occupy space on the command line. some users who are working regularly on terminal may be disturbed by how long the computer name and username appears on the terminal line, here’s how to hide them.

Open Terminal, then go to Terminal menu > Preferences > Shell then select “Run command:” and write the following command:

PS1=”\w\$”;clear;

And here we go, no more computer name and username šŸ™‚

 

Buttons should not have a “hand cursor” !

25th March 2017 UI No comments

han cursor

The hand cursor is forĀ links

The hand (and often underlined text) signifies a link. Links are not buttons. Links came along with the web. To help users understand that they are different, they are given the hand cursor. It serves as an extra clue. Hereā€™s why:

  1. Clicking a link opens a web page or resource.
  2. (On desktop) I can right-click on a link and do many things (that I canā€™t do with a button). Open in new tab/window, save a link, copy address, add to reading list, bookmark it and more.
  3. (On mobile devices) I can tap and hold on a link and get a similar context menu as per the previous point.
  4. A link also tells me that I am just going somewhere else. I am not modifying any data or making changes in anyway (like a button is likely to do).

When a button has the hand cursor, it subtly suggests that the user is interacting with a link when theyā€™re not. If you want to give visual feedback when the user hovers, you can do so with other style changes such as background colour. A well-designed button does not need a hand cursor to help the user realise it does something.

The hand cursor is reserved for links. This is because they are unique in their behaviour. Browsers and Operating Systems have done the work for youā€Šā€”ā€Šbecause contrary to popular beliefā€Šā€”ā€Šbrowsers know best.

Links have always been handled this way since the web came alongā€Šā€”ā€Šthis is the convention of the web that you need not innovate on. You can rest easy knowing that browsers have you covered. This leaves you and your team to solve real problems.

Microsoftā€™s design guides talk about weak affordance:

Text and graphics links use a hand [ā€¦] pointer [ā€¦] because of their weak affordance. While links may have other visual clues to indicate that they are links (such as underlines and special placement), displaying the hand pointer on hover is the definitive indication of a link.

To avoid confusion, it is imperative not to use the hand pointer for other purposes. For example, command buttons already have a strong affordance, so they donā€™t need a hand pointer. The hand pointer must mean ā€œthis target is a linkā€ and nothing else.

Appleā€™s Human Interface Guidelines states that the hand cursor should be used when ā€œthe content is a URL linkā€. W3C User Interface guidelines says the same thing again with ā€œThe cursor is a pointer that indicates a linkā€.

Reference:
Buttons shouldnā€™t have a hand cursor

Get data from specific table within past x of months

4th September 2016 MySQL No comments

Here’s how to get data from specific table within past 20 months of current data.

The problem is that you need to get exactly the start of first month in the same period in the criteria you specified. in below MySQL query, you can achieve that easily.

SELECT * FROM `table`
WHERE `date` BETWEEN DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 20 MONTH), INTERVAL DAY(DATE_SUB(CURDATE(), INTERVAL 20 MONTH)) -1 DAY) AND CURDATE()

Prevent CSS and JavaScript files caching !

4th September 2016 JavaScript and jQuery No comments

Simple Client-side Technique

In general, caching is good.. So there are a couple of techniques, depending on whether you’re fixing the problem for yourself as you develop a website, or whether you’re trying to control cache in a production environment.

General visitors to your website won’t have the same experience that you’re having when you’re developing the site. Since the average visitor comes to the site less frequently (maybe only a few times each month, unless you’re a Google or hi5 Networks), then they are less likely to have your files in cache, and that may be enough. If you want to force a new version into the browser, you can always add a query string to the request, and bump up the version number when you make major changes:

<script src="/myJavascript.js?version=4"></script>

This will ensure that everyone gets the new file. It works because the browser looks at the URL of the file to determine whether it has a copy in cache. If your server isn’t set up to do anything with the query string, it will be ignored, but the name will look like a new file to the browser.

On the other hand, if you’re developing a website, you don’t want to change the version number every time you save a change to your development version. That would be tedious.

So while you’re developing your site, a good trick would be to automatically generate a query string parameter:

<!-- Development version: -->
<script>document.write('<script src="/myJavascript.js?dev=' + Math.floor(Math.random() * 100) + '"\><\/script>');</script>

Adding a query string to the request is a good way to version a resource, but for a simple website this may be unnecessary. And remember, caching is a good thing.

It’s also worth noting that the browser isn’t necessarily stingy about keeping files in cache. Browsers have policies for this sort of thing, and they are usually playing by the rules laid down in the HTTP specification. When a browser makes a request to a server, part of the response is an EXPIRES header.. a date which tells the browser how long it should be kept in cache. The next time the browser comes across a request for the same file, it sees that it has a copy in cache and looks to the EXPIRES date to decide whether it should be used.

So believe it or not, it’s actually your server that is making that browser cache so persistent. You could adjust your server settings and change the EXPIRES headers, but the little technique I’ve written above is probably a much simpler way for you to go about it. Since caching is good, you usually want to set that date far into the future (a “Far-future Expires Header”), and use the technique described above to force a change.

If you’re interested in more info on HTTP or how these requests are made, a good book is “High Performance Web Sites” by Steve Souders. It’s a very good introduction to the subject.

Another useful article:

Can We Prevent CSS Caching?

Get the “text” of the selected “option” using PHP and JavaScript

19th August 2016 JavaScript and jQuery, PHP No comments

This is not something that can be done through PHP alone. The PHP script can only “see” the information which is posted (the value for the selected option that is posted). You can use javascript to alter a hidden input field with the text contents of a selected option, and this will be included in the $_POST array:

<form  action="test.php"  method="POST">  
    <select id="test" onchange="document.getElementById('text_content').value=this.options[this.selectedIndex].text">
     <option value="1">Test One</option>
     <option value="2">Test Two</option>
    </select>

<input type="hidden" name="test_text" id="text_content" value="" />
</form>

 

This will make the $_POST['test_text'] available with the selected index (but you should also force the onchange() function when the page loads so that it will be populated even if the user leaves the select field at the default value.

Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL

28th July 2016 MySQL No comments

Differences

  • KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don’t enforce any restraints on your data so they are used only for making sure certain queries can run quickly.
  • UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.Your database system may allow a UNIQUE index to be applied to columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (the rationale here is that NULL is considered not equal to itself). Depending on your application, however, you may find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns.
  • PRIMARY acts exactly like a UNIQUE index, except that it is always named ‘PRIMARY’, and there may be only one on a table (and there should always be one; though some database systems don’t enforce this). A PRIMARY index is intended as a primary means to uniquely identify any row in the table, so unlike UNIQUE it should not be used on any columns which allow NULL values. Your PRIMARY index should be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as “countrycode” in a list of countries, you can use that instead.Some database systems (such as MySQL’s InnoDB) will store a table’s records on disk in the order in which they appear in the PRIMARY index.
  • FULLTEXT indexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three – which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).Where the other index types are general-purpose, a FULLTEXT index is specialised, in that it serves a narrow purpose: it’s only used for a “full text search” feature.

Similarities

  • All of these indexes may have more than one column in them.
  • With the exception of FULLTEXT, the column order is significant: for the index to be useful in a query, the query must use columns from the index starting from the left – it can’t use just the second, third or fourth part of an index, unless it is also using the previous columns in the index to match static values. (For a FULLTEXT index to be useful to a query, the query must use all columns of the index.)

How to trigger jQuery ‘autocomplete’ manually on ‘keyup’ event

11th July 2016 JavaScript and jQuery No comments

Here, Try doing it like this :

var availableTags = [
  "Perl",
  "PHP",
  "Python",
  "Ruby"
];
$('input#mainSearchBox').autocomplete({
  source: availableTags,
  minLength: 0
});

$('input#mainSearchBox').autocomplete("disable");

$('input#mainSearchBox').keyup(function() {
  var value = $('input#mainSearchBox').val();
  var last = value.substr(value.length - 1);
  if (last == "*") {
    var valToSearch = value.substr(0, value.length - 1);
    $('input#mainSearchBox').autocomplete("enable");
    $('input#mainSearchBox').autocomplete("search", valToSearch);
  } else {
    $('input#mainSearchBox').autocomplete("disable");
  }
});

<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<input type="text" name="test" id="mainSearchBox">

References :

Have a look at :

  1. http://api.jqueryui.com/autocomplete/#method-search
  2. http://api.jqueryui.com/autocomplete/#method-enable
  3. http://api.jqueryui.com/autocomplete/#method-disable

What I have done :

  1. Loaded/Initialized autocomplete on the textbox and then disabled it.
  2. Whenever key-up event is triggered, I checked if last character in the input is *,

a. if it is, then enable autocomplete and force search on the text-box, with the input value without *.
b. If it is not, then disable the autocomplete.

Finally, my free space!

25th June 2016 Welcome page No comments

Here we go, finally I established my blog, or actually “my free space” as I love to call it. šŸ˜‰

I established this blog in order to publish code related articles, code hints, code workarounds and tips and tricks.

Here’s a small brief about me ( not small enough šŸ˜€ ).

It’s me Said Abdul Aziem Mohamed, a Webmaster Manager at DMG Group. I have a passion for User Experience and Web Development.

Everything I know about UI, UE, CSS, HTML, jQuery, PHP and MySQL was all self taught by looking online and buying books and video tutorials related to UX and Web Development. I have been continuously employed since high school and throughout college, which helped ramp me to my current positions at DMG. I’m also in love with my current position at DMG as Webmaster Manager.

I am discovering that I seriously enjoy user flow, User Experience and Web Development, and would like to transition my career towards this wire-framing and development path.

I love figuring out what makes users tick and click, also I adore developing easy to use and smooth back-ends.

I also am getting serious about my Web Design, so feel free to message me if you would like a web design, psd to html/css or anything else! I would currently like to learn (or get to know better): User Experience Research, SASS, HTML 5, jQuery/jQuery-UI, JavaScript, AJAX, IOS Development. ——-

Interesting Points of Discussion ——-

  1. What’s more important? Creating a product to create fantastic metrics? Or creating a product that creates a “good feeling” and a lasting impression?
  2. When should you use tables, and when should you use CSS? Always CSS for presentation, of course! But sometimes divinities will get you down. How do you get around that?
  3. Find me a way to wrap a really long word (like supercalifragilisticexpialidocious) onto the next line of a 20px width div in CSS that works in all browsers. (?)
  4. When to use CMS, and when to start a “From Scratch” website? Specialties: HTML Markup, CSS, User Experience, User Interface, Photoshop, Photography, Agile, JIRA, PHP, OOP, JavaScript, jQuery, PHP Frameworks.