Counting Number of Rows Via COUNT() And Not num_rows

[phpsane]

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

[phpsane]

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 December 20, 2018 by phpsane

[requinix]

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

[phpsane]

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 

[requinix]

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

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

[phpsane]

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 December 20, 2018 by phpsane

[requinix]

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

[phpsane]

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.

 

[phpsane]

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 December 20, 2018 by phpsane

[phpsane]

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

[requinix]

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.

[phpsane]

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 December 20, 2018 by phpsane

[requinix]

1 minute ago, phpsane said:

Yeah. I know.

Why did you ask if you knew the answer?

[phpsane]

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.