Jump to content
phpsane

Counting Number of Rows Via COUNT() And Not num_rows

Recommended Posts

Folks,

Having trouble here. Forgot how you use the COUNT. Not interested in the num_rows due to it being slow compared to COUNT.

I have users table like this:

id|username|sponsor_username

0|barand|requinix

1|phpsane|alison

2|mickey_mouse|requinix

Now, I want to check if a sponsor username exists or not. Such as does the entry "requinix" exists or not in one of the rows in the "sponsor_username" column.

In our example it does twice and so the variable $sponsor_username_count should hold the value "2". Else hold "0".

I get error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1 

 

My code:

	$stmt_1 = mysqli_prepare($conn,"SELECT COUNT(sponsor_username) from users = ?"); 
            mysqli_stmt_bind_param($stmt_1,'s',$sponsor_username_count); 
            mysqli_stmt_execute($stmt_1); 
            
            //Show error if 'users' tbl was not successfully queried". 
            if (!$stmt_1) 
            { 
                echo "ERROR 1: Sorry! Our system is currently experiencing a problem loading this page!"; 
                exit(); 
            } 
            else 
            { 
                mysqli_stmt_bind_result($stmt_1,$sponsor_username_count);     
                mysqli_stmt_fetch($stmt_1); 
                mysqli_stmt_close($stmt_1); 
                
                //Check if Sponsor Username in Url ($_GET) is already registered or not. If not then show "Invalid Url" or Link message. 
                if ($sponsor_username_count < 1) 
                { 
                    echo "<b>Invalid Url or Link!<br> Invalid Sponsor: \"$sponsor_username\"!</b><?php "; 
                    exit(); 
                } 
            } 
	

Share this post


Link to post
Share on other sites

This seems to be working but I need you folks to check it out:

	<?php 
//Check for username match in "Usernames" column in "users"    table. If there is a match then do the following ...
        $stmt = mysqli_prepare($conn, "SELECT COUNT(sponsor_username) FROM users WHERE sponsor_username = ?");
        mysqli_stmt_bind_param($stmt, 's', $sponsor_username);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $rows);
        if (mysqli_stmt_fetch($stmt) && $rows) 
        {
            die('That Username '.htmlspecialchars($sponsor_username).' is already registered!');
        }
?>
	

Ok, so I did it like this, the query:
SELECT COUNT(sponsor_username) FROM users WHERE sponsor_username = ?

Now, if I do it like this by adding the "AS sponsor count" then how do I make use of the alias "sponsor count" ? Forgotten how to make use of the alias. So, you may now keep busy showing me a sample code.

SELECT COUNT(sponsor_username) AS sponsor count FROM users WHERE sponsor_username = ?

Thanks!

Edited by phpsane

Share this post


Link to post
Share on other sites

Aliases don't matter when you use bind_param. It depends on column order, not column name.

  • Like 1

Share this post


Link to post
Share on other sites
37 minutes ago, requinix said:

Aliases don't matter when you use bind_param. It depends on column order, not column name.

So, you reckon my code is ok then ?

Then why I get the error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1 in 

Share this post


Link to post
Share on other sites

I meant bind_result. The variables given to that use column order.

You already solved the error. Why are you asking about it?

Share this post


Link to post
Share on other sites

What I dont understand is the $row has not been defined. So, how come I not getting an error ?

<?php 
//Check for username match in "Usernames" column in "users"    table. If there is a match then do the following ...
        $stmt = mysqli_prepare($conn, "SELECT COUNT(sponsor_username) FROM users WHERE sponsor_username = ?");
        mysqli_stmt_bind_param($stmt, 's', $sponsor_username);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $rows);
        if (mysqli_stmt_fetch($stmt) && $rows) 
        {
            die('That Username '.htmlspecialchars($sponsor_username).' is already registered!');
        }
?>
Edited by phpsane

Share this post


Link to post
Share on other sites

Do you have any idea what mysqli_stmt_bind_result does? What references are?

Share this post


Link to post
Share on other sites
2 minutes ago, requinix said:

Do you have any idea what mysqli_stmt_bind_result does? What references are?

Yeah. You reference the collected data from the cols & rows with variables. The collected data becomes the variable values.

My error is not gone. That code you see above is from a different file.My original file in this thread is still showing the error.

	$stmt_1 = mysqli_prepare($conn,"SELECT COUNT(sponsor_username) from users = ?"); 
	

You know what. That other script has the WHERE and so I might aswell try it with that by changing this original script's query to the following now and see what happens. If I get an error, I'll hassle you here again. ;)

 

Share this post


Link to post
Share on other sites
10 minutes ago, requinix said:

Do you have any idea what mysqli_stmt_bind_result does? What references are?

Oh!

I had a typo. You were hinting to that!

 


 
mysqli_stmt_bind_param($stmt, 's', $sponsor_username);

 

Should be

mysqli_stmt_bind_param($stmt, 's', $sponsor_username_count);
Edited by phpsane

Share this post


Link to post
Share on other sites
14 minutes ago, requinix said:

I meant bind_result. The variables given to that use column order.

You already solved the error. Why are you asking about it?

Error gone on my original script now.

Thanks!

	$stmt_1 = mysqli_prepare($conn,"SELECT COUNT(sponsor_username) from users WHERE sponsor_username = ?"); 
            mysqli_stmt_bind_param($stmt_1,'s',$sponsor_username); 
            mysqli_stmt_execute($stmt_1); 
            
            //Show error if 'users' tbl was not successfully queried". 
            if (!$stmt_1) 
            { 
                echo "ERROR 1: Sorry! Our system is currently experiencing a problem loading this page!"; 
                exit(); 
            } 
            else 
            { 
                mysqli_stmt_bind_result($stmt_1,$sponsor_username_count);     
                mysqli_stmt_fetch($stmt_1); 
                mysqli_stmt_close($stmt_1); 
                
                //Check if Sponsor Username in Url ($_GET) is already registered or not. If not then show "Invalid Url" or Link message. 
                if ($sponsor_username_count < 1) 
                { 
                    echo "<b>Invalid Url or Link!<br> Invalid Sponsor: \"$sponsor_username\"!</b><?php "; 
                    exit(); 
                } 
            } 
	

Share this post


Link to post
Share on other sites
3 minutes ago, phpsane said:

Yeah. You reference the collected data from the cols & rows with variables. The collected data becomes the variable values.

And it does that by you telling it what variables to use. It doesn't matter if they exist yet.

  • Like 1

Share this post


Link to post
Share on other sites
4 minutes ago, requinix said:

And it does that by you telling it what variables to use. It doesn't matter if they exist yet.

Yeah. I know. If you notice my scripts, the variables get created for first time in the bind_result. Anyway, good thing you still mentioned it to make sure I am aware of it. Am sure other newbies, who aren't aware of this, will learn from your comment. ;) 

Edited by phpsane

Share this post


Link to post
Share on other sites
1 minute ago, phpsane said:

Yeah. I know.

Why did you ask if you knew the answer?

Share this post


Link to post
Share on other sites
3 minutes ago, requinix said:

Why did you ask if you knew the answer?

Cos, I thought maybe what I thought the answer was probably was not correct somewhere as I was still getting the error. I think I forgot to refresh my browser.

You got a grip like a vice, haven't ya ? Yaa, won't let it go! Lol!

May my backside be excused from your rottweiler bite ? Lol! ;)

But don't go away as I want to complete this script I have been working on since feb 2017 and release it tomorrow. I might have more questions on upcoming new threads.

Stay tuned with me tonight.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.