Javascript, AJAX, PHP & MYSQL

Scottehh4 · August 7, 2015, 11:25am

Hey all,

Literally joined this forum in the last 10 mins as stackoverflow is full of jumped up a-holes that think they’re better than everyone else and i actually never get my questions answered. Hopefully, i’ll have a better experience here!

A bit of background of what i’m about, and what i’m trying to achieve: I’m 21 and a Junior Front End Software Developer (Technically). I work for a company called CodeMonkeyStudios which is an awesome small business. My boss saw my CV and decided to give me a go, even with my lack of experience. I’m currently building a front end Dispatch system for a local private ambulance service. All was going well, using my PHP, HTML and CSS skills, I made the majority of the site work, but then I moved onto the “Create Job” page. And what a nightmare this has been. I thought it would be just a form, but no, our customer wanted all sorts of fancy bits adding, like autocomplete on certain fields, and auto population of a hidden Div that will appear once a patients name is entered. So, I had to learn Javascript, which i already knew basics about, but also had to learn jQuery, which was new to me. But yeah, thats the background.

What this is about: Basically, I’ve done the autocomplete stuff, works a treat, and i’m rather proud of myself . Now i’m trying to tackle the whole “dynamic database results been fed into a hidden div” stuff. And, as I thought, this isn’t as simple as including a file that queries the database and adds “WHERE column = ‘input value’;” to the end of the query. Oh hell no. Then I learnt more and more about PHP and JS, and found that PHP is server side, JS is client side, which I knew, but didn’t really understand how it worked etc. So that was that idea out of the window. I’m now in the process of creating an AJAX request that will open the PHP file containing the query… and that’s where I need some assistance. So, i think I have created the AJAX request using this code:

var xmlhttp;
if(window.XMLHttpRequest) {
	xmlhttp = new XMLHttpRequest();
} else {
	xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}

function match() {
	var patient = document.getElementById("patient_search").value;
	xmlhttp.open("GET","/sql_queries/patients.php",true);
	xmlhttp.send;
}

What I need the AJAX to also do (or the JS), is pass the patient var over to the PHP file to include in the query.

For example, I would IDEALLY like it to look something like the below. Say for this example the JS patients var was passed and saved as $patients…

$query = "SELECT * FROM patients WHERE account_id = ".$_COOKIE['acc_id']." AND patient_surname = '".$patients."';";

How can I achieve this? Or something similar? Is it possible? If not, then why?

Thank you in advance for anyone that has any input!

EDIT: Just found the below… Which I am going to try and implement…

xmlhttp.open("GET",".sql_queries/patients.php?surname="+ patients"",true);
xmlhttp.send();

StarLion · August 7, 2015, 12:13pm

Welcome to SitePoint!

As you’re using GET, the thing you’ve posted in your EDIT is correct (though you’ve replaced a / with a . in there somehow ) - you’ll need to access it as $_GET[‘surname’] though.

I’m also going to make some general statements:

1: Learn prepared statements. Best way to do this is start out by learning [FPHP]PDO[/FPHP] instead of mysqli_. Especially as this is interacting with a potentially VERY sensitive database, SQL injection would be very, very bad.
2: You might want to look at jQuery as a next step in learning on the javascript side. imo, jquery is cleaner and easier than the default javascript ways of implementing AJAX calls. But that’s personal preference.

Scottehh4 · August 7, 2015, 12:41pm

Thanks Starlion, Yeah the ‘.’ was just a typo… My fault haha! I’ve accessed it a slightly different way…

This is my entire code, from the jQuery function that is initiated by an onClick event…

function show_div() {
	if(false == $("#patientContent").is(':visible')) {
		$('#patientContent').show(250);
		match();
	} else {
		$('#patientContent').hide(250);
	}
}

var xmlhttp;
if(window.XMLHttpRequest) {
	xmlhttp = new XMLHttpRequest();
} else {
	xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}

function match() {
	var name = document.getElementById("patient_search").value;
	var surname = name.substr(name.indexOf(' ') + 1);
	var test = "/sql_queries/patients.php?surname="+surname;
	xmlhttp.open("GET","/sql_queries/patients.php?surname="+patient,true);
	xmlhttp.send;
	alert(test);
}

