Jump to content

unemployment

Members
 View Profile  See their activity

  • Posts

    746

  • Joined

  • Last visited

 Content Type 

Posts posted by unemployment

    MySQL Join Returns 1 Null Row

    in MySQL Help

    Posted

    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

    MySQL Join Returns 1 Null Row

    in MySQL Help

    Posted

    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

    People You May Know Feature - Need Only Friends Friends

    in MySQL Help

    Posted

    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

     

    How to Perform Location Based Searches

    in PHP Coding Help

    Posted

    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?

    Send Array Object to jQuery Get

    in Javascript Help

    Posted

    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 } );

    Syntax Error

    in PHP Coding Help

    Posted

    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>" : ""; ?>

    Function Runs 100 Queries - Need This Reduced!

    in PHP Coding Help

    Posted

    Sounds to me like rather than grab a list of users, then loop that and get privcy settings you should work that into your initial query for the list of users.  Not sure how your tables are setup, but something like this is what i mean:

    SELECT
   * --the fields you need instead 
FROM users u
INNER JOIN privcy_settings ps ON ps.UserId=u.UserId
WHERE
   ps.VisibleToPublic=1

     

    A query that returns your list of users already filtered by whether they are visible or not.

     

    Yeah, that makes the most sense.  Thank you

    Function Runs 100 Queries - Need This Reduced!

    in PHP Coding Help

    Posted

    The first question would be, why do you need the privacy settings for all the users?

     

    If you do need all the privacy settings for all the users, you would not use a function that gets the settings one at a time, but a function that gets all the privacy settings at once. If you ever find yourself performing a SELECT query inside of a loop, there's probably something wrong with what you are doing.

     

    P.S. The SESSION keyword in the SHOW ... STATUS query should get the query count for the current connection.

     

    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

    Function Runs 100 Queries - Need This Reduced!

    in PHP Coding Help

    Posted

    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']);
}

    Function Runs 100 Queries - Need This Reduced!

    in PHP Coding Help

    Posted

    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"; ?>

-->

    Function Runs 100 Queries - Need This Reduced!

    in PHP Coding Help

    Posted

    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);

?>

    Function Runs 100 Queries - Need This Reduced!

    in PHP Coding Help

    Posted

    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;
}

?>

    Formatting Issue

    in PHP Coding Help

    Posted

    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>"; 

?>

    Reference Stored Image

    in PHP Coding Help

    Posted

    What will happen if the user has 5 images? All of those images will be named 1.jpg? 1.jpg, 1.jpg and so on...

     

    I still suggest my approach

    I would suggest using a table specifically for images. 

     

    Table user_images:

    imageid

    userid

    image_name

    image_path

    image_thumb

    image_default (set it to 0 or 1, 1 = profile image, 0 = just an image for that user)

     

    Then, when you look for users with images, then you can look up this table, and join the users table with the userid.

     

    This should make it a lot simpler to handle the images of each user.

     

     

     

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

    Reference Stored Image

    in PHP Coding Help

    Posted

    would unsetting that user if no file exists work?

     

    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']);

	 if (!file_exists($users[$i]['avatar'])) {
                 unset($users[$i]);
                 }


	$i++;
}

return $users;
}

     

    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?

    Reference Stored Image

    in PHP Coding Help

    Posted

    I see you have a function called getUserAvatar($row['username'])

     

    In that function I would just do a check if the image exists is their value, else display a default user image that you create

    To me this reminds them every day that they never uploaded an image.

    something like...

    $user_image_location = "/images/$username.png";
if (file_exists($user_image_location)) {
    $user_avatar = $user_image_location;
} else {
    $user_avatar = "/images/default-user-avatar.png";
}

     

    The way I do this all myself is that i just pull the users table and all info.

    I have a user_avatar field, at user creation I insert my default-user-avatar.png as their image

    If they decide to upload their own, their new image name is updated in the database

     

    or can leave the field empty and if not empty and file_exists....display their image

     

    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.

    Reference Stored Image

    in PHP Coding Help

    Posted

    how are you currently naming/identifying a picture as belonging to a particular user?

     

    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. 

     

     

    Reference Stored Image

    in PHP Coding Help

    Posted

    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;
}

    Animate then Change CSS - jQuery

    in Javascript Help

    Posted

    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"});
}
});

     

×
×
  • 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.