PHP/MYSQL select query not displaying all data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

PHP/MYSQL select query not displaying all data

Post by cjkeane »

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")
danwguy
Forum Contributor
Posts: 256
Joined: Wed Nov 17, 2010 1:09 pm
Location: San Diego, CA

Re: PHP/MYSQL select query not displaying all data

Post by danwguy »

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?
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: PHP/MYSQL select query not displaying all data

Post by cjkeane »

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";
?>
danwguy
Forum Contributor
Posts: 256
Joined: Wed Nov 17, 2010 1:09 pm
Location: San Diego, CA

Re: PHP/MYSQL select query not displaying all data

Post by danwguy »

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.
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: PHP/MYSQL select query not displaying all data

Post by cjkeane »

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.
Last edited by cjkeane on Thu Feb 17, 2011 7:00 pm, edited 1 time in total.
danwguy
Forum Contributor
Posts: 256
Joined: Wed Nov 17, 2010 1:09 pm
Location: San Diego, CA

Re: PHP/MYSQL select query not displaying all data

Post by danwguy »

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.
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: PHP/MYSQL select query not displaying all data

Post by cjkeane »

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

Post by litebearer »

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/
Post Reply