CU-BCA-SEM-V-Web Development Using Php Practical-Web Technologies Practical-Second Draft

named John and the number of their articles. Let's modify the query: SELECT `last_name`, `articles_count` FROM `user` WHERE `first_name` = 'John'; Output Smith 17 Johnson 6 if you don't need almost all the columns, list just the values you're interested in at the moment in your SELECT clauses. Always try to limit the condition as much as possible at the database level, don't pull the entire table into your application to filter it there. Let's say, your application wouldn't be very fast that way. The list of columns we want the query to return has nothing to do with other columns we use in the query. So we can search by ten columns but return only one. As with DELETE, a query without WHERE will also work: SELECT * FROM `user`; In this case, all the users will be selected from the table. As for conditions, it's the same as for DELETE, the WHERE clause works the same here. Let's try it. We'll select all users born after 1960 and with a number of articles greater than 5: SELECT * FROM `user` WHERE `birth_date` >= '1960-1-1' AND `articles_count` > 5; Output 8 John Smith 1984-11-03 17 16 Mark Wilson 1969-06-02 7 17 Sarah Johnson 1962-07-03 8 27 Helen Murphy 1980-08-11 8 28 Jeff Moore 1982-09-30 18 33 Paul Walker 1991-05-01 9 34 Carol Young 1992-12-17 9 Operators You can surely use basic operators =, >, <, >=, <=, != already. In SQL, there's more of them, let's talk about LIKE, IN, and BETWEEN.

LIKE LIKE allows us to search for text values by part of the text. It works similarly as the = (equal) operator, but we can use 2 wildcards:  % (percent) indicates any number of arbitrary characters.  _ (underscore) indicates one arbitrary character. Let's try some queries with the LIKE operator. We'll find last names of people starting with W: SELECT `last_name` FROM `user` WHERE `last_name` LIKE 'w%'; As usual, we enter the text we're searching for in apostrophes, we put wildcards just in some places. The search is case-insensitive. The query result is going to be as follows: Wilson Williams White Walker Now, let's try to find five-letter-long last names that have O as the 2nd character: SELECT `last_name` FROM `user` WHERE `last_name` LIKE '_o___'; Output Lopez Jones Moore Young Now you probably have an idea how LIKE works. We could come up with lots of usages, mostly we use it with percentage signs on both sides for full- text search (e.g. to search for a phrase in an article). IN == IN allows us to search by a list of values. Let's make a list of names and search for the users with the following names: SELECT `first_name`, `last_name` FROM `user` WHERE `first_name` IN ('John', 'Paul', 'Barbara'); Output John Smith Paul Lee

Barbara Thomas Paul Walker John Johnson The IN operator is also used in sub-queries. BETWEEN The last operator to explain today is BETWEEN. It's nothing more than a shortened condition >= AND <=. We already know that we can compare dates naturally, so let's find the users, who were born between 1980 and 1990: SELECT `first_name`, `last_name`, `birth_date` FROM `user` WHERE `birth_date` BETWEEN '1980-1-1' AND '1990-1-1'; We write AND between the 2 values. Output John Smith 1984-11-03 Paul Lee 1984-04-18 Helen Murphy 1980-08-11 Jeff Moore 1982-09-30 Lucy Harris 1983-03-02

EXPERIMENT 11-SORTING OF DATA. Solution Syntax The syntax for the ORDER BY clause in MySQL is: SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ ASC | DESC ]; Parameters or Arguments expressions The columns or calculations that you wish to retrieve. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that must be met for the records to be selected. ASC Optional. It sorts the result set in ascending order by expression (default, if no modifier is provider). DESC Optional. It sorts the result set in descending order by expression. We'll use the ORDER BY clause to do so, which comes at the end of the

query: SELECT `first_name`, `last_name` FROM `user` ORDER BY `last_name` The result: Patricia Adams James Baker Donald Brown David Clark Michelle Davis Lisa Green ... Of course, there could also be a WHERE clause in the query, but to keep things simple, we've just selected all the users. We can sort by several criteria (columns). Let's sort the users by the number articles they wrote and those with the same number also in alphabetical order: SELECT `first_name` ,`last_name`, `articles_count` FROM `user` ORDER BY `articles_count`, `last_name` The result: Michelle Davis 0 Charles Lopez 0 Aron Heaven 1 Daniel Jones 1 Paul Lee 1 Betty Miller 1

Jenifer Williams 1 Donald Brown 2 Lucy Harris 2 Anthony Jackson 2 ... Sort direction Of course, we can also sort in the other direction. We can sort in ascending order (the default direction) with the ASC keyword and in descending with the DESC keyword. Let's try to create a list of the users by the number of their articles. so we'll sort in descending order. Those with the same number of articles will be sorted in alphabetical order: SELECT `first_name`, `last_name`, `articles_count` FROM `user` ORDER BY `articles_count` DESC, `last_name`; The result: Jeff Moore 18 John Smith 17 Donna Johnson 12 Stephen Murphy 12 Paul Walker 9 Carol Young 9 ... We always have to specify the DESC keyword again. You can see that the ordering by last_name is ascending because we've put the DESC keyword only after articles_count.

