Pulling data from multiple mySQL tables... NEED HELP please!

compcentral

Okay, pulling data from a mySQL database using the basic PHP example file after configuring server_processing.php works just fine, but how can I relate tables to each other to get the data that I want. Let me try to explain this a little better... Here are the three tables that I want to get information from:

1. Courses Table
- id
- teacherID
- hostDistrictID
- courseName
- courseType
- startDate
- endDate

2. Teachers table
- id
- lastName
- firstName
- title
- emailAddress

3. Districts table
- id
- name
- websiteURL

The "teacherID" column in the Courses table relates to the the "id" column in the Teachers table and the "hostDistrictID" column related to the "id" column of the Districts table. I know how I would normally link all of this information together to display it in a table, but how can I do the same using DataTables?

So, in the end I want the following fields to be displayed to the user:

Course Name, Course Type, Teacher Name (first last with an tag to link to thier email), Host District Name (linked to thier website URL), Start Date, and End Date.

fasted004

Please keep the discussion to DataTables. There are much better places to go for help with general Javascript, PHP, SQL etc.

compcentral

What do you mean? This is specific to DataTables. If I wasn't using DataTables, I would be able to accomplish what I need to do. I want to know how to pull data from multiple tables... specifically, how to set the $sTable and $aColumns variables in the server side processing script to accomplish this.

Gerardo

You need a DataSource. This datasource (usually a server side script) knows about these details, dataTables does not.

Hth,
Gerardo

compcentral

I have a server side script (based on the server_processing.php example provided). I want to know how to configure this script to accomplish what I want to do. It's hard to believe that no one here is capable of providing an answer to a simple question like this.

Gerardo

what is your query to gather the data you want?

You can solve this purely in SQL, or you can get each piece of info separately in SQL (so you need more columns in the table), and use bVisible: false to hide the columns you don't want and fnRender to combine the data.

Hth,
Gerardo

compcentral

Thank you... finally some information I can use... at least for joining the columns after they are received.

Normally, my query would be something like this:

"SELECT courses.id, courses.teacherID, courses.hostDistrictID, courses.courseName, courses.courseType, courses.startDate, courses.endDate, teachers.id, teachers.lastName, teachers.firstName, teachers.title, teachers.emailAddress, districts.id, districts.name, districts.websiteURL
FROM courses, teachers, districts
WHERE teachers.id = courses.teacherID AND districts.id = courses.hostDistrictID"

but I'm just not sure how to implement the WHERE clause without breaking the filtering that is done when someone uses the search feature of DataTables. So my question is: how do I modify the example "server_processing.php" script to accomplish this?

Gerardo

You can do that AND hide columns and use fnRender as needed.

OR you can do the data managing in SQL, like below, leading to less columns in dataTables

