No erors reported, the data exists in the data base, when I login (mysql -u root -p), the data is visible, data can be used to add / sub update etc…
but when i use php the data is neither echoed nor added / sub update etc.
Well, to echo data from a query, you just use the data pulled in the query.
echo $row_data[“trans_date”]; Will display whatever is in the trans_date field…
( Normally, you would format the date to fit your area of the world in the format you prefer… )
Not sure what you are asking…
One thing you might want to try would be to add the following to the beginning of your module:
$debug=TRUE;
if($debug) {
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
}
The $debug variable could be set in your main module as a $_SESSION variable. But, you would be able to see all of the errors while you are in development mode and hide them when in production.
Another thing that I have found useful is to verify that you are generating the query that you think you are generating. Try adding die("$sql"); right after you build the $sql variable. When the program dies, you can copy and paste the query into your sql client or phpmyadmin to see if it is returning the result you expect.
I suspect that you may have a data type problem.
What I am saying is that some of the data which is in the database is being ignored.
This data (numbers) is taken into account when i open a terminal for mysql. But the same date is not being used when i try to query it.
the data base consists of 100 records of which php query is showing only 98. But when I see it through phpMyAdmin all 100 are visible!
Add the above line in your program. Copy it. Go to your phpmyadmin page live on your server.
enter the SQL and see what the results are. My guess is that you are NOT starting from 0 or have limited the number to 98. YOU must debug this as we do not have your database, nor the entire code. Nor do we want it. Just debug it.
Now, by “when you open a terminal”, is this a Wamp or Xammp server or a live server on the WWW? If in Wamp or Xamp, you open a browser window, go to localhost. There is will show you access to whatever you have installed and select phpmyadmin, then log into your database that way, select the database and table on the left side. Select SQL on the right side and enter whatever you copied from the die() function above. If you SSL into a command terminal into an online server, then, you can run the mysql system from that, but, seldom do programmers do it that way. It is so much easier to use the server’s control panel and just go to the phpmyadmin which is about the same as a local one.
I hope all this makes sense to you. Most likely it is a small error in how you create all of the query itself. Normally, if you want to see ALL of the data inside a table, you do not use a start or limit option. You would just use:
One more thing, perhaps the b_status is NOT NULL in two of the records… Good luck!
I am using Centos and using a terminal, this is what i did:
mysql -u root -p
selected my data base and first ran,
==>select * from bank;
this showed me all my data 100% of it, b_status verified as NULL.
Now assuming the record (which is not displaying in php) in question has an id 0f 78, if I run the following
==>select * from bank where id=78;
the record is displayed in my terminal.
With this i assume that the database is not corrupted, any suggestions to verify would be appreciated.
If I run a query in phpMyAdmin , the data is displayed.
In respect to the first half of your reply.
Now we come to php,
my query is as follows
$sql = "SELECT * FROM bank WHERE b_status IS NULL
ORDER BY trans_date ASC LIMIT $start_from, $per_page_record";
$result_data=mysqli_query($conn, $sql) or die ('Unable to execute query bank.php'.mysqli_error($conn));
.......This shows no errors
This however displays the data for the 77th and 79th record, but not the 78th record.
I have no issue with sharing my code if you think it is of help I will show it gladly.
I really appreciate the time from your side and would like to thank you for it.
You’re running a different query in the terminal to the one you’re running in your code. What do you get if you run your exact query in the terminal?
Your snippets of code and descriptions aren’t really telling us anything useful. We are not there with you and don’t know what you are seeing when you perform these actions, we don’t know what your data actually is, what the relevant code is, what it could be doing that could affect if a row is displayed or not, and what the resulting html output is that corresponds to the non-displayed/missing row.
Post an sql dump showing the 77-79th rows of data that you expect the query to match (I’m wondering if the trans_date is not in a format that sorts, so that the missing 78th row is somewhere else in the ordered output), post all the relevant code needed to reproduce the problem, and post the ‘view source’ from your bowser of the incorrect output.
Did you use the DIE() line I gave you:? You need to show us the EXACT query that you are running.
My guess is that the limit or offset is bad. You MUST find out what query string is being used in the PHP section first. Without knowing that we can not help you! Add the die($sql); line to your PHP code and tell us what it shows your for the true query you are running!
It show all relevant data, all required data is showing!
I reviewed your recent threads. You have a thread from December 2020 with this same issue - Data missing from php table You obviously have a reoccurring, fundamental, misunderstanding about either something in the data or in the php code. Until you post the information I requested in the reply above, no one can help you.
Try entering
show create table bank;
this will show the data types, attributes and default values.
It may be that you are dealing with data that is not null or defaults to null or some such thing.
Show us the query so we can see what is wrong.
sorry could not answer before, lockdown!
| bank | CREATE TABLE bank (
bank_id mediumint(9) NOT NULL AUTO_INCREMENT,
trans_date date DEFAULT NULL,
Particulars varchar(32) DEFAULT NULL,
Deposit decimal(12,2) DEFAULT NULL,
Withdraw decimal(12,2) DEFAULT NULL,
bank_name varchar(6) DEFAULT NULL,
u_id varchar(16) DEFAULT NULL,
b_status char(6) DEFAULT NULL,
PRIMARY KEY (bank_id)
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1 |
<div class="pagination">
<?php
$per_page_record = 26;
if (isset($_GET["page"])) {
$page = $_GET["page"];
$_SESSION['page']=$page;
} else {
$page=1;
}
$start_from = ($page-1) * $per_page_record;
$query = "SELECT COUNT(*) FROM bank";
$rs_result = mysqli_query($conn, $query);
$row = mysqli_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / $per_page_record);
$pagLink = "";
if($page>=2){
echo "<a href='bank.php?page=".($page-1)."'> Prev </a>"; }
for ($i=1; $i<=$total_pages; $i++) {
if ($i == $page) {
$pagLink .= "<a class = 'active' href='bank.php?page=" .$i."'>".$i." </a>";
} else {
$pagLink .= "<a href='bank.php?page=".$i."'> ".$i." </a>"; }
}
echo $pagLink;
if($page<$total_pages){
echo "<a href='bank.php?page=".($page+1)."'> Next </a>";
}
$sql = "SELECT * FROM bank WHERE b_status IS NULL
ORDER BY trans_date ASC LIMIT $start_from, $per_page_record";
$result_data=mysqli_query($conn, $sql) or die ('Unable to execute query bank.php'.mysqli_error($conn));
while ($row_data= mysqli_fetch_assoc($result_data)) {
$bankid=$row_data['bank_id'];
$user=$row_data['u_id'];
$date=$row_data['trans_date'];
$date=strtotime($date);
$sdate=date('d-m-Y', $date);
$spart=$row_data['Particulars'];
$deposit=$row_data["Deposit"];
$withdraw=$row_data["Withdraw"];
$dep_sum +=$deposit;
$with_sum +=$withdraw;
if(!(empty($user))) {
$sql = "SELECT * FROM user_details WHERE u_id=$user";
$result_user=mysqli_query($conn, $sql);
while ($row_user= mysqli_fetch_assoc($result_user)) {
$org=$row_user['org'];
}
} else {
$org='';
}
if ($page=1) {
$balance=$dep_sum-$with_sum;
$page=$page+1;
echo "<tr>";
echo "<td class='tg-0pky'>" .$bankid. "</td>";
echo "<td class='tg-0pky'>" .$sdate. "</td>";
echo "<td class='tg-0pky'>" .$org .' / ' .$spart. "</td>";
echo "<td class='tg-dvpl'>" .number_format($deposit, 2). "</td>";
echo "<td class='tg-dvpl'>" .number_format($withdraw, 2). "</td>";
}
if($page>1){
$query = " SELECT sum(deposit)-sum(withdraw) AS total FROM
(SELECT deposit,withdraw FROM bank ORDER BY trans_date ASC LIMIT $start_from) AS query";
$result = mysqli_query ($conn, $query);
while ($row = mysqli_fetch_array($result)) {
$total=$row['total'];
$balance=$total+$dep_sum-$with_sum;
}
} else {
$total = 0;
}
echo "<td class='tg-dvpl'>" .number_format($balance,2). "</td>";
echo "<td class='tg-dvpl'><form class='new' action= '' method='GET'>
<input type='hidden' name='id_delete' value=" .$row_data['bank_id'].">
<input type='submit' class='cancelbtn1' name='delete' value='DEL''>
<input type='hidden' name='id_edit' value=" .$row_data['bank_id'].">
<input type='submit' class='blue-thin' name='edit' value='EDIT''>
</form></td>";
}All the queries for the bank table must use the same WHERE … clause, so that they match and operate on the same set of data.
The common part of the query, starting with the FROM term, any JOINs, any WHERE clause, any GROUP BY clause, and any HAVING clause should be built once, in a php variable, then that variable should be used when building any sql query that needs it.
You also have a logical mistake in the above. One = is assigning 1 to $page, then testing the result of the assignment, which will always be true. Two == is a comparison operator.
All the logic and the query to get the starting total up to the requested page of data, doesn’t even belong inside the loop that’s outputting the data. It belongs before the query that gets the requested page of data.
I recommend that you first define what you need each section of code to do. What inputs, if any, there are, what processing you are gong to do based on those inputs, and what result you are going to produce or output. Include these definitions as comments in the code, then design, write, test, and debug the code/query(ies) needed for each section, one section as a time.
Also, don’t copy variables to other variables for nothing. This is just a waste of typing. Use the original variables the data is in.
sorry for my late reply, i checked this before itself!