Jump to content

Mysql Command To Show All Data Entered On The Table


satimis

Recommended Posts

Hi folks,After creating a table and entering data on it, how can I display all data just edited on table. I have been googling around and couldn't figure out what MySQL command I have to use. Please shed me some light. TIAB.R.satimis

Link to comment
Share on other sites

$Q = "SELECT * FROM my_table"; // select everything$result = mysql_query($Q);while ($row = mysql_fetch_assoc ($result) ) { // loop through the result set	echo $row["column1"];	echo $row["column2"];	echo $row["column2"];}

Link to comment
Share on other sites

$Q = "SELECT * FROM my_table"; // select everything$result = mysql_query($Q);while ($row = mysql_fetch_assoc ($result) ) { // loop through the result set	echo $row["column1"];	echo $row["column2"];	echo $row["column2"];}

Hi Deirdre's Dad,Where shall I put the php script, on which directory? ThanksB.R.satimis
Link to comment
Share on other sites

It doesn't matter which directory you put that in. Make sure you connect to the database first. There are examples on this page:http://www.php.net/manual/en/function.mysql-fetch-assoc.php
H justsomeguy,Thanks for your advice.php5 is running.mysql> SHOW tables;
+------------------+| Tables_in_maildb |+------------------+| aliases		  | | domains		  | | users			| +------------------+3 rows in set (0.00 sec)

# cat /home/satimis/phpusers

<?php$Q = "SELECT * FROM users"; // select everything$result = mysql_query($Q);while ($row = mysql_fetch_assoc ($result) ) { // loop through the result set	echo $row["column1"];	echo $row["column2"];	echo $row["column2"];}?>

# ls -l /home/satimis/phpusers

-rwxr-xr-x 1 satimis satimis 244 May 27 01:22 /home/satimis/phpusers

I can't not resolve where to run that script.if login the database it can't find the path/to/phpusersIf running the script direct on terminal it popup;# /home/satimis/phpusers

/home/satimis/phpusers: line 1: ?php: No such file or directory/home/satimis/phpusers: line 3: =: command not found/home/satimis/phpusers: line 3: //: is a directory/home/satimis/phpusers: line 4: syntax error near unexpected token `('/home/satimis/phpusers: line 4: `$result = mysql_query($Q);'

Please shed me some light. TIAB.R.satimis

Link to comment
Share on other sites

You need to put it inside a web server directory, somewhere under public_html, and access it using a browser. If you want to execute the script on the command line then you need to run the PHP binary and pass the filename of the script to execute. Your PHP script is also missing the database connection code.http://www.php.net/features.commandline

Link to comment
Share on other sites

You need to put it inside a web server directory, somewhere under public_html, and access it using a browser.
Hi justsomeguy,Noted and thanks
If you want to execute the script on the command line then you need to run the PHP binary and pass the filename of the script to execute. Your PHP script is also missing the database connection code.http://www.php.net/features.commandline
PHP is also new to me.Whether adding following lines on top of the script
$host="root"; //Database host.$user=""; //Database username.$pass="apassword"; //Database password.$dbase="maildb"; //Database.$connect=mysql_connect($host,$user,$pass); //Connect to the database.

This is a test. Only root can access the database, "maildb". So I will leave "user" blank;

$user=""; //Database username.

???If an user, "aaa" can access the database whether I leave the root blank instead;

$host=" "; //Database host.

TIAB.R.satimis

Link to comment
Share on other sites

The host is the server the database resides on, usually localhost. The user is root, aaa, etc.

Link to comment
Share on other sites

The host is the server the database resides on, usually localhost. The user is root, aaa, etc.
Hi Synook,Thanks for your advice.# cat /home/satimis/phpusers
<?php$host="localhost"; //Database host.$user="root"; //Database username.$pass="apassword"; //Database password.$dbase="maildb"; //Database.$connect=mysql_connect($host,$user,$pass); //Connect to the database.$Q = "SELECT * FROM users"; // select everything$result = mysql_query($Q);while ($row = mysql_fetch_assoc ($result) ) { // loop through the result set	echo $row["column1"];	echo $row["column2"];	echo $row["column2"];}?>

