----- Original Message -----
From: "/dev/null" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 05, 2004 11:27 PM
Subject: New to MySQL
> wow, nice product. Pretty easy to use and install - I like it.
>
> I know enough about SQL and databases to just about break any flavor of
them
> I put my hand on ;-). A couple of pointers would be great.
>
> I'm creating a handfull of tables that will inter-depend on eachother,
using
> it within php. I have three basic questions:
>
> 1. Some of the tables have a unique ID, each new record should get the
next
> number available. Is there an easy data type that will do this for me, or
> do I need to get a record count myself and use record count + 1 when I
> insert a new row?
>
CREATE TABLE `pricelist` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(255) NOT NULL default '',
`PRICE` float NOT NULL default '0',
PRIMARY KEY (`ID`)
);
The column ID will be automaticly incemented. You just write INSERT INTO
pricelist (`NAME`,`PRICE` ) VALUES ('coffee',200);
To get the ID of inserted record you just call mysql_insert_id() (in MYSQL
API, in another just find similar function;) or call
SELECT LAST_INSERT_ID();
> 2. Some of the tables will need a timestamp of the exact date/time the
> record was added. Does timestamp do this, or again do I need to get
time( )
> and use that value on the insert myself?
You can use TIMESTAMP data type. If you update the row with NULL for the
TIMESTAMP field it will enter
the current date and time. If you omit this column in INSERT statement, it
will alos enter the current date and time.
>
> 3. Some of the IDs from one table are used in another table, for example
I
> have an accounts table and a sites table. Each account can have several
> sites, and each site entry has an "account" field that has the account ID
> (from question 1) to tell who the account owner is. Isn't there a joint
> query that I can run that is like 'SELECT * FROM `sites` WHERE `AccountID`
=
> ( SELECT `ID` FROM `accounts` WHERE `Name` = 'First Last' )'??? Or do I
> need to run the inner select and get the ID myself, then run the outer
> select? I admit, I know just enough SQL to wipe the database and drop all
> the tables ;-), so any help would be greatly apreciate.
>
MySQL before versions 4.1 doesn't support subqueries (you sample works on
4.1 or greater).
But you can write standart SQL joins.
Some samples:
SELECT sites.* FROM `sites`,`accounts` WHERE `sites`.`AccountID`
=`accounts`.`ID` AND `accounts`.`Name` = 'First Last';
also this is possible:
SELECT sites.* FROM `sites` INNER JOIN `accounts` ON (`sites`.`AccountID`
=`accounts`.`ID`) WHERE `accounts`.`Name` = 'First Last';
when you need something like NOT IN (SELECT .... FROM ... WHERE) you can
write this:
mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;
This example finds all rows in `table1' with an `id' value that is
not present in `table2' (that is, all rows in `table1' with no
corresponding row in `table2'). This assumes that `table2.id' is
declared `NOT NULL', of course. *Note LEFT JOIN optimisation::.
If there is no matching record for the right table in the `ON' or
`USING' part in a `LEFT JOIN', a row with all columns set to
`NULL' is used for the right table. You can use this fact to find
records in a table that have no counterpart in another table:
> Thanks!
>
> /dev/null
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