Limit Let's stay with our user rating by their number of articles for a while. Now, we want to list just 10 best users. If you had one million users, it probably wouldn't be a good idea to select all of them and then use just 10 in your application and throw the 999 990 away. We'll define a limit, the maximum number of the entries we want to select. At the same time, we'll define the sorting order. The limit is always at the end of a query: SELECT `first_name`, `last_name`, `articles_count` FROM `user` ORDER BY `articles_count` DESC, `last_name` LIMIT 10; We can use LIMIT and ORDER BY for other commands as well, such as DELETE or UPDATE. This way, we can ensure that only one entry is deleted or updated. Aggregation functions The database offers many of so-called aggregation functions. These are functions that manipulate with multiple values in some way and return just a single value as the result. COUNT() An example of such a function is the COUNT() function, that returns the number of table rows that meet certain criteria. Let's count how many users wrote at least one article: SELECT COUNT(*) FROM `user` WHERE `articles_count` > 0; The result: 29 We use SELECT to get the value of COUNT(). COUNT() is not a command, it's a function which gets the table rows as inputs, and the results are returned by the SELECT. We use parentheses to call functions as in other programming languages (at least in most of them). The asterisk means that

we're interested in all the columns. AVG() AVG() returns the average of given values. Let's look at the average number of articles per user: SELECT AVG (`articles_count`) FROM `user`; The result: 5.3226 SUM() SUM() returns the sum of the given values. Let's see how many articles were written by people born since 1980: SELECT SUM(`articles_count`) FROM `user` WHERE `birth_date` > '1980-1-1'; The result: 65 MIN() The MIN() function returns the minimum (the lowest value). Let's find the lowest date of birth: SELECT MIN(`birth_date`) FROM `user`; The result: 1935-05-15 Beware, if we'd like to select the first and last names too, this code won't work: -- This code won't work SELECT `first_name`, `last_name`, MIN(`birth_date`) FROM `user`;

The aggregation function works with multiple column values and the selected columns (first_name and last_name) won't be related with the value returned by MIN() anyhow. We could solve this problem with a sub-query or even more easily, avoid the MIN() and MAX() functions and use ORDER BY and LIMIT instead: SELECT `first_name`, `last_name`, `birth_date` FROM `user` ORDER BY `birth_date` LIMIT 1; The result: Linda Martin 1935-05-15 MAX() Similar to MIN(), there's also the MAX() function. Let's find the maximum number of articles a user wrote: SELECT MAX(`articles_count`) FROM `user`; The result: 18 MySQL has some more aggregation functions, but these are no longer interesting to us. Grouping We can group database entries by certain criteria. We use grouping almost always together with aggregation functions. Let's group the users by first_name: SELECT `first_name` FROM `user` GROUP BY `first_name`; The result: Anthony Aron

Barbara Betty Carol ... We can see that each name is included only once, even if there are there multiple times in the database. We'll now add, besides the name, also the number of its occurrences in the table, using the COUNT(*) aggregate function: SELECT `first_name`, COUNT(*) FROM `user` GROUP BY `first_name`; The result: ... Jeff 1 Jenifer 1 John 2 Joseph 1 Linda 1 ... We can see that there are, for example, two Johns. AS To simplify a query, we can define aliases in it. For example, we can rename a column with a long name to make the query more readable. We'll find this often in queries through multiple tables where it's very useful. For tables, we use AS to simplify the operations within the query. For columns, we use AS so our application can see the data under a different name than it's really in the database. This can be especially useful for aggregation functions

because there's no column for their result in the database, so it could be difficult to work with the result instead. Let's modify our last query: SELECT `first_name`, COUNT(*) AS `cnt` FROM `user` GROUP BY `first_name`; The result: ... Jeff 1 Jennifer 1 John 2 Joseph 1 Linda 1 ...

EXPERIMENT 12-WORKING WITH STRING AND DATE FUNCTIONS. Solution Strings functions ASCII() This function returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL Syntax : ASCII(str) Example : SELECT ASCII('2'); Output : 50 Example : SELECT ASCII(2); Output : 50 Example : SELECT ASCII(‘An’); Output : 65 BIN() Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. Returns NULL if N is NULL. Syntax : BIN(N) Example : SELECT BIN(12); Output : 1100 BIT_LENGTH() Returns the length of the string str in bits. Syntax : BIT_LENGTH(str) Example : SELECT BIT_LENGTH('text'); Output : 32 CHAR()

