Running a "Select * From tableName" query using MySQL/OTP, with no 'WHERE'

Question

This Driver lets you call a mysql database within an Erlang program.

They provide an example for "Multiple Query and Multiple Result sets"

{ok, [{[<<"foo">>], [[42]]}, {[<<"bar">>], [[<<"baz">>]]}]} =
    mysql:query(Pid, "SELECT 42 AS foo; SELECT 'baz' AS bar;"),

But don't really really explain it, or show how to use it without the AS

I would like to execute a query like

{ok, [{[<<Latitude>>, [Longitude], [Image], [Username], [Bio]} = mysql:query(Db, "SELECT * FROM users",[])

This is my regular select * from users

mysql> select * from users;

+----------+-----------+-------+----------+-----------+
| latitude | longitude | image | username | bio       |
+----------+-----------+-------+----------+-----------+
| 44       | 44        |    45 | Sally    | Hello     |
| 42       | 43        |     0 | Bryce    | I'm me    |
| 20       | 24        |     0 | Richard  | I'm Great |
| 44       | 45        |     0 | Liz      | Yeah      |
+----------+-----------+-------+----------+-----------+

So I would want the first column stored within the variable Latitude, the second column stored in Longitude .....

Máté · Accepted Answer · 2018-12-13 09:38:53Z

From the library's example:

%% Select
{ok, ColumnNames, Rows} =
    mysql:query(Pid, <<"SELECT * FROM mytable WHERE id = ?">>, [1]),

You should be able to do it with the following query, with only 2 params on the query function call:

{ok, [<<"latitude">>, <<"longitude">>, <<"image">>, <<"username">>, <<"bio">>], Rows} 
    = mysql:query(Pid, <<"SELECT * FROM users">>),

Where Rows would be like this (based on the table you posted):

[ 
    [44, 44, 45,<<"Sally">>, <<"Hello">>],
    [42, 43, 0,<<"Bryce">>, <<"I'm me">>],
    [20, 24, 0,<<"Richard">>, <<"Hello">>],
    [44, 45, 0,<<"Liz">>, <<"Yeah">>]
]

Each list item in the above structure is a row from your DB table, field values in the same order as your select would request it (as you can see it from the ColumnNames list). You can use this list to transform it to whatever other structure you may need.

Collectives™ on Stack Overflow

Running a "Select * From tableName" query using MySQL/OTP, with no 'WHERE'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related