PHP: simple MySQL database query, form and button

Posted on July 2, 2021 by

Getting started with databases in PHP can seem daunting if you’re trying to eat an elephant right off the bat and making queries that are overly complex. Let’s start with the most basic simple database query to understand the general principle. A working code that can be copied is at the end of the post.

Examples:
1) output and print one value from the MySQL database in PHP, in our example we will extract the name of the seventh player;
2) make a form on the site (drop-down list) with a button to send the selected value from the form to the program and get the necessary data from the MySQL database.

Stages of connecting to MySQL database

  1. PHP code starts with <?php and ends with ?> This will tell the computer where the program starts and ends. 
  2. At the very beginning of the code, it is convenient to indicate how errors are displayed – if something goes wrong, you can quickly understand the error code. In the PHP programming language, the following commands are used for this:
    ᅠ
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL); 

  3. Connecting to the MySQL database: create a variable, for example, $connect, in which, through the connector, we enter access to our database (name, password, etc.). This MySQL database must already exist and “spin” either on the server or on the Local. We will have a separate material about the installation and creation of our own database. We connect now: 
    ᅠ
    $connect = mysqli_connect('localhost', 'database', 'password', 'user');

  4. We create a query to the database – what data we want to receive: for this we create a new variable, for example, $query1, to which we assign the query value in a language that is understandable for the MySQL database – choose <what? which column?> from <name of the table in the database> where <selection conditions, for example, where the value of N in the column of numbers = 7>. The request looks like this: 
    ᅠ
    $query1 = "SELECT Name FROM igroki WHERE N='7'";

  5. Preparation for returning query results for reading: create a new variable, for example, $result – which will receive the query result. The construction [DB connection] -> query ([DB query]) is used. In our example, returning results looks like this: 
    ᅠ
    $result = $connect->query($query1);

  6. Then you should get one record at a time from the result set of the returned query (essentially brute force). In our example – this will only be 1 record because you get id = 7. Typically you will use mysqli_fetch_array ($result) to return an array of data, or you can use mysqli_fetch_object ($result) to return a data object. In our example, we create a new variable, for example, $record and fetch the resulting array:
    ᅠ
    $record = mysqli_fetch_array($result);

  7. Print the name of the seventh player: for printing in PHP, the echo operator is used, where you can write text to print in quotes, and combine the text with a variable through a dot – since the returned array has several “columns”, and we need to display exactly the name, then we specify in the parentheses to the $record variable is ‘Name’:
    ᅠ
    echo 'Name of 7th player: ' . $record['Name'];

  8. Disconnecting from the database:
    ᅠ
    $connect -> close();

*If you have multiple records in your table and want to print them out, you can use a while loop to iterate over the data like this:
ᅠ
$query2 = "SELECT Name FROM igroki";
$result2 = $connect->query($query2);

while ($record2 = mysqli_fetch_array($result2))
{
    echo 'Name: ' . $record2['Name'] . '<br>';
}

MySQL Database Connections – Simple PHP Query Example

<?php
ini_set(‘display_errors’, 1);
ini_set(‘display_startup_errors’, 1);
error_reporting(E_ALL);

$connect = mysqli_connect(‘localhost’, ‘database’, ‘password’, ‘user’);

$query1 = “SELECT Name FROM igroki WHERE N=’7′”;

$result = $connect->query($query1);

$record = mysqli_fetch_array($result);

echo ‘Name of 7th player: ‘ . $record[‘Name’];

echo “<hr><hr><hr><br>”;  // html-separator

// Iterating over multiple values:

echo “All players:<br><br>”;
$query2 = “SELECT Name FROM igroki”;
$result2 = $connect->query($query2);
while ($record2 = mysqli_fetch_array($result2)) {
echo ‘Name: ‘ . $record2[‘Name’] . ‘<br>’;
}

$connect -> close();
?>

Creating a button on the site to get values from a MySQL database

To the code that you already know – the basic query to the database – it remains to add the html-code of the button through the <form> </form> tag (allows you to create interactive elements on the site and send data to the server).

To make a button appear inside the form, enter action – you can empty (action = '') so that when clicked, the same program is loaded, or you can specify a link in quotes to another file with code that will be launched when the button is clicked (for example, action = 'submit. php ').

It is important to give the select name to the form – any name that can then be referenced in the program).

On the site, a button, lists, drop-down menus can be made using forms that can be of the type POST (method = 'POST') or GET (method = 'GET'). If you make a POST form, then the user will not see the text corresponding to the selected options in the browser line. If you make a GET form, then the browser line will show the value after selecting the option (the search engine works in a similar way, if you make a request, then information about the request is added to the browser line).

<?php
if (isset($_POST[‘submit’]))
{
echo ” 
<form action=” method=’POST’>
<input type=’submit’ name=’submitID’ />
</form>“;
}

ini_set(‘display_errors’, 1);
ini_set(‘display_startup_errors’, 1);
error_reporting(E_ALL);

$connect = mysqli_connect(‘localhost’, ‘database’, ‘password’, ‘user’);
$query1 = “SELECT Name FROM igroki WHERE N=’7′”;
$result = $connect->query($query1);
$record = mysqli_fetch_array($result);

if (isset($_POST[‘submit’]))
{
echo ‘Name of 7th player: ‘ . $record[‘Name’];
}

