<?php
 include("DBconnect.php");
 $u="select count(username) as 'COUNTUSER' from users where username='shikha verma'";
 $result = mysql_query($u);

 while ($row = mysql_fetch_array($result))
 {

       echo $row[COUNTUSER];

 }
          /*if ($value[0]>0)
           {
        die("email already exist");
           }*/
?>
        the code inside the comment is not working please give me suggetions

$value is undefined. You need to assign it a value before you can evaluate it.

actually i want to store the value of "$row[COUNTUSER];" in a variablle to validate if the user already exist or not

<?php
include("DBconnect.php");
$u="select * from users where username='shikha verma'";
$result = mysql_query($u);
if( mysql_num_rows($result) > 0 ) {
    die("username found");
}
?>

Then if you actually need the user's info, you can use mysql_fetch_array().
Please note:

  1. The mysql extension is deprecated, consider moving to the mysqli extentsion
  2. You will need to replace shikha verma with a variable containing the username you want to check for.

EvolutionFallen, that's the worst way possible to accomplish this. You are fetching all the data from the user row just to count how many rows there are, never actually needing any of the data. It's a waste of resources. Shikha_1 had it right the first time; you are better of letting MySQL do the counting and then return that count to PHP.

actually i want to store the value of "$row[COUNTUSER];" in a variablle to validate if the user already exist or not

You don't need to store it in a variable. The $row['COUNTUSER'] array element already contains the count. Why copy it into a variable when it's already available right there?

Actually, since you are using the COUNT function in the SQL without a GROUP BY clause, there will only ever be one row in the set. There is no need to use a loop to go throug a set that can only ever contain exactly one row.

I would suggest something more like this:

<?php
include("DBconnect.php");

// Note that since you are only fetching a single column in a single row,
// there is really no need to name it. You can just return it based on
// the index number. (See below.)
$sql = "SELECT COUNT(username) FROM users WHERE username='shikha verma'";
$result = mysql_query($sql);

// Alwasy verify the result of a mysql query before using it!
if ($result) {
    // Fetch the number in the first column of the only row in 
    // the result set.
    $row = mysql_fetch_row($result);
    $user_count = (int)$row[0];

    // And finally check the number.
    if ($user_count == 1) {
        echo "User exists.";
    }
    else {
        echo "User does not exist.";
    }
}
else {
    // The MySQL query must have failed, so you'd want to stop with an errror.
    // A lot of people would use "die()" for this, but it's an inferior method.
    trigger_error("Failed to verify user existance: " . mysql_error(), E_USER_ERROR);
}

Like EvolutionFallen says, though, the old mysql_* functions are deprecated. You'd do well to move on to the MySLQi (Improved MySQL extension) or PDO. My suggestion would be PDO, since it's a common API for all the databases, not just MySQL. Experience with PDO will benefit you later if you need to use other database systems.

EvolutionFallen's approach has one advantage. The result already contains the data for the user in question so if you need it you do not have to shoot another query. The * in the query should be replaced with the fields you really need.

Atli's approach is slightly more optimized but I believe the difference is negligible.

And yes, try to avoid using mysql extension since it is going to be deprecated in PHP 5.5. You can replace all occurences of mysql with mysqli and everything will work just fine. But PDO in my opinion is what you should go for ASAP.

Granted, if you actually need the data you should of course fetch it, but it makes very little sense to be fetching data you don't need.

Normally it's probably not a great difference, but I've seen the method EvolutionFallen suggested used on tables with hundreds of fields, including huge text fields, and in such cases the difference will definitely matter. There is no way to know the impact without that info.

Both methods are simple to use. There is no reason not to use the more optimized one.

Both methods are simple to use. There is no reason not to use the more optimized one.

Agree.

Looking at this again I agree, Atli. Not sure what I was thinking, as it seems rather clear we won't be needing the user info later. Good post, thanks.

Member Avatar for diafol

This looks as though the query is for checking whether an user with that name already exists. Why should there be more than one user with the same username? I suppose you could, but this field is usually unique. Therefore, you probably don't even need a count, just a LIMIT 1.

$u="select username from users where username='shikha verma' LIMIT 1";

That should stop the query from checking the whole table. As soon as it gets one result - stop and return. What do you think? Faster or not?

That's a good point. If the field is unique, that may be preferable. - Two thoughts though.

First, if the the username is indeed unique, then it'll be indexed. The limit wouldn't really be
necessary in that case; MySQL isn't scanning the table, only the index, which is extremely fast and ordered in such a way that it would stop after finding the first value either way.

Second, my point about returning unnecessary data would still hold. You could just do SELECT TRUE FROM ... instead of fetching the username back. - A very minor concern, that's true, but I just really really hate queries that fetch unnecessary data! :)

Member Avatar for diafol

I never thought of SELECT TRUE. I'll do some tests :) Thanks for the idea.

php is mostly use for making dynamic websites..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.