CHAR () interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped. Syntax: CHAR (N,... [USING charset_name]) Example : SELECT CHAR(77,121,83,81,'76'); Output : MySQL Example : SELECT CHAR(77,77.3,'77.3'); Output : MMM CHAR_LENGTH() Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5. Syntax : CHAR_LENGTH(str) Example : SELECT CHAR_LENGTH('test string'); Output : 11 CONCAT() Returns the string that results from concatenating one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form. Syntax : CONCAT(str1,str2,...) Example : SELECT CONCAT('welcomeyou','.','com'); Output : welcomeyou.com CONCAT_WS() CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

Syntax : CONCAT_WS(separator,str1,str2,...) Example : SELECT CONCAT_WS(',','1st string','2nd string'); Output : 1st string,2nd string ELT() ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD(). Syntax : ELT(N,str1,str2,str3,...) Example : SELECT ELT(4,'this','is','the','elt'); Output : elt EXPORT_SET() Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Syntax : EXPORT_SET(bits,on,off[,separator[,number_of_bits]]) Example : SELECT EXPORT_SET(5,'Y','N',',',3); Output : Y,N,Y FIELD() Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found. Syntax : FIELD(str,str1,str2,str3,...) Example : SELECT FIELD('ank', 'b', 'ank', 'of', 'monk'); Output : 2 FIND_IN_SET() Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character. Syntax : FIND_IN_SET(str,strlist)

Example : SELECT FIND_IN_SET('ank','b,ank,of,monk'); Output : 2 FORMAT() Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. Syntax : FORMAT(X,D) Example : SELECT FORMAT(12332.123456, 4); Output : 12,332.1235 Example : SELECT FORMAT(12332.1,4); Output : 12,332.1000 Example : SELECT FORMAT(12332.2,0); Output : 12,332 HEX() MySQL HEX () returns a string representation of hexadecimal value of a decimal or string value specified as argument. If the argument is a string, each character in the argument is converted to two hexadecimal digits. If the argument is decimal, the function returns a hexadecimal string representation of the argument, and treated as a longlong(BIGINT) number. Syntax : HEX(str), HEX(N) Example : SELECT HEX(157); Output : 9D INSERT() Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Syntax : INSERT(str,pos,len,newstr) Example : SELECT INSERT('Originalstring', 4, 5, ' insert ');

Output : Ori insert string Example : SELECT INSERT('Originalstring', -3, 5, ' insert '); Output : Originalstring INSTR() MySQL INSTR() takes a string and a substring of it as arguments, and returns an integer which indicates the position of the first occurrence of the substring within the string Syntax : INSTR(str,substr) Example : SELECT INSTR('myteststring','st'); Output : 5 LCASE() MySQL LCASE() converts the characters of a string to lower case characters. Syntax : LCASE(str) Example : SELECT LCASE('MYTESTSTRING'); Output : myteststring LEFT() MySQL LEFT () returns a specified number of characters from the left of a given string. Both the number and the string are supplied in the arguments as str and len of the function. Syntax : LEFT(str,len) Example : SELECT LEFT('welcomeyou', 3); Output : wel LENGTH() MySQL LENGTH () returns the length of a given string. Syntax : LENGTH(str) Example : SELECT LENGTH('text'); Output : 4 LOCATE()

MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start. If this position is not mentioned, searching starts from the beginning. Syntax : LOCATE(substr,str,pos) Example : SELECT LOCATE('st','myteststring'); Output : 5 LOWER() MySQL LOWER () converts all the characters in a string to lowercase characters. Syntax : LOWER(str) Example : SELECT LOWER('MYTESTSTRING'); Output : myteststring LPAD() MySQL LPAD () left pads a string with another string. The actual string, a number indicating the length of the padding in characters (optional) and the string to be used for left padding - all are passed as arguments. Syntax : LPAD(str,len,padstr) Example : SELECT LPAD('Hello',10,'**'); Output : *****Hello Example : SELECT LPAD('hi',1,'**'); Output : h LTRIM(str) MySQL LTRIM () removes the leading space characters of a string passed as argument. Syntax : LTRIM(str) Example : SELECT LTRIM(' Hello') Output : Hello ( leading spaces have been exclude)

MAKE_SET() MySQL MAKE_SET () returns a set value (a string containing substrings separated by “,” characters) consisting of the strings that have the corresponding bit in the first argument. Syntax : MAKE_SET(bits,str1,str2,...) Example : SELECT MAKE_SET(1,'a','b','c'); Output : a Example : SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world'); Output : hello MID() MySQL MID () extracts a substring from a string. The actual string, position to start extraction and length of the extracted string - all are specified as arguments. Syntax : MID(str,pos,len) Example : SELECT MID('welcomeyou',4,3); Output : eso OCT() Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. Returns NULL if N is NULL. Syntax : OCT(N) Example : SELECT OCT(12); Output : 14 ORD() MySQL ORD () returns the code for the leftmost character if that character is a multi-byte (sequence of one or more bytes) one. If the leftmost character is not a multibyte character, ORD () returns the same value as the ASCII () function. Syntax : ORD(str) Example : SELECT ORD(\"welcomeyou\");

