Solved

Database connection to MySQL using PHP

Posted on 2007-03-22
26
211 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
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 250 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

820 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