Written by - Find your ‘MAMP’ directory in ‘Applications’
- Find ‘conf’ and open it up..
- Open up php4 or php5 (depending on your enabled version)
Line 270 should be something like this:270
error_reporting = E_ALL
Line 277 will be:
277
display_errors = Off
- Change this to:
277
display_errors = On
- Restart servers
Error Reporting in MAMP
SQL – part 36
Auto Increment
Say we had this table:
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL AUTO_INCREMENT, FirstName VARCHAR(255), LastName VARCHAR(255) NOT NULL, PRIMARY KEY (ID) ) |
We no longer have to populate the ID field as it will automatically increment. Starting at 0 and working up, even deleted fields mean that the next value takes the next unused (at all) unless specified.
You could change the starting point by using:
1 | ALTER TABLE People AUTO_INCREMENT=20 |
SQL – part 35
I want to ALTER the world!
So, say after we’ve created a databse we need to edit it slightly, we can use:
Add column:
1 | ALTER TABLE TABLE ADD COLUMN datatype |
Delete column:
1 | ALTER TABLE TABLE DROP COLUMN COLUMN |
Change datatype of column:
1 | ALTER TABLE TABLE ALTER COLUMN COLUMN datatype |
PHP Converting Time Stamps
YYYY-MM-DD HH:MM:SS ~ Unix Timestamp
1 2 3 4 5 6 7 8 9 10 | function convert_datetime($stamp){ list($date, $time) = explode(' ', $stamp); list($year, $month, $day) = explode('-', $date); list($hour, $minute, $second) = explode(':', $time); $timestamp = mktime($hour, $minute, $second, $month, $day, $year); return $timestamp; } |
Just stick that in somewhere and you’re good to go, this is useful when converting between SQL and more localised formats.
SQL – part 34
DROP THE INDEX
1 | ALTER TABLE TABLE_NAME DROP INDEX index_name |
DROP THE DATABASE
1 | DROP DATABASE database_name |
DROP THE TABLE
1 | DROP TABLE TABLE_NAME |
DROP THE (DATA IN THE) TABLE
1 | TRUNCATE TABLE TABLE_NAME |
SQL – part 33
The default values!
Whenever we instert data we might need a NOT NULL field, but what if we could just fill it in with blanks or default values?
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.
1 2 3 4 5 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255), Country VARCHAR(255) DEFAULT 'United Kingdom', ) |
And so, whenever someone tries to populate a record without Country set, it will automatically resort to ‘United Kingdom’!
And we can combine it with functions (coming soon) to make:
CREATE TABLE Orders (
ID int NOT NULL,
Number int NOT NULL,
Person int,
OrderDate date DEFAULT GETDATE()
)
And that’s it!
Or so you thought…
1 | ALTER TABLE People ALTER Person SET DEFAULT 0 |
And:
1 | ALTER TABLE People ALTER Person DROP DEFAULT |
SQL – part 32
Check
That’s right, check. We can set limits on things now. Say we want ages bigger than 0 so that they cant fool the system by typing a negative value!
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255), Age INT, CHECK (Age>0) ) |
And as usual you can add or remove as so:
ALTER TABLE People ADD CONSTRAINT checkAGE CHECK (Age>0)
And:
1 | ALTER TABLE People DROP CHECK checkAGE |
SQL – part 31
FOREIGN KEY
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Lets have a look:
People table:
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 1 | Simon | Dangers | 23 | England |
| 2 | Simon | Fridge | 25 | England |
| 3 | James | Fridge | 46 | America |
Orders table:
| ID | Number | Person |
|---|---|---|
| 1 | 01456 | 2 |
| 2 | 01457 | 2 |
| 3 | 01458 | 3 |
Note that the “Person” column in the “Orders” table points to the “ID” column in the “People” table.
The “ID” column in the “People” table is the PRIMARY KEY in the “People” table.
The “ID” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
1 2 3 4 5 6 7 | CREATE TABLE Orders ( ID INT NOT NULL, NUMBER INT NOT NULL, Person INT, PRIMARY KEY (ID), FOREIGN KEY (Person) REFERENCES People(ID) ) |
And the two datbases are linked!
SQL – part 30
PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
So…
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255) NOT NUL, Country VARCHAR(255), PRIMARY KEY (ID) ) |
And adding and deleting is actually just as easy as before just replace UNIQUE with PRIMARY KEY
SQL – part 29
UNIQUE
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
So…
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255) NOT NUL, Country VARCHAR(255), UNIQUE (ID) ) |
Means that ID is unique and NO two values can be the same.
And if we want a combination of two fields:
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255) NOT NULL, Country VARCHAR(255), CONSTRAINT constraintIDNAME UNIQUE (ID,FirstName) ) |
This will make the constraint constraintIDNAME relating to the unique values ini ID and FirstName, here you could have a lot of ID’s all the same and a lot of FirstNames all the same but only 1 combination of an ID and FirstName.
In order to add a constraint after the creation you can use:
1 | ALTER TABLE People ADD CONSTRAINT constraintNAME UNIQUE (ID) |
And the following to get rid of it:
1 | ALTER TABLE People DROP INDEX constraintNAME |