SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Using PHP to search a MySQL database and return information

    Using PHP to search a MySQL database and return information.
    I am building a directory site with a Joomla component that has a Google Map facility but not up to the high standard and functionality of another map component I am using.

    So, to save on work I ask this:

    Within the map an info block pops up when you click the marker icon. As with Google Maps. I need information in these blocks. I have the information already in the directory database so do not want to duplicate it. To display the same information in the directory within the map block can I use the following code where it allows me to insert code and add in the relevant information:
    {source}<?php
    $query = $db->getQuery(true);
    $query->select($db->nameQuote('something'));
    $query->from('#__mydatabasetable');
    $query->where($db->nameQuote('this').' = '.$db->quote('that'));
    $db->setQuery($query);
    $result = $database->loadResult();
    ?>{/source}
    Does this mean I can display information from another table within the same database?

    If so, I would like to do display the following:

    If the ID of this Marker icon matches the ID of e.g.databaseXYZ, then enter information from databaseabc, column123.

    If I can, what is the relevant information and where does it go?

  2. #2
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I don't completely understand your question, but I'll give it a try. When you want to combine data from more than one table it is called a "join". Most tables should have a unique identifier called a "primary key". This is usually a number. This key can be referenced in another table. In this case it is called a "foreign key".

    For example, you could have a table called "EVENTS":

    EVENTS
    id | title | description | location_id

    And a table called "LOCATIONS"

    LOCATIONS
    id | city | state

    The "location_id" in the EVENTS table is a foreign key referencing the primary key "id" of the LOCATIONS table.

    These would be joined like this:

    SELECT EVENTS.title, EVENTS.description, LOCATIONS.city, LOCATIONS.state
    WHERE EVENTS.location_id=LOCATIONS.id

    Using Joomla's setQuery the elements of the MYSQL above would be added in sections.
    I don't know Joomla well enough to provide you with the exact code.

    Let me know if you have any questions.

    E

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by eruna View Post
    These would be joined like this:

    SELECT EVENTS.title, EVENTS.description, LOCATIONS.city, LOCATIONS.state
    WHERE EVENTS.location_id=LOCATIONS.id
    your PK/FK explanation was great, but this query is poor, it's missing the FROM clause!

    also, you should be using explicit JOIN syntax, not the WHERE clause
    Code:
    SELECT events.title
         , events.description
         , locations.city
         , locations.state
      FROM events
    INNER
      JOIN locations       
        ON locations.id = events.location_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what I have now written:

    <?php
    $query = $db->getQuery(true);
    $query->select(‘_joomgalaxy_entries,title’)
    $query->from('_ joomgalaxy_entries ‘);
    $query->join(‘_zhgooglemaps_markers ').;
    $query->on(‘_zhgooglemaps_markers.id=_joomgalaxy_entries.id’)
    $db->setQuery($query);
    $result = $database->loadResult();
    ?>

    And guess what ios displayed on my page?

    Parse error: syntax error, unexpected T_VARIABLE in /home/sotonpla/public_html/placebookjoomla/plugins/system/sourcerer/helper.php(450) : runtime-created function on line 9

    Fatal error: Function name must be a string in /home/sotonpla/public_html/placebookjoomla/plugins/system/sourcerer/helper.php on line 454.


    Could somebody please explain to me in basic, plain, "I know nothing about this" kind of language. Or even, write it for me. LOL.

  5. #5
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it's missing the FROM clause!
    Duh! Thank you for the correction

    What is the advantage of the JOIN syntax over the WHERE syntax?

  6. #6
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You really have two questions.

    1) How do you query the database to get the results you want? MYSQL is making the query not PHP.
    2) How do you run the query within Joomla?

    It might help you to study up on join queries to get a better sense of the query you are trying to run, and then study the Joomla setQuery documentation to see how you run it.
    You can test out queries in phpMyAdmin if you have that installed on the server.

    I don't know Joomla well enough to tell you more than this.

    Good luck

    E

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by eruna View Post
    What is the advantage of the JOIN syntax over the WHERE syntax?
    orders of magnitude improvement in clarity

    also, you can't write an outer join without JOIN syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your response. More clarity is always a good thing.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •