Installing and configuring LAMP using yum

tklMe · 04-20-2010, 11:37 PM

I have 3 virtual machines set up. 2 are servers and one is a client. On one of the servers I have installed apache, mysql, and php and mod_ssl. I can get to the web site on the server from my client system. I can even get to the info php site--the screen that shows everything about php (it's purplish!) I can also get to the secure https site. The one thing I can't do is get my table to display.

I have set up a new user in mysql. I have granted that user all privileges. I have created a table on a new database in mysql. I then set up php code within html code But every time I try to go to my test.php site it only gives me the header. It obviously cannot find the table array. This is the code and someone already told me it looked right to them. So I am not sure what I am doing wrong.

<$php
$conn = mysql_connect("server2", "user1", "password");
mysql_select_db("db1", $conn);
$sql = "SELECT * FROM tbl";
$result = mysql_query($sql, $conn);
echo "<table border= '1'>\n;
echo "<tr>\n";
echo " <td>ID</td>\n";
echo " <td>Name</td>n";
echo " </tr>\n";

while ($row = mysql_fetch_array($result)){
echo " <td>{$row["ID"]}</td>\n";
echo " <td>{$row["Name"]}</td>\n";
echo " </tr>\n";
}
echo "</table>\n";
?>

If anyone can help me with this or point me to something I can read to figure out what I am doing wrong I would appreciate it. Thanks.

paulsm4 · 04-21-2010, 12:45 AM

Suggestion - "divide and conquer".

Try the following individual tests:

Code:

<?php
  // Test1: make sure PHP is working
  phpinfo ();
 ?>

Code:

<?php
  // Test2: make sure we see our table
  echo "<table border= '1'>\n";
  echo "<tr>\n";
  echo " <td>ID</td>\n";
  echo " <td>Name</td>n";
  echo " </tr>\n";

  echo " <td>{}</td>\n";
  echo " <td>{}</td>\n";
  echo " </tr>\n";
  echo "</table>\n";
?>

Code:

<?php
  // Test3: put it all together
  $conn = mysql_connect("server2", "user1", "password");
  mysql_select_db("db1", $conn);
  $sql = "SELECT * FROM tbl";
  $result = mysql_query($sql, $conn);
  echo "<table border= '1'>\n";
  echo "<tr>\n";
  echo " <td>ID</td>\n";
  echo " <td>Name</td>n";
  echo " </tr>\n";

  while ($row = mysql_fetch_array($result)){
    echo " <td>{$row["ID"]}</td>\n";
    echo " <td>{$row["Name"]}</td>\n";
    echo " </tr>\n";
  }
  echo "</table>\n";
?>

Use the standard "<?php ... ?>" syntax (I noticed "<$php": a typo?)

Double check to make sure you matched all quotes and double-quotes. I noticed a missing double-quote on this line: it's entirely possible this is the entire problem:

Code:

  // ERROR!  MISSING DOUBLE-QUOTE:
  echo "<table border= '1'>\n;

'Hope that helps .. PSM

Blue_Ice · 04-21-2010, 03:42 AM

Stupid question maybe, but does your table contain data on the new server...? In other words have you tested the query that it returns data from server2...?

As a (database) developer, I always used stored procedures to execute queries and made sure that these worked in such a way that I don't have to do much of developing in the application (and it actually has security benefits). An added advantage is that you can return a line that says no data available.

tklMe · 04-21-2010, 10:18 AM

I can get the phpinfo screen. I double checked and those were just typos into here. I have them typed correctly in the php/html script.I'm not seeing a table at all..just the heading--> ID and Name surrounded by a border.

tklMe · 04-21-2010, 10:22 AM

Blue Ice: I am new to Linux and I have no clue as to how to test the query. How do I check that my table contains data on the new server? What are you referring to? I created a table and added the info and then checked it thru mysql and saw the table and info in the table. Please elaborate or point me to something I can read.

Blue_Ice · 04-21-2010, 04:01 PM

Quote:

I can get the phpinfo screen. I double checked and those were just typos into here. I have them typed correctly in the php/html script.I'm not seeing a table at all..just the heading--> ID and Name surrounded by a border.

Quote:

Originally Posted by tklMe

Blue Ice: I am new to Linux and I have no clue as to how to test the query. How do I check that my table contains data on the new server? What are you referring to? I created a table and added the info and then checked it thru mysql and saw the table and info in the table. Please elaborate or point me to something I can read.

I meant that you have checked if the database table contains data by executing the sql query (SELECT * FROM tbl) directly on the correct MySQL server.
To connect to the server you can use the commandline tools of MySQL. Usually these are installed when the server is installed.
Try the following:

Code:

mysql -h <hostname> -u <username> -p
Enter password: 

mysql> USE <your_database>
Database changed

mysql>SELECT * FROM tbl;

If this returns data, then that means you have everything you need on the database side. If not, then you need to insert the data. Be sure to use the correct server (mysql host).

By the way, your issue has nothing to do with linux itself. It is a problem within your database or php-code. In my previous post I asked if you had checked if there is data in the table.

When working with databases you best work in steps.
1. Load data in the database tables (if needed, use some bogus data)
2. Build and execute queries to test if data is returned.
3. Write php code to load the data and keep it simple (paulsm4 already showed how to do this).

paulsm4 · 04-21-2010, 04:54 PM

Hi -

Quote:

I can get the phpinfo screen.

Good - an essential first step

Quote:

I double checked and those were just typos into here. I have them typed correctly in the php/html script.

Good - if the syntax is wrong, you won't see anything (except maybe an error msg - if you're luck

)

