CODEWITHSUNDEEP
jquerymysqlajax
John R
John R

Reputation: 3036

AJAX, Join Table and Complex Query

I have two MySQL tables named 'nodes' and 'joinTable' like shown below. I need to make an AJAX/jQuery?/MySQL call based on one node ID (in first table) that returns the following and pushes or places data into two JavaScript arrays and some variables:

  1. returns 'type' and 'text' from Node table. These will be placed in a seperate variables along with id.
  2. SELECT to_ FROM joinTable WHERE from_='$id' and then... goes through the nodes table to get all the associated 'type' and 'text' for each of the '_from' and places or pushes these into a javascript array called 'pointsTo' along with the nodeID.
  3. SELECT from_ FROM joinTable WHERE to_='$id' and then... goes through the nodes table to get all the associated 'type' and 'text' for each of the '_to' and places or pushes these into a javascript array called 'pointsFrom' along with the nodeID.

nodes:

nodeID    type  text  
0           1   Dr. Joelson  
1           1   Ms. Appletree 
2           1   Mr. Miller  
3           1   Dr. Wilson  
4           0   Pediatrician  
5           0   Teacher  
6           0   Waiter  
...

joinTable:

recordID    from_   to_   weight    type    typeText  
0              0    4        1        1      isa  
1              1    5        4        1      isa  
2              2    6        3        1      isa  
...

This AJAX options are overwelming to me and I have not done a MySQL call this complex before. I would not mind learning more about a jQuery methodology that is applicable but am open to different approaches.

Edit: The server is running PHP.

Upvotes: 1

Views: 2211

Answers (1)

mikermcneil
mikermcneil

Reputation: 11271

You'll need to start off by choosing a server-side language like PHP to query your database and send back a JSON response to the client. On the other end, use a javascript framework like jQuery to make an ajax call to said PHP script on the server from your javascript code:

// Get your nodeId from the user
var id = 2;

$.post("/scripts/doTheQuery.php",{
  nodeId: id
},function (result) {
  // when query finishes
  // do stuff with result
  console.log(result);
}

As far as your SQL question: 1. SELECT type,text FROM nodes WHERE nodeID = {$nodeIDinQuestion}

I'll punt on the join queries and let you get that far first.

Upvotes: 1

Related Questions