How can i changed this?

[Sayian]

I have a query here , that im using to pull user information based off a search from my database.

I have another table called shocc_account_photos where i store the photo_filenames of the user's pictures. A user can have more then 1 picture , so i mark their main picture as main = 1

Reminding you , this is a search , so its pulling many records from the database. I have no problem joining shocc_users with shocc_account_photos so i can get the main photo of each user who was returned in the search ... But what if a user doesnt have a main picture? then main=1 wouldnt be true , and they get excluded from the search .. but i still want user's with no pics to showup , but at the same time , I have to use WHERE main=1 to get the photo filename of the people who DO Actually have pictures ... so im really in a bind here ..

Trying to get the photo filenames of the people who have pics , yet at the same time trying to return info on those who dont.

 $sql = "SELECT u.city, u.state, u.username,
u.rating,u.age,u.ethnicity,u.gender,u.starsign,
p.photo_filename
FROM shocc_users u
NATURAL LEFT JOIN shocc_account_photos p
WHERE u.username REGEXP '$search_username' 
AND u.age >= '$search_low_age' 
AND u.age <= '$search_high_age'
AND main=1
$ssql
ORDER BY rand()

Ive tried and tried ... some help would be greatly appriciated

Thank you in advance.

[bastien]

left outer join

[Sayian]

I have tried to add left outer join

 $sql = "SELECT u.city, u.state, u.username,
u.rating,u.age,u.ethnicity,u.gender,u.starsign,
p.photo_filename
FROM shocc_users u
LEFT OUTER JOIN shocc_account_photos p
WHERE u.username REGEXP '$search_username' 
AND u.age >= '$search_low_age' 
AND u.age <= '$search_high_age'
AND main=1
$ssql
ORDER BY rand()

and now i get this error , that didnt happen with NATURAL LEFT JOIN

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/site1/public_html/search.php on line 60
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 'WHERE u.username REGEXP '.*' AND u.age >= '13' AND u.age <= '

[bastien]

I would guess that your regexp is not something the server likes. What is the goal with the regexp?

[Sayian]

Well this is the code associated with the REGEXP for the username

    if(isset($_GET['u']) && strlen($_GET['u'])) {
      $nav_search_username = addslashes(trim($_GET['u']));
      $search_username = ".*" . $nav_search_username . ".*";
    } else {
      $search_username = ".*";
    }

Im still new to regexp , so if theirs another way i can do that , and take that out , im all for it.

[bastien]

are trying for an exact match or a close match?

select * from table where username like '%$username%'

might be better

[Sayian]

I have changed and removed the regexp , and the same error as posted before , still happens.

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/site1/public_html/search.php on line 56
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 'WHERE u.username LIKE '%%' AND u.age >= '13' AND u.age <= '80

[bastien]

your $username variable has no value...check your code around that

[Lars_Berg]

You need to specify an on clause for the outer join and the restriction on main should be in the on clause as well.

$sql = "SELECT u.city, u.state, u.username,
u.rating,u.age,u.ethnicity,u.gender,u.starsign,
p.photo_filename
FROM shocc_users u
LEFT JOIN shocc_account_photos p on  u.<columnName> = p.<columnName>
and main = 1
WHERE u.username REGEXP '$search_username'
AND u.age >= '$search_low_age'
AND u.age <= '$search_high_age'
$ssql
ORDER BY rand()