unemployment

January 22, 2012

In my ajax php file for my messaging system I’m trying to get my pagination to work properly when sending an ajax request. I don’t know how to do it though. I’m trying to pass through the HTML generated from the php class into a JSON encoded array object. When I do this, I get a JS error.

Here is the code below:

if (isset($user_info['uid'], $_GET['id'], $_GET['page'], $_GET['s']) && $_GET['s'] === session_id())
{	
delete_conversation($_GET['id']);

$total 					= fetch_total_conversations();
$conversations 			= fetch_conversations($_GET['page'], 20);

foreach($conversations as $k => $r)
{ 
	$conversations[$k]['last_message'] = time_since_tstamp($r['last_message']).' ago';
}

$pages = new Paginator();  
$pages->items_total = $total;  
$pages->mid_range = 9;  
$pages->paginate(20);

$conversations['pagination'] = $pages->display_pages();

//print_array($conversations);
echo json_encode($conversations);
}

January 16, 2012

I created this function to update my tour system. The query is working and is updating one row in the table, but I get a resource boolean error in the return section of the function. Any idea why?

function update_tour($uid, $step)
{
$step 	= (int)$step;
$uid	= (int)$uid;

$sql =	"UPDATE `users`
		SET
			`tour_step` = ${step}
		WHERE `id` = '${uid}'";

$q = mysql_query($sql) or die(mysql_error());

return (mysql_num_rows($q) === 1) ? mysql_result($q, 0): false;
}

January 13, 2012

I'm currently making a product tour. I am rewriting the url with javascript and sending the user to their profile pages.

example:

http://dev.mysite.com/u/jason?welcome

I then use php to load my javascript via a get variable.

if (isset($_GET['welcome']))
{
echo 'test';
?>
<script type="text/javascript">
$(function(){
	focus.set('FOCUSTHIS', 0, 'absolute', 'Personal Information', function (body){
		$('<div class="test">.</div><div class="center ptm pbs"><a id="next_4" class="button" href="javascript: void(0)">Continue</a></div>').appendTo(body);
	});
});
</script>
<?php
}

Any reason why this wouldn't at least echo properly? I am rewriting the URLs with mod rewrite.

January 10, 2012

KICKSTART! Why are you so good at reading my mind? You're the best. Thanks again.

January 9, 2012

I fixed this query. It's working now since I guess all I had to do was change the group by column, but I still want to add in another subquery. Here is the final version so far:

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.companyid) SumCompanies
ON employees.companyid = SumCompanies.companyidz
WHERE `users`.`status` > 2

I need to add in:

SELECT COUNT(`partner_id`) FROM `partners` WHERE (`user_id` = ${uid} OR `friend_id` = ${uid}) AND `approved` = 1

When I made this query I wrote it to work with the variables specified. I tried adding this in as a subquery, but can't seem to get it to work. I don't want the variables in the subquery. I just want them to group for every user.

January 9, 2012

got it. I had to urlencode only the variables.

January 9, 2012

it should work if you wrap your strings thus:

$location_request 	= "http://maps.google.com/maps/api/geocode/json?address='$city',+'$real_state',+'$real_country'&sensor=false";

It's still not working. I get the same error. Any other thoughts?

January 9, 2012

I am trying to use the google maps to geocode a location but I get this error:

Warning: file_get_contents(http://maps.google.com/maps/api/geocode/json?address=New London,+Connecticut,+United States&sensor=false): failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request

$location_request 	= "http://maps.google.com/maps/api/geocode/json?address={$city},+{$real_state},+{$real_country}&sensor=false";
$geocode 	        = file_get_contents($location_request);

What am I doing wrong? I tried URL encoding the location request but that also didn't work.

January 9, 2012

I realized that I needed to use delegated events but I now have event bubbling. If I open the window 3 times and click the next button respectively, on the forth time when I click the next button the next event will fire four times. How do I stop this?

                                 $("#google_map_canvas").on("click", "#next", function() {	
				var toHighlight = $('.first').next().length > 0 ? $('.first').next() : $('#infoWin li').first();
				$(this).fadeOut(100);
				$('.first').fadeOut(100);
				$('.first').delay(100).removeClass('first');
				toHighlight.delay(100).addClass('first');
				$('.first').delay(100).fadeIn(100);
				$(this).delay(100).fadeIn(100);
			});

January 9, 2012

Yes, you will need to add companies to the FROM clause because the way it stands just now you arn't actualy refferencing it in the main SELECT. You are using it in a sub query, and that subquery is providing a specific result set that the main SELECT refferences using the aliases that you have given it as though it was a table it's self. the main Query does not get direct access to the tables used in the subquery as it is run independant of the main query.

Ok I sort of understand. Now I'm getting an error that says "Subquery returns more than 1 row". Any ideas?

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
SUM(
 CASE WHEN (
  SELECT employees.userid 
  FROM employees 
	INNER JOIN users 
	ON employees.userid = users.id 
	INNER JOIN companies 
	ON employees.companyid = companies.companyid 
  WHERE companies.companyid = employees.companyid AND users.id = employees.userid) 
 THEN (
  SELECT companies.capital
          FROM companies 
         )
 ELSE 
  0 
 END) 
AS `totalavailable`) SumCompanies
ON employees.companyid = SumCompanies.companyid
WHERE `users`.`status` > 2

