Articles added in Mathematics Lab | Cyber Lab is now open | Astronomy Lab is now open | VLSI Lab is now open | 120 SEO Tips article published | More exciting articles coming soon!




PHP DATABASE

Basic knowledge of sql required for this topic.

  • MySQL is a database that runs on various platforms and is free of cost and runs on various platforms. The database is stored in MYSQL is the form of tables.

  • A query is used to retrieve the information from a database by asking a question.



  • To Connect to a SQL Server:

  • The syntax to connect to the SQL server is:

  • mysqli_connect(host,username,password,dbname);

  • PARAMETER DESCRIPTION
    host Optional. Either a host name or an IP address
    username Optional. The MySQL user name
    password Optional. The password to log in with
    dbname Optional. The default database to be used when performing queries

  • After making the connection we store it in a variable $connect. After making the connection we ensure that the connection is made by checking it as follows:


  • The function mysqli_connect_errno() is used to check for an error in connection.

  • The connection ends automatically if the script ends. But the connection can be explicitly closed by using the syntax mysqli_close($connect) where$ connect is the variable to which we have assigned the connection.



  • CREATE DATABASE:

  • A database is created using the CREATE DATABASE statement. This statement is added to the mysqli_query() and thus a database is created.



  • It is then added to the mysqli_query and checked whether the creation was successful.





  • CREATE TABLE:

  • A CREATE TABLE statement is used in order to create a table in SQL. The create table statement must also be added to mysqli_query().



  • NOTE:
  • For INT you do not have to specify the length but for CHAR, VARCHAR you have to specify the maximum length.
  • VARCHAR has variable length, this is an added advantage of VARCHAR over CHAR.

  • Each table must have a primary key a primary key gives a unique identity to every element stored in the table. The primary key can be auto incremented by using AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added.



  • INSERT INTO:

  • After creating the tables insert the values by using the insert into function.

  • INERT INTO table_name VALUES(value1,value2,...)

  • INSERT INTO table_name (column1, column2, column3,...)

  • VALUES (value1, value2, value3,...)

  • The values entered should be of the same type mentioned during table creation.

  • E.g.:


    NOTE: The primary key should not be NULL and int values need not be enclosed in double quotes whereas the varchar and char values need to be enclosed in double quotes.

  • The insert into statement must be added to the mysqli_query().



  • SELECT:

  • The select statement is used to select data from the records stored in the database.

  • SELECT column_name(s)

  • FROM table_name

  • E.g:


    NOTE: When you specify * all the columns in the table will be printed.If you want to print some specific columns then you can specify the column name.

  • Even this statement needs to be passed to the mysqli_query() and the result is stored in a variable.



  • The mysqli_fetch_array fetches information from the database in the form of arrays. The index of this array is the name of the variable and can be referenced by using the name of the variable.



  • WHERE:

  • The where clause is used to specify the condition, only if the condition is specified will the records be printed.

  • SELECT column_name(s)

  • FROM table_name

  • WHERE column_name operator value

  • Here, the condition can be any expression which satisfies our requirements.



  • ORDER BY:

  • Order clause is used to arrange the records in a specific order. The default order is ascending.But you can also set the order to descending by using DESC keyword.

  • SYNTAX:


  • You can also arrange the order of two columns at the same time by using ORDER BY column1, column2.



  • UPDATE:

  • The update statement is used to change the value of certain records.



  • You specify the records you want to update in the where clause.



  • DELETE:

  • Delete statement is used to delete records from a database

  • SYNTAX:

    NOTE: You pass all the statements (I.e. update, delete, etc) to the mysqli_query().You can pass it directly or store it in some variable and pass the variable to the function.


    DATABASE ODBC:

  • ODBC is an Application Programming Interface (API) that allows you to connect to a data source (e.g. an MS Access database).

  • An application programming interface (API) specifies how some software components should interact with each other.



  • Create an ODBC Connection:

    1.Open the Administrative Tools icon in your Control Panel.
    2.Double-click on the Data Sources (ODBC) icon inside.
    3.Choose the System DSN tab.
    4.Click on Add in the System DSN tab.
    5.Select the Microsoft Access Driver. Click Finish.
    6.In the next screen, click Select to locate the database.
    7.Give the database a Data Source Name (DSN).
    8.Click OK.
  • As long as on ODBC connection is available you can connect to any database. CONNECTING TO AN ODBC:

  • The odbc_connect() function is used to connect to an ODBC data source. The function takes four parameters:
    • The data source name

    • Username

    • Password

    • An optional cursor type.

  • If, for example, you created a data source named DataDirectMSSQL for connecting to a Microsoft SQL Server database, and had a user name of engr and a password abc you would use the following syntax in PHP:






  • CACKLE comment system





    Programming Resources
    Computer Networking Fundamentals Android Application