Connecting MySQL with PHP

FlameDra · December 31, 2011

So I'm currently watching this tutorial : http://youtu.be/9E0s4gsUeU0

And am trying to build the sample application shown there. However I do seem to run into some problems, so if someone could take the time to help me out; I would be very grateful!

---

The application is a simple HTML form, which takes the data input and stores it into a MySQL database table.

My project is organized as this :

The actual form is stored on the insert.php file.

<?php

include_once('resources/init.php');

if (isset($_POST['title'], $_POST['post'])) {

// functions go here!

}


?>

<html>
<head>
<title>
Post something!
</title>
</head>

<body>

<div id="form">
<form method="POST" action= " ">
<label for="title">Title:</label><br>
<input type="text" name="title" id="title" /><br>
<label for="post">Post:</label><br>
<textarea name="post" id="post" rows="15" cols="50"></textarea><br>

</form>
</div>

</body>
</html>

The config.php and init.php are the configuration and initialize files.

config.php

<?php

$config['DB_HOST'] = 'localhost';
$config['DB_USER'] = 'root';
$config['DB_PASS'] = '';
$config['DB_NAME'] = 'form';

//foreach ( $config as $k => $v ) {
//  define(strtouper($k), $v);
//}

?>

init.php

<?php

include_once('config.php');

mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME);

?>

---

When I try this in my browser I get the error :

Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL server host 'DB_HOST' (11004) in C:\xampp\htdocs\form\resources\init.php on line 5

Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\form\resources\init.php on line 6

Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in C:\xampp\htdocs\form\resources\init.php on line 6

Could someone tell me what is wrong?

Thanks in advance!

sunfighter · December 31, 2011

I use this line to connect to my database connect file:

require ('./inc/DB_connect.php');

As you can see the file NAMED DB_connect.php is in a folder NAMED inc.

The DB_connect.php looks like this:

<?php

// set up strings needed to connect to the DB
$DBhost = "localhost";
$username="root";
$password="";
$database="my_db"; // This was given - other strings made up

// Connect to mysql here or die
$link = mysql_connect($DBhost,$username,$password) or die("Unable to connect to host");

// Connect to the database you want here or die
$link = mysql_select_db($database) or die( "Unable to connect database");

?>

Sub your info and use this.

FlameDra · December 31, 2011

Sub your info and use this.

Thank you so much! I wasn't expecting such a quick reply!

Its working perfectly now!

FlameDra · December 31, 2011

It seems I ran into another problem...

This is the 'form/resource/DB_connect.php' file which you suggested :

<?php

// set up strings needed to connect to the DB
$DBhost = "localhost";
$username="root";
$password="";
$database="form"; // This was given - other strings made up

// Connect to mysql here or die
$link = mysql_connect($DBhost,$username,$password) or die("Unable to connect to host");

// Connect to the database you want here or die
$link = mysql_select_db($database) or die( "Unable to connect database");

?>

And this is the edited 'form/insert.php' file :

<?php

require ('resources/DB_connect.php');

