SQL

change root password in MySQL

By greg | Posted in MySQL

how to change / fix the root password in MySQL If you have root authority on your server, login as root (su -) in a terminal session and at a command prompt, stop mysql and then restart it in a controlled mode: /etc/init.d/mysql stop or service mysql stop then /usr/sbin/mysqld...
Read more »

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...
Read more »

SQL: some basic JOIN syntax

By greg | Posted in MySQL

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 ...
Read more »

SQL: date – time displays, conversions

By greg | Posted in MySQL

UNIX TIME example: start = (1196440219) 2007-11-30 10:30:19 FROM_UNIXTIME(start) UNIX_TIMESTAMP() = NOW ex: SET start = UNIX_TIMESTAMP() mysql ...
Read more »

Increasing   phpMyAdmin’s export limits

By admin | Posted in MySQL

phpMyAdmin is one of the MySQL tools no one lives without. For non-programmers, it is great for looking at the contents of your database. For programmers, it is also indispensable for making changes, quick fixes. However, the 2 meg limit for uploading (importing) files can feel like a severe limit. It is set...
Read more »

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

By greg | Posted in all SQL's

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...
Read more »

SQL: from WHERE to HAVING

By admin | Posted in all SQL's

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 ...
Read more »

NorthWind db in MySQL

By greg | Posted 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...
Read more »

A few NorthWind MySQL PHP scripts

By greg | Posted in MySQL, PHP Advanced

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,...
Read more »