Category: MySQL

Getting Started with Database Management in PHP: Connecting, Querying, and Working with Results | PHP Beginner to Advance

When it comes to building web applications, databases play a crucial role in storing and managing data. PHP, being one of the most popular programming languages for web development, provides several ways to interact with databases. In this blog post, we’ll take a look at the basics of working with databases in PHP.

First, let’s talk about connecting to a database. In order to interact with a database, we first need to establish a connection. This can be done using the mysqli or PDO extension in PHP.

// Using mysqli
$mysqli = new mysqli("hostname", "username", "password", "database_name");

// Using PDO
$pdo = new PDO("mysql:host=hostname;dbname=database_name", "username", "password");

Once we have established a connection, we can execute queries on the database. These queries can be used to insert, update, delete or select data. Here’s an example of a SELECT query:

$query = "SELECT * FROM users";
$result = $mysqli->query($query);

We can also use prepared statements to prevent SQL injection attacks:

$stmt = $mysqli->prepare("SELECT * FROM users WHERE id=?");
$stmt->bind_param("i", $id);
$stmt->execute();

When we execute a query, we get a result set, which can be used to work with the data returned by the query. For example, we can use a while loop to iterate over the results:

while ($row = $result->fetch_assoc()) {
    echo "Name: " . $row["name"] . "<br>";
}

We can also use the fetchAll() method to retrieve all rows at once:

$users = $stmt->fetchAll();

In addition to these basic functions, there are many more advanced features available for working with databases in PHP. For example, you can use transactions to ensure that multiple queries are executed together or not at all, and you can use prepared statements to improve the performance of your queries.

In conclusion, working with databases in PHP is a crucial aspect of web development. By understanding the basics of connecting to a database, executing queries, and working with results, you can build powerful and dynamic web applications that can store and manage large amounts of data.

Here are the parts of the series

File Handling in PHP: A Beginner’s Guide | PHP Beginner to Advance

File handling is an important part of any web application that needs to read or write data to the server. PHP provides a number of built-in functions for working with files, making it easy to read, write, and upload files. In this blog post, we’ll be taking a look at the basics of file handling in PHP, including reading, writing, and uploading files.

Reading a File

To read a file in PHP, we can use the fopen() function, which opens a file for reading. Once the file is open, we can use the fread() function to read the contents of the file. Here’s an example of how to read the contents of a file called “example.txt”:

$file = fopen("example.txt", "r");
$contents = fread($file, filesize("example.txt"));
fclose($file);
echo $contents;

In this example, we first open the file “example.txt” for reading using the fopen() function. The first parameter of the fopen() function is the name of the file, and the second parameter is the mode in which the file should be opened. In this case, we’re opening the file in “r” mode, which stands for read mode. Once the file is open, we use the fread() function to read the contents of the file. The first parameter of the fread() function is the file handle that we got from the fopen() function, and the second parameter is the number of bytes to read from the file. In this case, we’re using the filesize() function to determine the number of bytes in the file. Finally, we close the file using the fclose() function and echo the contents of the file.

Writing a File

To write to a file in PHP, we can use the fopen() function, which opens a file for writing. Once the file is open, we can use the fwrite() function to write data to the file. Here’s an example of how to write some data to a file called “example.txt”:

$file = fopen("example.txt", "w");
$data = "This is some data to be written to the file.";
fwrite($file, $data);
fclose($file);

In this example, we first open the file “example.txt” for writing using the fopen() function. The first parameter of the fopen() function is the name of the file, and the second parameter is the mode in which the file should be opened. In this case, we’re opening the file in “w” mode, which stands for write mode. If the file does not exist it will be created. Once the file is open, we use the fwrite() function to write the data to the file. The first parameter of the fwrite() function is the file handle that we got from the fopen() function, and the second parameter is the data to be written to the file. Finally, we close the file using the fclose() function.

Uploading a File

Uploading files is a common feature in many web applications, and PHP provides a number of built-in functions for working with file uploads. In this post, we’ll be taking a look at how to upload a file in PHP, with a simple example to help illustrate the process.

First, let’s start with the HTML form that will be used to upload a file. Here’s an example of a simple form that allows a user to select a file and upload it to the server:

<form action="upload.php" method="post" enctype="multipart/form-data">
    <input type="file" name="fileToUpload">
    <input type="submit" value="Upload File">
</form>

In this example, we have a simple form with two inputs. The first input is of type “file” and is used to select a file to upload. The second input is of type “submit” and is used to submit the form. The form itself has an action of “upload.php”, which means that when the form is submitted, the data will be sent to the “upload.php” script on the server. The method attribute is set to “post” and enctype is set to “multipart/form-data”, which is required for uploading files.

