[RESOLVED] Combine 2 SQL queries with a JOIN?

[jamjam]

Hi

This is driving me crazy, please help.

I have 2 tables in MySQL database. The first table 'main' has 100's of records.
With the columns id and programme
The second table 'lookup' has only a few records, with columns title and type.
I want to echo a image that contains a link if a value in the programme column is equal to a value in title column.

I know it is possible to create a SQL that will retrieve all the relavant rows from the from both tables at once using a join, but I can't do this so I ended creating 2 MySQL queries.

Query 1

mysql_select_db($database_main, $main);
$query_schedule = sprintf("SELECT * FROM main WHERE category = 'comedy'", GetSQLValueString($schedule_schedule, "int"));
$schedule = mysql_query($query_schedule, $main) or die(mysql_error());
$row_schedule = mysql_fetch_assoc($schedule);
$totalRows_schedule = mysql_num_rows($schedule);

Query 2

mysql_select_db($database_lookup, $lookup);
$query_schedule2 = sprintf("SELECT * FROM main,lookup WHERE programme = title", GetSQLValueString($schedule_schedule2, "int"));
$schedule2 = mysql_query($query_schedule2, $look) or die(mysql_error());
$row_schedule2 = mysql_fetch_assoc($schedule2);
$totalRows_schedule2 = mysql_num_rows($schedule2);

Here is my PHP

<?php do { ?> // Start of Reapeating Region
<?php echo $row_schedule['programme']; ?>
<br />
<a href="<?php if($row_schedule['programme'] == $row_schedule2['title']) 
{echo "http://localhost/info/{$row_schedule2['type']}/?title={$row_schedule['programme']}";} ?>">
<?php if($row_schedule['programme'] == $row_schedule2['title']) {echo ("<img style= 'width: 25px; height: 25px;' src =http://localhost/files/info.png>");} else{echo "";}?></a>

<?php } while ($row_schedule = mysql_fetch_assoc($schedule)); ?> // End of Repeating Region

This almost works.

The first query does its job of returning all selected rows from the main table.
However the second query returns just one row.
My question is how do I get it to return all rows that match the criteria?

I think these maybe possible solutions to my problem which I cannot implement myself.

1. Combine the 2 queries with a join
2. Create a nested repeat region for the results of the second query.
3. Reset 'mysql fetch assoc' as this maybe causing the problem of only returning one row.

Will appriaciate the assistance

Thanks

[Krik]

but I can't do this so I ended creating 2 MySQL queries.

I am guessing you think you can't do that because there are 2 different databases. But you can join tables form 2 different databases.

SELECT * FROM `database_main`.`main` AS Main
LEFT JOIN `database_lookup`.`lookup AS Lookup ON Lookup.title = Main.programme
WHERE `category` = 'comedy'

Of course I don't know what your database names are, I just used the variable names you had. Nor do I know if that where clause will return the correct result set but you get the idea.

Now you will need to make sure the user for both databases is the same. Also do not specify the "[, resource $link_identifier]" part of the [man]mysql_query[/man].

[jamjam]

Krik;10962484 wrote:

I am guessing you think you can't do that because there are 2 different databases. But you can join tables form 2 different databases.

SELECT * FROM `database_main`.`main` AS Main
LEFT JOIN `database_lookup`.`lookup AS Lookup ON Lookup.title = Main.programme
WHERE `category` = 'comedy'

Of course I don't know what your database names are, I just used the variable names you had. Nor do I know if that where clause will return the correct result set but you get the idea.

Now you will need to make sure the user for both databases is the same. Also do not specify the "[, resource $link_identifier]" part of the [man]mysql_query[/man].

Hi

Thanks for the reply.

I guess I was not clear, there is just one database.

My problem is that I have limited knowledge of joins, this is why I was not able to do it.

Can you please rewrite the SQL with for a single database.

Thanks

[Krik]

Yea, having to join 2 table is a very common thing with databases. I currently have one case of joining 5 tables. And I am sure many more than that have been joined by others.

SELECT * FROM `main`
LEFT JOIN lookup ON lookup.title = main.programme
WHERE `main`.`category` = 'comedy'

EDIT: Oop's had a small error in that query

[jamjam]

Perfect!

Thank You.