Dump MySQL array result?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Dump MySQL array result?

Post by JAB Creations »

Simply put: how do I dump the data retrieved from MySQL? Since the data is in an array (now that I think of it I'm not sure when it's ever not in an array) using print_r isn't working and converting with mysql_fetch_array doesn't seem to work either?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Dump MySQL array result?

Post by onion2k »

Code: Select all

   $sql = "SELECT * FROM `gwu_user`";
    $result = mysql_query($sql, $databaseLink);
    
    $record = mysql_fetch_assoc($result);
    
    print_r($record);
Works for me. Obviously you'd need to loop through all the records in $result to dump the whole lot. My code only dumps the first one.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Dump MySQL array result?

Post by VladSun »

Jab, if you are going to learn SQL, forget PHP for a while ;)
Use a GUI MYSQL front-end and learn it :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Dump MySQL array result?

Post by JAB Creations »

I've sort of noticed I can learn from messing with phpMyAdmin. Are there join options available I can mess with? I'm not finding them...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Dump MySQL array result?

Post by VladSun »

JAB Creations wrote:I've sort of noticed I can learn from messing with phpMyAdmin. Are there join options available I can mess with? I'm not finding them...
?!? Stop using WYSIWYG-like tools ;)

Run:

Code: Select all

mysql -uJab -pJabsPassword
in your DOS prompt and here you go :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Dump MySQL array result?

Post by JAB Creations »

C:\MEDIA\INTERNET\XAMPP\mysql\bin>mysql -umy_user -pmy_pass
Welcome to MySQL Monitor.
Woot! :mrgreen: Yeah the lead developer said he'll want me to get comfortable to consoles and of course Linux (I can work in DOS just fine now and have rough experience with Linux console).

I just pasted this in...

Code: Select all

SELECT * FROM game_category INNER JOIN category ON game_category.category_id=category.id WHERE game_category.game_id=3
...and then it gives me this...
->
...it's waiting for me to do something though what?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Dump MySQL array result?

Post by VladSun »

;

:lol: :lol: :lol:

Yes... you can write your queris in multiline ... and you MUST - readability!!! ;)

And you need to select database first:

Code: Select all

use JabDatabase;
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Dump MySQL array result?

Post by JAB Creations »

Selecting the database works fine...

...I'm still stuck on what...

->

...is?

Typing help, go, MySQL queries...all result in nothing. It's like pressing enter at C:\, you just get another C:\ (or in this case simply another ->). All I can do is control-break out and re-enter MySQL manually.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Dump MySQL array result?

Post by onion2k »

When you enter anything into mysql you need to put a ; at the end to tell it that it's the end of the statement.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Dump MySQL array result?

Post by VladSun »

I should have written it in bold or something like that...

Hey, Jab ... when you finish your queries and you are getting the expected results you should start thinking about query performance.
Make a script that inserts e.g. 200k+ records in every table and run your queries again. If you are not satisfied with the time needed to perform your queries start digging ;)

Use EXPLAIN

First to check - indexes!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Dump MySQL array result?

Post by JAB Creations »

The font isn't mono so the database stuff won't look nice, might port to a text editor fine however.

I'm writing this down for future references. :mrgreen: Thanks for the suggestions! I'll be a bit more comfortable getting to and working with the basics of MySQL inside of DOS.

C:\MEDIA\INTERNET\xampp\mysql\bin>mysql -uuser -ppass
Welcome to MySQL


mysql> use gamer
Database Changed


mysql> SELECT * FROM game_category INNER JOIN category ON game_category.category_id=category.id WHERE game_category.game_id=3;
+----+-------------+---------+----+-----------+
| id | category_id | game_id | id | name|
+----+-------------+---------+----+-----------+
| 9 | 2 | 3 | 2 | Adventure |
| 10 | 7 | 3 | 7 | Sports |
+----+-------------+---------+----+-----------+
2 rows in set (0.09 sec)


mysql> explain SELECT * FROM game_category INNER JOIN category ON game_category.category_id=category.id WHERE game_category.game_id=3;
+----+-------------+---------------+--------+---------------+---------+---------
+---------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+---------------+--------+---------------+---------+---------
+---------------------------------+------+-------------+
| 1 | SIMPLE | game_category | ALL | NULL | NULL | NULL
| NULL | 25 | Using where |
| 1 | SIMPLE | category | eq_ref | PRIMARY | PRIMARY | 4
| gamer.game_category.category_id | 1 | Using where |
+----+-------------+---------------+--------+---------------+---------+---------
+---------------------------------+------+-------------+
2 rows in set (0.02 sec)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Dump MySQL array result?

Post by VladSun »

Another useful command is
[sql]DESCRIBE table_name;[/sql]
Last edited by VladSun on Wed Oct 22, 2008 12:24 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Dump MySQL array result?

Post by JAB Creations »

AWESOME! Plus when MySQL gives you...
->
...I typed a semi-colon and it executed the command. I suppose this is vaguely command line concatenation?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Dump MySQL array result?

Post by VladSun »

! Don't use SELECT * -especially when JOINing tables. It's a waste of resources - always specify the fields you want to get by name.
Could you show us the "DESCRIBRE" output of your 3 tables?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Dump MySQL array result?

Post by JAB Creations »

I understand about SELECT *, it's simply for practice. :wink:

Here you go!

describe category;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(128) | NO | | | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
describe games;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(128) | NO | | | |
| name_base | varchar(128) | NO | | | |
| author_name | varchar(128) | NO | | | |
| author_comments | varchar(8192) | NO | | | |
+-----------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
describe game_category;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| category_id | varchar(128) | NO | | | |
| game_id | varchar(128) | NO | | | |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
Post Reply