Since we established the "id" column as our PRIMARY key (when establishing the table), MySQL needs this info so it knows which row we are talking about. This action is dependant on the "id" because when doing a REPLACE, you must include the PRIMARY (unique) column. REPLACE INTO table_name ( `col_A`, `col_B`) VALUES ( `col A data`, `col B data`) $sql = "INSERT INTO music (album) VALUES ('Abbey Road 3 test')" Here, we're not including the "id" column, however, since the "id" column is set up to "AUTO INCREMENT" MySQL automatically assigns the new data the next incremental number. $sql = "INSERT INTO music (id,album) VALUES ('2','Abbey Road 2 test')" In this example, we don't include the "artist" column in our SQL. if there is no data available for col_B, use empty single quotes as: $sql = "INSERT INTO music (id,artist,album) VALUES ('1','the beatles','Abbey Road')" I've color coded each "pair" so you can see that the data " col_B_data' that is going to go into column name " col_B" must be second. Inserts a row into the table using the data defined in the VALUES section.Īs you can see, the column names are established within the first set of parenthesis, the order of the data in the second set of parenthesis must match the order of the column names defined int he first set of parentheses. INSERT INTO table_name ( ` col_A`, ` col_B`, ` col_C`) VALUES ( ` col_A_data`, ` col_B_data`, ` col_C_data`) $sql = "ALTER TABLE music MODIFY id int(8) unsigned NOT NULL AUTO_INCREMENT " You can modify the table using the following command: This is up to you on how you would like to maintain the id., which in this case is the "PRIMARY KEY" which needs to be unique and is what MySQL needs to keep things orderly: NOTE: You may want to use AUTO INCREMENT to have MySQL automatically maintain the id field when you add a new item to the table. Mysql_query($sql) $sql = "CREATE TABLE IF NOT EXISTS `music` ( Mysql_query($sql) CREATE TABLE $sql = "DROP TABLE IF EXISTS music" The example SQL statements are simple strings (no variables and such), so you should be able to simply use the "stuff between the double quotes" in your other language, app or directly in the MySQL console.ĬREATE DATABASE $sql = "CREATE DATABASE IF NOT EXISTS m圜oolDatabaseName"
The test file contains a few helper functions and outputs "what you do is what you see".Ĭlick here to download the test.php file (it's wrapped in a ZIP named "mysql_basics_php.zip") So there may be some differences in how it works on Apache or some other environment.
The test file was tested on a Windows 7 box running IIS. I've created a test file for you to download and use as you give things a whirl on your own system. Mysql_select_db("m圜oolDatabaseName", $link) Check username and passowrd.".mysql_error()) $link = mysql_connect( "localhost", "adminUserName", "adminPassword") We put a reminder in each example, which you should replace with: NOTE: For those using PHP, you'll need to established a connection before attempting to use any of the examples below. The following statements are covered in this page: If you are having difficulties, try using singe quotes ( ' ) or tricky quotes ( ` - on keyboard key next to number 1) around your data.
There may be some issues with the kind of quotes you use around your data. Here you will find a collection of basic MySQL statements that should prove useful for basic CRUDS operations (create, replace, update, delete, select).