Php select from view

PHP / MySQL select data and split on pages

This tutorial is going to show you how to SELECT data from a MySQL database, split it on multiple pages and display it using page numbers. Check our live demo.

We have MySQL table called «students» holding 100 records with the following fields:
ID: autoincrement ID
Name: varchar(250)
PhoneNumber: varchar(250)

Instead of doing a single SELECT query and display all the 100 records on a single page we can have 5 pages each containing maximum 20 records. To do this we will need to use the LIMIT clause for SELECT command so we can limit the query to show only 20 records. The LIMIT clause also allows you to specify which record to start from. For example this query

$sql = "SELECT * FROM students ORDER BY name ASC LIMIT 0, 20";

returns 20 records sorted by name starting from the first record. This next query

$sql = "SELECT * FROM students ORDER BY name ASC LIMIT 50, 20";

shows 20 records sorted again by name but this time it will start from the 50th record.
So basically in this clause (LIMIT start, count) «start» specify the starting record and «count» specifies how many records to show.

We will first define MySQL connection variables and connnect to the database

 error_reporting(0); 
$servername = "localhost"; 
$username = "****"; 
$password = "****"; 
$dbname = "****"; 
$datatable = "students"; // MySQL table name 
$results_per_page = 20; // number of results per page 
 
// Create connection 
$conn = new mysqli($servername, $username, $password, $dbname); 
// Check connection 
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error); 
> 
?>

Next thing to do is to make a PHP file called index.php which will show the first 20 records from our table. The code below selects and then prints the data in a table.

 if (isset($_GET["page"])) < $page = $_GET["page"]; >else < $page=1; >; 
$start_from = ($page-1) * $results_per_page; 
$sql = "SELECT * FROM ".$datatable." ORDER BY ID ASC LIMIT $start_from, ".$results_per_page; 
$rs_result = $conn->query($sql); 
?> 




 while($row = $rs_result->fetch_assoc()) ?> 


 >; 
?> 
 
 
ID
 
Name
Phone
 
  
 
 
 
 
 
 

Now, when you open index.php in your web browser you will see table showing the first 20 records from your «students» table.

The first 2 lines of the above code

if (isset($_GET["page"])) < $page = $_GET["page"]; >else < $page=1; >; 
$start_from = ($page-1) * $results_per_page;

are used to create a $start_from variable depending on the page that we want to view. Later you will see that we will pass a «page» value using the URL (e.g. index.php?page=2) to go to different pages. Next, we need to find out the total amount of records in our table and the number of pages that we will need. To do this we run another query using COUNT() function.

$sql = "SELECT COUNT(ID) AS total FROM ".$datatable; 
$result = $conn->query($sql); 
$row = $result->fetch_assoc(); 
$total_pages = ceil($row["total"] / $results_per_page);

The $total_records is now equal to the number of records that we have in our database, in our case 100. We have 20 records per page so the total number of pages that will be needed is 5 (4 pages with 20 records and last page will have 10 records).

Calculating the amount of pages needed using PHP can be done using ceil() function.

$total_pages = ceil($total_records / $results_per_page);

We divide the total number of records by records per page and then the ceil() function will round up the result. Now we have 2 new variables — $total_records equal to 100 and $total_pages equal to 5.
To print page numbers and associate URLs to each number we will use for() cycle.

Above code will print numbers from 1 to 5 and for each number will create different link.
index.php?page=1
index.php?page=2
index.php?page=3
index.php?page=4
index.php?page=5
as you can see each link passes different page value which is used in the SELECT query above.

At the end you should have a file like this (remember to add the MySQL connection string):

This index.php file will print a table with maximum 20 records per page and at the bottom 5 page numbers each pointing to a page showing different 20 records.

If you have any questions you can always use the form below to ask for free help.
Do not forget to check the demo here.

Источник

php — Select value from view to controller

I’m trying to pass a selected value in a dropdown from my view to my controller so i can make a query later.

