Dump MySQL array result?
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Dump MySQL array result?
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?
Code: Select all
$sql = "SELECT * FROM `gwu_user`";
$result = mysql_query($sql, $databaseLink);
$record = mysql_fetch_assoc($result);
print_r($record);Re: Dump MySQL array result?
Jab, if you are going to learn SQL, forget PHP for a while 
Use a GUI MYSQL front-end and learn it
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
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Dump MySQL array result?
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?
?!? Stop using WYSIWYG-like toolsJAB 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...
Run:
Code: Select all
mysql -uJab -pJabsPasswordThere are 10 types of people in this world, those who understand binary and those who don't
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Dump MySQL array result?
Woot!C:\MEDIA\INTERNET\XAMPP\mysql\bin>mysql -umy_user -pmy_pass
Welcome to MySQL Monitor.
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...it's waiting for me to do something though what?->
Re: Dump MySQL array result?
;
Yes... you can write your queris in multiline ... and you MUST - readability!!!
And you need to select database first:
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
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Dump MySQL array result?
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.
...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?
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?
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!
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
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Dump MySQL array result?
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.
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)
I'm writing this down for future references.
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?
Another useful command is
[sql]DESCRIBE table_name;[/sql]
[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
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Dump MySQL array result?
AWESOME! Plus when MySQL gives you...
->
...I typed a semi-colon and it executed the command. I suppose this is vaguely command line concatenation?
->
...I typed a semi-colon and it executed the command. I suppose this is vaguely command line concatenation?
Re: Dump MySQL array result?
! 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?
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
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Dump MySQL array result?
I understand about SELECT *, it's simply for practice. 
Here you go!
describe category;
Here you go!
describe category;
describe games;+-------+--------------+------+-----+---------+----------------+
| 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 game_category;+-----------------+------------------+------+-----+---------+----------------+
| 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)
+-------------+--------------+------+-----+---------+----------------+
| 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)