Solved

Database connection to MySQL using PHP

Posted on 2007-03-22
26
207 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now