HTML : Overview

HTML : Overview

INTRODUCTION TO WEB PROGRAMMING 0731213 LECTURE 1 PHP + MYSQL DATABASE SYSTEM (1) DATABASE QUERIES A query is a question or a request.

We can query a database for specific information and have a recordset returned. Look at the following query (using standard SQL): SELECT LastName FROM Employees The query above selects all the data in the "LastName" column from the "Employees" table. OPEN A CONNECTION TO MYSQL

Before we can access data in the MySQL database, we need to be able to connect to the server and database:

echo "Connected successfully"; ?> CLOSE A CONNECTION TO MYSQL The connection will be closed automatically when the script ends. To close the connection before, use the following: mysqli_close($conn); LECTURE 2 PHP + MYSQL DATABASE SYSTEM (2) INSERT DATA WITH MYSQLI Here are some syntax rules to follow:

The SQL query must be quoted in PHP String values inside the SQL query must be quoted Numeric values must not be quoted

The word NULL must not be quoted The INSERT INTO statement is used to add new records to a MySQL table: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) INSERT DATA WITH MYSQLI The following examples add a new record to the "MyGuests" table:

$dbname = "myDB"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if ($conn){ $sql = "INSERT INTO myguests (id, firstname, lastname) VALUES (1,'John', 'Doe')"; if (mysqli_query($conn, $sql)) echo "New record created successfully"; else echo "Error"; } mysqli_close($conn);

?> UPDATE DATA WITH MYSQLI The UPDATE statement is used to update existing records in a table: UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! UPDATE DATA WITH MYSQLI

The following examples update the record with id=2 in the "MyGuests" table:

else echo "Error updating record"; } mysqli_close($conn); ?> DELETE DATA WITH MYSQLI The DELETE statement is used to delete records from a table: DELETE FROM table_name WHERE some_column = some_value Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all

records will be deleted! DELETE DATA WITH MYSQLI The following examples delete the record with id=3 in the "MyGuests" table:

// sql to delete a record $sql = "DELETE FROM MyGuests WHERE id=3"; if (mysqli_query($conn, $sql)) echo "Record deleted successfully"; else echo "Error deleting record"; } mysqli_close($conn); ?> LECTURE 3 PHP + MYSQL DATABASE SYSTEM (3)

SELECT DATA WITH MYSQLI The SELECT statement is used to select data from one or more tables: SELECT column_name(s) FROM table_name or we can use the * character to select ALL columns from a table: SELECT * FROM table_name SELECT DATA WITH MYSQLI The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:

$password = ""; $dbname = "myDB"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if ($conn){ $sql = "SELECT id, firstname, lastname FROM MyGuests"; $result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) > 0) { // output data of each row while($row = mysqli_fetch_assoc($result)) { echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . }

} else { echo "0 results"; } } mysqli_close($conn); ?> $row["lastname"]. "
"; SELECT DATA WITH MYSQLI DIG DEEPER First, we set up an SQL query that selects the id, firstname and lastname columns

from the MyGuests table. The next line of code runs the query and puts the resulting data into a variable called $result. Then, the function num_rows() checks if there are more than zero rows returned. If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.

SELF STUDY Create database using php (https://www.w3schools.com/php/php_mysql_create.asp) Create table using php ( https://www.w3schools.com/php/php_mysql_create_table.asp)

PHP Limit Data Selections From MySQL ( https://www.w3schools.com/php/php_mysql_select_limit.asp) PHP Insert Multiple Records Into MySQL ( https://www.w3schools.com/php/php_mysql_insert_multiple.asp) PHP Prepared Statements ( https://www.w3schools.com/php/php_mysql_prepared_statements.asp)

REFERENCES https://www.w3schools.com/ Robin Nixon, Learning PHP, MySQL, JavaScript, and CSS, 2013 Mike McGrath, PHP & My SQL in easy steps, 2012. THE END

Recently Viewed Presentations

  • Julius Caesar by William Shakespeare

    Julius Caesar by William Shakespeare

    Antanagoge placing a good point or benefit next to a fault criticism, or problem in order to reduce the impact or significance of the negative point. Antiphrasis one word irony, established by context. Assonance repetition of the same sound in...
  • Safety & Health in the "Office" Work Environment

    Safety & Health in the "Office" Work Environment

    Safety & Health in the ... Only use a soft cloth for cleaning. No food or drink is allowed in the computer lab. Use good posture while sitting at the computer. Eyes level with the monitor Shoulders down, arms relaxed...
  • Modern World History Chapter 9, Section 1 The Beginnings of ...

    Modern World History Chapter 9, Section 1 The Beginnings of ...

    Road beds made with large stones provided drainage, while crushed rock on the top provided a smooth surface that wouldn't get muddy even with heavy rains. Turnpikes - roads built this way by investors in which travelers had to stop...
  • April 6, 2014 Prelude for Worship Martha Short

    April 6, 2014 Prelude for Worship Martha Short

    Romans 8:16-18 ( NASB ) 16 The Spirit Himself testifies with our spirit that we are children of God, 17and if children, heirs also, heirs of God and fellow heirs with Christ, if indeed we suffer with Him so that...
  • Oxidation Numbers/ Organic Chemistry Review

    Oxidation Numbers/ Organic Chemistry Review

    Arial MS Pゴシック Corbel Wingdings 2 Wingdings Wingdings 3 Calibri Module 1_Module 2_Module 3_Module 4_Module 5_Module 6_Module Oxidation Numbers/ Organic Chemistry Review Slide 2 Lesson Sequence Warm - Up Objectives Homework Agenda Oxidation Numbers (States) Oxidation Numbers (States) Oxidation Numbers...
  • BUSS3 Revision Guide 2012

    BUSS3 Revision Guide 2012

    for businesses. The highest labour turnover is in retailing, hotels, catering and leisure, call centres and other lower-paid private sector services groups. Some workers receive little training and may not be able to find alternative jobs if they find themselves...
  • Teaching new vocabulary - liceoberard.scuole.vda.it

    Teaching new vocabulary - liceoberard.scuole.vda.it

    CLIL Use flashcards, actions or objects Children need to repeat after the teacher (chorally/individually) Voice needs to vary (whisper/loud/high pitch/low pitch) Children can respond in their own way or copy your action Clap out the syllables and stress on words.
  • Repairing Write Performance on Flash Devices

    Repairing Write Performance on Flash Devices

    Final DaMoN presentation. Repairing Write Performance on Flash Devices Radu Stoica‡, Manos Athanassoulis‡, Ryan Johnson‡§, ... efficient Potentially huge impact Slow random write Read/write asymmetry -> not a HDD drop-in replacement * *Jim Gray, CIDR 2007 Flash devices are not...