?
Solved

Database connection to MySQL using PHP

Posted on 2007-03-22
26
Medium Priority
?
218 Views
Last Modified: 2013-12-13
Hi,
I'm having trouble connecting to my MySQL database using PHP.

The following is my simple code to add a user to a table called users in the DB. The table "users" has the two basic columns; "Username" and "Password".
The first page has a basic form, user enters Username and Password and the page clicks through to the next page call add.php

The code that I have for that page is:

<body>
<?
$connection = mysql_pconnect("$host","$username","$database!")
      or die ("Couldn't connect to server.");

$db = mysql_select_db("$database", $connection)
      or die("Couldn't select database.");
?>
            
<?php
$sql = mysql_query("INSERT INTO users (`Username`,`Password`) VALUES('$Username' , '$Password' )") or die (mysql_error());
if(!$sql){
      echo 'ERROR';
} else {
echo 'User added';

User has been added to the DB
}
?>
</body>
0
Comment
Question by:blaadom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 4
  • +1
26 Comments
 
LVL 28

Expert Comment

by:gamebits
ID: 18774471
In your conection string ("$host","$username","$database!")
 isn't suppose to be  $password instead of $database?
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18774476
When using mysql_pconnect(), the paramters should be Hostname, Username, Password.

It looks like you are passing '$database!' as the third parameter, rather than the database password.
0
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18774478
you have to enter password here not the database
$connection = mysql_pconnect("$host","$username","$database!")
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18774487
03.22.2007 at 01:13PM PDT
0
 

Author Comment

by:blaadom
ID: 18774529
Sorry my mistake, I had that it the code put when i copied it in here I must of changed $password to $database by accident. I have that in my code, but nothing gets submitted into the db
0
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 18774563
make sure you are connected with DB
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18774568
Can you make this modification to your code so we can troubleshoot the SQL statement:

