Query 2 Tables In 1 Query

[andymike07]

I'm new to PHP and MySQL and I'm trying to design my own script to handle a photo gallery system. However, I'm running into problems accessing 2 tables in the database.

I have 2 tables in the database, one for event sponsors and one for the photos from the event. I decided to use 2 tables because the each of the records in the sponsor table will have a different URL that may change. Each events will have a sponsor from the sponsor table in the database. Each record in both tables has a unique id number. In the photos table I have a space for the sponsor's ID number. 

What I am trying to do is query the database one time, but with that query access both tables to get the information needed for a gallery when the page is loaded. e.g. index.php?id=1 would grab the details from the photos table for record 1 as well as get the sponsor details for that event from the sponsors table.

Is this something that is possible to do, if so, what is the best (and easiest) way to do it?

[vbnullchar]

try joining...

[code]$strQuery="SELECT news.*, employees.id as emp_id, employees.alias
FROM news
                INNER JOIN employees ON news.author=employees.id
WHERE is_deleted=0 AND news.type='news'";[/code]

[Nelak]

while were on the subject is it possible to query 3 tables using like for a search?

[andymike07]

Thanks vbnullchar! I'll give that a try.  :)


[quote author=vbnullchar link=topic=123465.msg510376#msg510376 date=1169455168]
try joining...

[code]$strQuery="SELECT news.*, employees.id as emp_id, employees.alias
FROM news
                INNER JOIN employees ON news.author=employees.id
WHERE is_deleted=0 AND news.type='news'";[/code]
[/quote]

[Jessica]

[quote author=Nelak link=topic=123465.msg510407#msg510407 date=1169466093]
while were on the subject is it possible to query 3 tables using like for a search?
[/quote]

Yep, you just keep adding to it. Here's a simplified example, you'd change it for yours.
SELECT * FROM one, two, three, four, five WHERE one.id = two.id AND two.id = three.id AND three.id = four.id AND four.id = five.id

[andymike07]

I did a little research on JOINing MySQL tables and I'm running into problems. I decided that using the equi-join was the best option to fit what I want to do. When I load the page in the browser I get a "Error, query failed" error. My URLs are set up like 'index.php?id=###&s=###". (The id is pulled from the spotlights table in the database.)

In the page I have this coded:

[code]require('../_db/_db_connection.php');

$id = $_GET['id'];
$s = $_GET['s'];

$query = "select spon.spon_id, spon.site_name, spon.site_url, spotlights.mod_name, spotlights.long_des, spotlights.banner, spotlights.link_text, spotlights.folder_name, spotlights.spon_id, spotlights.id from spon, spotlights where spon.spon_id=$s and spotlights.id = $id";[/code]

The way the database is set up is to give each spon record a unique id number (spon.spon_id) and the details for that sponsor. In the spotlights table I have a spon_id which has the spon number that is pulled from spon.spon_id when a new record is entered via admin control panel.


I can't seem to figure out where I've made the error and how I can fix it. Can anyone spot what I've done wrong?

[sayedsohail]

Sorry for this silly opinion, can you try echo "$s"; echo "$id"; to see if it has got any value at first.

[andymike07]

Hi sayedsohail,

I tried echoing both of them out on the page and they both show up. Thank you for trying to help.  :)

[quote author=sayedsohail link=topic=123465.msg514703#msg514703 date=1169899363]
Sorry for this silly opinion, can you try echo "$s"; echo "$id"; to see if it has got any value at first.

[/quote]