Project mySQL: Step by step guide to creating a database, table, performing an insert and selecting data from a table in mySQL from PHP.

Purpose
The purpose of this project is to demonstrate how to create a database, table, performing an insert and selecting data from a table in mySQL from PHP.

<?php
/*
  This script will create a database, if not present by setting $dbCreateIfNotPresent to 1 (may need additional permissions for this action).
  This script will also create a new table and get data out of that new table.
*/

$dbCreateIfNotPresent = 0;						/* If my database does not exist but I want it created, set this to 1.  Otherwise, set this to 0. */
$mySQLDatabaseName = "may be user name, account name, etc";		/* The name of my database where all my tables are at */
$mySQLConnection = mysql_connect("localhost", "user", "password");	/* The connection string to my database */

/* Let's do something */
if (!$mySQLConnection) { /* Could not connect due to an error */ die('Error connecting to mySQL: ' . mysql_error()); }
else {
      /* Point to my database */
      if (mysql_select_db($mySQLDatabaseName, $mySQLConnection)) {
								  /* Let's design a new table.  uniqueID serves as the auto-incrementing primary key */
								  $mySQLParameters = "CREATE TABLE myTable (uniqueID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(uniqueID), FirstName varchar(15), LastName varchar(15), Age int)";
								  /* Let's create the new table */
								  if (mysql_query($mySQLParameters, $mySQLConnection)) {
															echo "The new table was created.  ";
      															/* Let's insert a row of data into the new table we just created */
															$insertMySQLData ="INSERT INTO myTable (FirstName, LastName, Age) VALUES ('fname', 'lname', 99)";
															if (mysql_query($insertMySQLData, $mySQLConnection)) {
																					      echo "The new row of data was inserted into the new table we just created.  ";
																					      /* Let's select the new row of data we just inserted into the table */
																					      $rowsOfDataFromTable = mysql_query("SELECT * FROM myTable");
																					      while($singleRowOfData = mysql_fetch_array($rowsOfDataFromTable)) {
																														 echo "PK = " . $singleRowOfData['uniqueID'] . ", FirstName = " . $singleRowOfData['FirstName'] . ", LastName = " . $singleRowOfData['LastName'] . ", Age = " . $singleRowOfData['Age'] . "<br />";
																														}
																					     }
															else {
															      /* Could not insert new row of data into the table.  Let's see why. */
															      echo "Error inserting row of data into table: " . mysql_error();
															     }
														       }
								  else {
									/* Could not create the new table.  Let's see why. */
									echo "Error creating the new table: " . mysql_error();
								       }
								 }
      else {
	    if ($dbCreateIfNotPresent == 1) {
					     if (mysql_query("CREATE DATABASE " . $mySQLDatabaseName, $mySQLConnection)) { echo "Database \"" . $mySQLDatabaseName . "\" created.  The new table, if you defined one in this script, was not created.  You will need to run this script again in order to create the new table in the database that was just created."; }
					     else { echo "Error creating new database \"" . $mySQLDatabaseName . "\": " . mysql_error(); }
					    }
	    else {
		  /* Could not connect to my database.  Let's see why. */
		  echo "Error connecting to database \"" . $mySQLDatabaseName . "\": " . mysql_error();
		 }
	   }
      /* Let's close the database connection now that we are done */
      mysql_close($mySQLConnection);
     }
?>

Some General Info on Queries:
Writing some queries for mySQL may not be as intuitive as one would think...

For example, let's say you want to get a count of all returned rows that match a criteria, but also get a count of individual rows with the same "user". Intutitively this may be written as something like:

SET @DaysPast = 7;
SELECT
 
	COUNT(*) AS 'total rows by user'
 	,SUM(COUNT(*)) AS 'total rows by category'
FROM sometable
 -- Go back # of days from today
WHERE date_created > CURDATE() - INTERVAL @DaysPast DAY
-- Group results by category and then sub-group users for each category
GROUP BY category, user;