<?php
$query = "INSERT INTO users (`Username`,`Password`) VALUES('$Username' , '$Password' )";
print $query . "<br />";
$sql = mysql_query($query) or die (mysql_error());
if(!$sql){
      echo 'ERROR';
} else {
echo 'User added';

We will then be able to see the exact statement that is being executed.
0
 
LVL 28

Expert Comment

by:gamebits
ID: 18774570
Do you get any error when you try the sript?
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18774607
Another minor issue, which may or may not apply here: In your first code block, you are using a short-open tag (<?), and in the second block you are using a normal-open tag (<?php). If your server has short-open tags disabled, the first block is never being executed, so you never connect to the MySQL server. You might try using long-open tags in both places, just to make sure.
0
 
LVL 28

Expert Comment

by:gamebits
ID: 18774630
Try to set your variables first

Add this above your insert statement.

$username = $_POST['username'];
$password = $_POST['password'];
0
 

Author Comment

by:blaadom
ID: 18774706
Ok I've tried all of the above, still nothing going in yet. I don't get any error messages either.
0
 

Author Comment

by:blaadom
ID: 18774757
This is what I put in for the table in the DB:

CREATE TABLE `users` (                  
          `Username` varchar(15) NOT NULL,      
          `Password` varchar(15) NOT NULL,      
          PRIMARY KEY  (`Username`)            
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Would you like me to post the php code I have again?
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18774815
What was the output of the 'print $query' statement?
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18774826
Yes, if you could post the entire script as it now stands, that would be helpful.
0
 
LVL 28

Expert Comment

by:gamebits
ID: 18774871
Put at the top of your script just after the <?

error_reporting(E_ALL);

I'd like to know if we have a parse error.
0
 

Author Comment

by:blaadom
ID: 18774873
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<?php
$connection = mysql_pconnect("$GenServer","$root","$password")
      or die ("Couldn't connect to server.");

$db = mysql_select_db("$generator", $connection)
      or die("Couldn't select database.");
?>
<body>
                        
User added

<?php
$username = $_POST['username'];
$password = $_POST['password'];

$sql = mysql_query("INSERT INTO users (`Username`,`Password`) VALUES('$username' , '$password' )";
print $query . "<br />";
$sql = mysql_query($query) or die (mysql_error());
if(!$sql){
      echo 'ERROR';
} else {
echo 'User added';
?>
</body>
</html>

The values "username" and "password" are on the previous page being submitted.
There is no output from the query statement, and even when I change the database or password for example the query dosen't seem to execute. All that shows is User added
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18774889
$sql = mysql_query("INSERT INTO users (`Username`,`Password`) VALUES('$username' , '$password' )";
print $query . "<br />";
$sql = mysql_query($query) or die (mysql_error());

Should be:

$query = mysql_query("INSERT INTO users (`Username`,`Password`) VALUES('$username' , '$password' )";
print $query . "<br />";
$sql = mysql_query($query) or die (mysql_error());
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18774909
This tells me that error reporting is turned off, because you should have received a message when you tried to print a non-existant variable ($query). gamebits' suggestion is sound: you should turn on error reporting when troubleshooting a PHP script.
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18774930
<?php
$connection = mysql_pconnect("$GenServer","$root","$password")
      or die ("Couldn't connect to server.");

$db = mysql_select_db("$generator", $connection)
      or die("Couldn't select database.");
?>

Where are these variables being set: $GenServer, $root, $password, $generator, $connection ?

If you are trying to pass strings, rather than variables, remove the '$' from these.
0
 

Author Comment

by:blaadom
ID: 18775051
I didn't have the variables set. I set them above but it still didn't work.
I'm using dreamweaver 8 to edit the files. Is there any error reporting on this do you know?
0
 
LVL 24

Accepted Solution

by:
glcummins earned 1000 total points
ID: 18775085
Error reporting is controlled by the server, not by the editor. You will need to edit your php.ini file, and look for the following entries:

error_reporting
   set this to 'error_reporting = E_ALL'

display_errors = On

Restart your webserver, and these changes will take effect.

0
 

Author Comment

by:blaadom
ID: 18775288
Unfortunately I don't have permission to get in to restart the server  :(
The query works in MySQL manually, it's just figuring what could be the reason why it's not submitting / connecting to the DB. I thought I'd just post both pages of code, to see if you could see anything that I'm missing. I've changed the code in add.php to see if that would make any difference.
Page one - the form

 <form id="form1" name="form1" method="post" action="add.php">
                    <table width="100%" height="58%" border="0" cellpadding="0" cellspacing="0">
                  <tr>
                    <td width="20%" height="36">&nbsp;</td>
                    <td width="50%" colspan="2">&nbsp;</td>
                    <td width="30%">&nbsp;</td>
                  </tr>
                  <tr>
                    <td width="20%" height="37">&nbsp;</td>
                    <td width="50%" colspan="2">
                      <label>
                      <div align="right"><strong>Username:
                        </strong>
                        <input name="username" type="text" id="username" maxlength="10" />
                      </div>
                      </label>
                      </td>
                    <td width="30%">&nbsp;</td>
                  </tr>
                  <tr>
                    <td width="20%">&nbsp;</td>
                    <td width="50%" colspan="2">
                      <label>
                      <div align="right"><strong>Password:
                        </strong>
                        <input name="password" type="password" id="password" maxlength="10" />
                      </div>
                      </label>
                      </td>
                    <td width="30%">&nbsp;</td>
                  </tr>
                  <tr>
                    <td width="20%">&nbsp;</td>
                    <td width="50%" colspan="2"><div align="right"></div></td>
                    <td width="30%">&nbsp;</td>
                  </tr>
                  <tr>
                    <td width="20%" height="37">&nbsp;</td>
                    <td width="25%">&nbsp;</td>
                    <td width="25%">
                      <div align="center">
                          <input name="Login" type="submit" id="Login" value="Login" />
                        </div>
                      </td>
                    <td width="30%">&nbsp;</td>
                  </tr>
                  <tr>
                    <td height="73" colspan="4"><p>&nbsp;</p>
                      <p>&nbsp;</p></td>
                    </tr>
                </table>
                  </form>

Page 2 - add.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<?php

mysql_connect("GenServer", "root", "G3nerator!") or die(mysql_error());
mysql_select_db("generator") or die(mysql_error());

$username = $_POST['username'];
$password = $_POST['password'];

mysql_query("INSERT INTO users (Username, Password) VALUES('username', 'password' ) ")
or die(mysql_error());  

echo "Data Inserted!";

?>
Added
</body>
</html>


0
 
LVL 24

Expert Comment

by:glcummins
ID: 18775314
mysql_query("INSERT INTO users (Username, Password) VALUES('username', 'password' ) ")

Should be:

mysql_query("INSERT INTO users (Username, Password) VALUES('$username', '$password' ) ")

0
 
LVL 24

Expert Comment

by:glcummins
ID: 18775325
Since you cannot edit the php.ini file or restart the server, you will need to use gamebits' suggestion to turn on error reporting in add.php. At the top of the script, right after '<?php', add:

error_reporting(E_ALL);
ini_set('display_errors', 'On');
0
 

Author Comment

by:blaadom
ID: 18775361
Still the same, is there any other way I could check the connection to the DB?
Or make a new table and check login details against whats in the DB. I reckon the query would work, as it's more or less going to be the same.
0
 

Author Comment

by:blaadom
ID: 18842767
Thanks for the help guys. I had to wait to get Error reporting turned on and now it's fine.
0

Featured Post

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question