some of my data in mysql shows when running Select *…
It adds / substracts correctly
but when echoing data through php the data is not visible, also it ignores data in calculations.
what are the reasons or trouble shooting steps to be taken
Nobody uses MySQL anymore. It is deprecated. Please upgrade to PDO or at the least MySQLi …
Also, we can not help you at all without seeing some of your code. You should at least show us the SELECT query that you are having issues with if you want us to help you!
Do you have php’s error_reporting set to E_ALL and display_errors set to ON, in the php.ini on your system, so that php will report and display all the errors it detects?
Do you have error handling for all the statements that can fail? For database statements that can fail - connection, query, prepare, and execute, the simplest way of adding error handling without needing to add logic at all the statements, is to use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings (see the above paragraph) to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.)
Is your php ‘business logic’, that knowns how to get/produce data, separate from your ‘presentation logic’, that knowns how to produce the output, so that any php errors, which will now include database statement errors (see the above paragraph), won’t be hidden in the ‘view source’ of the page?
Are you validating the result from each step and generating a user error message if a select query executes, without error, but doesn’t match any data?
I am using mysqli
to view data
$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);
while ($row_data= mysqli_fetch_assoc($result_data)) {
$bankid=$row_data['bank_id'];
$user=$row_data['u_id'];
$date=$row_data['trans_date'];
............
to add data
$query = "INSERT INTO bank (trans_date,Particulars,Deposit,bank_name,u_id)
VALUES ('$date', '$particular', '$amount','$bank','$cust_id')";
mysqli_query($conn,$query);
...............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>";
}