Jump to content

pocobueno1388

Members
 View Profile  See their activity

  • Posts

    3,369

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by pocobueno1388

    Combine Queries

    in MySQL Help

    Posted

    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

    .htaccess rewrite help

    in Apache HTTP Server

    Posted

    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?

    .htaccess rewrite help

    in Apache HTTP Server

    Posted

    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!

     

     

     

     

    ORDER BY help

    in MySQL Help

    Posted

    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

    ORDER BY help

    in MySQL Help

    Posted

    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?

    ORDER BY help

    in MySQL Help

    Posted

    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.

    ORDER BY help

    in MySQL Help

    Posted

    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!

    ORDER BY help

    in MySQL Help

    Posted

    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!

     

     

     

    Sorting by Zip code

    in MySQL Help

    Posted

    Ok, so I figured out that I would need a database full of zip codes along with their latitude/longitude. I now have that,  but I still need to figure out how to setup the query to sort users by distance.

     

    Here is the relevant table information tables:

     

    TABLE users
zip

TABLE zipcodes
zipcode
latitude
longitude

     

    So if a user is logged in, I want it to display the users nearest to THEIR zipcode. How would I setup this query??

     

    Thanks!

     

     

    Sorting by Zip code

    in MySQL Help

    Posted

    I have a table that stores the users zip code and I'm trying to display users by distance (nearest to furthest). I'm hoping there is a simple way to where I don't have to get a database with lat/long values...but when I think about it I doubt there is a formula that can measure the distance between two zip codes.

     

    I would appreciate if someone could point me in the right direction. If I do need to get a database with lat/long values, it would be great if you could refer me to a good one.

     

    Thanks!

     

     

     

     

    Using cURL to login to website

    in PHP Coding Help

    Posted

    Hello,

     

    I am trying to use cURL to login to a website, but I can't seem to get it working.

     

    Website I'm trying to login to:

    http://www.uniquearticlewizard.com/amember/member.php

     

    Here is what their form code looks like:

    <form name="login" method="post" action="/amember/member.php"> 

<table class="vedit" > 
    <tr> 
        <th>Username</th> 
        <td><input type="text" name="amember_login" size="15" value="" /></td> 
    </tr> 
    <tr> 
        <th>Password</th> 
        <td><input type="password" name="amember_pass" size="15" /></td> 
    </tr> 
        <tr> 
        <td colspan="2" style='padding:0px; padding-bottom: 2px;'> 
<input type="checkbox" name="remember_login" value="1"> 
<span class="small">Remember my password?</span> 
        </td> 
    </tr> 
    </table> 
<input type="hidden" name="login_attempt_id" value="1291657877" /> 
<br /> 

<span class='button'><input type="submit" value="   Login   " /></span>   
         
      
<span class='button'><input type="button" value="   Back   " onclick="history.back(-1)" /></span> 
</form>

     

    As you can see they are using a javascript button to submit the form, which doesn't have a name attribute. So I'm not sure how to get around this and tell cURL to submit the form. When I Googled I found something that said just submit the other information and it will submit itself, but I'm not sure if that's right.

     

    Here is my attempt, but I just get a blank screen. I think the script is working, but something on there end is exiting out due to me not supplying a required piece of information. I'm not sure what that is though.

     

    <?php

set_time_limit(0);

$options = array(
  CURLOPT_RETURNTRANSFER => true,     // return web page
  CURLOPT_HEADER         => false,    // don't return headers
  CURLOPT_FOLLOWLOCATION => true,     // follow redirects
  CURLOPT_ENCODING       => "",       // handle all encodings
  CURLOPT_USERAGENT      => "spider", // who am i
  CURLOPT_AUTOREFERER    => true,     // set referer on redirect
  CURLOPT_CONNECTTIMEOUT => 120,      // timeout on connect
  CURLOPT_TIMEOUT        => 120,      // timeout on response
  CURLOPT_MAXREDIRS      => 10,       // stop after 10 redirects
);

$ch      = curl_init( "http://www.uniquearticlewizard.com/amember/member.php" );
curl_setopt_array( $ch, $options );
$content = curl_exec( $ch );
$err     = curl_errno( $ch );
$errmsg  = curl_error( $ch );
$header  = curl_getinfo( $ch );
curl_close( $ch );

$header['content'] = $content;

preg_match('/name="login_attempt_id" value="(.*)" \/>/', $header['content'], $form_id);

$value = $form_id[1];

$ch = curl_init();

// SET URL FOR THE POST FORM LOGIN
curl_setopt($ch, CURLOPT_URL, 'http://www.uniquearticlewizard.com/amember/member.php');

// ENABLE HTTP POST
curl_setopt ($ch, CURLOPT_POST, 1);

$data = array('amember_login' => '*****', 'amember_pass' => '*****', 'login_attempt_id' => $value, 'remember_login' => '1');

// SET POST PARAMETERS : FORM VALUES FOR EACH FIELD
curl_setopt($ch, CURLOPT_POSTFIELDS, $data);

// IMITATE CLASSIC BROWSER'S BEHAVIOUR : HANDLE COOKIES
curl_setopt ($ch, CURLOPT_COOKIEJAR, 'cookie.txt');

# Setting CURLOPT_RETURNTRANSFER variable to 1 will force cURL
# not to print out the results of its query.
# Instead, it will return the results as a string return value
# from curl_exec() instead of the usual true/false.
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);

// EXECUTE 1st REQUEST (FORM LOGIN)
$store = curl_exec($ch);

echo $store;

curl_close ($ch); 

?>

     

    They do have a form value that changes on every page refresh, it just tracks the login attempt (which is a long number). I was able to scrape that and put it in the form with the correct value. I thought adding that would successfully log me in, but apparently there is something else going on.

     

    Any help would be greatly appreciated!

    Downloading file - Adding HTML to top

    in PHP Coding Help

    Posted

    I have a script that will download a file from my server onto the user's computer. The problem is it's adding the pages HTML to the top of the file. When I actually look at the file on my server it does NOT have the HTML there, so it has to be some sort of problem with my headers.

     

    <?php

	$fname = "keyword_files/".$this->fileID.".csv";

	header('Content-type: application/csv');
	header("Content-Disposition: inline; filename=".$fname);
	readfile($fname);

?>

     

    I would appreciate any help! Thanks.

    If statement always returning TRUE when it shouldn't

    in PHP Coding Help

    Posted

    I have an IF statement that is constantly returning TRUE, even when it shouldn't be. Basically it checks if an array is empty.

     

    <?php

      $error = array();
      

      //No blanks
      if ($street_addr == "" || $city == "" || $state == "" || $zip == "" || $phone1 == "" || 
            $phone2 == "" || $phone3 == "" || $county == "")
               $error[] = "You left something blank!";
      
      //validate zip
      if(!preg_match("/^[0-9]{5}$/", $zip)) 
          $error[] = "The ZIP code must be a 5-digit number.";
          
      //validate phone number
      if (!is_numeric($phone1) || !is_numeric($phone2) || !is_numeric($phone3))
         $error[] = "Phone can only contain digits";
         
      if (strlen($phone1) < 3 || strlen($phone2) < 3 || strlen($phone3) < 4)
         $error[] = "Invalid phone number";

      $count_errors = count($error);		
         
      if ($count_errors > 0){
      
         echo "ERROR:<br />";
         
         foreach ($error as $err){
         
            echo "-$err<br />";
         
         }
         
      } else { 
	         
          //Other code
      }

?>

     

    So the first part of the IF statement is always executing.

     

    $count_errors DOES return 0 when printed out.

     

    Any help would be greatly appreciated. Thanks!

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.