Mysql return table structure from query

Question

I have a mysql join query for retrieving the users a user subscribed to.

SELECT * FROM (subscriptions) JOIN users ON users.userid = subscriptions.following WHERE userid = $userid ORDER BY subscribed desc

In order to ensure that the query is correct, is there a php function that I can use to see what I produced? Maybe get a table structure of the query like below. Or is there another way to get a visual table design of my join?

+----+-------------+-------------------+
| ID | some column | some column       |
+----+-------------+-------------------+
| 1  | 102         | 213               |
| 2  | 64          | 23                |
| 3  | 4           | 344               |
+----+-------------+-------------------+

EDIT: It seems that I can test the query in phpmyadmin which produces a table of my join query. This is a great way to get a visual of your query!

Could you just test your query on the mysql> command line? mysql -u username -p. You'll be prompted for your database password. Then use dbname; Then type your query and see what scrolls across your screen. — DavidO
– DavidO, Commented Jul 19, 2011 at 5:09

Tudor Constantin · Accepted Answer · 2011-07-19 05:22:44Z

1

Not necessarily a single function, but you can create yourself one using:

mysql_fetchassoc() to get the resultset as key(col name)/value(col value) pairs
array_keys() to get all the column names from resultset and create your table header

edited Jul 19, 2011 at 5:22

answered Jul 19, 2011 at 5:16

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JohnP · Accepted Answer · 2011-07-19 05:09:58Z

1

Not with PHP. The best you could do is to loop through it and create a table. It might be better to just use something like HeidiSQL to visually look at your queries.

answered Jul 19, 2011 at 5:09

JohnP

50k13 gold badges112 silver badges142 bronze badges

Comments

rabudde · Accepted Answer · 2011-07-19 05:13:24Z

1

It's not clear, what you mean. To examine a JOIN in MySQL do a EXPLAIN SELECT * FROM (subscriptions) JOIN users ON users.userid = subscriptions.following WHERE userid = $userid ORDER BY subscribed desc and output this result in PHP

answered Jul 19, 2011 at 5:13

rabudde

7,7826 gold badges58 silver badges95 bronze badges

Comments

symcbean · Accepted Answer · 2011-07-19 13:07:37Z

1

It's not clear what you are asking for.

If you want a generic way to test that the results are as you expect - there's no way to this without detailed knowledge of the data.

You shouldn't be testing if your query is correct at run time. And there is no absolute value of 'correct' to measure against.

If you just want to see the meta-data regarding the columns in the returned dataset, use mysql_fetch_field()

answered Jul 19, 2011 at 13:07

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

Collectives™ on Stack Overflow

Mysql return table structure from query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related