----------------------------------------------------------------------------------------
Leo's Programming Reference Sheet
Revision Log:
v1.0 December 11th, 2009
----------------------------------------------------------------------------------------
Disclaimer: Most of these are ripped from tizag.com
----------------------------------------------------------------------------------------
--------------------------- Section 1: Common MySQL commands ---------------------------
INSERT INTO example (name, age) VALUES('Timmy Mellowman', '23')
SELECT * FROM example
SELECT * FROM example WHERE name='Sandy Smith'
SELECT * FROM example WHERE age BETWEEN x AND y (where x and y are integers)
UPDATE example SET age='22' WHERE age='21'
DELETE FROM example WHERE age='15'
--------------------------- Section 2: Common MySQL queries in PHP ---------------------
--------------------------- MySQL connect to database in PHP ---------------------------
<?php
mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
?>
--------------------------- MySQL query in PHP -----------------------------------------
<?php
// connect to database
$result = mysql_query("SELECT * FROM example") or die(mysql_error());
$row = mysql_fetch_array( $result );
echo "Name: ".$row['name'];
echo " Age: ".$row['age'];
?>
--------------------------- MySQL fetch array in PHP -----------------------------------
<?php
// connect to database
$query = "SELECT * FROM example";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['name']. " - ". $row['age'];
echo "
";
}
?>
--------------------------- Count rows in table ----------------------------------------
<?php
// connect to database
$result = mysql_query("SELECT * FROM tablename");
$num_rows = mysql_num_rows($result);
echo $num_rows;
?>
--------------------------- Prevent SQL injection --------------------------------------
$name_bad = mysql_real_escape_string($name_bad);