Select all but one (in my case two) fields in mysql database

[TOA]

Hello all.

 

Today I had a need to do as the title says and select all the fields except for two. (Think sticky form for updating user info, but not wanting to show the username which is not changeable in this case, and the password which is changed in a separate place) After searching high and low online to no avail, it hit me, and here's my solution (which is in php)

 

$res = mysql_query("SELECT * FROM User WHERE Username='$uName'", $connection);
while ($row = mysql_fetch_assoc($res)) {
foreach ($row as $key => $value) {
	// This says if the field is User_ID or Password to skip it and display the rest
	if ($key == "Username" || $key == "Password") {
		continue;
	} else {
		echo $value.'<br />';
	}
}
}	

I've gotten some good help here before so I wanted to share the love. I hope this helps someone

[Psycho]

Uh, no. Just include the fields you DO want in your query and leave the fields you don't want out of it. Personally, I try to make my code as flexible as possible, but for something like this I think you should explicitly identify the values you want to display:

 

<?php

$query = "SELECT field1, field2, field3, field4
          FROM User
          WHERE username='$uName'";
$result = mysql_query($query, $connection);

$records = '';
while ($row = mysql_fetch_assoc($res))
{
    $records .= "</tr>\n";
    $records .= "<td>{$row['field1']}</td>\n";
    $records .= "<td>{$row['field2']}</td>\n";
    $records .= "<td>{$row['field3']}</td>\n";
    $records .= "<td>{$row['field4']}</td>\n";
    $records .= "</tr>\n";
}

?>

<table>
  <tr>
    <th>Col Header 1</th>
    <th>Col Header 1</th>
    <th>Col Header 1</th>
    <th>Col Header 1</th>
  </tr>
  <?php echo $records; ?>
</table>

[TOA]

Uh, no. Just include the fields you DO want in your query and leave the fields you don't want out of it. Personally, I try to make my code as flexible as possible, but for something like this I think you should explicitly identify the values you want to display:

 

<?php

$query = "SELECT field1, field2, field3, field4
          FROM User
          WHERE username='$uName'";
$result = mysql_query($query, $connection);

$records = '';
while ($row = mysql_fetch_assoc($res))
{
    $records .= "</tr>\n";
    $records .= "<td>{$row['field1']}</td>\n";
    $records .= "<td>{$row['field2']}</td>\n";
    $records .= "<td>{$row['field3']}</td>\n";
    $records .= "<td>{$row['field4']}</td>\n";
    $records .= "</tr>\n";
}

?>

<table>
  <tr>
    <th>Col Header 1</th>
    <th>Col Header 1</th>
    <th>Col Header 1</th>
    <th>Col Header 1</th>
  </tr>
  <?php echo $records; ?>
</table>

I don't see that as being flexible at all, or we may just have different ideas of flexibility.  In this case, I didn't want to put all the fields in there for two reasons:

1. It would contain like 30 fields

2. If I add one field or remove one later, I would need to come and alter the query in all the places I use it...inefficient in my mind. This way, no matter what is in there it will grab all but those two fields, no matter how many or what they are.

That's my definition of flexibility, but this IS America and you are entitled to your's as well

I just thought someone might find this useful if they have a situation like mine.

Thanks for your feedback

[TOA]

Personally, I try to make my code as flexible as possible, but for something like this I think you should explicitly identify the values you want to display:

 

Ok, just hit me what you really said, I read it wrong...

 

Why would you do it differently in this case?

[Psycho]

If I add one field or remove one later, I would need to come and alter the query in all the places I use it...inefficient in my mind. This way, no matter what is in there it will grab all but those two fields, no matter how many or what they are.

 

Right, but just pulling all fields and displaying them can be dangerous. What happens if a new field is added that should not be displayed? That field WOULD be displayed until someone fixes that code. Imagine if a field is added to store the user's social security number, date of birth, CC number, etc.

 

You may be the only person working on that code at the moment, but that may not always be the case. If you are working with many people or if someone takes over for you they may not know about that particular code. For security reasons it is better to explicitly state the fields to display rather than to show all (except two) by default.

 

