Ajax Talk - Asynchronous javascript and xml discussion board
 FAQ   Search    Usergroups      Register   Profile   Check your messages   Log in 
Signup now to enjoy a range of free membership advantages !!
Ajax Talk - Moderators Wanted
Call Multiple Rows From MySQL Using PHP/AJAX

 
Post new topic   Reply to topic    Ajaxtalk.com Forum Index -> PHP
View previous topic :: View next topic  
Author Message
Keir
New one
New one


Joined: 11 Jul 2006
Posts: 1

PostPosted: Tue Jul 11, 2006 2:55 pm    Post subject: Call Multiple Rows From MySQL Using PHP/AJAX Reply with quote

Hi,

I have a bit of a problem I was hoping someone might be able to help with.

I have a page containing a dropdown menu. It's a note system i'm doing for myself, where I'll create a topic and be able to make notes within the topic.

I have 2 tables in the mySQL database.
1 - Subject
2 - Notes

What I want is to be able to select the note from the dropdown menu, and for it to display all related notes using AJAX.

The setup of the system thus far is I submit a Subject, and I am taken to a form on a seperate page where I enter the first note.

The note form submits the content to the Notes table in the database, using the ID of the row containing the subject from the page before, and because of that I am able to link notes to projects.

I am able to display the latest note added to a subject using AJAX easily enough, but no matter what I try (I'm very new to the whole AJAX thing) I am unable to show the rest of the notes under the selected subject.

I can duplicate the one entry it does show but not show the next.

I'm aiming to have it display the latest 10 notes under the Subject and then I'll have a button which will take me to a page listing all the notes under that subject.

If anyone can help i'd appreciate it!

To recap, the main issue I am having is with displaying multiple row's.

Many thanks,

- Keir
Back to top
View user's profile Send private message
Guest






PostPosted: Tue Jul 11, 2006 5:22 pm    Post subject: Reply with quote

Ok if I got it right you have some sort of topics to which you add notes? That means you have the following structure:
Code:
               +-Note----+
+-Topic---+    | ID      |
| ID      | <- | Topic   |
| Name    |    | Content |
+---------+    +---------+

If that is right you'll have to aggregate the data on the server-side (as an XML or JSON Document) and then send it over to the browser, which will take the data and fit it into the page.
Code:
SELECT * FROM Note WHERE topic='topicid' LIMIT 10;

Will give you the "pure" data. Now depending on your scripting language just format it as XML or JSON and send it to the browser.
That should be it ^^
Back to top
Snyke
Forum Assistant
Forum Assistant


Joined: 20 Jun 2006
Posts: 87
Location: Zurich

PostPosted: Tue Jul 11, 2006 5:29 pm    Post subject: Reply with quote

Nice, I just noticed that I submitted the first part as Guest...

Anyway:
Code:

$rs = mysql_query("SELECT ...");
// Put all the data into an array.
while($temp = mysql_fetch_array($rs,MYSQL_FETCHMODE_ASSOC)){
  $data[] = $temp;
}
// Now Format it correctly

For formatting I suggest either XML or JSON.
Code:
$json = "[" // We want it as an array
foreach($data as $d){
  // Add the next entry to our JSON-String.
  $json .= "{id:'".$d['id']."',topic:'".$d['topic']."',content:'".$d['content']."'},";
}
$json .= "];";

(I didn't test the code, but it should work as it is).
On the client side (browser) all you have to do is:
Code:
eval("data = " + jsonCode);

And you will be able to access the data from within JavaScript ^^
_________________
[::[ Snyke.net ]::]
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Snyke
Forum Assistant
Forum Assistant


Joined: 20 Jun 2006
Posts: 87
Location: Zurich

PostPosted: Mon Jul 17, 2006 10:49 pm    Post subject: Reply with quote

Ok we fixed it in a Chat session ^^
So for anybody interested here is the code that we wrote together, it also includes some tips that may be usefull for all PHP Novices Very Happy
Code:
<ul>
<?php

  /**
   * Database configurations and Database connections are usually stored
   * in the config/initialization files and then included when needed.
   */
  //database information
  $sDBServer = "localhost";
  $sDBName = "projectm";
  $sDBUsername = "root";
  $sDBPassword = "";
  //make the database connection
  mysql_connect($sDBServer,$sDBUsername,$sDBPassword) or die("Could not connect to Database Server.");
  mysql_select_db($sDBName) or die("Could not select Database.");

  //customer ID
  /**
   * This is good but I'd be cautious for SQL injections, and escape it
   * before adding it into the query.
   */
  $sID = addslashes($_GET["id"]);


  /**
   * Now we're sure that even if the user was trying some strange strings such as
   * "' OR 1=1" are safe. Otherwise this would have permitted listing ALL Notes.
   * Also constants in queries are usually quoted ('bla') for security and keywords
   * are UPPERCASE for easier understanding.
   */
  //create the SQL query string
  $sQuery = "SELECT * FROM notes WHERE pid='".$sID."';";

  /**
   * Now we create the resultset of our query or you can call it execute our query.
   * The result is a resource identifier.
   */
  $rs = mysql_query($sQuery) or die(mysql_error());
  /**
   * Now it get's messy: we cycle through the results of our query and for each we
   * put the data into a List Item and print it.
   */
  while($t = mysql_fetch_array($rs, MYSQL_ASSOC)){
    print("<li>".$t['Name']." ".$t['Date']." ".$t['Note']." ".$t['pid']."</li>");
  }
  /**
   * If we have no results we want to tell the user.
   */
  if(mysql_num_rows($rs) == 0){
    print("<li>No Notes for Project ".$sID.".</li>");
  }
  mysql_close();
?>


Hope this helps.

Regards,
Snyke
_________________
[::[ Snyke.net ]::]
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    Ajaxtalk.com Forum Index -> PHP All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
 Advertisements
 XML & Copyright Notice 
    


Powered by phpBB © 2001, 2005 phpBB Group

What is according to Wikipedia: Ajax Web2.0 XML E4X


Link to our site:

  


del.icio.us digg spurl wists simpy newsvine blinklist furl blogmarks yahoo! myweb smarking ma.gnolia segnalo reddit fark technorati cosmos


Normal Bookmark


Partner Button Links:
Codecrunch.com: Webmaster Tutorials    One Ajax


Partner Text Links:
quomon | web20log | ajaxmatters | tableless | 456bereastreet | music charts | gift ideas | competitions | free xbox 360 | link trade ?


Credits:
it consultancy & project management lead generation zoekmachine optimalisatie steal these buttons seo expert