cant figure out inner join

[rondog]

I need to search from two different tables and inner join looks like it would do the trick..

 

I am doing

SELECT keywords FROM video WHERE keywords = 'paintball' INNER JOIN photo ON video.keywords=photo.keywords

 

and it just says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN photo ON video . keywords = photo . keywords  LIMIT 0, 30' at line 1

 

Should I be using something else other than inner join?

[p2grace]

Depends on how you want the results.  For simple joins you could do this:

 

SELECT `v`.`keywords` FROM `video` v, `photo` p WHERE `v`.`keywords` = `p`.`keywords`

[rondog]

I dont really understand. I have two tables (video and photo). Both have a keywords field with keywords in it. Now when someone uses the search field, I want mysql to search the video and photo table(column keywords) and return results. I have it working with just searching the video:

<?php
$query = "SELECT * FROM video WHERE keywords LIKE '%$searchtxt%'";
foreach($arr2 as $v) {
$query .= " OR keywords LIKE '%$v%' OR keywords LIKE '%v' OR keywords LIKE 'v%'";
}
$query .= " LIMIT $limitvalue, $limit";
?>

 

How could I use your approach in my query?

[p2grace]

Ahh, you can't really use select * in this type of query, so specify all of your fields like this:

 

<?php
$query = "SELECT `v`.`id` AS `v_id`, `p`.`id` AS `p_id`, `v`.`name` AS `v_name`, `p`.`name` AS `p_name` FROM `video` v, `photo` p WHERE `v`.keywords LIKE '%$searchtxt%' OR `p`.`keywords` LIKE '%$searchtxt%'";
foreach($arr2 as $v) {
$query .= " OR `v.`keywords` LIKE '%$v%' OR `p`.`keywords` LIKE '%$v%' OR `v`.keywords LIKE '%$v' OR `p`.`keywords LIKE  '%$v' OR `v`.`keywords` LIKE '$v%' OR `p`.`keywords` LIKE '$v%'";
}
$query .= " LIMIT $limitvalue, $limit";
?>

 

Something like that should work

[DJTim666]

-deleted

[rondog]

ok i may be getting closer..ehhh so i now have:

<?php
$query = "SELECT video.keywords,photo.keywords FROM video,photo WHERE video.keywords LIKE '%$searchtxt%' OR photo.keywords LIKE '%$searchtxt%'";
foreach($arr2 as $v) {
$query .= " OR video.keywords LIKE '%$v%' OR photo.keywords LIKE '%$v%' OR video.keywords LIKE '%$v' OR photo.keywords LIKE '%$v' OR video.keywords LIKE '$v%' OR photo.keywords LIKE '$v%'";
}
$query .= " LIMIT $limitvalue, $limit";
?>

 

Its returning 70,000+ results..Thats not possible I only have about 2000 media files between images and videos. What do you think?

 

Its doing something bad because I sometimes get a "Lost connection to mysql server during query" error

[p2grace]

Hmm let's try this:

 

<?php
$query = "SELECT video.keywords,photo.keywords FROM video,photo WHERE (video.keywords LIKE '%$searchtxt%' OR photo.keywords LIKE '%$searchtxt%')";
foreach($arr2 as $v) {
$query .= " OR (video.keywords LIKE '%$v%' OR photo.keywords LIKE '%$v%') OR (video.keywords LIKE '%$v' OR photo.keywords LIKE '%$v') OR (video.keywords LIKE '$v%' OR photo.keywords LIKE '$v%')";
}
$query .= " LIMIT $limitvalue, $limit";
?>

[rondog]

It's producing the same result. I am thinking of just having two radio buttons where you search image or video. That would be easier and it would organize things up a bit.

[p2grace]

Sounds like a good idea