Jump to content

Counting Number of Rows Via COUNT() And Not num_rows


phpsane

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

Link to comment
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!

Link to comment
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 

Link to comment
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!');
        }
?>
Link to comment
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. ;)

 

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

Link to comment
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.

Link to comment
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. ;) 

Link to comment
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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.