Output : 119 POSITION() MySQL POSITION () returns the position of a substring within a string.. Syntax : POSITION(substr IN str) Example : SELECT POSITION(\"ou\" IN \"welcomeyou\"); Output : 6 QUOTE() Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (“\\”), single quote (“'”), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks. Syntax : QUOTE(str) Example : SELECT QUOTE('wele''source'); Output : 'wele\\'source' REPEAT() MySQL REPEAT() repeats a string for a specified number of times. The function returns NULL either any either of the arguments are NULL. Syntax : REPEAT(str,count) Example : SELECT REPEAT('**-',5); Output : **-**-**-**-**- REPLACE() MySQL REPLACE () replaces all the occurrences of a substring within a string. Syntax : REPLACE(str,from_str,to_str) Example : SELECT REPLACE('welcomeyou','ur','r'); Output : welesorce

REVERSE() Returns a given string with the order of the characters reversed. Syntax : REVERSE(str) Example : SELECT REVERSE('welcomeyou'); Output : ecruoser3w RIGHT() MySQL RIGHT() extracts a specified number of characters from the right side of a given string. Syntax : RIGHT(str,len) Example : SELECT RIGHT('welcomeyou',8); Output : resource RPAD() MySQL RPAD() function pads strings from right. The actual string which is to be padded as str, length of the string returned after padding as len and string which is used for padding as padstr is used as a parameters within the argument. Syntax : RPAD(str,len,padstr) Example : SELECT RPAD('welcomeyou',15,'*'); Output : welcomeyou***** RTRIM() MySQL RTRIM() removes the trailing spaces from a given string. Syntax : RTRIM(str) Example : SELECT RTRIM('welcomeyou '); (excludes the trailing spaces) Output : welcomeyou SOUNDEX() MySQL SOUNDEX() function returns soundex string of a string. Soundex is a phonetic algorithm for indexing names after English pronunciation of sound. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphabetic characters in str are ignored. All

international alphabetic characters outside the A-Z range are treated as vowels. Syntax : SOUNDEX(str) Example : SELECT SOUNDEX('welcomeyou'); Output : w6262 SPACE() MySQL SPACE() returns the string containing a number of spaces as specified in the argument. Syntax : SPACE(N) Example : SELECT 'start', SPACE(10), 'end'; Output : start end SPACE(10) SUBSTR() MySQL SUBSTR() returns the specified number of characters from a particular position of a given string. SUBSTR() is a synonym for SUBSTRING(). Syntax : SUBSTR(str,pos,len) Example : SELECT SUBSTR('welcomeyou',4,3); Output : eso SUBSTRING() MySQL SUBSTRING() returns a specified number of characters from a particular position of a given string. Syntax : SUBSTRING(str,pos,len) Example : SELECT SUBSTRING('welcomeyou',4,3); Output : eso Example : SELECT SUBSTRING('welcomeyou.com',5); Output : source.com Example : SELECT SUBSTRING('welcomeyou.com',-5); Output : e.com SUBSTRING_INDEX()

MySQL SUBSTRING_INDEX() returns the substring from the given string before a specified number of occurrences of a delimiter. Returns from the left of the final delimiter if the number is positive and right of the final delimiter when the number is negative. If the number is greater than the number of occurrence of delimiter, the returned substring will be the total string. If the specified number is 0, nothing will be fetched from the given string. Syntax : SUBSTRING_INDEX(str,delim,count) Example : SELECT SUBSTRING_INDEX('www.mytestpage.info','.',2); Output : www.mytestpage TRIM() MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string. Syntax : TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) Example : SELECT TRIM(' trim '); Output : trim (leading and trailing space removed) Example : SELECT TRIM(LEADING 'leading' FROM 'leadingtext' ); Output : text Example : SELECT TRIM(BOTH 'leadtrail' FROM 'leadtrailtextleadtrail'); Output : text UNHEX() MySQL UNHEX() function performs the opposite operation of HEX(). This function interprets each pair of hexadecimal digits (in the argument) as a number and converts it to a character. Syntax : UNHEX(str) Example : SELECT UNHEX('4D7953514C'); Output : MySQL Example : SELECT UNHEX(HEX('MySQL')); Output : MySQL UPPER()

MySQL UPPER() converts all the characters in a string to uppercase characters. Syntax : UPPER(str) Example : SELECT UPPER('myteststring'); Output : MYTESTSTRING Date functions MySQL DATE functions MySQL supports a bunch of date utility functions that we can use to handle DATE efficiently. NOW() This date function returns the current date and time of the running server instance. -- Print current date and time in MySQL SELECT NOW(); It’s output is: 2019-08-04 09:07:10 DATE() This date function extracts and returns the date part from the given DATETIME value. -- Print date part from current date and time in MySQL SELECT DATE(NOW()); It’s output is: 2019-08-04