Now let’s take a look at the PHP script that will handle the file upload:

<?php
    $target_dir = "uploads/";
    $target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
    move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file);
    echo "The file ". basename( $_FILES["fileToUpload"]["name"]). " has been uploaded.";
?>

In this script, we first define a target directory where the uploaded file will be stored, in this case “uploads/”. Next, we use the PHP function basename() to get the name of the uploaded file, which we then use to create the target file path. We then use the move_uploaded_file() function to move the uploaded file from its temporary location to the target location. Finally, we use an echo statement to confirm that the file has been uploaded.

It’s worth noting that this is just a basic example, and in a real-world application, you would likely want to add additional validation and error handling to ensure that the file is successfully uploaded and is of the correct type and size.

That’s it! With this simple example, you should now have a good understanding of how to upload a file in PHP. Happy coding!

In Conclusion

File handling in PHP is a powerful feature that allows developers to read, write, and upload files on a server. Understanding the basics of file handling, such as using built-in functions like fopen(), fread(), and fwrite() and the $_FILES superglobal for handling file uploads, is essential for building robust and dynamic web applications. Additionally, it’s important to consider security and validation when working with files, to ensure that only authorized users are able to upload and access files, and that the files themselves are of the correct type and size. With a solid understanding of file handling in PHP, you will be well on your way to building dynamic and powerful web applications.

Here are the parts of the series

Objects and Classes in PHP: A Beginner’s Guide | PHP Beginner to Advance

Object-oriented programming (OOP) is a popular programming paradigm that is widely used in modern programming languages, including PHP. In this blog post, we’ll be taking a look at the basics of OOP in PHP, including classes, objects, and inheritance.

Classes in PHP

A class in PHP is a template or blueprint for creating objects. It defines the properties and methods that an object of that class will have. Here’s an example of a simple class called “Person”:

class Person {
  public $name;
  public $age;

  public function sayHello() {
    echo "Hello, my name is " . $this->name;
  }
}

In this example, we’ve created a class called Person that has two properties, $name and $age, and one method, sayHello(). The properties and methods of a class are defined within the curly braces {}.

Objects in PHP

An object is an instance of a class. To create an object, we use the new keyword followed by the class name and parentheses. Here’s an example of how to create an object of the Person class:

$person = new Person();
$person->name = "John Doe";
$person->age = 30;
$person->sayHello();

In this example, we’ve created an object called $person of the Person class and set its properties, name and age. We’ve also called the sayHello() method of the class, which will output the string “Hello, my name is John Doe”.

Inheritance in PHP

Inheritance is a way for one class to inherit the properties and methods of another class. A class that inherits from another class is called a subclass or child class, and the class that is being inherited from is called the superclass or parent class. Here’s an example of how to create a subclass called “Student” that inherits from the “Person” class:

class Student extends Person {
  public $studentId;

  public function sayHello() {
    echo "Hello, my name is " . $this->name . " and my student ID is " . $this->studentId;
  }
}

$student = new Student();
$student->name = "Jane Smith";
$student->age = 25;
$student->studentId = 123456;
$student->sayHello();

In this example, we’ve created a subclass called Student that inherits from the Person class. The Student class has a new property, $studentId, and a new method, sayHello(). The sayHello() method of the Student class overrides the sayHello() method of the Person class. When we create an object of the Student class and call its sayHello() method, it will output the string “Hello, my name is Jane Smith and my student ID is 123456”.

In Conclusion

Object-oriented programming is a powerful programming paradigm that is widely used in PHP. Understanding the basics of classes, objects, and inheritance is an important part of becoming a proficient PHP developer. With the help of classes and objects, it is easier to structure, organize, and reuse the code for your projects.

Here are the parts of the series

PHP Functions: A Beginner’s Guide | PHP Beginner to Advance

Functions are an essential part of any programming language, and PHP is no exception. In this blog post, we’ll be taking a look at how to create and use functions in PHP, including built-in functions and user-defined functions.

Built-in Functions in PHP

PHP comes with a variety of built-in functions that can be used to perform common tasks, such as string manipulation, mathematical operations, and array manipulation. Some examples of built-in functions in PHP include:

  • strlen(): Returns the length of a string
  • round(): Rounds a number to a specified number of decimal places
  • count(): Counts the number of elements in an array

Here’s an example of how to use the built-in strlen() function to find the length of a string:

$name = "John Doe";
$length = strlen($name);
echo "The length of the string is: " . $length;