January 8, 2012

I made some more edits and added in the SELECT, but I still get #1054 - Unknown column 'companies.capital' in 'field list'

I don't understand this because capital is a column in the companies table. Does it need a FROM clause?

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
SUM(
 CASE WHEN (
  SELECT employees.userid 
  FROM employees 
	INNER JOIN users 
	ON employees.userid = users.id 
	INNER JOIN companies 
	ON employees.companyid = companies.companyid 
  WHERE companies.companyid = employees.companyid AND users.id = employees.userid) 
 THEN (
  SELECT companies.capital)
 ELSE 
  0 
 END) 
AS `totalavailable`) SumCompanies
ON employees.companyid = SumCompanies.companyid
WHERE `users`.`status` > 2

January 8, 2012

That syntax error is due to a missing "SELECT'

Where? I don't know what you mean.

January 6, 2012

Adam,

I created a public link where you can see what is going on http://pitchbig.com/people2.php

Click on a cluster in the map and then click the next button in the info window. You'll see that if you open the window twice the 'next' event won't fire. If you view my people2.js file you'll see the $(#next).on about half way down the page. This still doesn't work and I'm not sure what to do. Please help.

January 6, 2012

If I can't figure it out I'll post in this thread with a public link.

January 6, 2012

here is the mock-up of a possible solution (tested)... replace/add your table field names and aliases

SELECT a.uid, x.uname, a.fid, y.uname, b.fid, z.uname
  FROM partners a
  JOIN partners b ON a.fid = b.uid  AND b.fid NOT IN ( SELECT m.fid
                                                         FROM partners m
                                                              JOIN partners n ON m.fid = n.uid
                                                         WHERE m.uid =1)
  JOIN users x ON a.uid = x.uid
  JOIN users y ON a.fid = y.uid
  JOIN users z ON b.fid = z.uid
WHERE a.uid =1;

Note:

uid = user_id

uname = users.firstname or users.lastname // add fields as you need

fid = friend_id

The output of that query (using your data as example) is this

1, 'Jason', 2, 'Chelsea',   6, 'Jim'
1, 'Jason', 2, 'Chelsea', 12, 'Peter'
1, 'Jason', 2, 'Chelsea', 12, 'Cameron'
1, 'Jason', 2, 'Chelsea', 38, 'Felicia'
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', 20, 'Victor',   1, 'Jason'

the last record (yourself) could be eliminated easily adding the condition to the last where

So... I am much closer now with the code you provided. I put in the appropriate column names below. The problem with your output above is that Jeffrey shows up TWICE in the last column. I need the column to exclude duplicates. Then I also need to replicate this code for if my id was the friend_id and create a union for both queries. One more question: Why am I included in the last row? How do I remove that in the WHERE clause? Hopefully we can sort this out soon. Thanks.

SELECT a.user_id, x.username, a.friend_id, y.username, b.friend_id, z.username
  FROM partners a
  JOIN partners b ON a.friend_id = b.user_id  AND b.friend_id NOT IN ( SELECT m.friend_id
                                                         FROM partners m
                                                              JOIN partners n ON m.friend_id = n.user_id
                                                         WHERE m.user_id = 1)
  JOIN users x ON a.user_id = x.id
  JOIN users y ON a.friend_id = y.id
  JOIN users z ON b.friend_id = z.id
WHERE a.user_id =1;

January 5, 2012

I'm not sure I follow this. You have uname multiple times in the select. I'll give it a shot though.

January 5, 2012

What on earth is that bizarre case statement doing?

The goal of this is to only sum the column where the

user.id that is linked to employees.userid that is linked to employees.companyid that is linked to companies.companyid will then sum the companies.capital column.

It doesn't work though. You'll see in the first example I was tried to get the sum of capital column. The problem was that it was summing the entire capital column in the companies table and I only want it to sum the capital that is available to the specific employee / user. The user can have multiple companies.

January 5, 2012

To get your friend's friends, you'll need to join twice -- do you have this query working?

Well the first query I posted will grab my friends friends. I just need to compare those against my friends and exclude my friends.

January 5, 2012

Perhaps you need NOT EXISTS.

I'll look into it. I also tried a few other combinations but all I got were my friends and not my friends friends.

January 5, 2012