Where shall I run the script? If just run it on terminal it can't workroot@vz2:/# /home/satimis/phpusers

/home/satimis/phpusers: line 1: ?php: No such file or directory/home/satimis/phpusers: line 3: =localhost: command not found/home/satimis/phpusers: line 3: //Database: No such file or directory/home/satimis/phpusers: line 4: =root: command not found/home/satimis/phpusers: line 4: //Database: No such file or directory/home/satimis/phpusers: line 5: =vz2mysqlroot: command not found/home/satimis/phpusers: line 5: //Database: No such file or directory/home/satimis/phpusers: line 6: =maildb: command not found/home/satimis/phpusers: line 6: //Database.: No such file or directory/home/satimis/phpusers: line 8: syntax error near unexpected token `$host,$user,$pass'/home/satimis/phpusers: line 8: `$connect=mysql_connect($host,$user,$pass); //Connect to the database.'

Neither I understand what the errors indicate. Please help. TIAB.R.satimis

Link to comment
Share on other sites

You can't just execute the script. You need to execute the PHP binary and give it the script to process. See the link to php.net I posted above. Also, after you connect you need to select the database to use.mysql_select_db($dbase, $connect);

Link to comment
Share on other sites

It doesn't matter which directory you put that in. Make sure you connect to the database first. There are examples on this page:http://www.php.net/manual/en/function.mysql-fetch-assoc.php
ThanksStill failPerformed following steps# mv /home/satimis/phpusers /home/satimis/sqlusers.php# cat /home/satimis/sqlusers.php
<?php$host="localhost"; //Database host.$user="root"; //Database username.$pass="apassword"; //Database password.$dbase="maildb"; //Database.$connect=mysql_connect($host,$user,$pass); //Connect to the database.$Q = "SELECT * FROM users"; // select everything$result = mysql_query($Q);while ($row = mysql_fetch_assoc ($result) ) { // loop through the result set	echo $row["column1"];	echo $row["column2"];	echo $row["column2"];}?>

# chmod +x /home/satimis/sqlusers.php # php /home/satimis/sqlusers.php

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/satimis/sqlusers.php on line 12

line 12

