Need help with a simple SQL statement

Your code modifications and patches you want to share with others.
Locked
User avatar
mli
Joomla! Intern
Joomla! Intern
Posts: 72
Joined: Tue Jan 31, 2006 11:38 pm
Location: Sweden

Need help with a simple SQL statement

Post by mli » Wed Nov 15, 2006 9:44 pm

Hi everyone,

I just decided to take my development one step ahead of simple design and configuration and most of the stuff that I want to do is simply pull information that's already available in the database into a new presentation.

I'd really appreciate if someone could help me achieve my first SQL code in Joomla :>

-----------

What I want to is compile a list with information from two tables "jos_users" and "jos_comprofiler". I do understand the concept of relational database but I have absolutely no idea how to make it work in practice.

* I want to write a simple code that pulls "name" from "jos_users" and "cb_phone" from "jos_comprofiler" (if I could include the avatar in the list as well that would be even neater)

1) What would the SQL and PHP code look like?
2) Where do I put so that I can use it in a "publication"?

Many many thanks in advance!

/Joomla-fan taking his first dev-steps

Ps, if someone could point me in a direction of more specific hands-on tutorials in this area I'd really appreciate it, Ds
Just started with Joomla (convert from MDpro)
Zero PHP-knowledge, decent HTML / CSS
Using: CB, Gallery2, Joomlaboard

User avatar
pe7er
Joomla! Master
Joomla! Master
Posts: 23448
Joined: Thu Aug 18, 2005 8:55 pm
Location: Nijmegen, Netherlands
Contact:

Re: Need help with a simple SQL statement

Post by pe7er » Wed Nov 15, 2006 10:34 pm

Best way to do this is start with a Module. Backup mod_whosonline.php, make sure that the module is published. Then start changing the code. You will need something like:

$query = "SELECT name, cb_phone"
."\n FROM #__users"
."\n LEFT JOIN #__comprofiler ON #__comprofiler.user_id = #__users.id"
."\n ORDER BY name"
;
$database->setQuery($query);
$rows = $database->loadObjectList();

this will result in an Array called $rows, with all names+ phone numbers ordered on Name. To display each record of that array use something like:
foreach($rows as $row) {
  echo $row->name." has phone number: ".$row->cb_phone."
";
}


I haven't tested this code, so I don't know if the example works well.
More info: Developer Manual, http://help.joomla.org/content/section/12/125/
Kind Regards,
Peter Martin, Global Moderator
https://db8.nl - Joomla specialist, Nijmegen, Nederland
The best website: https://the-best-website.com


Locked

Return to “Core Hacks and Patches”