unemployment

I want the left outer join the sums the capital to only sum capital for companies that I am employed by. Right now it's summing capital for all companies is my companies table, NOT where my employee ID and company id are found in the employees table. SELECT `users`.`id`, `users`.`firstname`, `users`.`lastname`, `users`.`username`, `users`.`email`, `users`.`gender`, `users`.`accounttype`, `users`.`personalweb`, `users`.`guestviews`, `users`.`iviews`, `users`.`eviews`, `users`.`credentials`, `users`.`specialties`, `users`.`country`, `users`.`city`, `users`.`state`, `users`.`phonenumber`, `users`.`dateofbirth` AS `dob`, `users`.`mail_status`, `users`.`status`, `markers`.`lat`, `markers`.`lng`, `user_privacy`.`avatar` AS `privacy_avatar`, `user_privacy`.`city` AS `privacy_city`, `user_privacy`.`location` AS `privacy_location`, `investor_info`.`investor_type`, DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`, DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`, SUM(`investor_info`.`capital_available`) AS `totalavailable`, `companytype`, `capital` FROM `users` LEFT JOIN `investor_info` ON `users`.`id` = `investor_info`.`uid` LEFT JOIN `markers` ON `users`.`id` = `markers`.`uid` LEFT JOIN `user_privacy` ON `users`.`id` = `user_privacy`.`uid` LEFT OUTER JOIN employees ON users.id = employees.userid LEFT OUTER JOIN ( SELECT companies.companyid, companies.companytype AS `companytype`, SUM(companies.capital) AS `capital` FROM `companies` GROUP BY companies.companytype) SumCompanies ON employees.companyid = SumCompanies.companyid WHERE `users`.`status` > 2

Can someone please tell me what syntax I am screwing up? `users`.`id` AS `uid`, `users`.`username` AS `username`, `users`.`firstname` AS `firstname`, `users`.`lastname` AS `lastname`, `users`.`accounttype` AS `accounttype`, `users`.`country` AS `country`, `users`.`state` AS `state`, `users`.`city` AS `city`, `markers`.`lat`, `markers`.`lng`, `user_privacy`.`avatar` AS `privacy_avatar`, `user_privacy`.`city` AS `privacy_city`, `user_privacy`.`location` AS `privacy_location`, SUM(`investor_info`.`capital_available`) AS `totalavailable`, `capital` FROM `users` LEFT JOIN `investor_info` ON `users`.`id` = `investor_info`.`uid` INNER JOIN `assets_countries` ON `users`.`country` = `assets_countries`.`country_id` INNER JOIN `assets_states` ON `users`.`state` = `assets_states`.`state_id` LEFT JOIN `markers` ON `users`.`id` = `markers`.`uid` LEFT JOIN `user_privacy` ON `users`.`id` = `user_privacy`.`uid` LEFT OUTER JOIN employees ON users.id = employees.userid LEFT OUTER JOIN ( SELECT companies.companyid, SUM(companies.capital) AS `capital` FROM `companies` GROUP BY companies.companytype) SumCompanies ON employees.companyid = SumCompanies.companyid WHERE (`assets_countries`.`country` LIKE '%{$country}%' OR `assets_states`.`state` LIKE '%{$state}%' OR `users`.`city` LIKE '%{$city}%' ) AND `users`.`status` > 2

users table id firstname lastname partners table partner_id user_id friend_id approved Basically I need to create aliases to be able to get my friends friends. I just don't know how to restrict the results by removing people i'm already friends with.

I'm in the process of making a people you may know feature. To do this I am trying to grab my friends friends that I am not friends with. I need the SQL to say... If my friends friends ID matches any of my friends Id then exclude it from the select. I'm not sure how to do this or at least how to do it efficiently. Query: SELECT users.id, users.firstname, users.lastname, myfriend.id, myfriend.firstname, myfriend.lastname, theirfriend.id, theirfriend.firstname, theirfriend.lastname FROM users INNER JOIN partners ON partners.user_id = users.id AND partners.approved = 1 INNER JOIN users myfriend ON myfriend.id = partners.friend_id INNER JOIN partners partners2 ON partners2.user_id = myfriend.id INNER JOIN users theirfriend ON theirfriend.id = partners2.friend_id WHERE users.id = 1 Current Results My ID My Name My Friends Id My Friends Name My Friends Friends ID My Friends Friends Name 1 Jason 2 Chelsea 4 Davey 1 Jason 2 Chelsea 6 Jim 1 Jason 2 Chelsea 12 Peter 1 Jason 2 Chelsea 16 Cameron 1 Jason 2 Chelsea 38 Felicia 1 Jason 4 Davey 5 Adam 1 Jason 4 Davey 14 Jeffrey 1 Jason 5 Adam 6 Jim 1 Jason 5 Adam 14 Jeffrey 1 Jason 5 Adam 17 Dan 1 Jason 10 Michael 5 Adam 1 Jason 13 Jacek 4 Davey 1 Jason 20 Victor 1 Jason

I guess I'll start with the location based implementation first and work my way towards a more advanced approach.

This is more of a logic question. I have built a beta version of my site and I am currently working on getting beta V2 out, but I want to implement location based searching of users (particularly with a google api integration). Aside from the API stuff, how do I perform location based searches on the backend. For instance, I have all of my users locations (city, state, country), but how can I say to grab all users by that state when that state is typed in or grab all users by that city when the city is typed in and the same thing for country? Is there anyway to set this up with a default radius too? So if I search for a town, I get a 20 Mile radius result? I really have no idea how to go about the dynamic look up match. Any good tutorials on this?

I'm trying to send my array object through ajax using the jQuery .get method, but when it sends, ids show up as multiple parameters and I'm not sure if that's the way to do it. Here is my code: var val = []; $(':checkbox:checked').each(function(i){ val[i] = $(this).attr('id').substring(6); }); $.get("/assets/ajax/pm_change_status.php", { s: sess_id(), 'ids[]': val } );

I tried double quotes, but I'm still missing something <?php echo ((count($reminders)) > 0) ? "<span id=\"reminder_counter\" class=\"menu_number f_right mrm\"> {count($reminders)}</span>" : ""; ?>

How do I echo out the variable? <?php echo ((count($reminders)) > 0) ? '<span id="reminder_counter" class="menu_number f_right mrm"> {count($reminders)}</span>' : ''; ?>

Yeah, that makes the most sense. Thank you

I need to grab everyones privacy settings because I am outputting a list of user data publicly. The users can choose to opt out of the data. I guess I need to change the function to grab multiple privacy settings, but the logic just doesn't make any sense to me. Please explain

I've found the problem. In another spot in my code I found that I am looping through each users privacy setting which runs a query to check the privacy settings for that specific user. Any way to aggregate privacy settings queries? foreach($users as $k => $user) { $privacy = fetch_user_privacy_settings($users[$k]['id']); }

I have this in my footer <!-- Page Generation Time: <?php echo microtime(true) - $timer_start, "\n"; ?> Max RAM Used: <?php echo (memory_get_peak_usage() / 1024), " KB\n"; ?> Total MySQL Queries: <?php echo mysql_result(mysql_query("SHOW SESSION STATUS LIKE 'Queries'"), 0, 'Value') - $total_queries_start - 2, "\n"; ?> -->

That's odd because when I just print the array I get 130 queries, but then when I remove it I only run 13 queries. Here is what I have which forms 130 queries: <?php include("assets/init.inc.php"); include("assets/header.php"); $type = isset($_GET['type'])? $_GET['type'] : null; $country = isset($_GET['country'])? $_GET['country'] : null; $state = isset($_GET['state'])? $_GET['state'] : null; $page_count = (isset($_GET['page'])) ? (int)$_GET['page'] : 1; $total = fetch_total_users($type, $country, $state); $users = fetch_users($page_count, 20, $type, $country, $state); $hasLeftColumn = true; if($hasLeftColumn === true) { include("assets/menu.php"); } print_array($users); ?>

