the ASTERISK

[phppup]

Is there a REAL difference between using:

 

select * from mytable .. blah blah blah

versus

select COLUMN_NAME from mytable .. blah blah blah

 

I know the result wil be the same, but is there a REAl reason to concern myself or use one method rather than the other?

[Mahngiel]

Of course there's a real difference.

 

Using the asterik means all columns whilst only asking for a specific column will only return that one column.

 

Example:

id | first_name | last_name | avatar | date_joined

 

Select * will return all 5 columns.  Whereas if you Select 'first_name', your query will only return 'first_name'

 

Situationally, you won't want/need all of the data held by the database table, so you'll only select one or two items. Such a scenario, password auth when logging in.  All you'll need to perform your match against would be the user_id/email, the password, and commonly, the password salt.  No need to select all

[Psycho]

It is generally advised to only query the specific column names you need in your query. Using the * to get all columns if you don't actually need them adds additional overhead in running the query which could lead to performance problems. Also, if you use * you could potentially expose sensitive data. If someone were to find a hole in your security to, for example, use SQL Injection to potentially return records they shouldn't they would only get the fields returned by the query rather than all the data.

[phppup]

Perhaps I wasn't specific enough.... let me re-phrase:

 

If I need data form ONLY one COLUMN in my table, does it make a difference which way I write the quiery?

I know the result will be the same, but are there other reasons to use one method over the other??

[Mahngiel]

Consider the overhead if you had 1000 users simultaneously needing to access that query?  Does it make a difference then if you're selecting 1 or 50 columns?

[phppup]

Thank you Psycho... for the informative and educational response!

[scootstah]

Perhaps I wasn't specific enough.... let me re-phrase:

 

If I need data form ONLY one COLUMN in my table, does it make a difference which way I write the quiery?

I know the result will be the same, but are there other reasons to use one method over the other??

 

The result won't be the same. Using an asterisk selects all columns, specifying columns only gets data from the ones you specify.

[phppup]

Let's clarify:

 

I have a table with columns that cover items of men's clothing.  There's a column for each item, everything from shoes, socks, to hats and turbines.  Each item will receive a value from zero to 99,999 when inventoried.

 

So NOW, if I want to quiery the total of t-shirts, and then the total of boxer shorts, etc. will I NOT get the same result whether I use:

 

select * from MYTABLE.... order by pants

versus

select pants from MYTABLE... order by pants.

 

In either instance I believe I am ultimately filtering the results to a specific item.  So am I better off using the asterisk and then filtering down the result, or by simply using the specific column from the start.

 

I assume the latter is best, but wasn't certain if it REALLY makes a difference.

 

[scootstah]

Yes, it makes a big difference. Let me illustrate. Suppose you have the columns: pants, shirt, shoes, socks, and hat. If you use an asterisk, the result set will look something like:

array
(
0 => array(
	'pants' => 'jeans',
	'shirt' => 't-shirt',
	'shoes' => 'sneakers',
	'socks' => 'ankle',
	'hat'   => 'cap'
),
1 => array(
	'pants' => 'shorts',
	'shirt' => 'tanktop',
	'shoes' => 'sandles',
	'socks' => '',
	'hat'   => 'viser'
),
2 => array(
	'pants' => 'suit',
	'shirt' => 'polo',
	'shoes' => 'dress',
	'socks' => 'fancy',
	'hat'   => ''
)
)

 

But, if you only plan on using the "pants" data, and you do SELECT pants FROM ... then your result set will look like:

array
(
0 => array(
	'pants' => 'jeans',
),
1 => array(
	'pants' => 'shorts',
),
2 => array(
	'pants' => 'suit',
)
)

 

So even if you only use the "pants" data, the entire result set is loaded into memory and thrown around in loops.

 

With that said, I think your database design is a bit flawed if you have every article of clothing as columns. The articles of clothing should be "categories" in their own table.