Quote:

I'm not seeing a table at all..just the heading--> ID and Name surrounded by a border.

This means that you ARE seeing your table.

The table just doesn't have any data in it

.

Which goes back to what Blue_Ice has been trying to tell you:

* Check the database: make sure it has data (even bogus data)
* Double-check your PHP query
* Double-check your PHP results

'Hope that helps .. PSM

tklMe · 04-21-2010, 04:59 PM

Blue Ice: yes, I can see the data in the table. I will double check my code again. Thanks.

tklMe · 04-21-2010, 06:56 PM

I am going to attach what I have. Unfortunately they are just screen shots. I'm sure the error is somewhere in the database/how I am creating my user. Remember these are all virtual machines. Server1 is used as my dns server. They can see each other. I have the "AMP" portion installed on server2. From my client I type in the server2 address and it brings up the web page I've created.

Since there is a password, shouldn't it be asking for one when I try to get the table via the web? Also, I may even be setting up the user and password wrong in mysql. The screen shot will show my latest attempt. I've tried it without the "@ portion" too. And I've tried it from just mysql without changing to the database. The user is also just in mysql (and on my client system) but nowhere else on the server2.

The other issue may have to do with the client location. The name I have listed as the user is the same as what I use to log into my client system and the static IP used on that system. However, the hostname is different for the client system. Another issue may have to do with the browser. I think I may have read somewhere that mysql and mozilla don't get along.

I hope someone can point me in the right direction. Thanks.

paulsm4 · 04-21-2010, 09:58 PM

Hi, again -

I could only read the first screenshot, but why did you query DB "cats" and table "mine"? Why not "use server2.db1" and "select * from tbl", like in your PHP script?

SUGGESTION:
1. Make sure you have the mysql client on the same host as your web server.

2. Query the database from "mysql" EXACTLY as you're trying to do it from PHP.

3. Post the results.
Cut/paste your query and the results. Do *not* use a screenshot.
Use code blocks (the little "#" icon on the toolbar when you post a reply.

Please keep us posted .. PSM

tklMe · 04-21-2010, 11:06 PM

PaulSM4: I created a new db and a new table just in case I made some errors. This is what I have for the table info:

mysql> use cats
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from mine;
+----+---------+------+------+
| id | name | age | sex |
+----+---------+------+------+
| 1 | Leather | 21 | F |
| 2 | Zoey | 4 | F |
| 3 | Tavi | 2 | M |
| 4 | Tiki | 2 | M |
+----+---------+------+------+
4 rows in set (0.00 sec)

mysql> GRANT ALL ON cats.mine TO 'tklamb'@'192.168.139.129' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)

Here is the php code:

<html>
<head>
<title> Testing PHP/MySQl Connection </title>
</head>
<body>
<?php
$conn = mysql_connect("server2.lambott.local", "tklamb@192.168.139.129", "password");
mysql_select_db("cats", $conn);
$sql = "SELECT * FROM mine";
$result = mysql_query($sql, $conn);
echo "<table border='5'>\n";
echo " <tr>\n";
echo " <td>id</td>\n";
echo " <td>name</td>\n";
echo " <td>age</td>\n";
echo " <td>sex</td>\n";
echo " </tr>\n";

while ($row = mysql_fetch_array($result)){
echo " <tr>\n";
echo " <td>{$row[id]}</td>\n";
echo " <td>{$row["name"]}</td>\n";
echo " <td>{$row[age]}</td>\n";
echo " <td>{$row["sex"]}</td>\n";
echo " </tr>\n";
}
echo "</table>\n";
?>
</body>
</html>

As for the commands being the same, I'm pretty sure they are except maybe the capitalization part. Also I'm not sure I know what you mean by "Make sure you have the mysql client on the same host as your web server." If you mean where mysql is installed, then yes, it is installed on server2 along with Apache and php. None of these are installed on server1 or client.

I hope the cut and paste provides you with more info. Thanks for helping me by the way. I do appreciate it.

paulsm4 · 04-21-2010, 11:58 PM

Hi, again -

1. I created a test table as follows (you've already done the same, successfully):

Quote:

mysql -uMY_USER -pMY_PASSWORD mysql

use test;

create table mine (
id integer not null auto_increment,
name char(10) not null,
age integer not null,
sex char(1) not null,
primary key (id)
);

Code:

Query OK, 0 rows affected (0.00 sec)

insert into mine (name,age,sex) values ('Leather', 21, 'F');
insert into mine (name,age,sex) values ('Zoey', 4, 'F');
insert into mine (name,age,sex) values ('Tavi', 2, 'M');
insert into mine (name,age,sex) values ('Tiki', 2, 'M');

select * from mine;

Code:

+----+---------+-----+-----+
| id | name    | age | sex |
+----+---------+-----+-----+
|  1 | Leather |  21 | F   |
|  2 | Zoey    |   4 | F   |
|  3 | Tavi    |   2 | M   |
|  4 | Tiki    |   2 | M   |
+----+---------+-----+-----+
4 rows in set (0.00 sec)

2. I then created a test .php script, (cleverly named "test.php"

):

Code:

<html>
<head>
<title> Testing PHP/MySQl Connection </title>
</head>
<body>
<?php
  $conn = mysql_connect("localhost", "MY_USER", "MY_PASSWORD") 
    or die ("mysql_connect failed!");
  mysql_select_db("test", $conn)
    or die ("mysql_select_db failed!");
  $sql = "SELECT * FROM mine";
  echo "sql=" . $sql . "...<br/>\n";
  $result = mysql_query($sql, $conn)
    or die ("mysql_query failed!");
  $irow = 1;
  while ($row = mysql_fetch_array($result)){
    echo "row[" . $irow . "]: id=" . $row[id] . ",name=" . $row["name"] . ",age=" . $row[age] . "...<br/>\n";
    $irow++;
  }
  echo "Done.<br/>\n";
?>
</body>
</html>

Finally, I ran it:

Quote:

pointed browser to http://MYSERVER/test.php =>

Code:

sql=SELECT * FROM mine...
row[1]: id=1,name=Leather,age=21...
row[2]: id=2,name=Zoey,age=4...
row[3]: id=3,name=Tavi,age=2...
row[4]: id=4,name=Tiki,age=2...
Done.

'Hope that helps .. PSM

tklMe · 04-22-2010, 12:43 AM

PaulSM4: I noticed your code looked a bit different from mine. I will check it out tomorrow to see if it works. Also, you used localhost and I am assuming that is the name of the current system? It would be found under network and dns tab? Plus I didn't see how you created your user. I do have how I created mine and the IP is the system I am logging in from. Should it not ask for the username or password? The server2 has a different IP of 192.168.139.133 (this is where AMP are installed). Did I even create it correctly? Was it okay to put the client IP since that is where I am logging in from? Or should I be putting the server2 IP? (am I even making sense now?!) Thanks again for your help. (Did you check to see if my code worked at all?)

Blue_Ice · 04-22-2010, 01:48 AM

After you have created a user and granted privileges, you best execute the following query in MySQL:

Code:

FLUSH PRIVILEGES;

You are also using the following username in your php code tklamb@192.168.139.129. The tklamb part is the actual username. The rest is used by MySQL to determine from which computers a client can access. So you can only connect from a computer with the ipaddress 192.168.139.129. You might want to create the user with the following query:

Code:

GRANT ALL ON cats.mine TO 'tklamb'@'192.168.139.%' IDENTIFIED BY 'password';

This will allow access from all computers within 192.168.139.0/24.

tklMe · 04-22-2010, 09:23 PM

PaulSM4: I copied what you had, just changed the user name to tklamb and password to password and the name of the database to cats. And the error I got was mysql_connect failed! I tried changing the localhost to server2.lambott.local and still got the same thing. Any more suggestions?
(I dropped the other user, created just user tklamb and flushed privileges after using the grant command.)