And then to get the information back and add it to my statement… (this is within the /sql_queries/patients.php file)

$request_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$str = strstr($request_link, '=');
$last_name = substr($str, 1);

$acid = $_COOKIE['acc_id'];

$query = "SELECT * FROM patients WHERE account_id = ".$acid." AND patient_last_name = '".$last_name."';";

I’m not concerned about SQL injections at the moment, simply because this is an application what will only be accessible over an intranet network, not the general public. Literally around 10 people will use it, so if things start to go a bit haywire, we know where to point the finger! But thanks for the heads up!

I’m now having an issue where the onClick is firing, and i’m getting an alert with the URL in there, but it doesn’t open the hidden div… Hmm, i’ll post back shortly, need to debug!

Scottehh4 · August 7, 2015, 12:50pm

Partially figured the issue… I think…

In my match() function, i had the below:

var test = "/sql_queries/patients.php?surname="+surname;
xmlhttp.open("GET","/sql_queries/patients.php?surname="+patient,true);

Firstly, var test has surname="+surname; on the end, where as the xmlhttp.open has “+patient” instead… Changed both to surname as they should be. And I noticed a potential issue with my URL…

This is my new code:

var test = "https://amb.codemonkeystudios.co.uk/sql_queries/patients.php?surname="+surname;
	xmlhttp.open("GET","https://amb.codemonkeystudios.co.uk/sql_queries/patients.php?surname="+surname,true);

But still doesn’t open the “hidden div” and I don’t understand why… More debugging to come!

Scottehh4 · August 7, 2015, 1:08pm

Also, just tested $_GET[‘surname’] and wow, so much easier than my method!

Thanks!

StarLion · August 7, 2015, 1:09pm

probably because nowhere in your code that you’ve shown us do you call show_div() ?

Since you’ve got jquery already loaded into the page, i’d suggest looking at jquery’s own $.ajax() API

Scottehh4 · August 7, 2015, 1:11pm

show_div() is called via onClick of a button on the page:

<input type="button" id="confirm_name" class="confirm" value="Confirm Patient" onclick="show_div()" style="display: none;" name="input"/>

It has display:none as i have another function which this button replaces when the user starts typing into an input field… It’s very confusing!

I’ll have a look into the API, hopefully it will make my life 10x easier!

Thanks again!

Scottehh4 · August 7, 2015, 1:48pm

I’m finding this thread’s more just a way for me to get my thought process into words and help me think, i should start a blog!

New issue.

Got the AJAX working (I think), using:

function match() {
	var name = document.getElementById("patient_search").value;
	var surname = name.substr(name.indexOf(' ') + 1);
	$.ajax({
		url: "https://amb.codemonkeystudios.co.uk/sql_queries/patients.php?surname="+surname,
		context: document.body
	}).done(function() {
		$("#patientContent").show(250);
		alert("Complete");
	});
}

and then calling match(); onClick of the button. The hidden div is then displayed, but does not show the variables. So on each of the input fields, i’ve got: <?php echo $variable ?> (where $variable = the database fields) but the page doesn’t like this, stating: Notice: Undefined variable: forename in <path>\new_job.php on line 128.

I believe this is because i need to create functions to append the variables to the values right?

So for example something like this:

document.getElementById("inputName").value = "variable from database"..

Not entirely sure how this is going to work, guessing i’m going to need to pass the database variables back from the database into the javascript… Unless JS can work with PHP variables? No idea.

I’ll look into it.

StarLion · August 7, 2015, 2:08pm

So there’s a couple of different approaches to this.

1: Have PHP return pure data, use Javascript to style it and display it. (generally the preferred method)
2: Have PHP generate the entire contents of the target element, and then inject what it returns into the element with JS.

