Main Page
 The gatekeeper of reality is
 quantified imagination.

Stay notified when site changes by adding your email address:

Your Email:

Bookmark and Share
Email Notification
Project "mySQL"
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), '-', ' '))
)
)
,'%'
);


About Joe