User-Defined Functions in PHP

In addition to built-in functions, you can also create your own functions in PHP. User-defined functions allow you to organize your code and reuse it throughout your program. Here’s an example of how to create a simple user-defined function that adds two numbers together:

function add($a, $b) {
  return $a + $b;
}

$result = add(3, 5);
echo "The result is: " . $result;

In this example, we’ve created a function called add() that takes in two parameters, $a and $b. Inside the function, we use the return statement to return the sum of $a and $b.

Functions in PHP can also accept an optional parameter with a default value, which can be used if no value is passed to the function. Here’s an example of how to create a user-defined function that calculates the area of a rectangle with a default value for width.

function rectangle_area($height, $width = 5) {
  return $height * $width;
}

$area = rectangle_area(10);
echo "The area is: " . $area;

In this example, width parameter has a default value of 5, if no width value is passed to the function, it will use the default value.

In Conclusion

Functions are an essential part of programming and are a great way to organize your code and make it more reusable. Whether you’re using built-in functions or creating your own, understanding how to use functions in PHP is an important part of becoming a proficient PHP developer.

Here are the parts of the series

The Basic Syntax of PHP: A Beginner’s Guide | PHP Beginner to Advance

PHP, or Hypertext Preprocessor, is a popular server-side programming language that is widely used for building dynamic websites and web applications. In this blog post, we’ll be taking a look at the basic syntax of PHP, including variables, data types, and control structures.

Variables in PHP

A variable in PHP is a container that holds a value. Variables are used to store data and can be used throughout your program. In PHP, variables are declared using the dollar sign ($), followed by the variable name. For example:

$name = "John Doe";

In this example, we’ve declared a variable called $name and assigned it the value “John Doe”.

Data Types in PHP

In PHP, there are several data types that can be used to store different types of data. The most common data types in PHP are:

  • String: A string is a sequence of characters. Strings are enclosed in double or single quotes. For example:
$name = "John Doe";
  • Integer: An integer is a whole number (positive or negative) without a decimal point. For example:
$age = 25;
  • Float: A float is a number with a decimal point. For example:
$price = 9.99;
  • Boolean: A boolean is a true or false value. For example:
$is_admin = true;
  • Array: An array is a collection of values that can be accessed using an index. For example:
$colors = ["red", "green", "blue"];
  • Object: An object is an instance of a class. Objects can have properties and methods. For example:
class User {
  public $name;
  public $age;
}
$user = new User;
$user->name = "John Doe";
$user->age = 25;

Control Structures in PHP

Control structures are used to control the flow of execution of your program. The most common control structures in PHP are:

  • if/else: The if/else statement is used to test a condition and execute a block of code if the condition is true. For example:
if ($age >= 18) {
  echo "You are an adult.";
} else {
  echo "You are a minor.";
}

for loop: The for loop is used to iterate over a block of code a specific number of times. For example:

for ($i = 0; $i < 10; $i++) {
  echo $i;
}
  • while loop: The while loop is used to iterate over a block of code while a condition is true. For example:
$i = 0;
while ($i < 10) {
  echo $i;
  $i++;
}
  • foreach loop: The foreach loop is used to iterate over arrays and objects. For example:
$colors = array("red", "green", "blue");
foreach ($colors as $color) {
  echo $color;
}

This will give you a basic idea of PHP programming.

Here are the parts of the series

Recover your Hacked facebook account (No Email Access)

This post is about recovering your lost facebook account.

One of my friend’s facebook account was recently hacked. He changed name on my friend’s profile and all the recovery option. I 3 to 4 days trying himself, he asked me for help. So I started searching for how I can recover the hacked facebook account.

I got some good forum links which I am posting here for others who might get some help from it

https://www.facebook.com/help/community/question/?id=1051595624858089

If above link doesn’t help and if you ended on “No Email Access” page. I would suggest not creating new facebook account and follow below form of facebook.

https://www.facebook.com/help/contact/295309487309948?helpref=faq_content

Select Yes for “Is this account impersonating you?” question and upload your ID.

Please add your comment if you feel this helped to you anyway. I would love to here from you. Also post if what happened and what facebook reply. I will update this post later once my friend got any reply from facebook.

All the best.

Error : 1067 the process terminated unexpectedly in mysql wamp

Recently I encountered a problem with MySQL, It was showing an error “Error : 1067 the process terminated unexpectedly” when I tried to start the service in services.msc

So I googled it but didn’t found the perfect solution. I will show how can you debug the issue. First look for file my.ini which can be found in “C:\wamp\bin\mysql\mysql5.6.17” , open that file on look for “log-error”, this will show you the path where error log is stored. In my case it is, “c:/wamp/logs/mysql.log”.

