Tag: mysql

Latest Useful Web Resources and Links #1, March 2016

03/12/2016 Aaron Belchamber No Comments

I created this page as a running repository of useful, miscellaneous web notes for later reference.

Javascript & JQuery

How to get the latest version of JQuery:

alert('JQuery: '+$.fn.jquery);   // Outputs JQuery: x.x.x

Useful info about Javascript and JQuery source maps — an explanation about what they are and how they are used:

http://elijahmanor.com/the-magic-of-the-jquery-1-9-source-map/

Parsley — A reliable and easy Javascript form validation library:

http://parsleyjs.org/

Get hostname in Javascript:

// For the host name only, use:

window.location.hostname

Server Notes, LAMP/WAMP

Latest WAMP install instructions — in less than 5 minutes you can deploy WAMP with MySQL server on localhost.  Not bad for quick local dev server.

http://forum.wampserver.com/read.php?2,123606

Symfony

A utility for Symfony to convert those awful annotations to YML files:

https://packagist.org/packages/sed/route-exporter-bundle

Helpful Symfony 3.0 base commit, to start new PHP Symfony projects:

https://github.com/itrascastro/Symfony-3-Base-Project
https://github.com/keefekwan/symfony2_forms

Best example of a form collection at work I think is here (many thanks to the programmers making these publicly accessible!)

https://github.com/sevnekish/user_manager

Symfony 2 performance tips:

http://labs.octivi.com/mastering-symfony2-performance-internals/

Form Type setDefaultOptions weird error:

http://stackoverflow.com/questions/31659102/symfony2-error-on-submitting-form-with-a-collection-of-forms-warning-spl-obj?rq=1

General Web Notes & Utilities

Free OST file viewer worked well, allows you to save a person’s Microsoft Exchange mailbox email file and open and view the contents without messing with the .ost file in Outlook!

http://www.ostviewer.com/

Waka Time — Free web development time tracking utility:

https://wakatime.com

Websites that show the latest hiring trends in the tech industry:
https://news.ycombinator.com/item?id=11202954

https://whoishiring.io/#!/stats/

A good simplified explanation and list about HTTP 2:

https://www.keycdn.com/support/http2/

Good article about the anatomy of a URL:

http://www.skorks.com/2010/05/what-every-developer-should-know-about-urls/

Steps for setting up DNS on a Ubuntu server:

http://mixeduperic.com/ubuntu/seven-easy-steps-to-setting-up-an-interal-dns-server-on-ubuntu.html

PHP/MySQL

PHP SQL injection – good article:

http://phpdelusions.net/sql_injection


Aaron Belchamber
22 years experience in marketing and building businesses
Senior PHP & Symfony Web Developer, Business Analyst, Marketing Director
Belchamber.us / TopDocsTalk.com
(904) 294-0803 • Jacksonville, Florida


MySQL DATE_FORMAT: Grouping Date & Time value fields into single days made easy

09/24/2015 Aaron Belchamber No Comments

To conform a datetime field to just a date in your results and make it easy to group TIMESTAMP fields in the format “2015-12-25 12:23:23” so you can group any datetime value into actual days for daily reports, use “DATE_FORMAT” below:

DATE_FORMAT(datetime_field, '%Y-%m-%d')

In other words, if you have values from a SELECT statement that are date AND time stamps and you want to group all those values into the DAY, DATE_FORMAT converts the value for you.

So, with these values in the field “created_at”:

2015-03-01 11:11:11
2015-03-01 16:13:132015-03-01 19:01:01
2015-03-03 11:19:23

The following MySQL SELECT statement

SELECT DATE_FORMAT(created_at,'%Y-%m-%d') as created_at_days FROM your_table GROUP BY created_at_days

Will result in the following results:
2015-03-01
2015-03-03


Changing getArrayResult() to getArray() fixes weird Symfony Doctrine gotcha

06/18/2015 Aaron Belchamber No Comments

A strange Symfony Doctrine issue got me the other day.  I guess it’s a lack of clear understanding of how Doctrine hydrates database query results when using getArrayResult() instead of getArray().  It seems that getArrayResult() returns just the first record from your query, no matter how many records the query actually returns but getArray() returns an multi-dimensional array containing all results from the same query.

So, I needed a list of all records to display on screen, not just the first one.  All I did was change getArrayResult() to getArray() and this fixed my problem.

But why?!!  Can anyone please provide the answer?!  It’s not on Doctrine’s website, it’s not on stack.  I’ve Googled it I’ve Binged it, if I had nothing better to do maybe I’ll Duckduckgo it.  I’m afraid when I find the answer it will be so obvious that’s why I don’t get it.  I’ve read some users say it depends on what you are selecting in the query, if it’s the record index or some other value or combination of values but have not found any documentation explaining the differences clearly.  Doctrine’s horribly convoluted documentation  says this about getArrayResults():

Query#getArrayResult(): Retrieves an array graph (a nested array) that is largely interchangeable with the object graph generated by Query#getResult() for read-only purposes.

There’s money to be made — make an ORM that’s straightforward and intuitive.


Planning to design or redesign an organization’s database? Get back to basics and refer to MySQL’s reserved keyword list

06/16/2015 Aaron Belchamber No Comments

Oracle’s MySQL is a staple in the open source web environment world.  Being familiar with the reserved words the database uses is just one way to make sure you don’t have to refactor code or special-escape field names in queries later with the menacing back tick: `

Here is the link to MySQL’s reserved keyword’s list.  The MySQL keywords table is very useful to use in order to avoid collisions up front when designing new databases and troubleshooting possible problems of legacy systems.

I design, and redesign a lot of databases for different organizations and companies.  Planning and designing a database will require naming tables and fields logically so a successor can navigate the data and coders can troubleshoot problems without reinventing the wheel — it makes the system’s design an efficient part of the overall system of the organization.  This leads to less time wasted, more stable systems, and more up-time later.  That’s why when it comes to web development and software programming I’m a Business Systems Architect first– then many other job titles second.

– Aaron Belchamber


MySQL: Using sub-selects to filter results of one table that is not contained in another table

03/16/2015 Aaron Belchamber No Comments

In MySQL, if you have two tables with a shared index and you would like to show the results of one table where the index is not within another table, you can use “NOT IN”.

SELECT * FROM table2 WHERE some_field not in 
    (SELECT some_field FROM table1)

Try Googling “MySQL NOT IN” and you get everything but this command, it’s almost as if they need to have an alias for this simple command since these two words are in EVERYTHING and on so many web pages. Perhaps “NOTCONTAINEDIN” would be better, all in one word? Just a suggestion.

In the sub-select, the query inside parenthesis, you can take this a step further and apply conditions to the filter so you can exclude results in “table2” more narrowly than just being in “table1”, perhaps if “table1” had a status of “EXPIRED” in a field, then you could add that criteria to be more selective.