SELECT c.name, c.type, concat("", t.firstName, " ", t.lastName, ""), concat("

compcentral

Thanks for the advise, but if I use this, won't it break the filtering when it modifies the sql statement's where clause? How do I use that SQL statement in my processing script (partially shown below)? The commented out $aColumns and $sTable variables won't work. I'll give this another shot when I get a moment... might be a few days since I am rebuilding my web server right now, but

EDIT: code removed... updated in the next post.

The rest of the script is the same as the example server_processing.php file.

compcentral

OK.. I got the web server setup and created a test environment for this. Here is my server side code:

[code]
$aColumns = array( 'c.courseID', 'c.teacherID', 'c.districtID', 'c.name', 'c.courseType', 'c.startDate', 'c.endDate', 't.teacherID', 't.lastName', 't.firstName', 't.title', 't.email', 'd.districtID', 'd.shortName', 'd.websiteURL' );
$sIndexColumn = "c.courseID";
$sTable = "courses c, teachers t, districts d";
$sWhere = "WHERE c.teacherID = t.teacherID AND c.districtID = d.districtID";

/* Database connection information removed */

/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i

compcentral

The rest of the script is unchanged.

When I load the page, I get the following JSON response:

{"sEcho":3,"iTotalRecords":"3","iTotalDisplayRecords":"1","aaData":[[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]]}

I know there is data there. Any idea why I'm getting all null fields?

Oh, and the field names are all accurate. I don't get any errors... just null data.

compcentral

I revised the code more to use concat(), but still the same null responses. If I echo $sQuery using my revised code, the output is:

[code]
SELECT SQL_CALC_FOUND_ROWS c.name, c.courseType, concat( "", t.firstName, " ", t.lastName, "" ) fullname, concat( "", d.shortName, "" ) district, c.startDate, c.endDate
FROM courses c, teachers t, districts d
WHERE c.teacherID = t.teacherID AND c.districtID = d.districtID
[/code]

If I execute that query in phpmyadmin, I get the following response (which is correct):

name courseType fullname district startDate endDate
Test Course Math Joe Smith Website 02/02/2011 06/03/2011

However, the JSON response is:

{"sEcho":0,"iTotalRecords":"3","iTotalDisplayRecords":"1","aaData":[[null,null,null,null,null,null]]}

Gerardo

Check what happens when you construct the json.

Is the data in $rResult? What happens next?

compcentral

Ok.. after some more debugging, I discovered why the field values were null. I had to change this code:

[code]
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i

compcentral

So... here's what happens when I type "ma" into the search box...

The sql select statement becomes:

[code]
SELECT SQL_CALC_FOUND_ROWS c.name, c.courseType, concat( "", t.firstName, " ", t.lastName, "" ) fullname, concat( "", d.shortName, "" ) district, c.startDate, c.endDate FROM courses c, teachers t, districts d WHERE c.teacherID = t.teacherID AND c.districtID = d.districtID AND (c.name LIKE '%ma%' OR c.courseType LIKE '%ma%' OR concat( "", t.firstName, " ", t.lastName, "" ) fullname LIKE '%ma%' OR concat( "", d.shortName, "" ) district LIKE '%ma%' OR c.startDate LIKE '%ma%' OR c.endDate LIKE '%ma%' ) ORDER BY c.name asc LIMIT 0, 10
[/code]

I then get an error that states, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fullname LIKE '%ma%' OR concat( "", d.shortName, "<' at line 3" and of course a corresponding JSON error.

What should the SQL statement look like to enable the filtering/sorting to work properly? I've never tried using CONCAT and LIKE together like this before.

compcentral

Nevermind... found the problem. I removed the fieldnames from the concat function calls and it's working now!

Working code in case anyone has a similar problem:

[code]
<?php
$q1 = "'";
$q2 = '"';
$concat1 = "concat( ".$q2."".$q2.", t.firstName, ".$q2." ".$q2.", t.lastName, ".$q2."".$q2." )";
$concat2 = "concat( ".$q2."".$q2.", d.shortName, ".$q2."".$q2." )";
$aColumns = array( 'c.name', 'c.courseType', $concat1, $concat2, 'c.startDate', 'c.endDate');
$sIndexColumn = "c.courseID";
$sTable = "courses c, teachers t, districts d";
$sWhere = "WHERE c.teacherID = t.teacherID AND c.districtID = d.districtID";

/* Database connection information removed */

$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );
mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );


$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}


if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[/code]

KaM

compcentral
thank you so much .... you save a lot of my time ... thank you twice :)

Alawrence

May want to consider the use of a defined join instead of the assumed join to prevent data from being left out unless all the fields are required.

shelley

Where is the working code?????

pbagch200

compcentral - Thanks!!
Btw, did you try to add entries? I want to add an entry in the child table based on the already existing entry in the parent table. So when I click the add button in the form I want a drop down list of the existing parent entries from where I can choose one and then add an entry in the child table corresponding to it. I searched the forum but couldn't find a post relating to this.

pbagch200

Update: got the drop down working.