CURDATE() It is a simple date function which fetches the current date of the system running the MySQL instance. -- Print the current date in MySQL SELECT CURDATE(); The result is: 2019-08-04 DATE_FORMAT() Sometimes you need to display a date in a user-defined style. For example, you want to show the month first, then date, and the year in the last. -- Print the current MySQL date in a user-defined format SELECT DATE_FORMAT(DATE(NOW()), '%m_%d_%Y') Styled_date; Its output is going to be: 08_04_2019 DATEDIFF() You may want to count the difference between two dates. Therefore, you can use the DATEDIFF() function. -- Print the difference between two dates SELECT DATEDIFF('2019-08-04','2019-08-01') diff; The DATEDIFF() function would subtract the second date argument from the first and returns the diff in days. 3 DATE_ADD() It enables you to add any of the days or weeks or months or years to a given

date. Check the below example. -- Adding days, weeks, months, and years using DATE_ADD() SELECT '2019-08-04' ACTUAL, DATE_ADD('2019-08-04', INTERVAL 1 DAY) 'Added 1 day', DATE_ADD('2019-08-04', INTERVAL 1 WEEK) 'Added 1 week', DATE_ADD('2019-08-04', INTERVAL 1 MONTH) 'Added 1 month', DATE_ADD('2019-08-04', INTERVAL 1 YEAR) 'Added 1 year'; The result of the date addition operation is as follows: 2019-08-04 2019-08-05 2019-08-11 2019-09-04 2020-08-04 DATE_SUB() It enables you to subtract any of the days or weeks or months or years from a given date. Check the below example. -- Subtracting days, weeks, months, and years using DATE_SUB() SELECT '2019-08-04' ACTUAL, DATE_SUB('2019-08-04', INTERVAL 1 DAY) 'Subtracted 1 day', DATE_SUB('2019-08-04', INTERVAL 1 WEEK) 'Subtracted 1 week', DATE_SUB('2019-08-04', INTERVAL 1 MONTH) 'Subtracted 1 month', DATE_SUB('2019-08-04', INTERVAL 1 YEAR) 'Subtracted 1 year'; The result of the date subtraction operation is as follows: 2019-08-04 2019-08-03 2019-07-28 2019-07-04 2018-08-04

EXPERIMENT 13-DATABASE CONNECTIVITY IN PHP WITH MYSQL Solution Install XAMPP Open your browser and go to localhost/PHPMyAdmin or click “Admin” in XAMPP UI. When you first installed XAMPP, it only created the username for it to be accessed, you now have to add a password to it by yourself. For this, you have to go to User account where the user is the same as the one shown in this picture: Now click Edit privileges and go to Change Admin password, type your password there and save it. Remember this password as it will be used to connect to your Database.

Create Database Now return to the homepage of PHPMyAdmin. Click the New button to create a new database. In the new window, name your database as per your need, named it “practice”. Now select Collation as utf8_general_ci, as we are using it for learning purposes and it will handle all of our queries and data that will be covered in this tutorial series. Now click on Create and your database will be created.

The newly created database will be empty now, as there are no tables in it. I will be covering that in the upcoming series where we will learn how to create tables and insert data in it. In this tutorial, we are going to connect this database to localhost using PHP. Create Database Connection File In PHP <?php function OpenCon() { $dbhost = \"localhost\"; $dbuser = \"root\"; $dbpass = \"1234\"; $db = \"example\"; $conn = new mysqli($dbhost, $dbuser, $dbpass,$db) or die(\"Connect failed: %s\\n\". $conn -> error); return $conn; } function CloseCon($conn)

{ $conn -> close(); } ?> Where 1. $dbhost will be the host where your server is running it is usually localhost. 2. $dbuser will be the username i.e. root and $dbpass will be the password which is the same that you used to access your PHPMyAdmin. 3. $dbname will be the name of your database which we have created. Create a new PHP file to check your database connection <?php include 'db_connection.php'; $conn = OpenCon(); echo \"Connected Successfully\"; CloseCon($conn); ?> Run it Output



EXPERIMENT 14-WORDPRESS – INSTALLATION Solution a) S The recommended hardware requirements for running WordPress are: ystem Require Disk Space: 1GB+ ments Web Server: Apache or Nginx for Database: MySQL version 5.0.15 or greater or any version of MariaDB. WordPr RAM: 512MB+ ess PHP: Version 7.3 or greater. Processor: 1.0GHz+ It's recommended that your host supports PHP 7.3 or above, MySQL 5.6 or above, the mod_rewrite Apache module, and HTTPS support. Apache or Nginx are the most robust and feature-abundant servers for running WordPress, but any server that supports PHP and MySQL will do. A quick side note, if you only have older PHP or MySQL versions, WordPress also works with PHP 5.2.4+ and MySQL 5.0+, but your site may not run well. It's not required for a WordPress server, but you should have a memory limit of at least 512MB, increase the connection limit, increase the timeout setting of PHP, and disable aborting the request processing if a user closes the site on accident. b) D Before you begin the install, there are a few things you need to have and do. ownload Refer the article Before You Install. WordPr ess Basic Instructions #Basic Instructions Here’s the quick version of the instructions for those who are already comfortable with performing such installations. More detailed instructions

