CODEWITHSUNDEEP
mysql
Jason Chen
Jason Chen

Reputation: 2577

Performing multiple mysql queries in one call

I would like to understand how to retrieve multiple mysql values in one call. As is, I have the current query, which works. 

SELECT COUNT(*) AS cnt, 
        (3595 * 
            acos(
                cos(radians({$lat1}))
                * cos(radians(lattitude))
                * cos(radians(longitude) - radians({$lon1}))
                + sin(radians({$lat1}))
                * sin(radians(lattitude))
            )
        ) AS distance
        FROM members WHERE person = 'single'
        HAVING distance < 50
    );

This is later called by my PHP like so:

$single = $row['cnt'];

echo json_encode( array(
    'single' => $single
) );

My hypothetical table is comprised of people who are single, married, and divorced. In this example I have only performed a query for people who are single. I would like to know how I might modify my query so as to perform the same request for all these groups using the same mySQl query. Particularly using the json encode function.

One example I have seen relies upon multiple separate SELECT statements, combined with multiple instances of $row['cnt'].

Upvotes: 1

Views: 62

Answers (2)

tiomno
tiomno

Reputation: 2228

My version, very similar to @scaisEdge's. Actually, if there are no more categories than single, married and divorced, the WHERE clause is not needed.

$conn = new mysqli( $host, $user, $pass, $db );

$result = $conn->query( "
    SELECT
        COUNT(*) AS cnt, 
        (3595 * 
            acos(
                cos(radians({$lat1}))
                * cos(radians(lattitude))
                * cos(radians(longitude) - radians({$lon1}))
                + sin(radians({$lat1}))
                * sin(radians(lattitude))
            )
        ) AS distance,
        person -- <-- this gives you the category in the result
    FROM
        members 
    WHERE
        person in ('single', 'divorced', 'married')
    GROUP BY
        person
    HAVING
        distance < 50
" );

$rows =  $result->fetch_all( MYSQLI_ASSOC );

echo json_encode( $rows );

mysqli_result::fetch_all can help you to get what you need. http://php.net/manual/en/mysqli-result.fetch-all.php ;)

Update:

What I'd do in JS is something similar to this:

data.forEach( function( item )
{
    switch ( item.person )
    {
        case 'single':
            $( "#count_single" ).html( item.cnt )
            break

        case 'married':
            $( "#count_married" ).html( item.cnt )
            break

        case 'divorced':
            $( "#count_divorced" ).html( item.cnt )
            break
    }
} )

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

You could use group by 

SELECT person, COUNT(*) AS cnt, 
        (3595 * 
            acos(
                cos(radians({$lat1}))
                * cos(radians(lattitude))
                * cos(radians(longitude) - radians({$lon1}))
                + sin(radians({$lat1}))
                * sin(radians(lattitude))
            )
        ) AS distance
        FROM members 
        GROUP By person
        HAVING distance < 50
    );

In this way you get a row for each person value

Upvotes: 1

Related Questions