Displaying table data from DB by sql query

Posted by jeremydr on September 13, 2006 at 5:25am

Hi,

I have a question about displaying data from one of the tables in DB in my web page, is it possible with drupal?

for example:
I have a DB table named Students and I would like to display in one of my pages all the
students that are registered to a course (and all their details as well).
in order to do that I need to query mysql and present result in some kinf of table.

I read something about the Table Manager module, any ideas?

thanks for the help,
Jeremy

Comments

...

Posted by mooffie on September 13, 2006 at 8:08am

TableManager manages its own tables. You, OTOH, have an independent table, with a particular structure, and possibly updated regularly by some other application.

is it possible with drupal?

Everything is possible when you know some PHP.

You can enter PHP code into your node (either directly, when you choose the "PHP code" input format; or indirectly, when you use, e.g., the macrotags module). Don't fret: it's easier than it sounds, and we're here to help.

The basic PHP code you use is:

<?php
  $header = array('ID', 'Name', 'Age');
  $rows = array();

  $sql = 'SELECT studentID, name, age FROM {students} ORDER BY name';
  $res = db_query($sql);
  while ($row = db_fetch_array($res)) {
    $rows[] = $row;
  }

  print theme('table', $header, $rows);
?>

Tweak this code to suit your particular 'students' table, paste it into a node, choose the "PHP code" input format (visible only when you're Admin), and hit "Submit". (Later you may want to create some macrotag tags for this code.)

(BTW, you may want to enable your contact form.

Thanks for your answer, I

Posted by jeremydr on September 14, 2006 at 12:29pm

Thanks for your answer, I tried it and it works.

Thanks

Posted by ndvo on September 27, 2009 at 2:41pm

Your post really helps php noobs like myself.

Following up

Posted by coloryan on February 12, 2010 at 11:30am

Say, for instance, you want to put the age in paranthesis and make the name a link to (their profile). Can you elaborate on how to do that?

Very helpful - thanks!

Posted by furrie on April 21, 2010 at 8:54pm

I have used this extremely helpful post to help us track the status of particular webform submissions. My code is this: -

<?php
  $header = array('Change#', 'Requester', 'Description', 'Status');
  $rows = array();

  $sql = "SELECT parent.sid,
(SELECT data FROM {webform_submitted_data} AS child WHERE child.sid = parent.sid AND cid = 1) AS Requester,
(SELECT data FROM {webform_submitted_data} AS child WHERE child.sid = parent.sid AND cid = 27) AS Description,
(SELECT data FROM {webform_submitted_data} AS child WHERE child.sid = parent.sid AND cid = 28) AS Status
FROM {webform_submitted_data} AS parent JOIN webform_submissions ON webform_submissions.sid = parent.sid
WHERE cid = 28 AND data = 'Submitted'
ORDER BY parent.sid DESC";
  $res = db_query($sql);
  
  while ($row = db_fetch_array($res)) {
    $rows[] = $row;
  }

  print theme('table', $header, $rows);
?>

Like the "Following up" post from coloryan, I would love to be able to process the SQL output before adding something into the table fields. I'd like to turn the "Change#" (a number) which I get from the SQL, into a clickable URL very much like this: -

<a href="/node/3/submission/[Change#]" title="click to view">[change#]</a>

Any clues would be very welcome indeed - Cheers!

subscribing

Posted by capoyeti on June 28, 2011 at 6:10am

subscribing

Posted by amal850720 on November 9, 2011 at 4:31pm

subscribing