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 $cfg to...
SQL
SQL: some basic JOIN syntax
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...
SQL: date – time displays, conversions
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. example: $start = ’1234567890′...
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...
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...
NorthWind db in MySQL
Microsoft’s SQL Server 2000 comes with 2 sample databases, one of which is NORTHWIND a sample accounting database Below are the table definitions needed to export NorthWind from MS SQL Server 2000 and import NorthWind into MySQL I have exported out the main table definitions and their data and put them in the MySQL format and imported them into MySQL. (see the definitions below)...
A few NorthWind MySQL PHP scripts
Note that the dates on the NorthWind data range only across 1 full year, 1997: from July 1996 to May 1998. Each line of data below comes from 3 files (tables): the orders file, the order-details file, and the customer file. There are also files of employees, products, suppliers, shippers, and more. <p align="center"> Orders Shipped the 7 days before today in 1997 </p>...