list records in custom page using order by statement

mansour · Post by **mansour** » Thu Aug 28, 2014 1:06 pm

Hi,
i have a table named "appointments". i want to list appointments in a custom page and group records by a field(visithour).
this is my appointments table sql:
CREATE TABLE IF NOT EXISTS appointments (
id int(11) NOT NULL AUTO_INCREMENT,
TreatmentID int(11) NOT NULL,
VisitDay date NOT NULL,
VisitHour varchar(10) COLLATE utf8_unicode_ci NOT NULL,
VisitLength int(3) NOT NULL DEFAULT '1',
patient int(11) NOT NULL,
LastSession enum('Y','N') COLLATE utf8_unicode_ci DEFAULT 'N',
Treatment enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
note text COLLATE utf8_unicode_ci,
adminuser int(11) DEFAULT NULL,
SendSMS enum('Y','N') COLLATE utf8_unicode_ci DEFAULT 'N',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT AUTO_INCREMENT=21619 ;

this is my code:

<div class="panel panel-default">
<div class="panel-heading">appointmens list</div>
<div class="panel-body">
<?php
$sql = "SELECT " .
"patients.FullName AS FullName ," .
"appointments.VisitHour AS VisitHour" .
" FROM appointments JOIN patients ON (appointments.patient = patients.PatientID)" .
" WHERE " .
"appointments.VisitDay = '".$_GET["visitdate"]."' GROUP BY VisitHour";
echo $dbhelper->ExecuteHtml($sql, array("fieldcaption" => TRUE, "tablename" => array("appointments", "patients")));
?>
</div>
</div>

what's wrong with my code?
thank you
mansour

mobhar · Post by **mobhar** » Thu Aug 28, 2014 2:34 pm

Have you declared the database class helper? See the demo project for your reference, assume the database name is "demo":

<?php
$dbhelper = new cdemo_db(); // <-- c<databasename>_db()
?>

mansour · Post by **mansour** » Thu Aug 28, 2014 4:57 pm

Hi,
yes, i used the following code at the beginning of my code:
$dbhelper = new cclinic_db();

i can list records but i can't group them by visithour ?!!

Thank You
Mansour

mobhar · Post by **mobhar** » Thu Aug 28, 2014 5:14 pm

Post the "patients" table schema.

mansour · Post by **mansour** » Thu Aug 28, 2014 5:27 pm

Hi,
thank you for your reply.
CREATE TABLE IF NOT EXISTS patients (
PatientID int(11) NOT NULL AUTO_INCREMENT,
gender varchar(1) COLLATE utf8_unicode_ci NOT NULL,
BirthDate date DEFAULT '1360-00-00',
FullName varchar(255) COLLATE utf8_unicode_ci NOT NULL,
ForeignP enum('Y','N') COLLATE utf8_unicode_ci DEFAULT 'N',
NID varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
Prefix varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
EngTitle varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
telephone varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
mobile varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
address text COLLATE utf8_unicode_ci,
EmailAddress varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
PatientGroup varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'A',
AllowMsg enum('Y','N') COLLATE utf8_unicode_ci DEFAULT 'Y',
AllowFollowUp enum('Y','N') COLLATE utf8_unicode_ci DEFAULT 'Y',
PRIMARY KEY (PatientID),
UNIQUE KEY NID (NID) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT AUTO_INCREMENT=1729 ;

mansour

mobhar · Post by **mobhar** » Thu Aug 28, 2014 6:48 pm

Make sure the variable get "visitdate" contains the correct value.