By jeremydr on

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

mooffie’s picture

mooffie commented

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:

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

jeremydr’s picture

jeremydr commented

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

ndvo’s picture

ndvo commented

Your post really helps php noobs like myself.

coloryan’s picture

coloryan commented

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?

furriephillips’s picture

furriephillips commented

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


  $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!

capoyeti’s picture

capoyeti commented

subscribing

kaoutar’s picture

kaoutar commented

hi i am an biginner  in Drupal

please i have the same problem i want to display the Data, that i import als SQL in PHPMyadmin, in my drupal website 

und where i should paste this i mean witch file exactly

Thank you :)

amal850720’s picture

amal850720 commented

subscribing

kbss’s picture

kbss commented

It does not work for me. What am I doing wrong?

Here's my code.

  $header = array('field_type_value', 'Trauma');
  $rows = array();

  $sql = 'SELECT field_type_value, Trauma FROM {QueryReferencesNumbers} ORDER BY field_type_value';
  $res = db_query($sql);
  while ($row = db_fetch_array($res)) {
    $rows[] = $row;
  }

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

It says there is an error on line 7.
I am trying to call data from an SQL query table onto a page with php code enabled. This is the error message I am getting.
Fatal error: Call to undefined function db_fetch_array() in home/......../modules/php/php.module(80) : eval()'d code on line 7

I would appreciate your help.
Thanks
kbss

salkhalaf’s picture

salkhalaf commented

db_fetch_array is deprecated.