Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

How can I retrieve all values in a table's column?

I want to give an end-user the option to retrieve all of the values in 'col1' from a MySQL db.

The values of 'col1' are presented to the end-user by a <select option menu>

Here is an example:
<select name='col1'>
<option value='WHAT GOES HERE'>Anything on this list</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>

Below is an example of the query structure I'm using to retrieve data from MySQL:
"SELECT * from Table WHERE col1='$col1' AND col2='$col2' "

What value should $col1 have?
Any ideas guys?
0
sasnaktiv
Asked:
sasnaktiv
  • 8
  • 7
  • 6
  • +1
2 Solutions
 
Hugh McCurdyCommented:
How about

SELECT col1 from Table;
0
 
Hugh McCurdyCommented:
It occurs to me that I might not understand the question.  But if so, I don't know what you want.
0
 
sasnaktivAuthor Commented:
I need to get both SELECT *
As well as WHERE col1

As stated below:
"SELECT * from Table WHERE col1='$col1' AND col2='$col2' "

I don't know how to be any more clear.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Hugh McCurdyCommented:
This is the phrase that is throwing me off

I want to give an end-user the option to retrieve all of the values in 'col1' from a MySQL db, which to me would be all the values in col1 of the DB.  That's not what you mean.  

However, <select> gives you a list and the user gets to select one item (unless I'm mistaken).  So, $col1 would have only a single scalar value.  Right?  Wouldn't be values (plural) unless (again) I'm missing something important.

Perhaps you mean you want to select all rows that has the value of $col1 in col1.  If so, try something like this

"SELECT * from Table WHERE col1='" . $col1 . "' AND col2='" . $col2 . "'"

Open in new window


Is this what you want?
0
 
micropc1Commented:
I think I may see the confusion here... what you're referring to as a column isn't a column in the database, but rather your html select 'col1'. Because of the format of the select statement you're giving above I'm guessing that 'col1' and 'col2' actually reference the same database 'column'. If so, I think you'll want to do something like the following after the form is submitted...

mysql_query('SELECT * from Table WHERE col1='" . $_POST['col1'] . "'")

Open in new window

0
 
micropc1Commented:
I just got what you're asking.... make it a value of 0 then conditionally omit the WHERE clause by checking for the 0.
0
 
Hugh McCurdyCommented:
Yes, $_POST [ 'col1' ] sounds like what the author might have meant.
0
 
micropc1Commented:
To clarify my previous post a little...

your select...

<select name='col1'>
<option value='0'>Anything on this list</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>

Open in new window


after submit (pseudo code)...

if $col1 is 0

	SELECT * from Table 
if $col1 is not 0..

	SELECT * from Table WHERE col1='$col1' 

Open in new window

0
 
sasnaktivAuthor Commented:
Here's how I understand it.
SELECT * allows you to select all of the values in a particular table.

But I also want to get all of the values in "col1"

Based on that logic, the following query seems correct, but it does not work:
"SELECT * from Table WHERE col1='*' AND col2='" . $col2 . "'"

So what value should 'col1' contain to allow the query to function properly?

Do you see what I'm trying to do?
0
 
sasnaktivAuthor Commented:
Also, if I give this option a value of '0' as in:
<option value='0'>Anything on this list</option>

Then I would only reap from col1 values of '0'.
I also want to reap all cells in col1 that contain values '1', '2', '3'.

Therefore giving me all of the contents of 'col1' with the additional specifications of:
(SELECT * from Table")
in addition to the specifications:
(AND col2='$col2' )

Or have I confused you further?
0
 
micropc1Commented:
I guess I'm not understanding after all. I'm not sure if this will help, but here's what you should expect to see from each of those queries...


"select * from table" will retrieve all values from all columns of a table.

"select * from table where col1 = '$col1'" will retrieve all values from all db columns only where the value of the col1 db column equals '$col1' (a smaller result)

"select * from table where col1 = '$col1' and col2 = '$col2" will retrieve all values from all db columns only where the value of the col1 db column equals '$col1' and the value of col2 equals '$col2' (an even smaller result)
0
 
Hugh McCurdyCommented:
I'm confused too.  Are you trying to retrieve everything that is in col1 from the DB or are you trying to retrieve records from the DB regardless of the contents of col1 or something else?
0
 
sasnaktivAuthor Commented:
Yes, I'm trying to retrieve everything in col1.

Let's review my original question.

<select name='col1'>
<option value='WHAT GOES HERE'>Anything on this list</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>
0
 
micropc1Commented:
Ok, lets think about this... The WHERE clause of an SQL statement is used to filter results. So...if you want to filter results to only show only specific values for col1 you would use..

Select * from table where col1 = $col1

If you want everything you would use...

Select * from table

So for this to work you would need to put some value in place of "WHAT GOES HERE" and check for that value to conditionally add or omit the where clause. Ideally you'll want to use a value the doesn't exist in col1 - maybe "all" or "0" or "-1"...or whatever...
0
 
sasnaktivAuthor Commented:
Once you give $col1 the value of "all" or "0" or "-1"...or whatever... ,
the query will only search for those exact values.

When you give SELECT the "*" value it searches for everything in the Table.
Using the same logic $col1 = "*" should return everything in col1, depending on what the content is in col2.

But it does not!

So what's the equivalent  of "*" that will work for searching col1 ?

$col1 = "???"
"SELECT * from Table WHERE col1='$col1' AND col2='$col2' "
0
 
micropc1Commented:
I think you misunderstand how a SQL select works. Please read my previous post again - you need to omit the WHERE clause to retrieve all results, otherwise you will be filtering for specific data.

The * simply means you want all columns. Alternatively, you can specify the columns you want.

Maybe this will help...

http://www.w3schools.com/sql/sql_select.asp

http://www.w3schools.com/sql/sql_where.asp
0
 
Ray PaseurCommented:
Using the same logic $col1 = "*" should return everything in col1, depending on what the content is in col2.

I think you may want something like this:

SELECT myTable.col1 AS foo FROM myTable WHERE myTable.col2 = whatever

This will give you every row that satisfies the WHERE clause.  Each row of the results set will have one element with a key named "foo" and the value from the data base for "col1"
0
 
sasnaktivAuthor Commented:
Well, I thought I found another way to solve my problem.
Basically I'd delete 'col1' from the query if the value of $col1 is 'x'
I think my logic makes sense, but I'm getting: "Parse error: syntax error, unexpected '=' on line 13"

<select name='col1'>
<option value='x'>Anything on this list</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>

$col1=$_REQUEST['col1'];

if ($col1=='x'){$WHERE ="WHERE";} else {$WHERE ="WHERE col1='$col1' AND";}
getlist="SELECT * from $M_TableName $WHERE  col2 = '$col2'";   // THIS IS LINE 13

What do you think?
0
 
Hugh McCurdyCommented:
That approach can work but I'd make is simpler for better software maintainability
if ( $col1 == 'x' )
  $query = "SELECT * from $M_TableName WHERE col2='" . $col2 . "'";
else
  $query = "SELECT * from $M_TableName WHERE col1 = '" . $col1 . "' AND col2='" . $col2 "'";

Open in new window



Note: unless I'm mistaken, variables in single quotes aren't interpolated.
0
 
sasnaktivAuthor Commented:
That works! Thank you!
When I add: " ORDER BY 'points_earned' DESC"
Everything is returned in Ascending order.

When I add: " ORDER BY 'points_earned' ASC"
Everything is returned in Ascending order.

So no matter if I use DESC or ASC I still get the results in the Ascending order.

That doesn't make sense does it?
0
 
Hugh McCurdyCommented:
Get rid of the single quotation marks around   points_earned.
0
 
sasnaktivAuthor Commented:
Thanks for all the help.
It's much appreciated.
Sas
0
 
Ray PaseurCommented:
variables in single quotes aren't interpolated.

Correct.  When the single quotes are used, the value inside the quotes is a literal string, and not a variable.  Explanation here:
http://us3.php.net/manual/en/language.types.string.php
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now