scriptygoddess

Another mini-cheatsheet… I know this stuff, but I’m always looking it up for the exact syntax.

Connect to MySQL and to the database - mysql_connect
$connect = mysql_connect("localhost", "mysqluser", "userpassword") or die(mysql_error());

Select the database - mysql_select_db
mysql_select_db("databasename", $connect) or die(mysql_error());

Close connection - mysql_close
mysql_close($connect) or die(mysql_error());

Select data from the database - mysql_query
$myquery = "SELECT tablefield, tablefield2 FROM tablename WHERE tablefield = '" .$variable. "' AND tablefield2 = '" .$variable2. "'";
(or)
$myquery = "SELECT * FROM tablename WHERE tablefield = '" .$variable. "' AND tablefield2 = '" .$variable2. "'";
$myresult = mysql_query($myquery ) or die(mysql_error());

Selecting, Grouping, and Ordering the data
SELECT [ALL | DISTINCT] column1[,column2] FROM table1[,table2] [WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ]

Display Data - mysql_fetch_array
while ($row = mysql_fetch_array($myresult)) {
echo $row["tablefield"] . ” and ” . $row["tablefield2"] . “<br />”;
}

Insert data
$query_insert = "INSERT INTO tablename(tablefield, tablefield2) VALUES('" .$variable. "', '" .$variable2. "')";
$result_insert = mysql_query($query_insert) or die(mysql_error());

Insert data, multiple rows
$query_insert = "INSERT INTO tablename(tablefield, tablefield2) VALUES ('" .$variable. "', '" .$variable2. "'),
('" .$variable. "', '" .$variable2. "'),
('" .$variable. "', '" .$variable2. "')";
$result_insert = mysql_query($query_insert) or die(mysql_error());

Update data
$query_update = "UPDATE tablename SET tablefield = '" .$variable. "', tablefield2 = '" .$variable. "' WHERE tablefield3 = '" .$variable3. "'";
$result_update = mysql_query($query_update) or die(mysql_error());

Delete a row from a table
$query_delete = "DELETE FROM tablename WHERE tablefield = '" .$variable. "'";
$result_delete = mysql_query($query_delete) or die(mysql_error());

Some good references:
http://sqlcourse.com
http://www.w3schools.com/sql/

This entry was posted on Saturday, March 24th, 2007 at 12:27 pm and is filed under PHP, mySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.