While 'total rows by user' may tally up correctly, tallying up or summing the count used by 'total rows by user' to get a count of all rows per category will not work. Instead, a sub-query (or a select inside a select) could be used:

SET @DaysPast = 7;
SELECT
 
	COUNT(*) AS 'total rows by user'
	,(
		-- Tally up total number of rows for all users of a category
		SELECT COUNT(*)
		FROM sometable sau
		WHERE sau.date_created > CURDATE() - INTERVAL @DaysPast DAY

		AND sau.category_id = st.category_id

	) AS 'total rows by category'
FROM sometable st
 -- Go back # of days from today
WHERE st.date_created > CURDATE() - INTERVAL @DaysPast DAY
-- Group results by category and then sub-group users for each category
GROUP BY st.category, st.user;

NOTE: The use of GROUP BY filters out duplicate rows being returned.

The CASE statement and CONCAT can be used in a statement such as what was previously shown to, in this case (see below), also show the name of a user if it is present:

SET @DaysPast = 7;
SELECT
 
	COUNT(*) AS 'total rows by user'
	,(
		-- Tally up total number of rows for all users of a category
		SELECT COUNT(*)
		FROM sometable sau
		WHERE sau.date_created > CURDATE() - INTERVAL @DaysPast DAY

		AND sau.category_id = st.category_id

	) AS 'total rows by category'
	,CASE
		WHEN NOT ISNULL(st.fName)
 AND NOT ISNULL(st.lName)
		THEN CONCAT(st.fName, ' ', st.lName)
		ELSE 'Incomplete Name'
	 END AS 'Username'
FROM sometable st
 -- Go back # of days from today
WHERE st.date_created > CURDATE() - INTERVAL @DaysPast DAY
-- Group results by category and then sub-group users for each category
GROUP BY st.category, st.user;

Associative Arrays:
More than likely you'll end up needing to dump more than one row of data from the database into a PHP array. The example below shows how to dump a row of data into an associative array and also read that array.

Place row(s) of data into array:

$salesitems = array();
while(...) {
$rowbuilder = array(
		'column1'=>$row["column1"]
		,'column2'=>$row["column2"]
		);
array_push($salesitems, $rowbuilder);
}

Read row(s) of data from the array:

for ($q = 0; $q < sizeof($salesitems); $q++) {
	$column1 = $salesitems[$q]["column1"];
	$column2 = $salesitems[$q]["column2"];
}

Extracting The 1st And 2nd Word From a String:
The ability to get the first or second word out of a string is fairly common. You could create a mySQL function to do it, but if you do not have the ability to create functions / stored procedures and use them, you could opt for something similar to the following:

-- Grabs 1st word
SET @ManufacturerName = 'Firstword-Secondword';
SELECT
	-- Grab the first word
	CONCAT(
		'%'
		,LCASE(
			SUBSTRING_INDEX(
				-- Replace any dashes with whitespace and return the 1st word
				REPLACE(TRIM(@ManufacturerName), '-', ' ')
				, ' ', 1)
			)
		,'%'
	);

-- Grabs 2nd word
SET @ManufacturerName = 'Firstword-Secondword';
SELECT
	CONCAT(
	'%'
	,LCASE(
		-- Grab the second word
		SUBSTRING(
			-- String to split to return the second word
			REPLACE(TRIM(@ManufacturerName), '-', ' ')
			 -- Locate the end of the first word
			,LENGTH(SUBSTRING_INDEX(
				-- Replace any dashes with whitespace and return the 1st word in this context as the length of the 1st word
				REPLACE(TRIM(@ManufacturerName), '-', ' ')
				, ' ', 1)) + 2
			 -- Return the length of the whole string so the substring can get the second word
			,LENGTH(REPLACE(TRIM(@ManufacturerName), '-', ' '))
			)
		)
	,'%'
	);