Open the log file and look for last error you got, you should be today’s date when you tried to start the wampmysqld from services. At this stage, you will get error and understand what it say. In my case it is,

InnoDB: Attempted to open a previously opened tablespace. Previous tablespace kmk/players uses space ID: 58 at filepath: .\kmk\players.ibd. Cannot open tablespace ocean/acos which uses space ID: 58 at filepath: .\ocean\acos.ibd
InnoDB: Error: could not open single-table tablespace file .\ocean\acos.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

Looking at error, it seems that there is some problem with “kmk\players.ibd” , here kmk it one of the my database. The folders for all databases are in “C:\wamp\bin\mysql\mysql5.6.17\data”. To fix I moved kmk folder to some other place. After this, wampmysqld in services.msc is getting started without any error.

That fixed my error.

Share your experience and error in log file in comment section. This will also prevent other from reinstalling wamp server.

Thanks

ACID Properties – SQL Database

ACID is acronym of Atomicity, Consistency, Isolation and Durability

Atomicity

Atomicity requires that database modifications must follow an all or nothing rule. Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails and database state is left unchanged. It is critical that the database management system maintains the atomic nature of transactions in spite of any application, DBMS, operating system or hardware failure.

An atomic transaction cannot be subdivided, and must be processed in its entirety or not at all. Atomicity means that users do not have to worry about the effect of incomplete transactions.

Transactions can fail for several kinds of reasons:

  • Hardware failure: A disk drive fails, preventing some of the transaction’s database changes from taking effect
  • System failure: The user loses their connection to the application before providing all necessary information
  • Database failure: E.g., the database runs out of room to hold additional data
  • Application failure: The application attempts to post data that violates a rule that the database itself enforces, such as attempting to create a new account without supplying an account number

Consistency

The consistency property ensures that the database remains in a consistent state; more precisely, it says that any transaction will take the database from one consistent state to another consistent state.

The consistency property does not say how the DBMS should handle an inconsistency other than ensure the database is clean at the end of the transaction. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction could be rolled
back to the pre-transactional state – or it would be equally valid for the DBMS to take some patch-up action to get the database in a consistent state. Thus, if the database schema says that a particular field is for holding integer numbers, the DBMS could decide to reject attempts to put fractional
values there, or it could round the supplied values to the nearest whole number: both options maintain consistency.

The consistency rule applies only to integrity rules that are within its scope. Thus, if a DBMS allows fields of a record to act as references to another record, then consistency implies the DBMS must enforce referential integrity: by the time any transaction ends, each and every reference in the database must be valid. If a transaction consisted of an attempt to delete a record referenced by
another, each of the following mechanisms would maintain consistency:

  • Abort the transaction, rolling back to the consistent, prior state;
  • Delete all records that reference the deleted record (this is known as cascade delete); or,
  • nullify the relevant fields in all records that point to the deleted record.

These are examples of Propagation constraints; some database systems allow the database designer to specify which option to choose when setting up the schema for a database.

Application developers are responsible for ensuring application level consistency, over and above that offered by the DBMS. Thus, if a user withdraws funds from an account and the new balance is lower than the account’s minimum balance threshold, as far as the DBMS is concerned, the
database is in a consistent state even though this rule (unknown to the DBMS) has been violated.

Isolation

Isolation refers to the requirement that other operations cannot access or see data that has been modified during a transaction that has not yet completed. The question of isolation occurs in case of concurrent transaction, (i.e. transaction occurring at the same time) and with the same database. To preserve the database consistency, the need of isolation arises. Each transaction must remain unaware of other concurrently executing transactions, except that one transaction may be forced to wait for the completion of another transaction that has modified data that the waiting transaction requires. If the isolation system does not exist, then the database may remain in an inconsistent state. This may happen as in case of concurrent transaction, one transaction may leave some data-items in mid process and at the same time another concurrent transaction may try to access/alter the same data-item which may cause data inconsistency and may leave the database in an inconsistent
state.

Durability

Durability is the ability of the DBMS to recover the committed transaction updates against any kind of system failure (hardware or software). Durability is the DBMS’s guarantee that once the user has been notified of a transaction’s success, the transaction will not be lost. The transaction’s data changes will survive system failure, and that all integrity constraints have been satisfied, so the DBMS won’t need to reverse the transaction. Many DBMSs implement durability by writing transactions into a transaction log that can be reprocessed to recreate the system state right before any later failure. A transaction is deemed committed only after it is entered in the log.

