Here’s a collection of useful lines of script that I’ve been putting into place over the last 24 hours as I update some PHP scripts to work with PDO rather than the old mySQL. It seems mySQL is soon to be unsupported by PHP and so future updates of PHP could break your old code. Hopefully some of this is of use. I’ve not included much (if any) error handling or echos to keep the code nice and clear. For a more indepth look at using PDO code and moving away from mySQL check out this great walkthrough tutorial/overview.
I’m very new to all this server coding so please let me know if you can see any major errors or ways to make items more secure.
Here’s the useful code:
CONNECT TO DATABASE
// connect to server
/*** mysql hostname (this connects to your local host, i.e. if you've got a local server running with MAMP, enter your needed info) ***/
$dsn = 'mysql:host=localhost;dbname=your_database_name';
/*** mysql username ***/
$username = 'root';
/*** mysql password ***/
$password = 'root';
/*** connect to server This allows you to easily call the server using $db later on in your script. ***/
$db = new PDO($dsn, $username, $password);
QUERY TABLE
//This selects everything from Login Outcome.
$query = $db->query("SELECT * FROM table_name");
//This selects all the info on a row that meets certain criteria… i.e. where username = a variable called $username
$check = $db->query("SELECT * FROM table_name WHERE column_name = '$variable or your text/value'");
GET NUMBER OF ROWS GATHERED IN A QUERY
$number_of_rows = $check->rowCount();
INSERT INTO TABLE
$sql2="INSERT INTO table_name(column_name_1, column_name2)VALUES('your $variable, text or value here', 'your $variable, text or value here')";
// insert SQL statement
$update_table = $db->exec($sql2);
CREATE AN A SINGLE ROW ARRAY FROM QUERIED DATA USING PDO
// create an array
$rows = array();
// loop through the table and drop the data into the array
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$rows[] = $row;
}
DELETE A ROW FROM TABLE
$clear_row = ("DELETE FROM table_name WHERE column_name = '$variable'");
CLEAR A WHOLE TABLE
$clearResult = $db->query('TRUNCATE TABLE table_name');
CLOSE THE SERVER CONNECTION
// close the SQL connection
$db = null;
I’ll update this as I come across more useful code.