CODEWITHSUNDEEP
phpmysql
Thelson Richardson
Thelson Richardson

Reputation: 107

Left Join PHP/SQL with LIKE and logical AND

I have the following Mysql query where im trying to run a left join to define a pages table with a images table. 

$query = mysql_query("SELECT * FROM pages
                    WHERE `fname` LIKE '%$fname%' AND `lname` LIKE '%$lname%' AND `city` LIKE '%$citysearch%' AND `state` LIKE '%$statesearch%' AND `activities` LIKE '%$locationsearch%' AND `startyr` LIKE '%$startyearsearch%' AND `endyr` LIKE '%$endyearsearch%'
                     LEFT JOIN `images.pgname` WHERE `pages.pgname` = 'images.pgname' LIMIT $start, $perpage
                ");

I get "Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given on line 72"

What im trying to do is join the pages table with the images table. The pages table has the following feilds: fname, lname, city, state, activities, startyr, endyr, pgname

And the images table has the following fields: pgname, id, imageUrl, username

What I want to do is return all of the images where pages.pgname is equal to images.pgname, and return the image's URL with imageUrl.

Any and all help is welcome!

Upvotes: 1

Views: 444

Answers (1)

Niko
Niko

Reputation: 26730

Looks like you're trying to join a column "images.pgname"? You can only join complete tables. Also, the JOIN needs to be before the WHERE clause.

Now, lets decipher that procedure: if you need to select values from multiple tables, you first list all the values you'd like to select. In this case, that is a) all columns from pages (pages.*) and b) the imageUrl from images (images.imageUrl):

SELECT pages.*, images.imageUrl

Then you specify the table(s) from which you're selecting, just as usual:

FROM pages

Then you do the join. Left join means, that the database will try to find a corresponding row in the other table (images). It is not necessary that this row actually exists. However, you do the join by specifying the table and the condition that defines the connection:

LEFT JOIN images ON pages.pgname = images.pgname

The rest stays the same:

WHERE pages.fname LIKE '%$fname%' AND pages.lname LIKE '%$lname%' AND pages.city LIKE '%$citysearch%' AND pages.state LIKE '%$statesearch%' AND pages.activities LIKE '%$locationsearch%' AND pages.startyr LIKE '%$startyearsearch%' AND pages.endyr LIKE '%$endyearsearch%'
LIMIT $start, $perpage

However, if you have multiple images per page, you need to do the joining the other way around:

SELECT pages.*, images.imageUrl
FROM images
JOIN pages ON pages.pgname = images.pgname
WHERE ...

Upvotes: 2

Related Questions