That's because the click event is only bound to the existing #next element. When you regenerate the HTML it's a brand new element, just with the same ID. Look into the live() method (or on() if you're using jQuery 1.7) that will bind the event to any current or future element(s) matching the selector.

I upgrade to version 1.7 but the .on() doesn't seem to work. I tried using the .live() but the event kept bubbling and flashing on my screen like crazy.

Here is the code I tried:

$("#next").on("click", function(){

var toHighlight = $('.first').next().length > 0 ? $('.first').next() : $('#infoWin li').first();

$(this).fadeOut(100);

$('.first').fadeOut(100);

$('.first').delay(100).removeClass('first');

toHighlight.delay(100).addClass('first');

$('.first').delay(100).fadeIn(100);

$(this).delay(100).fadeIn(100);

});

Any other thoughts?

January 5, 2012

not sure how deep you can nest IN's, but this should work (in theory):

SELECT firstname, lastname 
FROM users 
LEFT JOIN partners ON users.id = patrners.user_id 
WHERE (users.id IN (SELECT frend_id FROM partners WHERE user_id IN (SELECT friend_id FROM partners where user_id = 1)))
AND 
(users.id NOT IN (SELECT friend_id FROM partners WHERE user_id = 1))

This unfortunately doesn't work. I just get an awkward list of my username over and over with a list of a few other peoples which surely this can't be correct. I also don't think the NOT IN in the where clause makes sense because there is another side to this. Your id can also be the friend_id. Your id is the user_id in the partners table if you sent the request. If you are receiving the request then your id is the friend_id. Any other thoughts?

January 5, 2012

Here is the SQL. I've made some modification though they still aren't quite right. I really do appreciate the help, but I just wish I could get it working. I wasn't sure how to adjust the VALUE in the case statement. Any thoughts?

Here is my error: #1064 - 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 'SUM( CASE WHEN ( SELECT employees.userid FROM employees ' at line 46

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 (
			SUM(
			 CASE WHEN (
			  SELECT employees.userid 
			  FROM employees 
				INNER JOIN users 
				ON employees.userid = users.id 
				INNER JOIN companies 
				ON employees.companyid = companies.companyid 
			  WHERE company.companyid = employees.companyid AND users.id = employee.userid) 
			 THEN (
			  SELECT `company.capital`)
			 ELSE 
			  0 
			 END) 
			AS `totalavailable`) SumCompanies
            ON employees.companyid = SumCompanies.companyid
		WHERE `users`.`status` > 2

January 5, 2012

I am integrating the google maps api into my application on the map it has an info window that will display information about a marker / cluster in that location. My problem is that when the map loads, the $('next') click event will only fire once. Once I close the info window and open another info window the $('next') click event won't fire. I find this to be odd because both times I am creating and destroying the next link element. What am I doing wrong?

google.maps.event.addListener(markerCluster, 'clusterclick', function(cluster) {
var markers = cluster.getMarkers();
var html2 = '';
  
html2 += '<div id="infoWin">';
html2 += '<ul class="addrlist">';

for (var i=0; i < markers.length; i++){
	if(i > 0){
		html2 += '<li style="display: none;">' + markers[i].html + '</li>';
	}else{
		html2 += '<li class="first">' + markers[i].html + '</li>';
	}

}

html2 += '</ul>';
html2 += '<div style="position: relative; bottom: 0;"><a id="next" class="f_right pts" href="javascript: void(0)">Next</a></div>';
html2 += '</div>';

infowindow.setContent(html2);
infowindow.open(map, markers[0]);

$('#infoWin').parent().css({overflow: 'hidden'});

$('ul.addrlist li').click(function() {
	var p = $(this).find("a").attr("rel");
	return infopop(markers[p]);
}); 

$('#next').click(function() {
	console.log('test');
	var toHighlight = $('.first').next().length > 0 ? $('.first').next() : $('#infoWin li').first();
	$(this).fadeOut(100);
	$('.first').fadeOut(100);
	$('.first').delay(100).removeClass('first');
	toHighlight.delay(100).addClass('first');
	$('.first').delay(100).fadeIn(100);
	$(this).delay(100).fadeIn(100);
});
});

January 4, 2012

changing this line :
SUM(`investor_info`.`capital_available`) AS `totalavailable`,
To incorporate a CASE like this:
SUM(CASE WHEN SELECT `check_criteria`THEN SELECT `capital_field.value`ELSE 0 END) AS `totalavailable`,
Should get the result you're after.

Why would I need to check criteria?

In three tables there are...

Users

user_id

Employees

id user_id company_id

Companies

company_id

I just need to join those so that I only total the amount of capital where the user is employed by that company.

January 4, 2012

And next time, don't start another thread.

I only started a different thread because I saw them as two separate problems. I'll look into the case statement though I've never used them before.

Sign In

unemployment

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by unemployment

PHP/Ajax For Messaging System

Resource Boolean Error

GET Not Working

MySQL Join Returns 1 Null Row

MySQL Join Returns 1 Null Row

file_get_contents failed to open stream

file_get_contents failed to open stream

file_get_contents failed to open stream

Click Event Only Fires Once

MySQL Join Returns 1 Null Row

MySQL Join Returns 1 Null Row

MySQL Join Returns 1 Null Row

Click Event Only Fires Once

Click Event Only Fires Once

People You May Know Feature - Need Only Friends Friends

People You May Know Feature - Need Only Friends Friends

MySQL Join Returns 1 Null Row

People You May Know Feature - Need Only Friends Friends

People You May Know Feature - Need Only Friends Friends

Click Event Only Fires Once

People You May Know Feature - Need Only Friends Friends

MySQL Join Returns 1 Null Row

Click Event Only Fires Once

MySQL Join Returns 1 Null Row

MySQL Join Returns 1 Null Row

Browse

Activity

Important Information