Performing multiple mysql queries in one call

Refresh

December 2018

Views

43 time

1

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'].

2 answers

1

Вы можете использовать группу,

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

Таким образом, вы получаете строку для каждого значения лица

2

Моя версия очень похожа на @ scaisEdge годов. На самом деле, если нет больше категорий , чем single, marriedи divorced, то WHEREпункт не требуется.

$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 может помочь вам получить то , что вам нужно. http://php.net/manual/en/mysqli-result.fetch-all.php ;)

Обновить:

То, что я хотел бы сделать в JS нечто похожее на это:

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
    }
} )
Смотреть фильмы онлайн бабушка легкого поведения 2 фильм бесплатно