How to use ajax in jquery to retrieve information from a database?

FairyBusiness
FairyBusiness used Ask the Experts™
on
Hi, I would like to retrieve information from my database using ajax in jquery.  I googled this but I have not found a tutorial for exactly what I am trying to do.  (also, many of them had asp.net or C# in them, which I am not familiar with yet)

I know how I would normally retrieve info from the database with php:  (example)

      $query = "SELECT name FROM submenus WHERE id=" . $id;
      $result = mysql_query($query, $conn);
      confirm_query($result);
    while ($row = mysql_fetch_assoc($result)){
                        $name = $row['name'];
    }

but if you want this to change each time according to an item being clicked the page would have to reload.  I would like to display information (details) next to an image (according to the image that is clicked) without the page reloading.

Can anyone tell me a real SIMPLE way of retrieving data from a database or know of a good tutorial for this (jquery/ajax/php)?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
leakim971Multitechnician
Top Expert 2014

Commented:
With jQuery :

$(document).ready(function() {

  $("item-being-clicked-selector").click(function(e) {
        // e.preventDefault(); // uncomment this if the item is a link/anchor
        $.getJSON("/path/to/page.php", {"id":$(this).attr("id) }, function(data) {
              $("put-data-here-selector").html(data); // put-data-here-selector is a div, a span, a cell, ...
        });
  });

});

Open in new window


page.php something like:

<?PHP

$name = "error";

if( isset($_GET["id"]) ) {

  $id = $_GET["id"];
  $query = "SELECT name FROM submenus WHERE id=" . $id;
      $result = mysql_query($query, $conn);
      confirm_query($result);
    if($row = mysql_fetch_assoc($result)){
       $name = $row['name'];
    }
}
echo $name;
?>

Open in new window

Author

Commented:
I can I make a call to my library?  can I specify which function?  or must it be a page unto itself?

Author

Commented:
also, did you mean to only have one quote?

.attr("id)
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

leakim971Multitechnician
Top Expert 2014

Commented:
You ajax page need to ONLY RETURN the data asked and not a full page, so no HTML or something else, only data.
In the page.php provide we return only the name not something like :

<html><head></head><body>
FairyBusiness
</body></html>

Open in new window



Else you can use what you want (the library you want to do the query or any other work)
leakim971Multitechnician
Top Expert 2014

Commented:
>also, did you mean to only have one quote?

Typo

Author

Commented:
ok, can you give me an example of how to specify which function from the library to use in jquery/ajax?

example: function  get_info()
leakim971Multitechnician
Top Expert 2014

Commented:
Line 5 we call the php page with ajax : $.getJSON
http://api.jquery.com/jQuery.getJSON/

Author

Commented:
ok but how will it know which function to run if there are many functions on the page??
leakim971Multitechnician
Top Expert 2014

Commented:
You ajax page need to ONLY RETURN the data asked and not a full page, so no HTML or something else, only data.

Author

Commented:
...... not really understanding this part. .. ..

but I put the link and the div to be written in here:

<a id="lala" href="">Hello</a>
<div id="stuff"></div>

http://gowiththemaster.com/tleithoff/index.php?id=1&type=1blurb&table=submenus

but when I click on the link nothing happens.  I put the jquery function in my action.js and I call to page.php  (which has many functions so I dont know how it would know, which function to run). and I want it to use the details() function

It did not work though.
leakim971Multitechnician
Top Expert 2014

Commented:
$("#lala").click(function(e) {

and not :

$("lala").click(function(e) {

Author

Commented:
I changed it but still nothing.  I dont think it knows which function to run from page.php
leakim971Multitechnician
Top Expert 2014

Commented:
You need to write : page.php

This page query your database from a query based on the ID (lala ????) and RETURN ONLY the name for "lala" ID
Check my previous post to see an example of page.php

Author

Commented:
ok i changed the id to #1, because I know i have an id of 1 in my table. but still nothing

I dont want it to get the id from the url though, just from what it being clicked
leakim971Multitechnician
Top Expert 2014

Commented:
still get a 404 error for page.php...
Did you create it this page? Check my previous post to see an example of page.php
leakim971Multitechnician
Top Expert 2014

Commented:
The right path :
http://gowiththemaster.com/tleithoff/includes/page.php

The current path used by getJSON
http://gowiththemaster.com/includes/page.php

So we update it :
$("#1").click(function(e) {
    e.preventDefault(); // uncomment this if the item is a link/anchor
    $.getJSON("/tleithoff/includes/page.php", {"id":$(this).attr("id") }, function(data) {
        $("#stuff").html(data); // put-data-here-selector is a div, a span, a cell, ...
    });
});

Open in new window

Author

Commented:
changed it, but still nothing.  I really dont think it knows which function to look for. . .
leakim971Multitechnician
Top Expert 2014

Commented:
Ok it run fine but the function return nothing, are you sure you have data for : SELECT name FROM submenus WHERE id=1
leakim971Multitechnician
Top Expert 2014

Commented:
perhaps page.php is not complete, did you add the connexion database part and so on ?

<<  know how I would normally retrieve info from the database with php:  (example) >>

Author

Commented:
i changed my function to this:

function details() {
	global $conn;
	$id = 1;
	$query = "SELECT blurb FROM submenus WHERE id=" . $id;
		  $result = mysql_query($query, $conn);
		  confirm_query($result);
	while($row = mysql_fetch_assoc($result)){
		   $name = $row['blurb'];
		}
	echo $name;
}

Open in new window


and I echo the function.  it works
leakim971Multitechnician
Top Expert 2014

Commented:
if you want to be sure it work, just replace the content of page.php by the following and only the following :
<?PHP

echo "FairyBusiness";

?>

Open in new window

Author

Commented:
uhh I did that but it changed my whole page to just say Fair Business!!!  I wanted it to write in the div. . .
leakim971Multitechnician
Top Expert 2014

Commented:
You modify something else than the previous page, don't know why.
It work previously fine (but no result from the query or an error)

Author

Commented:
i dont know what you are talking about it never worked before because it didnt know which function to run on the page.php.  Now all page.php says is FairyBusiness so that is what my page says!

I guess it is clicking my link automatically.  but i did not change anything other then what you told me to change

<article id="page_1">
	<div class="wrapper" id="page1">
		<div class="prefix_2">
		<div class="grid_20 grid_box">
			<div class="box">
				<div class="text">
					<h2><?php echo get_data('header'); ?></h2>
					<div class="pad_bot2">
					<div class="scroll-pane scroll scroll2">
						<?php echo get_data('content'); ?>
					<a id="1" href="">Hello</a>
					<div id="stuff"></div>
					</div>
					</div>
				</div>
			</div>	
		</div>
		</div>
	</div>
</article>

Open in new window

Author

Commented:
ok I made a separate php file named finishes and I put the php code there

Author

Commented:
finishes.php

include 'includes/library.php';
	global $conn;
	//$id = 1;
	$query = "SELECT blurb FROM submenus WHERE id=" . $id;
		  $result = mysql_query($query, $conn);
		  confirm_query($result);
	while($row = mysql_fetch_assoc($result)){
		   $name = $row['blurb'];
		}
	echo $name;

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
>ok I made a separate php file named finishes and I put the php code there
Read again : ID:35779496

This is what the page currently return
<br />
<b>Warning</b>:  include(includes/library.php) [<a href='function.include'>function.include</a>]: failed to open stream: No such file or directory in <b>/home/content/19/7229919/html/tleithoff/includes/finishes.php</b> on line <b>3</b><br />
<br />
<b>Warning</b>:  include() [<a href='function.include'>function.include</a>]: Failed opening 'includes/library.php' for inclusion (include_path='.:/usr/local/php5/lib/php') in <b>/home/content/19/7229919/html/tleithoff/includes/finishes.php</b> on line <b>3</b><br />
<br />
<b>Warning</b>:  mysql_query(): supplied argument is not a valid MySQL-Link resource in <b>/home/content/19/7229919/html/tleithoff/includes/finishes.php</b> on line <b>7</b><br />
<br />
<b>Fatal error</b>:  Call to undefined function  confirm_query() in <b>/home/content/19/7229919/html/tleithoff/includes/finishes.php</b> on line <b>8</b><br />

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
Put ONLY the following in finishes.php :


<?PHP

echo "FairyBusiness";

?>

Open in new window

Author

Commented:
I forgot they were in the same directory, now is should work except that it does not have a value for $id
leakim971Multitechnician
Top Expert 2014

Commented:
Currently : Query failed: 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 '' at line 1

Author

Commented:
I changed it to fairybusiness. it had that error message before bc there was nothing for $id
leakim971Multitechnician
Top Expert 2014

Commented:
A small update :

<?PHP

echo "[\"name\":\"FairyBusiness\"]";

?>

Open in new window


And use :
$("#1").click(function(e) {
    e.preventDefault(); // uncomment this if the item is a link/anchor
    $.getJSON("/tleithoff/includes/page.php", {"id":$(this).attr("id") }, function(data) {
        $("#stuff").html(data.name); // put-data-here-selector is a div, a span, a cell, ...
    });
});

Open in new window

name.jpg
leakim971Multitechnician
Top Expert 2014

Commented:
Sorry finishes.php :


<?PHP

echo "{\"name\":\"FairyBusiness\"}";

?>

Open in new window

Author

Commented:
how did you get finishes.php?id=1

but I clicked it and nothing still
leakim971Multitechnician
Top Expert 2014

Commented:
>how did you get finishes.php?id=1

Yes, because it return what you can see in the pic ID:35780181
It's not a JSON object
That's why I asked you to modify/update finishes.php with the code ID:35780197

Author

Commented:
ok, yes it works!!

I dont understand the code that its in. . .

how do I get it to do this though:

include 'includes/library.php';
	global $conn;
	//$id = 1;
	$query = "SELECT blurb FROM submenus WHERE id=" . $id;
		  $result = mysql_query($query, $conn);
		  confirm_query($result);
	while($row = mysql_fetch_assoc($result)){
		   $name = $row['blurb'];
		}
	echo $name;

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
Yes, it's a basic test to handle the way ajax work.


Clipboard06.jpg

Author

Commented:
ok I tried this but no luck:

include 'includes/library.php';
	global $conn;
	//$id = 1;
	$query = "SELECT blurb FROM submenus WHERE id=" . $id;
		  $result = mysql_query($query, $conn);
		  confirm_query($result);
	while($row = mysql_fetch_assoc($result)){
		   $name = $row['blurb'];
		}
	echo $name;

Open in new window

Multitechnician
Top Expert 2014
Commented:
Try this :


<?PHP

include 'includes/library.php';
global $conn;
//$id = 1;
$query = "SELECT blurb FROM submenus WHERE id=" . $id;
$result = mysql_query($query, $conn);
confirm_query($result);
if($row = mysql_fetch_assoc($result)){
  $name = $row['blurb'];
}
echo "{\"name\":\"" . $name . "\"}";

?>

Open in new window

Author

Commented:
nope, didnt work.  Is the id of the link transfering to the $id of php code??
leakim971Multitechnician
Top Expert 2014

Commented:
>Is the id of the link transfering to the $id of php code??
Yes, id = 1 well transmitted to php code (the page finishes.php)

>nope, didnt work.
Yes, here the current error (it look like you're using the wrong path to your library POV finishes.php) :
<br />
<b>Warning</b>:  include(includes/library.php) [<a href='function.include'>function.include</a>]: failed to open stream: No such file or directory in <b>/home/content/19/7229919/html/tleithoff/includes/finishes.php</b> on line <b>3</b><br />
<br />
<b>Warning</b>:  include() [<a href='function.include'>function.include</a>]: Failed opening 'includes/library.php' for inclusion (include_path='.:/usr/local/php5/lib/php') in <b>/home/content/19/7229919/html/tleithoff/includes/finishes.php</b> on line <b>3</b><br />
<br />
<b>Warning</b>:  mysql_query(): supplied argument is not a valid MySQL-Link resource in <b>/home/content/19/7229919/html/tleithoff/includes/finishes.php</b> on line <b>7</b><br />
<br />
<b>Fatal error</b>:  Call to undefined function  confirm_query() in <b>/home/content/19/7229919/html/tleithoff/includes/finishes.php</b> on line <b>8</b><br />

Open in new window

Author

Commented:
oh, yeah i forgot about that path

but still did not work

leakim971Multitechnician
Top Expert 2014

Commented:
Query failed: 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 '' at line 1

Author

Commented:
yes i know, thats why I am thinking the $id is not making it


<?PHP

include 'library.php';
global $conn;
//$id = 1;
$query = "SELECT blurb FROM submenus WHERE id=" . $id;
$result = mysql_query($query, $conn);
confirm_query($result);
var_dump($query);
if($row = mysql_fetch_assoc($result)){
  $name = $row['blurb'];
}
echo "{\"name\":\"" . $name . "\"}";

?>

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
To be sure try to replace line 6 with :
$query = "SELECT blurb FROM submenus WHERE id=1";

Open in new window

Author

Commented:
done
leakim971Multitechnician
Top Expert 2014
Commented:
ok it work, remove line 9 and read again ID:35779496

add : $id = $_GET["id"]; // yes this is the missing part...

Author

Commented:
I did this, but it did not work:

<?PHP

include 'library.php';
global $conn;
if( isset($_GET["id"]) ) {
	$id = $_GET["id"];
	$query = "SELECT blurb FROM submenus WHERE id=" . $id;
	$result = mysql_query($query, $conn);
	confirm_query($result);
	var_dump($query);
	if($row = mysql_fetch_assoc($result)){
	  $name = $row['blurb'];
	}
	echo "{\"name\":\"" . $name . "\"}";
}
?>

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
remove line 9 and read again ID:35779496
leakim971Multitechnician
Top Expert 2014

Commented:
now line 19 in your last code

Author

Commented:
I did not know the var_dump counted. but yes it works!! finally!!

Author

Commented:
thanks!
leakim971Multitechnician
Top Expert 2014

Commented:
>I did not know the var_dump counted

it count because it return something to the browser
You MUS return ONLY data or embed anything you want to send to client side as data :

echo '{"name":"' . $name . '", "vardump":"' . var_dump($query) . '"}';

so you can read it client side with : data.vardump

Author

Commented:
ok, thanks again!
leakim971Multitechnician
Top Expert 2014

Commented:
You're welcome! Thanks for the points!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial