Using PHP to search a MySQL database and return information

PHP
1

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

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

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

SELECT events.title
     , events.description
     , locations.city
     , locations.state
  FROM events
INNER
  JOIN locations       
    ON locations.id = events.location_id
4

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?

[COLOR=“#FF0000”]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.
[/COLOR]

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

Duh! Thank you for the correction :slight_smile:

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

6

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

orders of magnitude improvement in clarity

also, you can’t write an outer join without JOIN syntax

8

Thanks for your response. More clarity is always a good thing.