SQL

MySQL Debug: How to log all queries of an event

December 6, 2016

updated 12-14-2016 MySQL – DEBUG example: You are running a software package – which hides its queries so far down in so many included and-or overriding class-function (php?) files it is not worth any more effort trying to find the right one(s). The easiest method to find all the queries that run during a given event requires only running 2+ commands: To start and...

Read more »

MySQL: Some complex syntax

February 17, 2014

updated 2016-10-17 note: "Using comma separated lists in a database field is an anti-pattern and should be avoided at all costs." "properly normalize the table structure" "I strongly advise that you fix your current database structure so you are not storing the data in a comma separated list." – from www.stackoverflow.com If you cannot alter your table, then you should be able to query...

Read more »

SQL: some basic JOIN syntax

November 15, 2012

INNER JOIN and , (comma) are equivalent in the absence of a join condition and "JOIN" is defined to be equivalent to an "INNER JOIN" Examples: SELECT * FROM customer AS c, orders AS o WHERE c.custkey = o.custkey or (same): SELECT * FROM customer AS c JOIN orders AS o WHERE c.custkey = o.custkey or (same): SELECT * FROM customer AS c INNER...

Read more »

in SQL, first, select, then update or delete

May 9, 2012

updated 2017-02-14 My notes and sample code from experimenting and cleaning-up data. Always run a select statement before running an update or delete. Note: The phpMyAdmin tool, which is very valuable, adds "limit 0,30″ to the end of most SELECT statements so don’t be tricked into thinking you have been shown all the selected records. You can change it by setting the config variable...

Read more »

SQL: date – time displays, conversions

November 1, 2011

updated 09-22-2016 Unix Time UNIX TIME: the number of seconds since the first second of the first day of 1970. The CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() functions return values in the connection’s current time zone, which is available as the value of the time_zone system variable. In addition, UNIX_TIMESTAMP() assumes that its argument is a datetime value in the current time zone. Functions that...

Read more »

Enhancing, expanding limits, customizing, phpMyAdmin

October 29, 2011
Enhancing, expanding limits, customizing, phpMyAdmin

The database tool, phpMyAdmin is and has been, the most popular and most awarded among MySQL administrators and developers. It was mentioned by the author of the first php and MySQL book I read. The administrators and developers I have met since, all use it. It is also one which can be installed on the website. Over time, I have learned ways to customize...

Read more »

change root password in MySQL

September 30, 2010

how to change / fix the root password in MySQL First: If you install MySQL through an X-window GUI application, like System >> Administration >> Add-Remove Software, then, it will install without prompting, and the one mysql user that is created, "root" will have a blank password. From a terminal command line, type $ mysql -u root -p Enter password: [just hit enter...

Read more »

SQL: from WHERE to HAVING

June 22, 2010

WHERE filters results before they are grouped. HAVING filters results after they are grouped. correct: count players by team: SELECT COUNT(*) count, plheight_ft FROM ALplayers WHERE plheight_ft = 6 GROUP BY plheight_ft ORDER BY count error: SELECT COUNT(*) count, plheight_ft FROM ALplayers WHERE count = 6 // 1 ORDER BY ckname ( — o.k. — I saw 7 dups.(2 each) ) now, to see...

Read more »

SQL db Injection, Cross-Scripting, RFI, and LFI

March 27, 2010

It is possible for a hacker to enter the following seemingly innocuous text into the UserName textbox to gain entry to the system without having to know a valid user name and password: ‘ Or 1=1 — The hacker breaks into the system by injecting malformed SQL into the query. This particular hack works because the executed query is formed by the concatenation of...

Read more »