Solved

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

Posted on 2012-03-10
23
231 Views
Last Modified: 2012-03-11
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
Comment
Question by:sasnaktiv
  • 8
  • 7
  • 6
  • +1
23 Comments
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 37706123
How about

SELECT col1 from Table;
0
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 37706124
It occurs to me that I might not understand the question.  But if so, I don't know what you want.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 37706136
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
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 37706149
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37706158
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37706162
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
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 37706166
Yes, $_POST [ 'col1' ] sounds like what the author might have meant.
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37706172
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
 
LVL 1

Author Comment

by:sasnaktiv
ID: 37706182
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
 
LVL 1

Author Comment

by:sasnaktiv
ID: 37706187
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37706196
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 37706208
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
 
LVL 1

Author Comment

by:sasnaktiv
ID: 37706877
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37706982
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
 
LVL 1

Author Comment

by:sasnaktiv
ID: 37707092
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37707129
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37707303
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
 
LVL 1

Author Comment

by:sasnaktiv
ID: 37707379
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
 
LVL 13

Accepted Solution

by:
Hugh McCurdy earned 500 total points
ID: 37707404
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
 
LVL 1

Author Comment

by:sasnaktiv
ID: 37707464
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
 
LVL 13

Assisted Solution

by:Hugh McCurdy
Hugh McCurdy earned 500 total points
ID: 37707482
Get rid of the single quotation marks around   points_earned.
0
 
LVL 1

Author Comment

by:sasnaktiv
ID: 37707511
Thanks for all the help.
It's much appreciated.
Sas
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37707538
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now