Using my method, if a new field is added, the developer can identify that the field needs to be added to that display or not. And, if it is missed, it is a minor bug that would be identified rather than potentially exposing sensitive data.

 

And, yes, that is my opinion and you are free to do it as you are.

 

but this IS America

No, it is not. This is the internet. People from all over the world visit this forum

[TOA]

 

Right, but just pulling all fields and displaying them can be dangerous. What happens if a new field is added that should not be displayed? That field WOULD be displayed until someone fixes that code. Imagine if a field is added to store the user's social security number, date of birth, CC number, etc.

In this case, the only person ever seeing the data is the user themselves (they must access it through a "members area" or "profile page" whatever you want to call it)

 

You may be the only person working on that code at the moment, but that may not always be the case. If you are working with many people or if someone takes over for you they may not know about that particular code. For security reasons it is better to explicitly state the fields to display rather than to show all (except two) by default.

 

I see your point, maybe not in this instance, but it is noteworthy

 

No, it is not. This is the internet. People from all over the world visit this forum

 

Touche, but makes my point even more valid, that you are free to think as you want.

[Psycho]

Touche, but makes my point even more valid, that you are free to think as you want.

Tell that to the millions of people that are persecuted every day for their beliefs. 

[TOA]

Touche, but makes my point even more valid, that you are free to think as you want.

Tell that to the millions of people that are persecuted every day for their beliefs. 

 

A worthy adversary!  True, but since we are talking about the internet, where the exchange of ideas and opinions are unregulated as of yet, we are still free to think and speak as we want to over that medium, maybe not in complete anonimity though, but that's a whole different ball of wax and a completely different thread.

I'll take your suggestions into account, thanks.

[Psycho]

... but since we are talking about the internet, where the exchange of ideas and opinions are unregulated as of yet...

http://en.wikipedia.org/wiki/Internet_censorship_in_the_People's_Republic_of_China

http://en.wikipedia.org/wiki/Censorship_in_Iran

http://www.nytimes.com/2006/10/22/technology/22iht-won.3251122.html

http://www.cpj.org/reports/2006/05/10-most-censored-countries.php

 

People who have made derogatory comments about their country or leadership on the internet have been known to disappear. Don't assume everyone has the same rights as in the US

[TOA]

Don't assume everyone has the same rights as in the US

 

I'm not assuming anything. Everyone on this earth has the right to make their life their own, no matter what country.

 

To get this back on topic... thanks for your input

[mrMarcus]

Everyone on this earth has the right to make their life their own, no matter what country.

You can't be serious.  That's the most naive thing I've heard/read in quite some time.  You need to precede that comment with, "It would be nice if everyone on this earth ...".  But you're obviously still young and/or very, very inexperienced and under-traveled, otherwise, you'd know differently.

 

And even the US is lacking in human rights.  How about the right to affordable health care?  You need to show me this lollipop lane you're living on, 'cause it sounds pretty sweet (no pun intended).

 

/my2cents

[TOA]

Oh my god people, this is not a political thread. I was simply offering a solution I used. Don't like it, Don't use it.

 

As for this:

Everyone on this earth has the right to make their life their own, no matter what country.

You can't be serious.  That's the most naive thing I've heard/read in quite some time.  You need to precede that comment with, "It would be nice if everyone on this earth ...".  But you're obviously still young and/or very, very inexperienced and under-traveled, otherwise, you'd know differently.

 

And even the US is lacking in human rights.  How about the right to affordable health care?  You need to show me this lollipop lane you're living on, 'cause it sounds pretty sweet (no pun intended).

 

/my2cents

 

I mean exactly what I said. Human nature insists we change the things we need. If you're being suppresed, move, change it, whatever, but every person has that decision to make. Take Iraq for example who just held there first election ever. Progress..Change..Get it? I feel no sympathy for anyone who says "Nobody will let me".

And as for being young and nieve...you're entitled to your opinion, but just to set the record straight, I'm neither young nor untraveled. Seems like you are if you feel the rest of the world has to share your opinion.

 

Can we now get back on topic?