Avatar of FairyBusiness
FairyBusiness
Flag for United States of America 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)?
AJAXPHPJavaScript

Avatar of undefined
Last Comment
leakim971

8/22/2022 - Mon
leakim971

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

FairyBusiness

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

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

.attr("id)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
leakim971

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

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

Typo
FairyBusiness

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()
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
leakim971

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

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

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
FairyBusiness

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

ASKER
leakim971

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

and not :

$("lala").click(function(e) {
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
FairyBusiness

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

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
FairyBusiness

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
leakim971

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
FairyBusiness

ASKER
leakim971

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
FairyBusiness

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

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

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) >>
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
FairyBusiness

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
leakim971

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

FairyBusiness

ASKER
uhh I did that but it changed my whole page to just say Fair Business!!!  I wanted it to write in the div. . .
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
leakim971

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

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

FairyBusiness

ASKER
ok I made a separate php file named finishes and I put the php code there
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
FairyBusiness

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

leakim971

>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

leakim971

Put ONLY the following in finishes.php :


<?PHP

echo "FairyBusiness";

?>

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
FairyBusiness

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

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
FairyBusiness

ASKER
I changed it to fairybusiness. it had that error message before bc there was nothing for $id
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
leakim971

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
leakim971

Sorry finishes.php :


<?PHP

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

?>

Open in new window

FairyBusiness

ASKER
how did you get finishes.php?id=1

but I clicked it and nothing still
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
leakim971

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

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

leakim971

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


Clipboard06.jpg
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
FairyBusiness

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
leakim971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
FairyBusiness

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

>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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
FairyBusiness

ASKER
oh, yeah i forgot about that path

but still did not work

leakim971

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
FairyBusiness

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
leakim971

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

Open in new window

FairyBusiness

ASKER
done
SOLUTION
leakim971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
FairyBusiness

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
leakim971

remove line 9 and read again ID:35779496
leakim971

now line 19 in your last code
FairyBusiness

ASKER
I did not know the var_dump counted. but yes it works!! finally!!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
FairyBusiness

ASKER
thanks!
leakim971

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

ASKER
ok, thanks again!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
leakim971

You're welcome! Thanks for the points!