Hello,
PDO code used in this paging
How to convert mysqli?
$statement = $connect->prepare($query);
$statement->execute();
$total_data = $statement->rowCount();
$statement = $connect->prepare($filter_query);
$statement->execute();
$result = $statement->fetchAll();
$total_filter_data = $statement->rowCount();The posted code is misusing prepare()/execute() for apparently non-prepared queries or is insecurely putting external data into the un-shown sql query statements, is misusing rowCount() to get the total number of rows from the first query that should be using a COUNT(*) query, and is probably producing an out of sync error at the second query due to not fetching the data from the first query. Why do you want to convert it? It will still have these mistakes in it.
I want to use this pagination code: https://www.webslesson.info/2020/02/instant-search-with-pagination-in-php-mysql-jquery-and-ajax.html
It has pagination ajax and search feature
There is no error message
I use Mysqli
$connect = new PDO("mysql:host=localhost; dbname=antenfiyati", "root", "");
$limit = '10';
$page = 1;
if($_POST['page'] > 1)
{
$start = (($_POST['page'] - 1) * $limit);
$page = $_POST['page'];
}
else
{
$start = 0;
}
$query = "SELECT
multiswitchler.id,
multiswitchler.urun_kodu,
multiswitchler.multiswitch_markasi,
multiswitchler.multiswitch_tipi,
multiswitchler.input,
multiswitchler.output,
multiswitchler.birim_fiyati,
multiswitchler.iskonto,
multiswitchler.multiswitch_para_birimi,
multiswitchler.multiswitch_adi,
multiswitchler.stok,
multiswitch_tipi.multi_tipi,
parabirimi.para_birimi,
markalar.urun_markasi
FROM multiswitchler
INNER JOIN parabirimi ON multiswitchler.multiswitch_para_birimi = parabirimi.id
INNER JOIN multiswitch_tipi ON multiswitchler.multiswitch_tipi = multiswitch_tipi.id
LEFT JOIN markalar ON multiswitchler.multiswitch_markasi = markalar.id ";
if($_POST['query'] != '') {
$ara = $_POST['query'];
$query .= "
WHERE
multiswitchler.urun_kodu LIKE '$ara%' OR
markalar.urun_markasi LIKE '$ara%' OR
multiswitch_tipi.multi_tipi LIKE '$ara%' OR
multiswitchler.input LIKE '$ara' OR
multiswitchler.output LIKE '$ara' OR
multiswitchler.birim_fiyati LIKE '$ara%' OR
multiswitchler.iskonto LIKE '$ara' OR
parabirimi.para_birimi LIKE '$ara' OR
multiswitchler.multiswitch_adi LIKE '$ara%'";
}
$query .= " ORDER BY multiswitchler.id ASC ";
$filter_query = $query . 'LIMIT '.$start.', '.$limit.'';
$statement = $connect->prepare($query);
$statement->execute();
$total_data = $statement->rowCount();
$statement = $connect->prepare($filter_query);
$statement->execute();
$result = $statement->fetchAll();
$total_filter_data = $statement->rowCount();The server-side code at that link is insecure and has no error handling. It is also doing pagination in the most inefficient way, by selecting all the columns and all the rows of data just to get a count of the number of total rows. As already mentioned, the query to get the total number of rows should use SELECT COUNT(*) …
The main point of a prepared query is to separate the data from the sql syntax, so that any sql special characters in the data cannot break or alter the sql syntax, which is how sql injection is accomplished. Sql injection in a SELECT query would allow a hacker to append a UNION SELECT … query to your existing query to get and display the contents of any of your database tables, such as a user table. Do you really want someone to be able to see all of your user’s data?
A prepared query involves putting a simple ? place-holder into the sql query statement for each data value, preparing the query, then supplying the data values when the query is executed. When using wild-card match characters - % and _, these are added to the data values, not to the sql query statement.
Now the bad news. The mysqli prepared query programming interface is overly complicated and inconsistent, requiring you to learn two completely different usages for a non-papered and a prepared query. Even fetching data from a prepared query is totally different from what you are expecting. If you however switch to use the much simpler and more consistent PDO extension, you can treat the result from a non-prepared and a prepared query in exactly the same way and fetching data works like you would expect it to.
Thank you for the explanation
I can’t fully understand because I’m a novice
I’m trying to do something for myself amateurly
My other codes are encoded as Mysqli
It is very difficult for me to switch to PDO
I need a paging with paging and searching with Ajax
I can not write from the beginning
I have to use it ready
Is there an example that you can suggest?
Most of the code/examples posted on the web don’t directly get to the point, or are misusing things. Here’s a what pagination should be doing -
Pagination involves two SELECT queries.
The two queries need the same FROM/JOIN/WHERE/HAVING terms. If you build this common part of the query in a php variable, you can use it when building the two actual queries. You would also build the corresponding common prepared query input parameters in an array.
The first query is to get the number of matching rows. The number of matching rows and the number of rows per page setting are used to calculate the number of pages. The number of pages is used to test/limit the requested page number and is used when building the pagination links. This query uses SELECT COUNT(*), followed by the common part of the query.
The second query is to get the requested page of data. It would instead SELECT the list of columns you want, followed by the common part of the query, plus any ORDER BY term, and a LIMIT offset, row_count term. The offset value is calculated from the requested page number and the number of rows per page setting - (requested page number - 1) * number of rows per page. The row_count value is the number of rows per page setting. The offset and row_count values would be supplied via prepared query place-holders and would be added to the end of the array of prepared query input parameters.
All you have to do is execute those queries and fetch the data from them. If you are already using the mysqli extension, you should be able to do this without our help.
Note: a LIKE … comparison in a query, without any wild-card characters, is the same as an equal = comparison.
I’m trying to do something amateurish
I have to be a professional to do what you say
I don’t know that much
It would be more correct to proceed with what I understand step by step.
1st.
For the total row
$top_page = ceil(
intval(
current(
$mysqli->query("SELECT COUNT(*) FROM multiswitchler")->fetch_row()
)
)
);
2nd.
To list the content
$result = $mysqli->query("SELECT
multiswitchler.id,
multiswitchler.urun_kodu,
multiswitchler.multiswitch_markasi,
multiswitchler.multiswitch_tipi,
multiswitchler.input,
multiswitchler.output,
multiswitchler.birim_fiyati,
multiswitchler.iskonto,
multiswitchler.multiswitch_para_birimi,
multiswitchler.multiswitch_adi,
multiswitchler.stok,
multiswitch_tipi.multi_tipi,
parabirimi.para_birimi,
markalar.urun_markasi
FROM multiswitchler
INNER JOIN parabirimi ON multiswitchler.multiswitch_para_birimi = parabirimi.id
INNER JOIN multiswitch_tipi ON multiswitchler.multiswitch_tipi = multiswitch_tipi.id
LEFT JOIN markalar ON multiswitchler.multiswitch_markasi = markalar.id
ORDER BY multiswitchler.id ASC LIMIT ".$start_from.", ".$limit);
It works fine so far (even if encoding is wrong)
3rd.
For content search
$result = $mysqli->query("SELECT
multiswitchler.id,
multiswitchler.urun_kodu,
multiswitchler.multiswitch_markasi,
multiswitchler.multiswitch_tipi,
multiswitchler.input,
multiswitchler.output,
multiswitchler.birim_fiyati,
multiswitchler.iskonto,
multiswitchler.multiswitch_para_birimi,
multiswitchler.multiswitch_adi,
multiswitchler.stok,
multiswitch_tipi.multi_tipi,
parabirimi.para_birimi,
markalar.urun_markasi
FROM multiswitchler
INNER JOIN parabirimi ON multiswitchler.multiswitch_para_birimi = parabirimi.id
INNER JOIN multiswitch_tipi ON multiswitchler.multiswitch_tipi = multiswitch_tipi.id
INNER JOIN markalar ON multiswitchler.multiswitch_markasi = markalar.id WHERE
multiswitchler.urun_kodu LIKE '$ara%' OR
markalar.urun_markasi LIKE '$ara%' OR
multiswitch_tipi.multi_tipi LIKE '$ara%' OR
multiswitchler.input LIKE '$ara' OR
multiswitchler.output LIKE '$ara' OR
multiswitchler.birim_fiyati LIKE '$ara%' OR
multiswitchler.iskonto LIKE '$ara' OR
parabirimi.para_birimi LIKE '$ara' OR
multiswitchler.multiswitch_adi LIKE '$ara%'
ORDER BY multiswitchler.id ASC LIMIT ".$start_from.", ".$limit);
The problem is here
How to get the number of search results?