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 common string & math functions

November 26, 2016

updated 2021-01-06 POSITION() returns the position of the first occurrence of a substring within a string. Syntax : POSITION(substr IN str) Example : SELECT POSITION("ou" IN "w3resource"); Output : 6 Example : SELECT POSITION("w" IN "w3rewind"); Output : 1 LOCATE() Syntax : LOCATE(substr, str, pos) returns the position of the first occurrence of a substring within a string. An optional argument may be used...

Read more »

moodle: A query to see where course lessons are in the database

June 18, 2014

updated 2019-11-06 To see where-how the modules for each “Topic” are stored and kept in order. To see the record-id (instance) for the particular lesson, questionnaire, feedback request, or etc. The instance is the mdl_{MODULE}.id for a long time, if the module id is 11, it is the lesson module; if the module id is 19, it is the feedback module; etc. 1st query...

Read more »

MySQL: Some complex syntax

February 17, 2014

updated 2020-08-16 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

updated 2019-04-20 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...

Read more »

SQL: date – time displays, conversions

November 1, 2012
SQL: date – time displays, conversions

updated 10-28-2019 The CURDATE(), 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. The output of CURRENT_DATE has the format: YYYY-MM-DD The DATE_FORMAT() function allows you to return a date in a...

Read more »

in SQL, first, select, then insert, update, or delete

May 9, 2012

updated 2019-04-19 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 »

Enhancing, expanding limits, customizing, phpMyAdmin

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

The database tool, phpMyAdmin is NOT the only mysql database tool but it 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...

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 »