if ( isset($_POST['title'], $_POST['post']) ) {
$title = HTML_ENTITIES($_POST['title']);
$post = HTML_ENTITIES($_POST['post']);


mysql_query("INSERT INTO 'posts' SET
            title ="{$title}",
            post ="{$post}"");

}



?>

<html>
<head>
<title>
Post something!
</title>
</head>

<body>

<div id="form">
<form method="POST" action= " ">
<label for="title">Title:</label><br>
<input type="text" name="title" id="title" /><br>
<label for="post">Post:</label><br>
<textarea name="post" id="post" rows="15" cols="50"></textarea><br>
<input type="submit" value="Submit" />
</form>
</div>

</body>
</html>

On Line 11 I get 'unexpected {' error.

I think something is wrong with the syntax, could someone explain a bit? :confused:

sunfighter · December 31, 2011

Your not escaping the double quote that is contained inside of a double quote. Easy to see if you don't break the line into three parts or if you are in the habit of using single quote. If you really want the column called TITLE to hold the brackets like {title name} or do you want the column to hold title name?

I re=wrote your query and removed the curly brackets if you want them back in just put them in.

code:

mysql_query("INSERT INTO 'posts' SET title ='$title', post ='$post'");

using single quotes inside of doubles.

FlameDra · December 31, 2011

Your not escaping the double quote that is contained inside of a double quote. Easy to see if you don't break the line into three parts or if you are in the habit of using single quote. If you really want the column called TITLE to hold the brackets like {title name} or do you want the column to hold title name?

I re=wrote your query and removed the curly brackets if you want them back in just put them in.

code:
mysql_query("INSERT INTO 'posts' SET title ='$title', post ='$post'");
using single quotes inside of doubles.

Sorry, I'm really new to this so I don't know much about what you mean. I was just following the the way the code was written in the tutorial.

I replaced the old code with the one you mentioned, however when I click submit I get :

Object not found!
The requested URL was not found on this server. The link on the referring page seems to be wrong or outdated. Please inform the author of that page about the error. 

If you think this is a server error, please contact the webmaster. 

Error 404
localhost
31/12/2011 10:27:09 PM
Apache/2.2.12 (Win32) DAV/2 mod_ssl/2.2.12 OpenSSL/0.9.8k mod_autoindex_color PHP/5.3.0 mod_perl/2.0.4 Perl/v5.10.0

Any idea why? :/

jcbones · December 31, 2011

The reason your first attempt could not connect is because you were using an array to assign the data, a constant to call the data.

EXAMPLES:

<?php
//define an array, and it's index.
$config['DB_HOST'] = 'localhost';
$config['DB_USER'] = 'root';
$config['DB_PASS'] = '';
$config['DB_NAME'] = 'form';

//call the array into a functions arguments.
mysql_connect($config['DB_HOST'],$config['DB_USER'], $config['DB_PASS']);
mysql_select_db($config['DB_NAME']);

//set constants.
define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PASS','');
define('DB_NAME','form');

//call the constants into a functions arguments.
mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME);

I would write your insert as:

<?php

require ('resources/DB_connect.php');

if ($_SERVER['REQUEST_METHOD'] == 'POST') { //if your page receives a post.
     if(empty($_POST['title'])) { //if the title is empty, include the error.
            $error[] = 'You must include a Title!';
    } elseif(empty($_POST['post'])) { //if the post is empty, include the error.
           $error[] = 'You must include a Post!';
    } else { //if everything is filled out, process the insert.
$title = mysql_real_escape_string(htmlentities($_POST['title']));
$post = mysql_real_escape_string(htmlentities($_POST['post']));

       $sql = "INSERT INTO posts(title,post) VALUES ('$title','$post')"; //I stick with the common insert syntax, nothing wrong with the other update/insert syntax though.

     if(!mysql_query($sql)) { //if the query fails.
          trigger_error($sql . ' has encountered an error<br />' . mysql_error()); //trigger an error.
    }
  }
}
?>

<html>
<head>
<title>
Post something!
</title>
</head>

<body>
<?php echo (isset($error)) ? implode('<br />',$error) : NULL; //if either of the inputs are blank, post the errors here, if there is an error here, there was no database insert. ?>
<div id="form">
<form method="POST" action= "">
<label for="title">Title:</label><br>
<input type="text" name="title" id="title" /><br>
<label for="post">Post:</label><br>
<textarea name="post" id="post" rows="15" cols="50"></textarea><br>
<input type="submit" value="Submit" />
</form>
</div>

</body>
</html>

FlameDra · December 31, 2011

Thank you!

Its successfully adding data to the tables now

But is there a reason you did not use a '?>' tag at the end of your first EXAMPLE?

jcbones · December 31, 2011

It was an example, and the first <?php was just so the code block would highlight the code. In essence, it was me being lazy!

FlameDra · January 2, 2012

Well, today I tried displaying the data which the form submits into the database.

I made a new file called posts.php :