follow. Download and unzip the WordPress package if you haven’t already. Create a database for WordPress on your web server, as well as a MySQL (or MariaDB) user who has all privileges for accessing and modifying it. (Optional) Find and rename wp-config-sample.php to wp-config.php, then edit the file (see Editing wp-config.php) and add your database information. Note: If you are not comfortable with renaming files, step 3 is optional and you can skip it as the install program will create the wp-config.php file for you. Upload the WordPress files to the desired location on your web server: If you want to integrate WordPress into the root of your domain (e.g. http://example.com/), move or upload all contents of the unzipped WordPress directory (excluding the WordPress directory itself) into the root directory of your web server. If you want to have your WordPress installation in its own subdirectory on your website (e.g. http://example.com/blog/), create the blog directory on your server and upload the contents of the unzipped WordPress package to the directory via FTP. Note: If your FTP client has an option to convert file names to lower case, make sure it’s disabled. Run the WordPress installation script by accessing the URL in a web browser. This should be the URL where you uploaded the WordPress files. If you installed WordPress in the root directory, you should visit: http://example.com/ If you installed WordPress in its own subdirectory called blog, for example, you should visit: http://example.com/blog/ That’s it! WordPress should now be installed. Detailed Instructions #Detailed Instructions Step 1: Download and Extract #Step 1: Download and Extract Download and unzip the WordPress package from https://wordpress.org/download/.

If you will be uploading WordPress to a remote web server, download the WordPress package to your computer with a web browser and unzip the package. If you will be using FTP, skip to the next step – uploading files is covered later. If you have shell access to your web server, and are comfortable using console-based tools, you may wish to download WordPress directly to your web server using wget (or lynx or another console-based web browser) if you want to avoid FTPing: wget https://wordpress.org/latest.tar.gz Then extract the package using: tar -xzvf latest.tar.gz The WordPress package will extract into a folder called wordpress in the same directory that you downloaded latest.tar.gz. c) C Using Plesk #Using Plesk reate If your hosting provider supplies the Plesk hosting control panel and you Store want to install WordPress manually, follow the instructions below to create a Databas database: e Log in to Plesk. Click Databases in the Custom Website area of your website on the Websites & Domains page: Plesk panel highlighting the Custom Website box with the databases button highlighted Plesk custom website databases 3. Click Add New Database, change database name if you want, create database user by providing credentials and click OK. You’re done!

Using cPanel #Using cPanel If your hosting provider supplies the cPanel hosting control panel, you may follow these simple instructions to create your WordPress username and database. A more complete set of instructions for using cPanel to create the database and user can be found in Using cPanel. Log in to your cPanel. Click MySQL Database Wizard icon under the Databases section. In Step 1. Create a Database enter the database name and click Next Step. In Step 2. Create Database Users enter the database user name and the password. Make sure to use a strong password. Click Create User. In Step 3. Add User to Database click the All Privileges checkbox and click Next Step. In Step 4. Complete the task note the database name and user. Write down the values of hostname, username, databasename, and the password you chose. (Note that hostname will usually be localhost.) Using Lunarpages.com’s custom cPanel (LPCP) #Using Lunarpages.com’s custom cPanel (LPCP) Lunarpages has developed their own version of cPanel. Log in to your account. Go to Control Panel. Click on the button on the left panel labeled ‘Go to LPCP’. Go to MySQL Manager. Add the user name and database name but leave the host name as the default IP number. Note the IP address of the database on the right which is different from the default IP number of the host indicated in the above step. When modifying the wp-config.php file, use the DB IP number, not ‘LOCALHOST’. When modifying the wp-config.php file, be sure to use the full name of the database and user name, typically ‘accountname_nameyoucreated’.

Using phpMyAdmin #Using phpMyAdmin If your web server has phpMyAdmin installed, you may follow these instructions to create your WordPress username and database. If you work on your own computer, on most Linux distributions you can install PhpMyAdmin automatically. Note: These instructions are written for phpMyAdmin 4.4; the phpMyAdmin user interface can vary slightly between versions. If a database relating to WordPress does not already exist in the Database dropdown on the left, create one: Choose a name for your WordPress database: ‘wordpress’ or ‘blog’ are good, but most hosting services (especially shared hosting) will require a name beginning with your username and an underscore, so, even if you work on your own computer, we advise that you check your hosting service requirements so that you can follow them on your own server and be able to transfer your database without modification. Enter the chosen database name in the Create database field and choose the best collation for your language and encoding. In most cases it’s better to choose in the “utf8_” series and, if you don’t find your language, to choose “utf8mb4_general_ci” (Reference: [1]). phpMyAdmin language encoding dropdown with utf8mb4_general_ci selected phpMyAdmin language encoding drop down 2. Click the phpMyAdmin icon in the upper left to return to the main page, then click the Users tab. If a user relating to WordPress does not already exist in the list of users, create one: phpMyAdmin Users Tab selected phpMyAdmin Users Tab Click Add user.

Choose a username for WordPress (‘wordpress’ is good) and enter it in the User name field. (Be sure Use text field: is selected from the dropdown.) Choose a secure password (ideally containing a combination of upper- and lower-case letters, numbers, and symbols), and enter it in the Password field. (Be sure Use text field: is selected from the dropdown.) Re-enter the password in the Re-typefield. Write down the username and password you chose. Leave all options under Global privileges at their defaults. Click Go. # Return to the Users screen and click the Edit privileges icon on the user you’ve just created for WordPress. # In the Database-specific privileges section, select the database you’ve just created for WordPress under the Add privileges to the following database dropdown, and click Go. # The page will refresh with privileges for that database. Click Check All to select all privileges, and click Go. # On the resulting page, make note of the host name listed after Server: at the top of the page. (This will usually be localhost.) Databaser Server selected showing 'localhost' Using the MySQL Client #Using the MySQL Client You can create MySQL users and databases quickly and easily by running mysql from the shell. The syntax is shown below and the dollar sign is the command prompt: $ mysql -u adminusername -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 5340 to server version: 3.23.54 Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer. mysql> CREATE DATABASE databasename;

Query OK, 1 row affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON databasename.* TO \"wordpressusername\"@\"hostname\" -> IDENTIFIED BY \"password\"; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> EXIT Bye $ The example shows: that root is also the adminusername. It is a safer practice to choose a so-called “mortal” account as your mysql admin, so that you are not entering the command “mysql” as the root user on your system. (Any time you can avoid doing work as root you decrease your chance of being exploited.) The name you use depends on the name you assigned as the database administrator using mysqladmin. wordpress or blog are good values for databasename. wordpress is a good value for wordpressusername but you should realize that, since it is used here, the entire world will know it, too. Hostname will usually be localhost. If you don’t know what this value should be, check with your system administrator if you are not the admin for your WordPress host. If you are the system admin, consider using a non-root account to administer your database. password should be a difficult-to-guess password, ideally containing a combination of upper- and lower-case letters, numbers, and symbols. One good way of avoiding the use of a word found in a dictionary is to use the first letter of each word in a phrase that you find easy to remember. If you need to write these values somewhere, avoid writing them in the

system that contains the things protected by them. You need to remember the value used for databasename, wordpressusername, hostname, and password. Of course, since they are already in (or will be shortly) your wp-config.php file, there is no need to put them somewhere else, too. Using DirectAdmin #Using DirectAdmin a. If you’re a regular User of a single-site webhosting account, you can log in normally. Then click MySQL Management. (If this is not readily visible, perhaps your host needs to modify your “package” to activate MySQL.) Then follow part “c” below. b. Reseller accounts Admin accounts may need to click User Level. They must first log in as Reseller if the relevant domain is a Reseller’s primary domain… or log in as a User if the domain is not a Reseller’s primary domain. If it’s the Reseller’s primary domain, then when logged in as Reseller, simply click User Level. However if the relevant domain is not the Reseller’s primary domain, then you must log in as a User. Then click MySQL Management. (If not readily visible, perhaps you need to return to the Reseller or Admin level, and modify the “Manage user package” or “Manage Reseller package” to enable MySQL.) c. In MySQL Management, click on the small words: Create new database. Here you are asked to submit two suffixes for the database and its username. For maximum security, use two different sets of 4-6 random characters. Then the password field has a Random button that generates an 8-character password. You may also add more characters to the password for maximum security. Click Create. The next screen will summarize the database, username, password and hostname. Be sure to copy and paste these into a text file for future reference. d) S To install WordPress using the Website Setup Wizard, first access your Client et Up Area. After you sign up for our WordPress hosting package, you will receive Wizard all the necessary information you need to log in there. Once you log in for the

first time, you will see a notice asking you if you want to Set Up a new website or to migrate it on your account. Click on Set Up Site. A new section would appear where you should select to Start a New Website or to Migrate an existing one. Click on Select under Start New Website. Choose WordPress and enter the preferred login details for your new WordPress application. Once you are ready, click on Continue. Next, choose what enhancements you wish to add to your account – if you want to add any. When you are ready with this step – click on Finish.

That’s it! You can now go to the front page of your site and check out the newly installed WordPress application.

EXPERIMENT 15-CUSTOMIZING WORDPRESS WEBSITE Solution: When you make a website, it needs to be customized to your needs. Whether you build an eCommerce website, a small business website, a personal website, or a blog, each different type of website will need different features and a unique look. This is where WordPress customization comes in. Plus, not only will customizing WordPress make your website look the way you want it, but it will also help: Make your WordPress site more secure Optimize your site for search engines Make your site easier to manage Improve the user experience for your site visitors Luckily, WordPress is super customization-friendly. So, let’s get started with this step by step guide on how to customize your WordPress website.



EXPERIMENT 16-INSTALLING WORDPRESS THEMES Solution Before you can vamp up your site with themes, you need to install and activate them. There are several ways to do that. The simplest way is right from within WordPress admin. Install a theme from WordPress dashboard These are the basic steps for most themes: 1. Log in to your WordPress admin page, then go to Appearance and select Themes. 2. To add a theme, click Add New. From this page, you have two ways to add a new theme. If you know the name of the theme you want, search for it in the Theme directory. If you don’t know your preferred theme’s name, use the Feature Filter to hone down your selection, check any tags and click Apply Filter for a screen filled with themes that meet your search criteria. 3. To unlock a theme's options, hover over it; you can either choose Preview to see a demo of the theme or install it by clicking the Install button once you're ready. Once installed, click the Activate link. 4. You're all done, now preview your site to see how it looks. We recommend reading through any instructions that come with your chosen theme regarding installation. Some themes will require more steps than others, going beyond the basic steps covered here. Follow the instructions provided, and if you run into any problems, contact the theme’s author for help.

Download WordPress themes If you purchased a theme from a developer that operates outside of the WordPress themes directory, you need to upload it before installing. Follow these simple instructions to upload and install a theme. 1. Begin by downloading the theme’s .zip file from the source onto your local computer. 2. From your WordPress Administration area, head to Appearance > Themes and click Add New. 3. The Add New theme screen has a new option, Upload Theme.

4. The theme upload form is now open, click Choose File, select the theme zip file on your computer and click Install Now. 5. Once the theme is downloaded from the zip file, you can activate it from your admin. Select the Appearance tab and open the Themes directory, locate the theme and click the Activate link. How to install WordPress themes using FTP

If you have a WordPress theme on your local computer, it’s possible to install it using File Transfer Protocol (FTP). By using this method there is a risk of deleting or modifying files, which could bring your site offline. For this reason, we advise that only advanced users install themes this way. To use the FTP to add new themes manually, follow these steps. To begin, you must have an FTP hostname, username and password, and an FTP client. We’ve used FTP client Filezilla to show the steps in this tutorial. 1. Launch Filezilla, enter your login details and click Quickconnect. 2. The following window contains Remote Site and a root directory (the root directory is your site's name). Select /rootdirectory/web/content/wp-content/themes. 3. In the Local Site window, select the folder containing your saved theme, right click for more options and hit Upload. 4. You've now successfully uploaded your theme to WordPress. To finalize, log in to your control panel, head to Appearance > Themes and hover your mouse over the new theme. Now click Activate. 5. You've now successfully uploaded your theme to WordPress. To finalize, login to your control panel, head to Appearance > Themes and hover your mouse over the new theme. Now click Activate. 6. All that’s left is to check out your new theme and see how you like your new site design. Finally, if you’re a fan of doing things yourself, it’s possible to bypass the WordPress theme uploader tools and download your theme manually. Installing a theme manually takes a few extra steps. It involves uploading your theme directly to your server via your host’s cPanel.

EXPERIMENT 17-ADDING WIDGETS Solution Adding widgets in WordPress is very simple thanks to the click and drag interface WordPress utilizes. There are two main ways to add widgets in WordPress, the first is by the default drag and drop method, while the second is by using accessibility mode. In this mode, you will not have to drag and drop, which is difficult for some people. Instead, you can add them through a series of button presses. Both of them are equally easy to do, so let’s get started. On the left-hand admin panel, click on Appearance and select the Widgets option. Here you can view all of the widgets currently available on your website and the areas you can add them. The areas you can add them are dependent on the theme you are using. Each theme is unique so you may have different options available. However, they all work the same. Simply click on a widget and drag it to the appropriate area.

Note: The order of the widgets is the order they will appear as. Thus, the widget at the top of the list will appear first, and so on. When you add a widget, you will be presented with options to configure it in a variety of ways. However, each widget is unique.

EXPERIMENT 18-WORDPRESS – INSTALLATION OF PLUG-IN Solution 1. Go to “Plugins” in your WordPress dashboard Start by going to your admin panel – http://your-domain.com/wp-admin/ and click on “Plugins” in the sidebar menu: 2. Click on “Add New” You can find the “Add New” button near the top of the page:

3. Find your plugin via search What you’ll see next is a page that shows you a short list of featured/recommended plugins, and a handy search field on the right – this is the one we’re going to use. Input the name of the plugin that you want to install in that search field. Now, the great thing here is that you don’t even need to input the plugin’s whole name. For example, I’m just going to use “revive” and press the enter key on my keyboard. 4. Install your plugin Once you see the plugin you’re looking for on the list, all you need to do now is click on the “Install Now” button. When you do that, WordPress is going to fetch the plugin and have it installed on your site. It works much like installing a new app on your phone.