Asked  7 Months ago    Answers:  5   Viewed   39 times

I am able to get both the value and row of the mysql query result.

But I am struggling to get the single output of a query. e.g.:

$result = mysql_query("SELECT COUNT(*) FROM Students;");

I need the result to display. But I am not getting the result.

I have tried with the following methods:

  1. mysql_fetch_assoc()
  2. mysql_free_result()
  3. mysql_fetch_row()

But I didn't succeed to display (get) the actual value.

 Answers

82

You need to alias the aggregate using the as keyword in order to call it from mysql_fetch_assoc

$result=mysql_query("SELECT count(*) as total from Students");
$data=mysql_fetch_assoc($result);
echo $data['total'];
Wednesday, March 31, 2021
 
Noob_Programmer
answered 7 Months ago
40

from http://www.php.net/manual/en/function.mysql-fetch-assoc.php:

Returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows.

(emphasis mine) It's probably just easier for the MySQL driver to give you everything as a string, and assume you will know what to do with it. Just use intval() to get the integer value.

Wednesday, March 31, 2021
 
codingb
answered 7 Months ago
25

It was really really awesome http://www.phpsimplicity.com/tips.php?id=1

it is so simple! no need to work with huge classes! I'm Happy:D

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Paginate</title>
    </head>
    <body>
    <form method='get'>
        <?php
        $connection = Mysql_connect( 'server', 'user', 'pass' );
        if ( ! $connection ) {
            echo 'connection is invalid';
        } else {
            Mysql_select_db( 'DB', $connection );

        }
        //Check if the starting row variable was passed in the URL or not
        if ( ! isset( $_GET['startrow'] ) or ! is_numeric( $_GET['startrow'] ) ) {

            //We give the value of the starting row to 0 because nothing was found in URL
            $startrow = 0;

            //Otherwise we take the value from the URL
        } else {
            $startrow = (int) $_GET['startrow'];
        }
        //This part goes after the checking of the $_GET var
        $fetch = mysql_query( "SELECT * FROM sample LIMIT $startrow, 10" ) or
        die( mysql_error() );
        $num = Mysql_num_rows( $fetch );
        if ( $num > 0 ) {
            echo '
        <table border=2>';
            echo '
            <tr>
                <td>ID</td>
                <td>Drug</td>
                <td>quantity</td>
            </tr>
            ';
            for ( $i = 0; $i < $num; $i ++ ) {
                $row = mysql_fetch_row( $fetch );
                echo '
            <tr>';
                echo "
                <td>$row[0]</td>
                ";
                echo "
                <td>$row[1]</td>
                ";
                echo "
                <td>$row[2]</td>
                ";
                echo '
            </tr>
            ';
            }//for
            echo '
        </table>
        ';
        }

        //Now this is the link..
        echo '<a href="' . $_SERVER['PHP_SELF'] . '?startrow=' . ( $startrow + 10 ) . '">Next</a>';

        $prev = $startrow - 10;

        //only print a "Previous" link if a "Next" was clicked
        if ( $prev >= 0 ) {
            echo '<a href="' . $_SERVER['PHP_SELF'] . '?startrow=' . $prev . '">Previous</a>';
        }
        ?>
    </form>
    </body>
</html>

By the way link of rickyduck was good too

Saturday, May 29, 2021
 
Lorav
answered 5 Months ago
91
SELECT  r.*, COUNT(v.record_id)
FROM    records r
LEFT JOIN
        views v
ON      v.record_id = r.record_id
WHERE   r.keyword = 'stack'
GROUP BY
        r.record_id

Create the following indexes:

records (keyword, record_id)
views (record_id)

for this to work fast.

Saturday, May 29, 2021
 
Kevin_Kinsey
answered 5 Months ago
42

First of all you should use count because of speed issues:

$sql = "SELECT COUNT(id) FROM persons";

To write a function that returns the number, you can do something like

function registredMemberCount ($connection) 
{
    $sql = "SELECT COUNT(id) FROM persons";
    $result = mysqli_query($connection,$sql);
    $rows = mysqli_fetch_row($result);
    return $rows[0];
}

and call it with

registredMemberCount($connection);
Sunday, August 15, 2021
 
octern
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share