count all the values from the database

Question

  <?php
    session_start();
    include "config.php";
    if(isset($_POST['sub'])) {
        if(!empty($_POST["column"])) {
              $a = $_POST['column'];  // column name 

    } else {
              $a ="";
              echo "please click on the right input";
    }
    $r="select '".$a."' , count(*) as '".$a."' from emc_leadgen group by '".$a."' ";
    $t=mysql_query($r);
    if (!$t) 
    {
        $message = 'ERROR:' . mysql_error();
        return $message;
    }
    else
    {
        $i = 0;
        ?>
    <html xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns="http://www.w3.org/TR/REC-html40">
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html>
    <head>
    WELCOME <?=$_SESSION['user']?></br>
    <title> emc promo 1</title>
    <meta http-equiv="Content-type" content="text/html;charset=utf-8" />
    </head>
    <body>
    <?PHP
        echo '<html><body><table><tr>';
        while ($i < mysql_num_fields($t))
        {
            $meta = mysql_fetch_field($t);
            echo '<td>' . $meta->name . '</td>';
            $i = $i + 1;
        }
        echo '</tr>';

        $i = 0;
        while ($row = mysql_fetch_row($t)) 
        {
            echo '<tr>';
            $count = count($row);
            echo $count;
            $y = 0;
            while ($y < $count)
            {
                $c_row = current($row);
                echo '<td>' . $c_row . '</td>';
                echo "</br>";
                next($row);
                $y = $y + 1;
            }
            echo '</tr>';
            $i = $i + 1;
        }
        echo '</table></body></html>';
        mysql_free_result($t);
    }

    }{echo "there is no data";}*/
    }
    ?``>
    </body>
    </html>

// I am using COUNT in my query .........In my result I am getting an array format but I need unique value from the database .It will count the no of values from the database and then show it like i have written below i need this result new delhi=4 ranchi=2 faridabad=3 but I am getting city = 9 //

Please be sure to update your code to use parametrized queries to prevent SQL injection attacks. http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — Dark Falcon, Jun 05 '13 at 14:01
It would be great to see your database structure and to know what result you are precisely expecting — Brewal, Jun 05 '13 at 14:40

Ryank · Accepted Answer · 2013-06-10T10:11:48.273

0

Can you put the database structure to get a better understanding of the data.

If I am thinking right you are wanting to get the row number of which a value sits in the database which this should work:

SELECT a.name, @curRow := @curRow + 1 AS row_number FROM contacts a JOIN (SELECT @curRow := 0) r

This would output a column called 'row_number' with the actual row number - (I have just used a local database I have running to try it out)

EDIT:

This query will return the city and a user count allocated with that city:

SELECT city.name, COUNT(*) AS user_count FROM users INNER JOIN city ON users.city = city.id GROUP BY city

I did a test as follows:

$conn = mysqli_connect('localhost', 'root', 'root', 'test');
$query = mysqli_query($conn, "SELECT city.name, COUNT(*) AS user_count FROM users INNER JOIN city ON users.city = city.id GROUP BY city");
print '<ul>';
while($row = mysqli_fetch_array($query)):
    print '<li>'.$row['name'].' - Users = '.$row['user_count'].'</li>';
endwhile;
print '</ul>';

This output the following:

London - Users = 1
Southampton - Users = 3
Manchester - Users = 2

edited Jun 10 '13 at 10:11

answered Jun 05 '13 at 15:18

Ryank

507
2
6

NO NO , I want to know how many times the value is coming in the column............for eg In a database there is a column name (city)...........there are different types of user from different cities like new delhi , ranchi , faridabad gurgoan , noida etc ...............i want to know how many users are coming from new delhi , ranchi , faridabad , gurgoan etc...............it will show me the result like this ...........new delhi=4 , ranchi=1, faridabaad=0 and gurgoan=2....................i dont want the ROW NO – PHP_USER1 Jun 06 '13 at 05:54
I want to know one more thing , this question is not related to my previous question .....................i am using a function to fetch my data ..........it is in arrray format ..........and i am using FOREACH to get the values and the column name ............so my problem is this HOW CAN I CHANGE MY ARRAY FORMAT IN TABULAR FORMAT .................So i want column name first and then their values in respective fields – PHP_USER1 Jun 06 '13 at 06:11
please rply to my question ...........as a inetern i very much need your help .................... – PHP_USER1 Jun 10 '13 at 05:15
With regards to your question about array to tabular data try this http://stackoverflow.com/questions/4529965/how-to-convert-an-array-to-a-html-table – Ryank Jun 10 '13 at 10:15
thanks for the above ans ..........but i want to ask one more question ...................i am pasing a row[id] using hidden form ....... – PHP_USER1 Jun 14 '13 at 09:11

count all the values from the database

1 Answers1