Back To Programming / Scripting / Coding Forum

Sql Query Arrange Selected Data

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Joined: Nov 18, 2007
  • Posts: 429
  • Status: Offline

Post February 13th, 2009, 1:54 pm

I'm using MySQL and I want a better query to handle what I am trying to do.

I have an 'options' table which has two columns:

primarykey and options

My userinterface sends the primarykey to php which finds the selected row with

Code: [ Select ]
"SELECT * FROM options WHERE primarykey = '$key'";
$var = $arr1['options'];
  1. "SELECT * FROM options WHERE primarykey = '$key'";
  2. $var = $arr1['options'];


However, I'd like to send that row of data along with the rest of the rows in the table back to my program with the row that matches the primarykey at the front instead of having to follow with another query that selects all and joins the variables to the tail like:

Code: [ Select ]
"SELECT * FROM options"
$var .= $arr['options'];
  1. "SELECT * FROM options"
  2. $var .= $arr['options'];


I'm sure there's an easy SQL method that will selectively place one row in the front and join the rest of the rows to the tail. I'd like to use one query but am unsure if ORDER BY can handle something like this or what because it seems when I select WHERE primarykey = 'somevalue' it's only going to return that one row.
Many thanks in advance.
Moderator Remark: Added code tags
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post February 13th, 2009, 1:54 pm

  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post February 13th, 2009, 3:20 pm

SQL Code: [ Select ]
"SELECT * FROM optiones WHERE (SELECT * FROM options) AND primarykey = '$key'"
Try that and tell me what you get... I doubt that would work or something... :roll:
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Joined: Nov 18, 2007
  • Posts: 429
  • Status: Offline

Post February 13th, 2009, 4:21 pm

I get operand should contain one column. So, I used

Code: [ Select ]
SELECT * FROM options WHERE (SELECT options FROM options) AND primarykey = '$key'



and it returns error #1242:
subquery returns more than one row


I'm wanting the subquery to return more than one row. Seems like the right direction.

I had to query using phpmyadmin but I'll try it in my php script when I get a chance but if it didn't work in phpmyadmin query window then I don't think it's going to populate mysql_fetch_array. right?
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post February 13th, 2009, 9:17 pm

Right... I'll let the SQL gurus help you on this one... I got that SQL from extensive google search for 20 minutes... that's how bad I am at SQLs :roll:
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Joined: Nov 18, 2007
  • Posts: 429
  • Status: Offline

Post February 15th, 2009, 9:52 pm

Thanks for the attempt. I've been all over google/ozzu to make sure it's not something simple already answered elsewhere but I haven't had any luck so I posted here. Hopefully, one of the gurus will see this post. I usually just go to my texts for something like this, but my SQL book is MIA. I usually never use it cause the basics I know get me thru everything I've been doing as of late. This is a difficult howto subquery operation to find online. Thnks again for your time.
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post February 15th, 2009, 11:51 pm

First an example table.

Code: [ Select ]
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
 
mysql> USE test
Database changed
 
mysql> CREATE TABLE always_first(
   -> pkey INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   -> options INT(8)
   -> );
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO always_first (options)
   -> VALUES (1),(2),(4),(8),(16);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM always_first;
+------+---------+
| pkey | options |
+------+---------+
|    1 |       1 |
|    2 |       2 |
|    3 |       4 |
|    4 |       8 |
|    5 |      16 |
+------+---------+
5 rows in set (0.00 sec)
  1. mysql> CREATE DATABASE test;
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. mysql> USE test
  5. Database changed
  6.  
  7. mysql> CREATE TABLE always_first(
  8.    -> pkey INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  9.    -> options INT(8)
  10.    -> );
  11. Query OK, 0 rows affected (0.01 sec)
  12.  
  13. mysql> INSERT INTO always_first (options)
  14.    -> VALUES (1),(2),(4),(8),(16);
  15. Query OK, 5 rows affected (0.00 sec)
  16. Records: 5  Duplicates: 0  Warnings: 0
  17.  
  18. mysql> SELECT * FROM always_first;
  19. +------+---------+
  20. | pkey | options |
  21. +------+---------+
  22. |    1 |       1 |
  23. |    2 |       2 |
  24. |    3 |       4 |
  25. |    4 |       8 |
  26. |    5 |      16 |
  27. +------+---------+
  28. 5 rows in set (0.00 sec)


Let's see if it's as easy as using CONCAT.

Code: [ Select ]
mysql> SELECT CONCAT(
   -> (SELECT options FROM always_first WHERE pkey = 3),
   -> (SELECT options FROM always_first WHERE pkey <> 3)
   -> );
ERROR 1242 (21000): Subquery returns more than 1 row
  1. mysql> SELECT CONCAT(
  2.    -> (SELECT options FROM always_first WHERE pkey = 3),
  3.    -> (SELECT options FROM always_first WHERE pkey <> 3)
  4.    -> );
  5. ERROR 1242 (21000): Subquery returns more than 1 row


FAIL. :(

When working with multiple rows, it looks like we need to use the GROUP BY functions.
http://dev.mysql.com/doc/refman/5.0/en/ ... oup-concat

Code: [ Select ]
mysql> SELECT CONCAT(
   -> (SELECT options FROM always_first WHERE pkey = 2),
   -> (SELECT GROUP_CONCAT(options SEPARATOR '') FROM always_first WHERE pkey <> 2)
   -> ) AS result;
+--------+
| result |
+--------+
| 214816 |
+--------+
1 row in set (0.00 sec)
  1. mysql> SELECT CONCAT(
  2.    -> (SELECT options FROM always_first WHERE pkey = 2),
  3.    -> (SELECT GROUP_CONCAT(options SEPARATOR '') FROM always_first WHERE pkey <> 2)
  4.    -> ) AS result;
  5. +--------+
  6. | result |
  7. +--------+
  8. | 214816 |
  9. +--------+
  10. 1 row in set (0.00 sec)
Strong with this one, the sudo is.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Joined: Nov 18, 2007
  • Posts: 429
  • Status: Offline

Post February 16th, 2009, 10:01 am

Interesting. I like it. It should work when I get a chance to use it as it looks like you got the result I'm looking for. It's even a little different than the mysqldev example from the link you provided. I don't see where they wrapped the two subqueries in SELECT CONCAT() which I assume makes this whole thing work for my purpose. Very cool twist on the group-concat. Thanks for you help. This gets me moving forward with much less code and I'm going to continue reading over group-concat functionality.
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post February 16th, 2009, 10:11 am

I only read enough to get something working, which is why I included the link for you. :)
Strong with this one, the sudo is.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post February 23rd, 2009, 7:02 pm

How about the following? (I just found out about this SQL function today :roll: although I've read about it before and just forgotten about it)
SQL Code: [ Select ]
"SELECT * FROM options WHERE primarykey = '$key'
UNION
SELECT * FROM options"
  1. "SELECT * FROM options WHERE primarykey = '$key'
  2. UNION
  3. SELECT * FROM options"
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Joined: Nov 18, 2007
  • Posts: 429
  • Status: Offline

Post February 24th, 2009, 7:47 pm

Good work Bogey. You got it. The UNION call is better for what I need. It seems the GROUP_CONCAT only got one(1) total rows and made a single string out of the results. Did you guys get that same outcome with the GROUP_CONCAT, which makes sense because it is concatenate? Both ways are good to know.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post February 24th, 2009, 8:00 pm

Awesome :D Glad to know that it helped you fix it :)
"Bring forth therefore fruits meet for repentance:" Matthew 3:8

Page 1 of 1

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 164 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.