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...
SQL
mySQL common string & math functions
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...
moodle: A query to see where course lessons are in the database
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...
MySQL: Some complex syntax
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...
SQL: some basic JOIN syntax
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...
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...
in SQL, first, select, then insert, update, or delete
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...
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...
change root password in MySQL
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...
SQL: from WHERE to HAVING
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...
SQL db Injection, Cross-Scripting, RFI, and LFI
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...