9 Replies - 836 Views - Last Post: 29 August 2012 - 08:34 PM Rate Topic: -----

#1 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 762
  • Joined: 31-August 11

Select * From vs Select Count(*)?

Posted 29 August 2012 - 12:33 PM

I'm not noticing any difference can someone explain? Thanks guys
Is This A Good Question/Topic? 0
  • +

Replies To: Select * From vs Select Count(*)?

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 12:36 PM

Well, SELECT * FROM tbl selects all the data from the table and returns that. The SELECT COUNT(*) FROM tbl counts the rows that would be returned and returns just that one number.

If you are asking which should be used in PHP to count the number of rows, then the second option is FAR better. Fetching all the data in a table just to count how many rows there are is wasteful, when the database itself is more than capable of doing that much more efficiently.
Was This Post Helpful? 2
  • +
  • -

#3 no2pencil  Icon User is online

  • Toubabo Koomi
  • member icon

Reputation: 5313
  • View blog
  • Posts: 27,216
  • Joined: 10-May 07

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 01:00 PM

select count(*) from column is the sql way of doing the following in php :
$result = select * from column;
$num_rows = mysql_num_rows($result);


Was This Post Helpful? 0
  • +
  • -

#4 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 762
  • Joined: 31-August 11

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 03:34 PM

View Postno2pencil, on 29 August 2012 - 01:00 PM, said:

select count(*) from column is the sql way of doing the following in php :
$result = select * from column;
$num_rows = mysql_num_rows($result);




For some reason whenever I try to do something like in the code below it NEVER and I mean NEVER works for me. I always have to use SELECT * not the COUNT version because it never returns and int for me. I know I'm probably doing something wrong in some small way any ideas


function bUserLogin($user, $pass)
{
$mysqli = new mysqli("127.0.0.1","adn","adn","test");
if (mysqli_connect_errno())
{
   printf("Connect failed: %s\n", mysqli_connect_error());
    exit();	
}

$match = $mysqli->query("SELCT COUNT(*) FROM main WHERE username='$user' AND password='$pass' AND activated='1'");
if ($match > 0)
{
 return true;	
}
echo $match;
return false;
	
}




Was This Post Helpful? 0
  • +
  • -

#5 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2990
  • Posts: 10,329
  • Joined: 08-August 08

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 04:14 PM

Just because you're using mysqli doesn't mean that you're using prepared statements.
From here:

Quote

query
The query string.

Data inside the query should be properly escaped.

You need to start working on scrubbing user data or start using prepared statements.

This post has been edited by CTphpnwb: 29 August 2012 - 04:14 PM

Was This Post Helpful? 0
  • +
  • -

#6 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 762
  • Joined: 31-August 11

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 04:37 PM

View PostCTphpnwb, on 29 August 2012 - 04:14 PM, said:

Just because you're using mysqli doesn't mean that you're using prepared statements.
From here:

Quote

query
The query string.

Data inside the query should be properly escaped.

You need to start working on scrubbing user data or start using prepared statements.


No offense you don't explain yourself well at least when you answer my questions. I haven't a clue what you're even talking about remember I'm new to php. Scrubbing what's that? Prepared statements what are you talking about? Also why again isn't it returning true or false there's always a reason? It works with mysqli->query(UPDATE queries but not this it seems to return an object and you have to use num_rows which I thought defeats the purpose of using COUNT anyway?

Wow. Confusing.
Was This Post Helpful? 0
  • +
  • -

#7 Sho Ke  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 110
  • View blog
  • Posts: 250
  • Joined: 13-October 11

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 04:49 PM

Basically, "scrubbing" user data is a way of making sure your user's inputted data won't harm your database. The first result after searching "scrubbing user data" says the following:

Quote

Data scrubbing, also called data cleansing, is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated


And as for prepared statements, Dormilich did an excellent job explaining what prepared statements are and how to use them here. I highly recommend reading those links he posted at the top as well.

For future reference, if someone uses a term you aren't familiar with, Google is extremely helpful. To find that link about what "scrubbing user data" meant, I didn't even have to mention a specific programming language, let alone programming at all, and it was still the first result.
Was This Post Helpful? 4
  • +
  • -

#8 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 05:02 PM

View Postadn258, on 29 August 2012 - 10:34 PM, said:

For some reason whenever I try to do something like in the code below it NEVER and I mean NEVER works for me. I always have to use SELECT * not the COUNT version because it never returns and int for me. I know I'm probably doing something wrong in some small way any ideas

The $mysqli->query() function will not return the integer directly. It'll return the result set, like normally. You'll have to fetch the COUNT() value from the result set. Like:
$result = $mysqli->query(...);
if ($result) {
    $row = $result->fetch_row();
    $count = $row[0];

    // Now $count holds your row count.
}


Was This Post Helpful? 2
  • +
  • -

#9 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 762
  • Joined: 31-August 11

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 07:15 PM

View PostAtli, on 29 August 2012 - 05:02 PM, said:

View Postadn258, on 29 August 2012 - 10:34 PM, said:

For some reason whenever I try to do something like in the code below it NEVER and I mean NEVER works for me. I always have to use SELECT * not the COUNT version because it never returns and int for me. I know I'm probably doing something wrong in some small way any ideas

The $mysqli->query() function will not return the integer directly. It'll return the result set, like normally. You'll have to fetch the COUNT() value from the result set. Like:
$result = $mysqli->query(...);
if ($result) {
    $row = $result->fetch_row();
    $count = $row[0];

    // Now $count holds your row count.
}



What's wrong with using num_rows instead that also does appear to work and what I've been using?
Was This Post Helpful? 0
  • +
  • -

#10 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Select * From vs Select Count(*)?

Posted 29 August 2012 - 08:34 PM

Like I said in my first post, it's a waste of resources. The database and PHP exist separately. When you do: SELECT * FROM ... you are asking the database to transfer all the data that matches the SELECT back to PHP. If you actually need to use all that data, then that's fine. However if all you need to do with the data is count how much data there is, then transferring it to PHP in order to do that is a waste, when the database can count it for you and just give you the results.

It's like, if you're not home and suddenly find yourself needing to know how many different items you have in your refrigerator. Do you call somebody at home and ask them to list all the items so you can count them yourself, or do you just ask them to count the items for you?
Was This Post Helpful? 4
  • +
  • -

Page 1 of 1