<?php

require ('resources/DB_connect.php');

$query = mysql_query("SELECT 'id', 'title', 'post' FROM 'posts'  ");

while ($row = mysql_fetch_assoc($query)) {
?>
    <h2> <a href="posts.php?=id<?php echo $row['id']; ?>"> <?php echo $row['title']; ?></a></h2>
    <p> <?php echo $row['post']; ?></p>

<?php 
}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>View Posts</title>
</head>
<body>

</body>
</html>

When I run it I get the error :

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\form\posts.php on line 7

How can I fix this?

Pikachu2000 · January 2, 2012

Your query is failing, and is returning a boolean FALSE to mysql_fetch_assoc(). mysql_error is the function that will tell you what the actual error message is.

Hint: field/table/database names don't get quoted. If anything, they would be in `backticks`.

FlameDra · January 2, 2012

Your query is failing, and is returning a boolean FALSE to mysql_fetch_assoc(). mysql_error is the function that will tell you what the actual error message is.

Hint: field/table/database names don't get quoted. If anything, they would be in `backticks`.

I tried changing it to :

$query = mysql_query("SELECT `id`, `title`, `post` FROM `posts`  ");

and :

$query = mysql_query("SELECT id, title, post FROM posts  ");

It still gave the same error message :S

Zane · January 2, 2012

it May have something to do with that whitespace at the end.

FlameDra · January 2, 2012

it May have something to do with that whitespace at the end.

$query = mysql_query("SELECT id, title, post FROM posts");

Same result

Pikachu2000 · January 2, 2012

And what is the error returned by mysql_error()?

FlameDra · January 2, 2012

And what is the error returned by mysql_error()?

I'm sorry, but I'm really new at this and am not sure how to use that function.

But I tried doing :

echo mysql_errno($query) . ": " . mysql_error($query). "\n";

But it did not work :/

Zane · January 2, 2012

Typically, this is how you use it

$query = mysql_query("SELECT 'id', 'title', 'post' FROM 'posts'") or die("Your error is:
\n" . mysql_error());

FlameDra · January 2, 2012

And what is the error returned by mysql_error()?

My error checker has an error :

Warning: mysql_errno() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\form\posts.php on line 7

Warning: mysql_error() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\form\posts.php on line 7
: 
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\form\posts.php on line 10

FlameDra · January 2, 2012

Typically, this is how you use it

$query = mysql_query("SELECT 'id', 'title', 'post' FROM 'posts'") or die("<b>Your error is:</b><br />\n" . mysql_error());

Thanks.

The error I now get is :

Your error is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''post'' at line 1

jcbones · January 2, 2012

Table and columns are not wrapped in quotes ('), but can be wrapped in backticks(`). Unless you run into reserved keywords, I would just drop the quotes and add nothing.

$query = mysql_query("SELECT id, title, post FROM posts") or die("<b>Your error is:</b><br />\n" . mysql_error());

FlameDra · January 2, 2012

Table and columns are not wrapped in quotes ('), but can be wrapped in backticks(`). Unless you run into reserved keywords, I would just drop the quotes and add nothing.
$query = mysql_query("SELECT id, title, post FROM posts") or die("<b>Your error is:</b><br />\n" . mysql_error());

Thanks to everyone who helped! Its finally working!

Full code below of anyone interested :

post.php

<?php

require ('resources/DB_connect.php');


$query = mysql_query("SELECT id, title, post FROM post") or die("<b>Your error is:</b><br />\n" . mysql_error());


while ($row = mysql_fetch_assoc($query)) {
?>
    <h2> <a href="posts.php?=id<?php echo $row['id']; ?>"> <?php echo $row['title']; ?></a></h2>
    <p> <?php echo $row['post']; ?></p>

<?php 
}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>View Posts</title>
</head>
<body>

</body>
</html>

I'd recommend other newbies (like me xD) to try this example. I learned a LOT from this!

Thank you guys again.

Sign In

Connecting MySQL with PHP

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information