Differences

This shows you the differences between two versions of the page.

--- php_mysql [2018/12/08 12:49] – created 0.0.0.0
+++ php_mysql [2019/01/30 11:32] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== PHP/MySQL ======
+  * [[https://www.codeofaninja.com/2017/02/create-simple-rest-api-in-php.html| Create a REST API in PHP]]
+  * [[http://coreymaynard.com/blog/creating-a-restful-api-with-php/|Creating a RESTful API with PHP]]
-  * [[https://github.com/mevdschee/php-crud-api|Self-contained REST API in PHP - github.com/mevdschee]]
+  * [[https://shareurcodes.com/blog/creating%20a%20simple%20rest%20api%20in%20php|Creating a simple REST API in PHP]]
-  * [[https://github.com/mevdschee/php-api-auth|PHP Authentication - github.com/mevdschee]]
+  *  [[https://github.com/mevdschee/php-crud-api|Self-contained REST API in PHP - github.com/mevdschee]]
-=====Excellent tutorial on Object Oriented PHP=====
+  *  [[https://github.com/mevdschee/php-api-auth|PHP Authentication - github.com/mevdschee]]
+==== Excellent tutorial on Object Oriented PHP ====
 [[http://www.killerphp.com/tutorials/object-oriented-php/|www.killerphp.com]]
-=====Using prepared statements to avoid SQL injection=====
+==== Using prepared statements to avoid SQL injection ====
 Using this method of writing SQL removes the necessity of attempting to clean the input with mysql_real_escape_string()
-<code>0@@</code>
+<code>
+$dbPreparedStatement = $db->prepare('INSERT INTO table (postId, htmlcontent) VALUES (:postid, :htmlcontent)');
+$dbPreparedStatement->bindParam(':postid', $userId, PDO::PARAM_INT);
+$dbPreparedStatement->bindParam(':htmlcontent', $yourHtmlData, PDO::PARAM_STR);
+$dbPreparedStatement->execute();
+</code>
 Fill your boots on PDO [[http://be2.php.net/book.pdo|here]]
 [[CSV tables - equivalent of External tables in Oracle]]
-=====Force user logout if session is inactive for a certain period of time=====
+==== Force user logout if session is inactive for a certain period of time ====
 As seen on [[https://stackoverflow.com/questions/572938/force-logout-users-if-users-are-inactive-for-a-certain-period-of-time|stackoverflow.com]]
-<code>1@@</code>
+<code>
+// Add the following into your HEAD section
+var timer = 0;
+function set_interval() {
+  // the interval 'timer' is set as soon as the page loads
+  timer = setInterval("auto_logout()", 10000);
+  // the figure '10000' above indicates how many milliseconds the timer be set to.
+  // Eg: to set it to 5 mins, calculate 5min = 5x60 = 300 sec = 300,000 millisec.
+  // So set it to 300000
+}
+function reset_interval() {
+  //resets the timer. The timer is reset on each of the below events:
+  // 1. mousemove   2. mouseclick   3. key press 4. scroliing
+  //first step: clear the existing timer
+  if (timer != 0) {
+    clearInterval(timer);
+    timer = 0;
+    // second step: implement the timer again
+    timer = setInterval("auto_logout()", 10000);
+    // completed the reset of the timer
+  }
+}
+function auto_logout() {
+  // this function will redirect the user to the logout script
+  window.location = "your_logout_script.php";
+}
+// Add the following attributes into your BODY tag
+onload="set_interval()"
+onmousemove="reset_interval()"
+onclick="reset_interval()"
+onkeypress="reset_interval()"
+onscroll="reset_interval()"
+</code>
 or a "cheaper" way...
-<code>2@@</code>
+<code>
+<META HTTP-EQUIV="refresh" CONTENT="1800;URL=logout.php?timeout">
+</code>
-=====Run a shell script with an html button=====
+==== Run a shell script with an html button ====
-<code>3@@</code>
+<code>
-=====Start MySQL at boot time=====
+<?php
+if ($_GET[['run']]) {
+  # This code will run if ?run=true is set.
+  exec("/path/to/name.sh");
+}
+?>
+<!-- This link will add ?run=true to your URL, myfilename.php?run=true -->
+<button type="button" onclick="?run=true">Click Me!</button>
+</code>
+==== Load an SQL file into a MySQL database from the command line ====
+mysql -u my-mysql-username -p my-database-name < name-of-sql-file.sql
+==== Start MySQL at boot time ====
 To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system
-=====Set a root password after installing MySQL=====
+==== Set a root password after installing MySQL ====
-<code>4@@</code>
+<code>
+/usr/bin/mysqladmin -u root password 'new-password'
+/usr/bin/mysqladmin -u root -h <hostname> password 'new-password'
+</code>
 Alternatively you can run:
-<code>5@@</code>
+<code>
+/usr/bin/mysql_secure_installation
+</code>
 which will also give you the option of removing the test databases and anonymous user created by default.  This is strongly recommended for production servers.
-=====Start the MySQL daemon=====
+==== Start the MySQL daemon ====
-<code>6@@</code>
+<code>
+cd /usr ; /usr/bin/mysqld_safe &
+</code>
 You can test the MySQL daemon with:
-<code>7@@</code>
+<code>
-=====Reset a forgotten MySQL root password=====
+cd /usr/mysql-test ; perl mysql-test-run.pl
-  * [[http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html|mysql.com]]
+</code>
-  * [[https://www.debian-administration.org/article/442/Resetting_a_forgotten_MySQL_root_password|debian-administration.org]]
+==== Reset a forgotten MySQL root password ====
-Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.<br />
+  *  [[http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html|mysql.com]]
+  *  [[https://www.debian-administration.org/article/442/Resetting_a_forgotten_MySQL_root_password|debian-administration.org]]
+Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.\\
 Connect to the mysqld server with this command:
-<code>8@@</code>
+<code>
+shell> mysql
+</code>
 Issue the following statements in the mysql client. Replace the password with the password that you want to use.
-<code>9@@</code>
+<code>
-The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.<br />
+mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
+mysql> FLUSH PRIVILEGES;
+</code>
+The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.\\
 You should now be able to connect to the MySQL server as root using the new password. Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networking options).