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/

I’m working on a plugin that required getting the earliest date in the database, and also getting the oldest date in the database. I was hoping that there was a built-in function that did this - and there is.

I found this helpful article on various mySQL techniques. And this is the one I was going to use:

MIN() will return the earliest date, while MAX() returns the latest date.

So the mySQL statement will look something like this:

SELECT MIN(DateFieldName) FROM TableName

So tired of searching for this, (I know it’s simple, but for some reason I can’t seem to keep it in memory. LOL!)

You just deleted all the rows in a table - but when you add new rows - they’re still starting where the deleted ones left off.

Run this sql query to reset the autoincrement value:

TRUNCATE TABLE (TABLENAME)