Durability does not imply a permanent state of the database. A subsequent transaction may modify data changed by a prior transaction without violating the durability principle.

Thanks for reading the article.

Source: allinterview.com

Please comment if you have any questions or suggestions. Thanks

What are DDL and DML statements in MySQL?

DDL and DML are different type of SQL queries in MySQL. The full form of DDL and DML is Data Definition language and Data Manipulation Language.

DDL – Data Definition Language:

This are the SQL statements which are most related to Database creating/changing structure. The List of DDL statements are as follow:

  • CREATE – statement is used to create database and other objects
  • ALTER – statement is used to change the database structure
  • DROP – statement is used to delete the database and other objects
  • TRUNCATE – statement  to remove all the records from a table, including all spaces allocated for the records are removed
  • COMMENT – add comments to the data dictionary
  • RENAME – rename is used to rename the objects

DML – Data Manipulation Language:

These are the SQL statements which are related to Database manipulation like fetching, adding, updating data to table. The List of DML statements are as follow:

  • SELECT – statement is used to retrieve/fetch data from the a database
  • INSERT – statement used to insert data into a table
  • UPDATE – statement used to  updates existing data within a table
  • DELETE – deletes all records from a table, the space for the records remain
  • CALL – call a PL/SQL or Java subprogram

DCL – Data Control Language

These are the SQL statements which are related to control the user access to the Database. The List of DCL statements are as follow:

  • GRANT – statement  allows specified privileges to database
  • REVOKE – withdraw access privileges given with the GRANT command

TCL – Transaction Control Language

There are the SQL statements which are related to control transactions. The List of TCL statements are as follow:

  • COMMIT – save work done
  • SAVEPOINT – identify a point in a transaction to which you can later roll back
  • ROLLBACK – restore database to original since the last COMMIT
  • SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use

 

Thank you for reading the article. Please comment if you have any confusion.

Faceted Search (Layered Search) on MySQL database with example.

Last few day I was searching for the layered search using MySQL. Layered Search is actually a Faceted Search. (Magento, a highly professional e-commerce platform on php name it Layered Search). Faceted Search can be done using two way MySQL/Any DB Application or using Apache Solr.

In this post I will show you how we can do Faceted search using MySQL database. You need a specific database schema, but it’s feasible. Here’s a simple example:

product Table

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
|  2 | red paint  |
+----+------------+

classification Table

+----+----------+
| id | name     |
+----+----------+
|  1 | color    |
|  2 | material |
|  3 | dept     |
+----+----------+

product_classification Table

+------------+-------------------+-------+
| product_id | classification_id | value |
+------------+-------------------+-------+
|          1 |                 1 | blue  |
|          1 |                 2 | latex |
|          1 |                 3 | paint |
|          1 |                 3 | home  |
|          2 |                 1 | red   |
|          2 |                 2 | latex |
|          2 |                 3 | paint |
|          2 |                 3 | home  |
+------------+-------------------+-------+

So, say someones search for paint, you’d do something like:

SELECT p.* FROM product p WHERE name LIKE '%paint%';

This would return both entries from the product table.

Once your search has executed, you can fetch the associated facets (filters) of your result using a query like this one:

SELECT c.id, c.name, pc.value FROM product p
   LEFT JOIN product_classification pc ON pc.product_id = p.id
   LEFT JOIN classification c ON c.id = pc.classification_id
WHERE p.name LIKE '%paint%'
GROUP BY c.id, pc.value
ORDER BY c.id;

This’ll give you something like:

+------+----------+-------+
| id   | name     | value |
+------+----------+-------+
|    1 | color    | blue  |
|    1 | color    | red   |
|    2 | material | latex |
|    3 | dept     | home  |
|    3 | dept     | paint |
+------+----------+-------+

So, in your result set, you know that there are products whose color are blue and red, that the only material it’s made from is latex, and that it can be found in departments home and paint.

Once a user select a facet, just modify the original search query:

SELECT p.* FROM product p
   LEFT JOIN product_classification pc ON pc.product_id = p.id
WHERE 
   p.name LIKE '%paint%' AND (
      (pc.classification_id = 1 AND pc.value = 'blue') OR
      (pc.classification_id = 3 AND pc.value = 'home')
   )
GROUP BY p.id
HAVING COUNT(p.id) = 2;

So, here the user is searching for keyword paint, and includes two facets: facet blue for color, andhome for department. This’ll give you:

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
+----+------------+

So, in conclusion. Although it’s available out-of-the-box in Solr, it’s possible to implement it in SQL fairly easily.

 

Thanks stackoverflow.com