Page 1 of 2

Dump MySQL array result?

Posted: Tue Oct 21, 2008 4:29 pm
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?

Re: Dump MySQL array result?

Posted: Tue Oct 21, 2008 4:34 pm
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.

Re: Dump MySQL array result?

Posted: Tue Oct 21, 2008 6:39 pm
by VladSun
Jab, if you are going to learn SQL, forget PHP for a while ;)
Use a GUI MYSQL front-end and learn it :)

Re: Dump MySQL array result?

Posted: Tue Oct 21, 2008 6:45 pm
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...

Re: Dump MySQL array result?

Posted: Tue Oct 21, 2008 6:47 pm
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 :)

Re: Dump MySQL array result?

Posted: Tue Oct 21, 2008 6:58 pm
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?

Re: Dump MySQL array result?

Posted: Tue Oct 21, 2008 7:00 pm
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;

Re: Dump MySQL array result?

Posted: Tue Oct 21, 2008 8:08 pm
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.

Re: Dump MySQL array result?

Posted: Wed Oct 22, 2008 2:49 am
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.

Re: Dump MySQL array result?

Posted: Wed Oct 22, 2008 6:19 am
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!

Re: Dump MySQL array result?

Posted: Wed Oct 22, 2008 11:25 am
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)

Re: Dump MySQL array result?

Posted: Wed Oct 22, 2008 11:34 am
by VladSun
Another useful command is
[sql]DESCRIBE table_name;[/sql]

Re: Dump MySQL array result?

Posted: Wed Oct 22, 2008 12:16 pm
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?

Re: Dump MySQL array result?

Posted: Wed Oct 22, 2008 1:40 pm
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?

Re: Dump MySQL array result?

Posted: Wed Oct 22, 2008 2:03 pm
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)