Here is my view with the select object:

   Select something option 1 option 2  Copy code

Then according to documentation and a lot of answer/examples i’m trying to get the value from the select on this line:

  $ColumnType->ColumnType])>> Copy code

My controller:

 public function TrendFilter($ColumnType) < $SelectedTrend = DB::table('SubSectorsBPIsData')->select('SectorID', 'ColumnType')->where('ColumnType', $ColumnType)->get(); echo $selectedTrend; //or whatever else i want to do > Copy code

Error I keep getting: Undefined variable: ColumnType (View: . I have tried to change several variables names in that line: $ColumnType->ColumnType])>> *Where does it comes from that undefined variable: $ColumnType ? I’m not reloading the page since i want to collect several dropdowns values and then make the query. Not sure if i got the right approach, maybe there is a better way to make it work. JavaScript maybe?
Thanks in advance.

Answer

Solution:

To clarify: The code you write in your views is HTML. If you use the > the code in the braces is evaluated in php first. All of this happens on your server. The $ColumnType -Variable does not exist when this happens (and also not later). The user has not even seen your site, when your server tries to know what the value of the variable is.

To send data back from the user to your controller, I suggest using a form:

  Select something option1 option2  Copy code

Share solution ↓

Additional Information:

Didn’t find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Similar questions

Find the answer in similar questions on our website.

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

About the technologies asked in this question

PHP

PHP (from the English Hypertext Preprocessor — hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites. The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/

JavaScript

JavaScript is a multi-paradigm language that supports event-driven, functional, and mandatory (including object-oriented and prototype-based) programming types. Originally JavaScript was only used on the client side. JavaScript is now still used as a server-side programming language. To summarize, we can say that JavaScript is the language of the Internet.
https://www.javascript.com/

HTML

HTML (English «hyper text markup language» — hypertext markup language) is a special markup language that is used to create sites on the Internet. Browsers understand html perfectly and can interpret it in an understandable way. In general, any page on the site is html-code, which the browser translates into a user-friendly form. By the way, the code of any page is available to everyone.
https://www.w3.org/html/

Welcome to programmierfrage.com

programmierfrage.com is a question and answer site for professional web developers, programming enthusiasts and website builders. Site created and operated by the community. Together with you, we create a free library of detailed answers to any question on programming, web development, website creation and website administration.

Get answers to specific questions

Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.

Help Others Solve Their Issues

Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.

Источник

PHP MySQL Select Data

The SELECT statement is used to select data from one or more tables:

or we can use the * character to select ALL columns from a table:

To learn more about SQL, please visit our SQL tutorial.

Select Data With MySQLi

The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:

Example (MySQLi Object-oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>

$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);

if ($result->num_rows > 0) // output data of each row
while($row = $result->fetch_assoc()) echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else echo «0 results»;
>
$conn->close();
?>

Code lines to explain from the example above:

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.

The following example shows the same as the example above, in the MySQLi procedural way:

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>

$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»]. » » . $row[«lastname»]. «
«;
>
> else echo «0 results»;
>

You can also put the result in an HTML table:

Example (MySQLi Object-oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>

$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);

if ($result->num_rows > 0) echo «

«;
// output data of each row
while($row = $result->fetch_assoc()) echo «

«;
>
echo «

ID	Name
«.$row[«id»].»	«.$row[«firstname»].» «.$row[«lastname»].»

«;
> else echo «0 results»;
>
$conn->close();
?>

Select Data With PDO (+ Prepared Statements)

The following example uses prepared statements.

It selects the id, firstname and lastname columns from the MyGuests table and displays it in an HTML table:

Example (PDO)

class TableRows extends RecursiveIteratorIterator <
function __construct($it) <
parent::__construct($it, self::LEAVES_ONLY);
>

function current() return «

» . parent::current(). «

«;
>

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;

try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare(«SELECT id, firstname, lastname FROM MyGuests»);
$stmt->execute();

Источник