I dont know what $(“#patientContent”) looks like or contains atm, so it’s a little tricky to try and advise you.

As you’re using jQuery’s done function (i might recommend the success function instead, but regardless), take a look at the first two examples in the Examples section on the ajax() API page. It shows you how you can use what PHP spits out.

Lemon_Juice · August 7, 2015, 4:06pm

Also remember to escape any values coming from user input when you insert them into a URL - in this case it’s the surname. So your line should look like this:

url: "https://amb.codemonkeystudios.co.uk/sql_queries/patients.php?surname="+encodeURIComponent(surname),

Without escaping the value you might run into unexpected problems when the user enters certain special characters into the field. Even if surnames don’t contain special characters this is a good security measure.

SpacePhoenix · August 7, 2015, 5:26pm

$acid = $_COOKIE['acc_id'];

Is the value that you expect to receive from the cookie for acc_id always expected to be an integer? If it is do:

$acid = (int) $_COOKIE['acc_id'];

If the value is not an integer then the value for $acid will end up being 0

Scottehh4 · August 16, 2015, 4:39pm

Here’s one for you all…

My code has changed massively from the above as the required functionality has been changing. This is how i need this to work:

User enters “known information” into several fields.
This information is then passed to a database query within a php file via AJAX
The SQL Query result is encoded as JSON and passed back to the JS as
“this.responseText”
I then have to use “JSON.parse(this.responseText)” and i’m not sure why, as i thought it was already JSON from when the PHP encoded it… Weird.
Once it has been parsed as JSON again, i then pass it to a query that pulls out the objects from the JSON array and outputs them into a table using a plugin called DataTables (DataTables Link)
This should output the information in a pretty table so the user can click a row and it will select the corresponding patient and autofill all the information on the page for the user.

Point 5 is where everything grinds to a halt. See below code:

AJAX Call to PHP file (Point 4):
You can see the var “pass” that contains the JSON array from the PHP file, and you can see that it is been encoded again…

var vars = new XMLHttpRequest();
	vars.onload = function() {			
		document.getElementById("resultInsert").innerHTML = "";
		var pass = JSON.parse(this.responseText);
		outputTable(pass);
		document.getElementById("empty-message").innerHTML = pass;
	}

vars.open("GET", "https://amb.codemonkeystudios.co.uk/sql_queries/findPatient.php?forename="+forename+"&surname="+surname+"&dob="+dob+"&nhs="+nhs+"&alle="+allergies+"&markers="+markers+"&house="+house+"&street="+street+"&area="+area+"&town="+town+"&county="+county+"&postcode="+postcode, true);
vars.send();

This is the sent onto another function (Point 5):
I think the issue is where it states “ajax”: json, In the example that the plugin provides, they use a text file here containing the JSON array, where as i try to use the pass var that i had previously sent through, which did not work. I can’t be having my code making new files every time a user searches for a patient. (Link further down the page as new users can only have 2 links per post - Annoying.)

function outputTable(json) {
	$("#logTable").DataTable( {
		"ajax": json,
		"columns": [
		{"patient_first_name": "forename"},
		{"patient_last_name": "surname"},
		{"patient_dob": "dob"},
		{"patient_nhs_no": "nhs"},
		{"patient_allergies": "allergies"},
		{"patient_markers": "markers"},
		{"patient_house_name": "house"},
		{"patient_street_name": "street"},
		{"patient_area": "area"},
		{"patient_town": "town"},
		{"patient_county": "county"},
		{"patient_postcode": "postcode"},
		]
	});
}

I think i have an idea as to why its not working, but not sure how to resolve.

The JSON array in the example here starts with {“data”: [ and ends with ]} with the JSON array data in between the “[” and “]”… Mine does not have this, nor do i know how to tell my PHP file to give it this format. The json_encode function gives it the below format (I’ve split the lines for readability.):

{"id":"10001",
"account_id":"1",
"patient_first_name":"Scott",
"patient_last_name":"ehh",
"patient_dob":"1234-12-12",
"patient_nhs_no":"123456789012"}
{"id":"10002",
"account_id":"1",
"patient_first_name":"Test",
"patient_last_name":"Patient",
"patient_dob":"1975-09-14",
"patient_nhs_no":"210987654321"}

Any ideas? I’m stumped

system · November 15, 2015, 11:42pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.