[Pikachu2000]

Let's clarify:

 

I have a table with columns that cover items of men's clothing.  There's a column for each item, everything from shoes, socks, to hats and turbines.  Each item will receive a value from zero to 99,999 when inventoried . . .

 

That design sounds like you're trying to force a relational database behave as a spreadsheet.

[phppup]

See, I always learn something here... even if it's not what I intended.

 

So I get it about the design, BUT what I REALLY meant to say was:

 

Is there a REAL difference between using:

 

select * from mytable .. blah blah blah

versus

select COLUMN_NAME from mytable .. blah blah blah

 

if I am then going to use something along the lines of:

 

ECHO COLUMN_NAME

 

and essentially get a print out of the data for the single item.

 

I know the result wil be the same, but is there a REAL reason to concern myself or use one method rather than the other?

[Jessica]

 

Yes, and you've already been told that in this thread, with an explanation of how.

[scootstah]

I know the result wil be the same, but is there a REAL reason to concern myself or use one method rather than the other?

 

Yes! How many more times must we tell you that before it penetrates?

 

Using SELECT * when you are not using the data from all of the columns is less efficient and a waste of resources.

[phppup]

When Jesirose said: "Yes, and you've already been told that in this thread, with an explanation of how" a kitten MUSt have died, as it was as effective as posting "it doesn't work."

 

Thanks for the explanation Scootstah.

 

The BOLD typeface was a real eye-opener too!  LOL

 

Hope you've been well.  Thanks again!

[Mahngiel]

When Jesirose said: "Yes, and you've already been told that in this thread, with an explanation of how" a kitten MUSt have died, as it was as effective as posting "it doesn't work."

 

Thanks for the explanation Scootstah.

 

The BOLD typeface was a real eye-opener too!  LOL

 

Hope you've been well.  Thanks again!

 

Being an asshat is a great way to ensure you don't get help in the future.

[phppup]

Not trying to be rude.  Just wanted to thank those whose help was informative, and indicate how another response, while well intentioned, really did nothing in the way of guiding me to the solution I was seeking.

 

Jes has aided me in the past, and I appreciate that.  But sometimes the answers without explanations are more confusing to US with the questions.  Just as questions without info can be frustrating to the more advanced.

 

There is a talent to conveying the lesson, and not everyone who has the answer can adequately teach it.

 

I salute those that can, and do.  And express this so that we can rise to a higher level in the forum.

 

No ill intended.

 

[Jessica]

If you ask the same question 5 times over, by the sixth time no one gives a damn whether your feelings get hurt. You're asking people to coddle you because you can't be bothered to read the responses. I already have a baby to take care of, this forum is not it.

[Psycho]

Not trying to be rude.  Just wanted to thank those whose help was informative, and indicate how another response, while well intentioned, really did nothing in the way of guiding me to the solution I was seeking.

 

Jes has aided me in the past, and I appreciate that.  But sometimes the answers without explanations are more confusing to US with the questions.  Just as questions without info can be frustrating to the more advanced.

 

There is a talent to conveying the lesson, and not everyone who has the answer can adequately teach it.

 

I salute those that can, and do.  And express this so that we can rise to a higher level in the forum.

 

You were given a satisfactory response to your question in the very first and second responses. Mahngiel explained in the first response that using '*' would return all data - even the data you didn't need. However, I could see that that response may not have explained why that is a bad thing. So, I followed that up with explanations as to WHY that is ill-advised due to performance and security reasons.

 

Yet, you continued to repeatedly ask for more explanation. And the final response that you say "you got" provided no more information than you had been provided before. Apparently because the text was bolded (?). You failed to do your part as the requester in this thread to read and analyze the information provided to understand it.

 

By not taking the time to try and comprehend the information provided (unless it is bolded) you are going to alienate people from helping you. While there are a great many people that visit these forums, the vast majority of help is provided by a very small number of people that volunteer their time to do so.