$connect -> close();
?>

Creating a form (drop-down list) with a button on the site

Having learned how to access the database and make simple buttons, it’s time to make the form on the site in the form of a dropdown menu (dropdown menu) and an associated button that the user can click to select a value, send a query to the database and get the results on the monitor screen.

On the site, a drop-down menu is created using forms that can be of the type POST (method = 'POST') or GET (method = 'GET').

To create a dropdown menu with a button, use the html form tag <form> </form> with a choice of options via tags:

<select> (you can specify action = ” so that when clicked, the same program is loaded, or you can specify a link in quotes to another file with the code that will be launched when the button is clicked; it is important to assign select name – any name that can then be referenced in a programme);

<option> (option value is the value that will be written to the variable when this item is selected, and Seven and Eight is what is shown to the user on the site in the drop-down menu);

Inside the form, add a button via the html <input> tag.

Example code for a dropdown menu with a POST method button:
ᅠ

<form action=” method=’POST’>

<select name=”PlayerID”>
<option value=”7″>Seven</option>
<option value=”8″>Eight</option>
</select>

<input type=’submit’ name=’submitID’ />

</form>

Having created a form with a button, we now need to be able to receive a custom choice. If the user clicks the option button, then the program should understand this. Let’s teach the computer to communicate with the user. To do this, we will create a condition with a button click test – if the button was clicked (isset in PHP checks if a variable has a value other than null), then make a query to the database and print what the user selected.

This code is very similar to what we already wrote at the beginning of this post, creating a simple database query.

Getting the user’s choice after clicking the button on the site – print one value from the database (the name of the seventh player):
ᅠ

if (isset($_POST[‘submit’]))
{
    $number = $_POST[‘PlayerID’];
    $query = “SELECT Name FROM igroki WHERE N=’$number'”;
    $result = $connect->query($query);
    $record = mysqli_fetch_array($result);
    echo ‘Name of 7th player: ‘ . $record[‘Name’];
}

Creating a form (drop-down list) with a button that sends a query to the MySQL database in html + PHP with the output of the results on the site

 

<form action=” method=’POST’>

<select name=”PlayerID”>
<option value=”7″>Seven</option>
<option value=”8″>Eight</option>
</select>

<input type=’submit’ name=’submitID’ />

</form>

<?php
ini_set(‘display_errors’, 1);
ini_set(‘display_startup_errors’, 1);
error_reporting(E_ALL);

$connect = mysqli_connect(‘localhost’, ‘database’, ‘password’, ‘user’);

if (isset($_POST[‘submit’]))
{
    $number = $_POST[‘PlayerID’];
    $query = “SELECT Name FROM igroki WHERE N=’$number'”;
    $result = $connect->query($query);
    $record = mysqli_fetch_array($result);
    echo ‘Name of 7th player: ‘ . $record[‘Name’];
}

$connect -> close();
?>

Working PHP code to copy with examples

Working code that can be copied – it remains to paste your data to connect to the MySQL database and everything will work:

  1. MySQL database query: 
    <?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$connect = mysqli_connect('localhost', 'ribnick_tgladder', 'kotoParolkins74350', 'ribnick_tgladder');

$query1 = "SELECT Name FROM igroki WHERE N='7'";

$result = $connect->query($query1);

mysqli_fetch_array($result) // to return an array of the data, or 
mysqli_fetch_object($result) // to return an object of the data.

$record = mysqli_fetch_array($result);

echo 'Name of 7th player: ' . $record['Name'];

echo "<hr><hr><hr><br>";

// If you have multiple records use a while:

echo "All players:<br><br>";

$query2 = "SELECT Name FROM igroki";
$result2 = $connect->query($query2);
while ($record2 = mysqli_fetch_array($result2)) {
    echo 'Name: ' . $record2['Name'] . '<br>';
}

$connect -> close();
?>
  2. Button and data output on request from MySQL: 
    <?php
if(!isset($_POST['submit']))
{
echo "
<form action='' method='POST'>
<input type='submit' name='submit' />
</form>";
}

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
$connect = mysqli_connect('localhost', 'db', 'pass', 'user');

$query1 = "SELECT Name FROM igroki WHERE N='7'";
$result = $connect->query($query1);
$record = mysqli_fetch_array($result);

if(isset($_POST['submit']))
{
    echo 'Name of 7th player: ' . $record['Name'];
}

$connect -> close();
?>
  3. Form (drop-down list) with a choice and a button for a query to the MySQL database with the subsequent display of the results on the site: 
    <form action='' method='POST'>

<select name="PlayerID">
  <option value="7">Seven</option>
  <option value="8">Eight</option>  
</select>

<input type='submit' name='submit' />
</form>

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
$connect = mysqli_connect('localhost', 'db', 'pass', 'user');
    
if (isset($_POST['submit']))
{
    $number = $_POST['PlayerID'];
    $query = "SELECT Name FROM igroki WHERE N='$number'";
    $result = $connect->query($query);
    $record = mysqli_fetch_array($result);
    echo 'Name of 7th player: ' . $record['Name'];
}

$connect -> close();
?>

This entry was posted in PHP (en) and tagged , , , . Bookmark the permalink.

Leave a Reply

🇬🇧 Attention! Comments with URLs/email are not allowed.
🇷🇺 Комментарии со ссылками/email удаляются автоматически.