Start Free Trial

asked on

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

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)?

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

ASKER

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

ASKER

also, did you mean to only have one quote?

.attr("id)

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)

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

Typo

ASKER

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()

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

ASKER

ok but how will it know which function to run if there are many functions on the page??

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

ASKER

...... 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.

ASKER

http://gowiththemaster.com/tleithoff/includes/page.txt

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

and not :

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

ASKER

I changed it but still nothing. I dont think it knows which function to run from page.php

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

ASKER

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

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

ASKER

http://gowiththemaster.com/tleithoff/includes/page.php

http://gowiththemaster.com/tleithoff/includes/page.txt (last function)

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

ASKER

changed it, but still nothing. I really dont think it knows which function to look for. . .

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

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) >>

ASKER

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

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

ASKER

uhh I did that but it changed my whole page to just say Fair Business!!! I wanted it to write in the div. . .

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)

ASKER

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

ASKER

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

ASKER

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

>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

Put ONLY the following in finishes.php :

<?PHP

echo "FairyBusiness";

?>

Open in new window

ASKER

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

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

ASKER

I changed it to fairybusiness. it had that error message before bc there was nothing for $id

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

Sorry finishes.php :

<?PHP

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

?>

Open in new window

ASKER

how did you get finishes.php?id=1

but I clicked it and nothing still

>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

ASKER

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

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

Clipboard06.jpg

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

nope, didnt work. Is the id of the link transfering to the $id of php code??

>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

ASKER

oh, yeah i forgot about that path

but still did not work

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

ASKER

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

To be sure try to replace line 6 with :

$query = "SELECT blurb FROM submenus WHERE id=1";

Open in new window

ASKER

done

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

remove line 9 and read again ID:35779496

now line 19 in your last code

ASKER

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

ASKER

thanks!

>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

ASKER

ok, thanks again!

You're welcome! Thanks for the points!