Hi everyone.
I'm having a display issue with a query. All column rows are displayed, except: "CONCAT(NameLast, ', ', NameFirst) As Beneficiary". Can you see anything i'm doing wrong? Is there a problem because i'm using two functions 'CONCAT and SUM' in the same line? Also. I only get 1 row displayed, but when i look in the db, i have entered 2 rows.
$result = mysql_query("SELECT CONCAT(NameLast, ', ', NameFirst) As Beneficiary, IDNumber, CompanyName, CompanyBranch, CompanyReferenceNumber, Status, AssistanceStatus, AdministrationStatus, BilledStatus, NegotiationsStatus, DatabaseStatus, TransitFileStatus, CaseOwnerSelect, DateRecorded, DateClosed, Sum(AmountTypeOfCase+AmountSimpleCase+AmountTypeStandardCase+AmountTypeDifficultCase+AmountTypeExtraordinaryCase+AmountTypeComplexCase+AmountTypeAdministrationCosts+AmountTypeCommunicationCosts+AmountTypeBankCharges+AmountOtherCharge+AmountOtherCharge_1+AmountOtherCharge_2) as TotalCaseFees, year(DateRecorded) as Year FROM records")
PHP/MYSQL select query not displaying all data
Moderator: General Moderators
Re: PHP/MYSQL select query not displaying all data
Post up the whole shebang so we can see where the problem is, what comes after you select are you using a while() statement to get all the data?
Re: PHP/MYSQL select query not displaying all data
here's the whole shebang! I don't receive any error messages. All column data is displayed, but only for 1 record and the beneficiaryname is missing from being displayed as well. I am using the same concat statement on other pages and it works, so i'm not sure why it doesnt work on this one. plus the fact that only 1 row of data is being display has stumped me too!
I also need to employ pagination for the data to only display 30 records per page.
<?php
/* settings */
$db="db";
$link = mysql_connect('localhost', 'root', 'pw');
if (! $link) die(mysql_error());
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());
$result = mysql_query("SELECT CONCAT(NameLast, ', ', NameFirst) As Beneficiary, IDNumber, CompanyName, CompanyBranch, CompanyReferenceNumber, Status, AssistanceStatus, AdministrationStatus, BilledStatus, NegotiationsStatus, DatabaseStatus, TransitFileStatus, CaseOwnerSelect, DateRecorded, DateClosed, Sum(AmountTypeOfCase+AmountSimpleCase+AmountTypeStandardCase+AmountTypeDifficultCase+AmountTypeExtraordinaryCase+AmountTypeComplexCase+AmountTypeAdministrationCosts+AmountTypeCommunicationCosts+AmountTypeBankCharges+AmountOtherCharge+AmountOtherCharge_1+AmountOtherCharge_2) as TotalCaseFees, year(DateRecorded) as Year FROM records")
or die("SELECT Error: ".mysql_error());
//$num_rows = mysql_num_rows($result);
//print "There are $num_rows records.<br>";
$numofrows = mysql_num_rows($result);
?>
<?php
echo "<TABLE width=\"100%\" BORDER=\"0\" cellpadding=\"2\" cellspacing=\"2\">\n";
echo "<TR ><TD nowrap>Year Recorded</TD><TD nowrap>Case #</TD><TD nowrap>Status</TD><TD nowrap>Beneficiary</TD><TD nowrap>Principal</TD><TD nowrap>Principal Branch</TD><TD nowrap>Principal Ref</TD><TD nowrap>Assistance</TD><TD nowrap>Administration</TD><TD nowrap>Billed</TD><TD nowrap>Negotiation</TD><TD nowrap>Database</TD><TD nowrap>Transit</TD><TD nowrap>Case Fees</TD><TD nowrap>Ownership</TD><TD nowrap>Date Recorded</TD><TD nowrap>Date Closed</TD></TR>\n";
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR >\n";
} else { //if there isn't a remainder we will do the else
echo "<TR > \n";
}
echo "<TD nowrap>".$row['Year']."</TD></TD><TD nowrap><a href=\"edit.php?id=".$row['IDNumber']."\">".$row['IDNumber']."</a></TD><TD nowrap>".$row['Status']."</TD><TD nowrap>".$row['Beneficiary']."</TD><TD nowrap>".$row['CompanyName']."</TD><TD nowrap>".$row['CompanyBranch']."</TD><TD nowrap>".$row['CompanyReferenceNumber']."</TD><TD nowrap>".$row['AssistanceStatus']."</TD><TD nowrap>".$row['AdministrationStatus']."</TD><TD nowrap>".$row['BilledStatus']."</TD><TD nowrap>".$row['NegotiationsStatus']."</TD><TD nowrap>".$row['DatabaseStatus']."</TD><TD nowrap>".$row['TransitFileStatus']."</TD><TD nowrap>".$row['TotalCaseFees']."</TD><TD nowrap>".$row['CaseOwnerSelect']."</TD><TD nowrap>".$row['DateRecorded']."</TD><TD nowrap>".$row['DateClosed']."</TD>\n";
echo "</TR>\n";
}
//close the table
echo "</TABLE>\n";
?>
I also need to employ pagination for the data to only display 30 records per page.
<?php
/* settings */
$db="db";
$link = mysql_connect('localhost', 'root', 'pw');
if (! $link) die(mysql_error());
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());
$result = mysql_query("SELECT CONCAT(NameLast, ', ', NameFirst) As Beneficiary, IDNumber, CompanyName, CompanyBranch, CompanyReferenceNumber, Status, AssistanceStatus, AdministrationStatus, BilledStatus, NegotiationsStatus, DatabaseStatus, TransitFileStatus, CaseOwnerSelect, DateRecorded, DateClosed, Sum(AmountTypeOfCase+AmountSimpleCase+AmountTypeStandardCase+AmountTypeDifficultCase+AmountTypeExtraordinaryCase+AmountTypeComplexCase+AmountTypeAdministrationCosts+AmountTypeCommunicationCosts+AmountTypeBankCharges+AmountOtherCharge+AmountOtherCharge_1+AmountOtherCharge_2) as TotalCaseFees, year(DateRecorded) as Year FROM records")
or die("SELECT Error: ".mysql_error());
//$num_rows = mysql_num_rows($result);
//print "There are $num_rows records.<br>";
$numofrows = mysql_num_rows($result);
?>
<?php
echo "<TABLE width=\"100%\" BORDER=\"0\" cellpadding=\"2\" cellspacing=\"2\">\n";
echo "<TR ><TD nowrap>Year Recorded</TD><TD nowrap>Case #</TD><TD nowrap>Status</TD><TD nowrap>Beneficiary</TD><TD nowrap>Principal</TD><TD nowrap>Principal Branch</TD><TD nowrap>Principal Ref</TD><TD nowrap>Assistance</TD><TD nowrap>Administration</TD><TD nowrap>Billed</TD><TD nowrap>Negotiation</TD><TD nowrap>Database</TD><TD nowrap>Transit</TD><TD nowrap>Case Fees</TD><TD nowrap>Ownership</TD><TD nowrap>Date Recorded</TD><TD nowrap>Date Closed</TD></TR>\n";
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR >\n";
} else { //if there isn't a remainder we will do the else
echo "<TR > \n";
}
echo "<TD nowrap>".$row['Year']."</TD></TD><TD nowrap><a href=\"edit.php?id=".$row['IDNumber']."\">".$row['IDNumber']."</a></TD><TD nowrap>".$row['Status']."</TD><TD nowrap>".$row['Beneficiary']."</TD><TD nowrap>".$row['CompanyName']."</TD><TD nowrap>".$row['CompanyBranch']."</TD><TD nowrap>".$row['CompanyReferenceNumber']."</TD><TD nowrap>".$row['AssistanceStatus']."</TD><TD nowrap>".$row['AdministrationStatus']."</TD><TD nowrap>".$row['BilledStatus']."</TD><TD nowrap>".$row['NegotiationsStatus']."</TD><TD nowrap>".$row['DatabaseStatus']."</TD><TD nowrap>".$row['TransitFileStatus']."</TD><TD nowrap>".$row['TotalCaseFees']."</TD><TD nowrap>".$row['CaseOwnerSelect']."</TD><TD nowrap>".$row['DateRecorded']."</TD><TD nowrap>".$row['DateClosed']."</TD>\n";
echo "</TR>\n";
}
//close the table
echo "</TABLE>\n";
?>
Re: PHP/MYSQL select query not displaying all data
are you sure you want those commas in the concat statement, Looks like you have an extra comma in there, shouldn't it be CONCAT(NameLast, NameFirst) but you have three commas in there.
EDIT: Please use the "PHP Code" button on the post page when posting up the code, just makes it easier to read. Thank you.
EDIT: Please use the "PHP Code" button on the post page when posting up the code, just makes it easier to read. Thank you.
Re: PHP/MYSQL select query not displaying all data
yes i want the extra comma. i want names displayed as
Smith, John
not
Smith John
OK. I figured out my issue with the records displaying. All records now display and so does the beneficiary name. it seems i was missing a groupby and/or an orderby statement.
the only thing i'm left with figuring out is how to limit 20 records per page. Anyone have any good references articles for me?
thx.
Smith, John
not
Smith John
OK. I figured out my issue with the records displaying. All records now display and so does the beneficiary name. it seems i was missing a groupby and/or an orderby statement.
the only thing i'm left with figuring out is how to limit 20 records per page. Anyone have any good references articles for me?
thx.
Last edited by cjkeane on Thu Feb 17, 2011 7:00 pm, edited 1 time in total.
Re: PHP/MYSQL select query not displaying all data
I get that part but you have CONCAT(NameLast, ',' ,NameFirst) wouldn't you just run that as CONCAT(NameLast, ',' NameFirst) Other than that I don't see why it's not running right. There are some pointless things in there and I think there are a few easier ways to go about doing what you're doing but it should work non the less.
Re: PHP/MYSQL select query not displaying all data
I removed the extra comma and the query failed.
-
litebearer
- Forum Contributor
- Posts: 194
- Joined: Sat Mar 27, 2004 5:54 am
Re: PHP/MYSQL select query not displaying all data
re: 20 per page - PAGINATION
Here it is using images; however, the same pagination class can be used for any type of data...
http://www.nstoia.com/sat/disp_pag/
source for the pagination class...
http://www.phpsense.com/
Here it is using images; however, the same pagination class can be used for any type of data...
http://www.nstoia.com/sat/disp_pag/
source for the pagination class...
http://www.phpsense.com/