PHP code for various mysql statement



In Order to read or write data in mysql server, first we have to connect with mysql server. The bellow PHP code can use to connect the mysql server.

$servername = "localhost";
$username = "your_user_nmae";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

When ever the above code execute, The $conn variable will have the connection with the particular database. If we want to read the database name from file we can use the following PHP code

$dbname = file_get_contents("database_name.txt");

Reading data from a mysql table

After making connection with particular database , we can read the data from any table in the database with following PHP code.

$sql="SELECT * FROM table_name   WHERE column_name LIKE '%$q%'";
$result = $conn->query($sql);

Here the SELECT will tell the sql instruction to select the all the rows from the table’ table_name‘. And this instruction will select all the row which contain the word in the $q variable in the column ‘column_name‘. % sumble before the$q variable says that that any words before the $q variable will not consider.  Sme way % symbol on the right side of the $q variable says that any words after the $q variable will not consider. After executing the first line of code The instruction will be in the $sql variable. During the execution of the second line of the code, all the rows as per the instruction will store in the variable $result. Please see the various row selection instructions below.

$sql="SELECT * FROM table_name   WHERE column_name LIKE '%$q%' ORDER BY column_name DESC LIMIT 100 ";
$result = $conn->query($sql);

The above selection will same as previous one. But the row will be arrange in the varble in descending order of any column name. For making ascending order chege the keyword DESC to ASC.

Another selection

$sql1 = "SELECT * FROM table_name WHERE column_name = '$invoice_number' ";
$result1 = $conn->query($sql1);

In the above selection the row will select if the exact value of ‘$invoice_number‘ variable found in the column_name.



Another selection

$sql1 = "SELECT * FROM table_name WHERE time_stamp >= '$from_time_stamp' AND time_stamp <= '$to_time_stamp' ";
$result1 = $conn->query($sql1);

In the above selection the variable is compare with logical operator. Here  we are checking the variable with multiple column by connecting the instruction with ‘AND ‘operator. We can also use the ‘OR’ operator. Once the rows are selected and stored in a variable by executing the above code, we can get the number of rows in the variable by the following code.

$num1=$result1->num_rows;

Now we can get the all values in the selected rows by following code.

$i=0;
while ($i < $num1) {$row1 = $result1->fetch_assoc();

$id=$row1['id'];

$date =$row1['date'];

$disc=$row1['disc'];

$invoice_number=$row1['invoice_number'];

$trn=$row1['trn'];

$code1=$row1['code1'];

$code2=$row1['code2'];}

In the above code during the execution of the while loop, in each step the fetch_assoc()

will assign the first row values to the variable $row1 and in the second step row2 and in the third step row 3 and complete the all row selected. Now we can assign the values of each column name in that particular row to avariabel by $code2=$row1['code2'];

Inserting a new row in to a table

To insert a new row into the table first we have to connect the database with the connecting PHP code. Then we can use the following PHP SQL statement to insert a new row.

$sql = "INSERT INTO table_name (partno, dis, make, price, qty, date, unit, vat, search, orginal_price)
VALUES ('$partno', '$disc', '$make', '$price', '$qty', '$d/$m/$y', '$unit', '$vat', '$partno $disc $make', '$cost_price')";

$result = $conn->query($sql);

$last_id = $conn->insert_id;

The above code will insert the values of the variables inside the value brackets. Each value will inserted in the column described in the table_name bracket respectively. The Table name tels in which table the values will enter. And the result of the insertion will assign to the $result variable. We can also get the inserted row id with last line of code.

Editing a row In a SQL table

By using the following code we can edit a particular row in a table.

$sql4="UPDATE table_name SET column_name='$last_id' WHERE id='$last_id'";
$result4 = $conn->query($sql4);

The above code will update the table ‘table_name‘ by setting the new value in the column name ‘column_name‘ with the $last_id  variable. with matching condition of row with WHERE keyword. We can also use various conditions with different logical operators.



Deleting a row from SQL table

By using the following code we can edit a particular row in a table.

$sql18 = "DELETE FROM table_name  WHERE id='$id'";
$result18 = $conn->query($sql18);

The above code will delete the rows from the table_name table, where the column name is id and  the values of the rows equal to the value in the $id variable. We can also use various type of logical operators in the WHERE keyword.

Creating a new table in a SQL database

Please use the below code to create anew table in the connected database.

$sql = "CREATE TABLE table_name(id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT, partno VARCHAR(300), disc VARCHAR(300), qty BIGINT(15), unit VARCHAR(50), price FLOAT(12,2), tprice FLOAT(12,2), vat FLOAT(12,2),vat1 FLOAT(12,2), itemcode VARCHAR(300), gtprice FLOAT(12,2), cost_price FLOAT(12,2), total_cost_price FLOAT(12,2) )";
$result12 = $conn->query($sql);

By the above code we can create a new table ‘table_name‘ in the connected database. We should take care the new table name is not already in the same database.

Notes:

As we open the connection to a database, we need to close the connection after the use. By using the following code we can close thee database connection.

mysqli_close($conn);

$conn variable name should be same as used in the connection code.

All the above codes will execute in side a php opening and close tag as shown below.

<?php      ?>

If any one need more details or help in this topic, Please give a comment bellow.




Leave a Reply