pocobueno1388

Anyone?

Please ask questions if you think something is to vague. Thanks

Hello everyone, I have a script that will display users according to the distance they are from the logged in user. It works great, it is just extremely slow to load, which I'm assuming it's from all the queries I'm doing. I'm pretty sure I should be able to combine most of them and just have one, I'm just not advanced enough to figure it out. To start there is a query to display the users picture. Then I do a query to display all the people the user has "favorited". Then I select the users who they have unread messages from. After that I then do a query to grab the rest of the users and display them in order of distance from the logged in user. So first I display the logged in user SELECT u.name, u.id, u.last_login, p.path FROM users u LEFT JOIN photos p ON p.userID = u.id AND p.type=1 WHERE u.id = '$user->userID' Then I display their favorites SELECT favorite_userID FROM favorites WHERE userID='$user->userID' SELECT u.name, u.id, u.last_login, p.path FROM users u LEFT JOIN photos p ON p.userID = u.id AND p.type=1 WHERE u.id IN ($favorites) AND u.id NOT IN ($all_blocked) Now I select the people they have unread messages from, but before that I have to do a query to get all the blocked users SELECT block_userID FROM block WHERE userID='$user->userID' SELECT DISTANCE QUERY as distance, u.name, u.id, u.last_login, p.path FROM users u LEFT JOIN photos p ON p.userID = u.id AND p.type=1 HAVING distance <= ".$distance." AND u.id != '$user->userID' AND u.id NOT IN ($favorites) AND u.id NOT IN ($all_blocked) ORDER BY distance Lastly I select the rest of the users and display them by distance from the person SELECT DISTANCE QUERY as distance, u.name, u.id, u.last_login, u.sessionID, p.path, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(u.birthday)), '%Y')+0 AS age FROM users u LEFT JOIN photos p ON p.userID = u.id AND p.type=1 HAVING distance <= ".$distance." AND u.id != '$user->userID' AND u.id NOT IN ($favorites) AND u.id NOT IN ($all_blocked) AND u.sessionID='1' AND age >= $min_age AND age <= $max_age So that is already 6 queries which are all pretty large. If anyone can help me combine these, I would GREATLY appreciate it! Thanks

Oh sheesh, well aren't I just a genius. Thank you so much, works great!

Hmmm...that didn't seem to do anything at all, it didn't even force the www

Thanks requinix, This is giving my site a redirect loop error. I know it's an odd request to have the index page display a pointless variable, but I promise there is method to my madness So I want to keep the code I already have in the .htaccess file in order for my site to always display http://www.domain.com no matter what they type in. So now I just need to figure out how to get the index to always show the URL with the GET variable in it. Any suggestions to the previous code you posted to make it stop going into a redirect loop? Also, this isn't really a redirect...so do you know if this will hurt my SEO if I already have the index page ranking for multiple keywords?

I'm trying rewrite the index URL of my website to always include some extra information in the URL. I want to do this for ONLY the index file. For example, I want to change: www.domain.com ---> www.domain.com?index=true I have it working for if they enter the URL WITHOUT the WWW, but I want it to work no matter what format they put the URL in. Here is the semi-working code RewriteEngine on RewriteCond %{HTTP_HOST} ^domain.com [NC] RewriteRule ^(.*)$ http://www.domain.com?index=true/$1 [L,R=301] I'm just not sure what to change the second line to in order to include ALL formats of the name. Any help is greatly appreciated, thanks!

I was finally able to figure it out. What I did was find the equation for getting the distance in miles, then just sorted them by distance. Here is the query for anyone else who needs help with this: SELECT (((acos(sin((".$latitude."*pi()/180)) * sin((z.latitude*pi()/180))+cos((".$latitude."*pi()/180)) * cos((z.latitude*pi()/180)) * cos(((".$longitude."- z.longitude)*pi()/180))))*180/pi())*60*1.1515) as distance, u.name FROM `zipcodes` z RIGHT JOIN users u ON u.zip = z.zipcode HAVING distance <= ".$distance." ORDER BY distance

fenway - I'm still not quite sure how/if that would work in my case. Yes, that would join the queries but wouldn't give me my desired results. I'm really not even sure if the queries can be combined and get the results I want. I need to first get all the zipcodes that are in the radius, THEN get all the users with those zipcodes and order them by distance from the currently logged in users zipcode. So lets say I had the following zipcodes returned from the first query and the logged in users zipcode was 76017: 76015 76019 76008 76028 I would want them ordered like: 76015, 76019, 76008, 76028 Because those are ordered from closest to furthest from the users zipcode. Does this make sense?

Fenway - I have a query that gets all the users in a specified radius of a given zip code. Now I'm just trying to order the users by distance from that zip code. My first query just selects all the zip codes within that radius, then I am using a second query to select all the users with a matching zip code. I'm sure both of these queries can be combined and it will make it simpler to sort it, but I can't quite wrap my head around all of it.

Still stuck =/

Anyone else want to give it a shot?

I played with it for a while, but I still can't figure it out

Thanks Pikachu2000, but that is only going to order the zip codes by distance, which I believe it's already doing. Either way, even if those come out in the correct order it still doesn't make my second query come out in the correct order. Can you see a way to combine the first and second query so I can select the users information as well? I know there is a way to combine it, but it's just too much for me to wrap my head around!

I successfully found some code that will display all users within a certain radius of a specific zip code. Now I'm trying to order the users it returns sorted by distance from the same zip code. Here is the query that gets me the list of zipcodes that are within a certain radius of another zip code - SELECT distinct(zipcode) FROM zipcodes WHERE (3958*3.1415926*sqrt((latitude-'.$lat.')*(latitude-'.$lat.') + cos(latitude/57.29578)*cos('.$lat.'/57.29578)*(longitude-'.$lon.')*(longitude-'.$lon.'))/180) <= '.$radius Now here is my query that selects the users that have matching zip codes from the ones the previous query returns - SELECT name FROM users WHERE zip IN (".implode(",", $zips).")" So again, I'm trying to order the last query by distance from a specified zip. I have the long/lat of all the zipcodes if that's needed. I feel like I need something like ORDER BY zip ASC STARTING WITH [this zip code] I'm not quite sure how to translate that to the query. Thanks for the help!