user data from 2 tables

[shaiss]

I'm using Drupal to build a site for a local school.

the temporary site is at http://ride4.org/beacon

 

The faculty page http://ride4.org/beacon/?q=node/5 is php based to pull user data from the mysql table.

http://pastie.caboo.se/188159<--Thats the php code for faculty.php that generates the html for the page above.

 

Theres 2 tables that hold the user data and their data exported in XML format

drupal_profile_values - http://pastie.caboo.se/188122

drupal_users - http://pastie.caboo.se/188118

 

drupal_profile_fields (explains the fid value in the drupal_profile_values) - http://pastie.caboo.se/188120

 

 

I'm no expert so I know I'm not doing this properly.  You can see that I keep the mysql connection open till the end, nest while loops, and use if commands to output the value.  I'm sure theres a better and cleaner way to do this. 

The output I have now on http://ride4.org/beacon/faculty.php is okay, but I'd like to clean it up

[craygo]

couple things you can do to help clean up your code. You really shouldn't need 2 queries to get the data you need. Better off giving us a dump of your tables in sql format instead

 

Do you have 2 tables of 3 tables?? If you have phpmyadmin give us a sql dump of the structure and data if you want.

 

Ray

 

 

[phorman]

Your problem as I see it, has nothing to do with pulling from two tables, but rather the time field Drupal passes back is not recognized by mktime.

 

As I do not know what format the drupal time is in, I just wrote a simple regex to parse the date out for me.

 

<?php
  function convert_date($drupal_date) {
    preg_match('/[a-z]:[0-9]{0,2}:\{[a-z]:[0-9]{0,2}:"[a-z]*";[a-z][0-9]{0,2}):"[0-9]{0,2}";[a-z]:[0-9]{0,2}:"[a-z]*";[a-z]:[0-9]{0,2}:"([0-9]{0,2})";[a-z]:[0-9]{0,2}:"[a-z]*";[a-z]:[0-9]{0,2}:"([0-9]{4})";\}/si', $date,$date_array);
    return($date_array);
  }


  $date = 'a:3:{s:5:"month";s:1:"2";s:3:"day";s:2:"25";s:4:"year";s:4:"2008";}';

  $fixeddate = convert_date($date);
  //echo date("l dS \of F Y h:i:s A",$date);
  $time = mktime(0,0,0,$fixeddate[1],$fixeddate[2],$fixeddate[3]);
  $date = date("l dS \of F Y h:i:s A",$time);
  echo $date

?>

 

So all you have to change, to call it is:

 

function convert_profile_date($profile_date_ar)  {
       // takes a profile date array and converts it to a date variable.
    $profile_date = convert_date($profile_date_ar);
    $output = mktime(0, 0, 0, $profile_date[1], $profile_date[2], $profile_date_ar[3]);
    return $output;

}

[phorman]

couple things you can do to help clean up your code. You really shouldn't need 2 queries to get the data you need. Better off giving us a dump of your tables in sql format instead

 

Do you have 2 tables of 3 tables?? If you have phpmyadmin give us a sql dump of the structure and data if you want.

 

Ray

 

 

 

 

Ah, If you want to clean up your SQL calls, just do the following.  You do not need to determine the number of users.

 

$query = "SELECT drupal_profile_values.fid, 
	drupal_profile_values.uid, 
	drupal_profile_values.value, 
	drupal_users.mail, 
	drupal_users.picture
FROM drupal_profile_values INNER JOIN drupal_users ON drupal_profile_values.uid = drupal_users.uid
WHERE drupal_profile_values.uid = {$user}
ORDER BY drupal_profile_values.uid ASC";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {

  $profilevalid = $row["drupal_profile_values.fid"];
  $profileval   = $row["drupal_profile_values.value");

  ......continue with your other code here .......


}

 

The key being that in the while clause, you populate the db table rows into $row, until no more rows are available to populate, eliminating the need to know the count beforehand.