while ($row = mysql_fetch_assoc ($result) ) { // loop through the result set

Edit:If adding following line on the script;

mysql_select_db($dbase, $connect);

after;

$connect=mysql_connect($host,$user,$pass); //Connect to the database.

Nothing print out on running the script;# php /home/satimis/sqlusers.php B.R.satimis

Link to comment
Share on other sites

Do you have anything in the users table? It's only going to print information if it finds it in the users table. You can add print statements so that it tells you what it's trying to do, or how many records it found in the table.

Link to comment
Share on other sites

Do you have anything in the users table? It's only going to print information if it finds it in the users table. You can add print statements so that it tells you what it's trying to do, or how many records it found in the table.
Yes, there are data on "users' table.mysql> DESCRIBE users;
+-----------------+----------------------+------+-----+-------------------------+-------+| Field		   | Type				 | Null | Key | Default				 | Extra |+-----------------+----------------------+------+-----+-------------------------+-------+| id			  | varchar(128)		 | NO   | PRI |						 |	   | | name			| varchar(128)		 | NO   |	 |						 |	   | | uid			 | smallint(5) unsigned | NO   |	 | 5000					|	   | | gid			 | smallint(5) unsigned | NO   |	 | 5000					|	   | | home			| varchar(255)		 | NO   |	 | /var/spool/mail/virtual |	   | | maildir		 | varchar(255)		 | NO   |	 | blah/				   |	   | | enabled		 | tinyint(3) unsigned  | NO   |	 | 1					   |	   | | change_password | tinyint(3) unsigned  | NO   |	 | 1					   |	   | | clear		   | varchar(128)		 | NO   |	 | ChangeMe				|	   | | crypt		   | varchar(128)		 | NO   |	 | sdtrusfX0Jj66		   |	   | | quota		   | varchar(255)		 | NO   |	 |						 |	   | | procmailrc	  | varchar(128)		 | NO   |	 |						 |	   | | spamassassinrc  | varchar(128)		 | NO   |	 |						 |	   | +-----------------+----------------------+------+-----+-------------------------+-------+13 rows in set (0.00 sec)

Ran following steps to enter data

mysql> INSERT INTO users (id,name,maildir,crypt) VALUES	-> ('satimis@satimis.com','satimis','satimis/', encrypt('pass_A') ),	-> ('smsliu@satimis.com','smsliu','smsliu/', encrypt('pass_B') ),	-> ('albert@satimis.com','albert','albert/', encrypt('pass_C') ),	-> ('postmaster@satimis.com','postmaster','postmaster/', encrypt('pass_D') );etc.

Edit:If changing

....	echo $row["column1"];	echo $row["column2"];	echo $row["column3"];....

as;

....		echo $row['id'] . "<BR>";		echo $row['name'] . "<BR>";		echo $row['crypt'] . "<BR>";....

Then it works;# php /home/satimis/sqlusers.php

satimis@satimis.com<br>satimis<br>0e3QZ140ZrOOE<br>smsliu@satimis.com<br>smsliu<br>0eWBW8//TnfEg<br>albert@satimis.com<br>albert<br>XNP5IEvi8VZS6<br>patricia@satimis.com<br>patricia<br>0eg78AAkIGLWM<br>postmaster@satimis.com<br>postmaster<br>0e130m.vyL/aU<br>albertcheung@satimis.com<br>Albert Cheung<br>XXvTmrqgLrBGY<br>root@vz2:/#

But the output is NOT in table form. The passwords are already encrypted. How to show the passwords in original text (i.e. before encrypted)? Thankssatimis

Link to comment
Share on other sites

But the output is NOT in table form.
That's because you're not telling it to output in table form, you're just having it print each column followed by "<br>".
How to show the passwords in original text
MySQL doesn't have a way to decrypt the passwords. The encrypt function uses the Unix crypt command to encrypt, so you may be able to use a system call to use crypt to decrypt the passwords. If you're going to be using encrypt, you need to give it a salt to use or else you won't be able to decrypt it because you won't know what the salt is. Check the documentation here:http://dev.mysql.com/doc/refman/5.1/en/enc...unction_encrypt
Link to comment
Share on other sites

That's because you're not telling it to output in table form, you're just having it print each column followed by "<br>".
Modified the script as follow;
<?php//These variables will determine the search parameters later$host="localhost"; //Database host.$user="root"; //Database username.$pass="apassword"; //Database password.$dbase="maildb"; //Database.$connect=mysql_connect($host,$user,$pass); //Connect to the database.mysql_select_db($dbase, $connect);//after the connection is made use the INSERT command to enter the values in the db$Q = "SELECT * FROM users"; // select everything//result set$result = mysql_query($Q);//creating the table /w headersecho "<html><body>";echo "<table><tr><td>id</td><td>name</td><td>crypt</td></tr>";//row for each recordwhile ($row = mysql_fetch_assoc ($result) ) { // loop through the result setecho"<tr><td>" . $row['id'] . "</td><td>" . $row['name'] . "</td><td>" . $row['crypt'] . "</td></tr>";}echo "</table>";echo "</body></html>";//close the dbmysql_close();?>

Put it on /var/www/sqlusers.php. Fire a browser to evoke it. Then the output is displayed as table but without table frame. How to add it? TIA

MySQL doesn't have a way to decrypt the passwords. The encrypt function uses the Unix crypt command to encrypt, so you may be able to use a system call to use crypt to decrypt the passwords. If you're going to be using encrypt, you need to give it a salt to use or else you won't be able to decrypt it because you won't know what the salt is. Check the documentation here:http://dev.mysql.com/doc/refman/5.1/en/enc...unction_encrypt
Thanks for your URL. I need to digest it first. Is it to decrypt the encrypted password one by one? OR to display all of them on the table?B.R.satimis
Link to comment
Share on other sites

If you want to add a table border, most people just put a border attribute onto the table tag. Check the HTML reference for tables to see that.You can run the MySQL functions on any data, I'm not sure what your question is. You can run any MySQL function on any set of data in the database.If you need to encrypt and decrypt, check one of the other functions that has a decrypt method, like the AES methods. Most systems today don't encrypt passwords in a way that can be decrypted, instead people usually hash the passwords. Hashes can't be decrypted. SHA-1 is a good hash to use. If you use a hash, you can't look up what someone's current password is. You can check if the password they enter is the same as the one they started with, but you can't look up what their password is. If someone forgets their password then you need to let them change it, you can't get the one they current have if you hash it.

Link to comment
Share on other sites

If you want to add a table border, most people just put a border attribute onto the table tag. Check the HTML reference for tables to see that.
Hi justsomeguy,I got it done just adding border=1 inside <table border=1>. To increase the table size just use 2/3/4 etc.
You can run the MySQL functions on any data, I'm not sure what your question is. You can run any MySQL function on any set of data in the database.
I just want to check the data entered on the database tables. I'm NOT aware how to display the data using MySQL function. Now I use the php script instead. However if there are many tables I have to create many php scripts. It is NOT convenient.
If you need to encrypt and decrypt, check one of the other functions that has a decrypt method, like the AES methods. Most systems today don't encrypt passwords in a way that can be decrypted, instead people usually hash the passwords. Hashes can't be decrypted. SHA-1 is a good hash to use. If you use a hash, you can't look up what someone's current password is. You can check if the password they enter is the same as the one they started with, but you can't look up what their password is. If someone forgets their password then you need to let them change it, you can't get the one they current have if you hash it.
I just want to check whether the passwords entered are correct. Now I have to use copy/paste taking down what I have entered. I can't retrieve the password entered on hash. Maybe it can work only by intensive testing and guessing. If some body complains unable to login with his/her password then I have to create a new password for them.B.R.satimis
Link to comment
Share on other sites

I'm NOT aware how to display the data using MySQL function.
MySQL functions don't display data, you either get data from the database, or you change the data in the database. The data goes to PHP, and you use PHP to display it on your web page.
However if there are many tables I have to create many php scripts. It is NOT convenient.
I don't understand. What specifically are you trying to do that is not convenient? I don't understand what you mean when you say you want to check the data.
I just want to check whether the passwords entered are correct.
Use SHA1. If someone registers for a new account and uses the password "test", you run "test" through SHA1 and get "A94A8FE5CCB19BA61C4C0873D391E987982FBBD3", which is what you store in the database. When the user logs in again and you want to check their password, you run the password they entered through SHA1 and see if the SHA1 hash from the password they entered matches the SHA1 hash you saved in the database. If the hashes are the same then they entered the right password.
Link to comment
Share on other sites

- snip -I don't understand. What specifically are you trying to do that is not convenient? I don't understand what you mean when you say you want to check the data.
The php script abovementioned can work only on the "users" table inside MySQL database. If there are several tables inside under different name, I must create several scripts to read them. I think one php script wouldn't work on all "tables".
Use SHA1. If someone registers for a new account and uses the password "test", you run "test" through SHA1 and get "A94A8FE5CCB19BA61C4C0873D391E987982FBBD3", which is what you store in the database. When the user logs in again and you want to check their password, you run the password they entered through SHA1 and see if the SHA1 hash from the password they entered matches the SHA1 hash you saved in the database. If the hashes are the same then they entered the right password.
Which package provides SHA1 command? I think SHA1 and sha1sum are NOT the same.B.R.satimis
Link to comment
Share on other sites

The php script abovementioned can work only on the "users" table inside MySQL database. If there are several tables inside under different name, I must create several scripts to read them. I think one php script wouldn't work on all "tables".
If you make the table name a variable then you can run the same SQL code over however many tables you want.
Which package provides SHA1 command?
SHA1 is built into most languages, it's built into PHP and it's built into MySQL, you don't need a separate package for it. For languages that don't have it built-in, like Javascript, there are several algorithms you can find online to implement it. I don't know what sha1sum is.
Link to comment
Share on other sites

If you make the table name a variable then you can run the same SQL code over however many tables you want.
Hi justsomeguy,Whether adding following heading on top
$table=users, table_name2, table_name3, etc

changing following line;

$Q = "SELECT * FROM users"; // select everything

to;

$Q = "SELECT * FROM $table"; // select everything

???I haven't figured out how to make following items as variable;

//row for each recordwhile ($row = mysql_fetch_assoc ($result) ) { // loop through the result setecho"<tr><td>" . $row['id'] . "</td><td>" . $row['name'] . "</td><td>" . $row['c$}

Any advice? Thanks

SHA1 is built into most languages, it's built into PHP and it's built into MySQL, you don't need a separate package for it. For languages that don't have it built-in, like Javascript, there are several algorithms you can find online to implement it. I don't know what sha1sum is.
Following PHP script works for me generating SHA1 hash
<?php$str="apassword";$hash=sha1($str);  print $hash;?>

I haven't figured a script to be run on variable. Say for example;On terminal$ php -f /path/to/script.php password_1/password_2/password-3 etc.Any advice? TIAEdit:Just discovered;The sha1 hash generated by running "script.php" differed from the encrypted password on MySQL table ?On MySQL table;

0eWBW8//TnfEg

On terminal;$ php -f /home/satimis/php_pw.php

99232baee6cb82c26cdd2fafebac37d9e2020976

It seems MySQL running different encrypting method ?B.R.satimis

Link to comment
Share on other sites

$table=users, table_name2, table_name3, etc
That's not the correct syntax. You can use an array to hold table names, and you would loop through the array and run whatever code you want for each table name.
$tables = array(  'table1',  'table2',  'table3');foreach ($tables as $table){  $sql = 'SELECT * FROM ' . $table;  ...}

I haven't figured out how to make following items as variable;
I don't understand what you're asking.
I haven't figured a script to be run on variable. Say for example;On terminal$ php -f /path/to/script.php password_1/password_2/password-3 etc.Any advice? TIA
I'm not sure what you're going for, do you want a PHP script where you can send command-line arguments?
On MySQL table;0eWBW8//TnfEg
That's not SHA1, SHA1 always produces a 40-character string. MySQL also has a SHA1 function if you want to use that.SELECT SHA1('test')
Link to comment
Share on other sites

That's not the correct syntax. You can use an array to hold table names, and you would loop through the array and run whatever code you want for each table name.
$tables = array(  'table1',  'table2',  'table3');foreach ($tables as $table){  $sql = 'SELECT * FROM ' . $table;  ...}

Noted and thanks.
I don't understand what you're asking.
table1,table2 and table3 may not have the same number of items. The items are on other names.E.G.
table1;[id], [name] and [crypt]table2;[owner], [company], [location],[date], etc.table3;[admin], [full name], [company], [add], [tel], etc

How to set those variables? Thanks

I'm not sure what you're going for, do you want a PHP script where you can send command-line arguments?
Yes.Because there are many plain passwords used to create the MySQL table, say password1, password2, password3, etc. I don't expect editing the php script each time to generate the corresponding hash?Whether it would be possible creating a single script for all passwords?
That's not SHA1, SHA1 always produces a 40-character string. MySQL also has a SHA1 function if you want to use that.SELECT SHA1('test')
Where shall I put "SELECT SHA1('test')" ?Would it be possible to generate the same pattern of hash as "0eWBW8//TnfEg? TIAB.R.satimis
Link to comment
Share on other sites

First, what's the purpose of this script, what are you trying to use it for? I'm not sure if the way you're approaching whatever you're trying to do is the easiest way.
No particular purpose. I just try to learn whether it is possible. I may not use it. If it is too complicate I'll not pursue further.What about is it possible to encrypt "test" in this format 0eWBW8//TnfEg ? ThanksB.R.satimis
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...