I have this function and for some reason it is running over 100 queries on page load. I'm not sure what I am doing wrong. Can I please get some guidance on this? <?php function fetch_users($page = 1, $per_page = 20, $type = null, $country = null, $state = null) { $start = (int)(($page - 1) * $per_page); $per_page = (int)$per_page; $type = (int)$type; $country = (int)$country; $state = (int)$state; $sql = "SELECT `users`.`id`, `users`.`firstname`, `users`.`lastname`, `users`.`username`, `users`.`email`, `users`.`gender`, `users`.`accounttype`, `users`.`personalweb`, `users`.`guestviews`, `users`.`iviews`, `users`.`eviews`, `users`.`credentials`, `users`.`specialties`, `users`.`country`, `users`.`city`, `users`.`state`, `users`.`phonenumber`, `users`.`dateofbirth` AS `dob`, `users`.`mail_status`, `users`.`status`, `investor_info`.`investor_type`, DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`, DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`, SUM(`investor_info`.`capital_available`) AS `totalavailable`, `companytype`, `capital` FROM `users` LEFT JOIN `investor_info` ON `users`.`id` = `investor_info`.`uid` LEFT OUTER JOIN employees ON users.id = employees.userid LEFT OUTER JOIN ( SELECT companies.companyid, companies.companytype AS `companytype`, SUM(companies.capital) AS `capital` FROM `companies` GROUP BY companies.companytype) SumCompanies ON employees.companyid = SumCompanies.companyid WHERE `users`.`status` > 2 "; if($type != null) { $acctype = $type - 1; $sql.= "AND`users`.`accounttype` = {$acctype} "; } if($country != null) { $sql.= "AND`users`.`country` = {$country} "; } if($state != null) { $sql.= "AND`users`.`state` = {$state} "; } $sql.= "GROUP BY `users`.`id` ORDER BY `users`.`id` DESC LIMIT {$start}, {$per_page}"; $result = mysql_query($sql) or die(mysql_error()); $users = array(); $i = 0; while($row = mysql_fetch_assoc($result)) { $users[$i] = array( 'id' => $row['id'], 'firstname' => $row['firstname'], 'lastname' => $row['lastname'], 'username' => $row['username'], 'email' => $row['email'], 'gender' => $row['gender'], 'accounttype' => $row['accounttype'], 'guestviews' => $row['guestviews'], 'iviews' => $row['iviews'], 'eviews' => $row['eviews'], 'credentials' => $row['credentials'], 'specialties' => $row['specialties'], 'country' => $row['country'], 'state' => $row['state'], 'city' => $row['city'], 'phonenumber' => $row['phonenumber'], 'dob' => $row['dob'], 'mail_status' => $row['mail_status'], 'status' => $row['status'], 'investor_type' => $row['investor_type'], 'dayofbirth' => $row['dayofbirth'], 'monthofbirth' => $row['monthofbirth'], 'yearofbirth' => $row['yearofbirth'], 'dateofbirth' => $row['dateofbirth'], 'signupdate' => $row['signupdate'], 'totalavailable' => $row['totalavailable'], 'companytype' => $row['companytype'], 'capital' => $row['capital'], ); $i++; } return $users; } ?>

Please explain in more detail. I feel like I have single quotes in the second argument.

Please help me sort out the syntax for this <?php echo '<a href="/u/${user_info["username"]}"><img src="', getUserAvatar($user_info['username']), "\" class=\"avatar f_left small\" title=\"${user_info['display_name']}\" alt=\"${user_info['display_name']}\" /></a>"; ?>

Good point, but my system isn't that dynamic. The user can currently only upload one picture.

That works, but it doesn't work with a limit of 5. I want to limit the SQL when 5 images have been found. Any way to do this?

the getUserAvatar function that I have just get either the users avi or the default if no image exists. I'm not sure if you suggestion fits my goal.

Just a little more background on this... The goal of the function is to get the most recent users with profile pictures uploaded. I am naming the images by id... so uid of 1 will have picture 1.png and so forth.

I was thinking about doing that, but I just didn't want to do the migration. It does seem like it's my easier bet though.

I am writting a php function that uses mysql to get user data - pretty common, right Well, my issue is that I need to run a check in my file system. Users profile pictures are stored in my image directory as .png's. I need to have my function check that directory and if an image matches their id, then return their information. I only want the user data if they have an image uploaded. Here is my current function: function fetch_users_login($limit) { $limit = $limit(int); $sql = "SELECT `users`.`id`, `users`.`firstname`, `users`.`lastname`, `users`.`username`, `user_privacy`.`avatar` FROM `users` LEFT JOIN `user_privacy` ON `users`.`id` = `user_privacy`.`uid` WHERE `users`.`status` > 2 AND `user_privacy`.`avatar` = 1 ORDER BY `users`.`id` DESC LIMIT 0, {$limit}"; $result = mysql_query($sql) or die(mysql_error()); $users = array(); $i = 0; while(($row = mysql_fetch_assoc($result)) !== false) { $users[$i] = array( 'id' => $row['id'], 'firstname' => $row['firstname'], 'lastname' => $row['lastname'], ); $users[$i]['avatar'] = getUserAvatar($row['username']); $i++; } return $users; }

Good catch, thanks.

How can I animate a function and then change the same properties with css? Right now I am using.... $('#wrapper').animate({ scrollTop: $("#arrow_down").offset().top, height: "600px", marginTop: 189 - doc_height + "px"}, 1000, 'easeInOutCirc', remove_btn_down()); Then when the window resizes I want to adjust the margin but these margins keep conflicting and cause the screen to flicker. The animate margin is usually the more dominant force. $(window).resize(function() { window_height = $(window).height(); doc_height = $(document).height(); if($('#arrow_down').hasClass('arrow_up')){ var new_margin = 1171 - doc_height; $('#wrapper').css({marginTop: new_margin + "px"}); } });