[RESOLVED] First Attempt at connecting to MySQL using PHP code

[LTJason]

Hello people, I'm new here

I've been following a guide in order to build a form for submitting basic content to a MySQL table.

The code I've used for the forms goes like this

 <?php

// Check if form submitted
if (isset($_POST['submitted'])) {

	require_once
	('Connections/AberDigs.php');  // Connect to DB

	$errors = array(); //Error array.

	// Check for a title:
	if (empty($_POST['Title'])) {
		$errors[] = 'You forgot to enter your
		title.';

	} else {

		$title = mysqli_real_escape_string
		($AberDigs, trim (stripslashes
		($_POST['Title'])));
	}

	// Check for content
	if (empty($_POST['Content'])) {
		$errors[] = 'You forgot to enter your 
		content.';

	} else {

		$cont = mysqli_real_escape_string
		($AberDigs, trim (stripslashes
		($_POST['Title'])));
	}

	// Check for an author
	if (empty($_POST['Author']));
		$errors[] = 'You forgot to assign an
		author for the post.';

	} else {

		$atr = mysqli_real_escape_string
		($AberDigs, trim (stripslashes
		($_POST['Author'])));
	}

	if (empty($errors)) { // If all is OK

		//Input into database

		//Query

		$q = "INSERT INTO NewsFeed (Title, Date, Content,
		Author) VALUES ('$title', CURDATE(), '$cont', '$atr' )";

		$r = @mysqli_query ($AberDigs, $q); //Run Query
		if ($r) { //if it ran OK.

			//Confirmation message
			echo '<h1>You have submitted this story</h1>';

		} else { //If did not run OK

			//Error message
			echo '<h1>Failed to submit</h1>';

			echo '<p>' . mysqli_error($AberDigs) .
				'<br /><br />Query: ' . $q .
				'</p>';
		}

		mysqli_close($AberDigs);  //close DB connection

		exit(); //Quit the script

	} else { //report errors

		echo '<h1>Error!</h1>

		<p class="error>The following
		error(s) occurred: <br />';

		foreach ($errors as $msg) { // print each error

			echo " - £msg<br />\n";
		}

		echo '</p><p>Please try 
		again. </p><p><br /></p>';

	}

		mysqli_close($AberDigs);  //close DB connection
?>
<h1>News Edit</h1>
<p>Edit News</p>

<form action="newsedit.php" method="post">

	<p>Title: <input type="text"
    name="Title" size="15"
    maxlength="25" value="<?php if
	(isset($_POST['Title'])) echo
	$_POST['Title']; ?>" /></p>

    <p>Content:
      <textarea name="Content" cols="40" rows="8"><?php if
	(isset($_POST['Content'])) echo
	$_POST['Content']; ?>
      </textarea>
    </p>

<p>Author: <input type="text"
    name="Title" size="15"
    maxlength="20" value="<?php if
	(isset($_POST['Author'])) echo
	$_POST['Author']; ?>" /></p>

    <p><input type="submit" name="submit"
    value="Register" /></p>

    <input type="hidden" name="submitted"
    value="TRUE" />

</form>

I've tried possible solutions from the book but I don't seem to be getting anywhere, my webpage simply displays this from the start:

Failed to submit

Query: INSERT INTO NewsFeed (Title, Date, Content, Author) VALUES ('', CURDATE(), '', '' )

If anyone can point me in the right direction I would appreciate it, thank you 🙂

Jason

[LTJason]

As an add on to this, I've noticed that the connection doesn't seem to be working which might be where the problem lies.

I've followed the instructions for connecting it up, still nothing!

[bradgrafelman]

Try using [man]mysqli_error/man to figure out what the exact error is.

[LTJason]

bradgrafelman;10911042 wrote:

Try using [man]mysqli_error/man to figure out what the exact error is.

I used mysqli_error here:

			$q = "INSERT INTO NewsFeed (Title, Date, Content,
			Author) VALUES ('$title', CURDATE(), '$cont', '$atr' )";

		$r = @mysqli_query ($AberDigs, $q); //Run Query
		if ($r) { //if it ran OK.

			//Confirmation message
			echo '<h1>You have submitted this story</h1>';

		} else { //If did not run OK

			//Error message
			echo '<h1>Failed to submit</h1>';

			echo '<p>' . [B]mysqli_error($AberDigs) .[/B]
				'<br /><br />Query: ' . $q .
				'</p>';
		}

Is that not good enough?

Where else should I put it?

[laserlight]

Three suggestions:

• Get rid of that error suppression operator (@). You can get valuable information from a warning that would otherwise be suppressed. For a production website, configure the server settings to not display errors instead.

• Use the object oriented version of the mysqli interface.

• Make use of prepared statements.

Okay, the second is more a matter of style.

EDIT:
This is what I would suggest:

<?php

// Check if form submitted
if (isset($_POST['submitted'])) {
    $errors = array(); // error messages

// Check for a title:
if (empty($_POST['Title'])) {
    $errors[] = 'You forgot to enter your title.';
}

// Check for content
if (empty($_POST['Content'])) {
    $errors[] = 'You forgot to enter your content.';
}

// Check for an author
if (empty($_POST['Author'])) {
    $errors[] = 'You forgot to assign an author for the post.';
}

if (empty($errors)) { // If all is OK
    require_once 'Connections/AberDigs.php';  // Connect to DB

    //Input into database
    $stmt = $AberDigs->prepare("INSERT INTO NewsFeed
        (Title, Date, Content, Author)
        VALUES (?, CURDATE(), ?, ?)");
    $stmt->bind_param('sss',
        $_POST['Title'], $_POST['Content'], $_POST['Author']);
    if ($stmt->execute()) {
        echo '<h1>You have submitted this story</h1>';
    } else {
        echo '<h1>Failed to submit</h1>'
            . '<p>' . htmlspecialchars($AberDigs->error) . '</p>';
    }

    $AberDigs->close();

    exit();
} else { //report errors
    echo '<h1>Error!</h1>

    <p class="error>The following error(s) occurred: <br />';

    foreach ($errors as $msg) { // print each error
        echo htmlspecialchars($msg) . "<br />\n";
    }

    echo '</p><p>Please try again. </p>';
}
}
?>
<h1>News Edit</h1>
<p>Edit News</p>

<form action="newsedit.php" method="post">

<p>Title: <input type="text"
name="Title" size="15"
maxlength="25" value="<?php if
(isset($_POST['Title'])) echo
$_POST['Title']; ?>" /></p>

<p>Content:
  <textarea name="Content" cols="40" rows="8"><?php if
(isset($_POST['Content'])) echo
$_POST['Content']; ?>
  </textarea>
</p>

<p>Author: <input type="text"
    name="Title" size="15"
    maxlength="20" value="<?php if
    (isset($_POST['Author'])) echo
    $_POST['Author']; ?>" /></p>

<p><input type="submit" name="submit"
value="Register" /></p>

<input type="hidden" name="submitted"
value="TRUE" />

</form>

Of course, since I have changed the code to use object oriented mysqli syntax, your Connections/AberDigs.php would need to change to create $AberDigs as a mysqli object.

[LTJason]

laserlight;10911129 wrote:

Three suggestions:

• Get rid of that error suppression operator (@). You can get valuable information from a warning that would otherwise be suppressed. For a production website, configure the server settings to not display errors instead.

• Use the object oriented version of the mysqli interface.

• Make use of prepared statements.

Okay, the second is more a matter of style.

EDIT:
This is what I would suggest:

<?php

// Check if form submitted
if (isset($_POST['submitted'])) {
    $errors = array(); // error messages

// Check for a title:
if (empty($_POST['Title'])) {
    $errors[] = 'You forgot to enter your title.';
}

// Check for content
if (empty($_POST['Content'])) {
    $errors[] = 'You forgot to enter your content.';
}

// Check for an author
if (empty($_POST['Author'])) {
    $errors[] = 'You forgot to assign an author for the post.';
}

if (empty($errors)) { // If all is OK
    require_once 'Connections/AberDigs.php';  // Connect to DB

    //Input into database
    $stmt = $AberDigs->prepare("INSERT INTO NewsFeed
        (Title, Date, Content, Author)
        VALUES (?, CURDATE(), ?, ?)");
    $stmt->bind_param('sss',
        $_POST['Title'], $_POST['Content'], $_POST['Author']);
    if ($stmt->execute()) {
        echo '<h1>You have submitted this story</h1>';
    } else {
        echo '<h1>Failed to submit</h1>'
            . '<p>' . htmlspecialchars($AberDigs->error) . '</p>';
    }

    $AberDigs->close();

    exit();
} else { //report errors
    echo '<h1>Error!</h1>

    <p class="error>The following error(s) occurred: <br />';

    foreach ($errors as $msg) { // print each error
        echo htmlspecialchars($msg) . "<br />\n";
    }

    echo '</p><p>Please try again. </p>';
}
}
?>
<h1>News Edit</h1>
<p>Edit News</p>

<form action="newsedit.php" method="post">

<p>Title: <input type="text"
name="Title" size="15"
maxlength="25" value="<?php if
(isset($_POST['Title'])) echo
$_POST['Title']; ?>" /></p>

<p>Content:
  <textarea name="Content" cols="40" rows="8"><?php if
(isset($_POST['Content'])) echo
$_POST['Content']; ?>
  </textarea>
</p>

<p>Author: <input type="text"
    name="Title" size="15"
    maxlength="20" value="<?php if
    (isset($_POST['Author'])) echo
    $_POST['Author']; ?>" /></p>

<p><input type="submit" name="submit"
value="Register" /></p>

<input type="hidden" name="submitted"
value="TRUE" />

</form>

Of course, since I have changed the code to use object oriented mysqli syntax, your Connections/AberDigs.php would need to change to create $AberDigs as a mysqli object.

Thanks, this seems to have gotten rid of the error which was appearing as it now displays the form correctly.

However, the connection is still not being made.

How do I go about changing how it connects?

I don't think that it's connecting up at all, I've looked up what could be preventing a connection and none of it is making much sense to me :queasy:

Any help would be appreciated in this regard.

[laserlight]

LTJason wrote:

However, the connection is still not being made.

How do I go about changing how it connects?

In that case, post what is in your Connections/AberDigs.php file, remembering to remove the database user's username and password.

[LTJason]

laserlight;10911135 wrote:

In that case, post what is in your Connections/AberDigs.php file, remembering to remove the database user's username and password.

This is the one which I have been trying, it was automatically generated by dreamweaver when I connected to my database:

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_AberDigs = "db1.srv.host-it.co.uk";
$database_AberDigs = "aberdigsleague";
$username_AberDigs = "xxxx";
$password_AberDigs = "xxxxxxx";
$AberDigs = mysql_pconnect($hostname_AberDigs, $username_AberDigs, $password_AberDigs) or trigger_error(mysql_error(),E_USER_ERROR); 
?>

I have also tried using this one which I created with a help guide:

<?php

DEFINE ('DB_USER', 'xxxxx');
DEFINE ('DB_PASSWORD', 'xxxxxxx');
DEFINE ('DB_HOST', 'db1.srv.host-it.co.uk');
DEFINE ('DB_NAME', 'aberdigsleague');

$AberDigs = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not
connect to MySQL: ' .
mysqli_connect_error() );


?>

I've swapped the filepaths around to see if either works, but I have had no luck with either.

I only created the second one as the first one didn't appear to be working

Thanks

[LTJason]

thanks to whoever edited the second one, I forgot to hide the details in that one

[laserlight]

I propose that you change your code to:

<?php

DEFINE ('DB_USER', 'xxxxx');
DEFINE ('DB_PASSWORD', 'xxxxxxx');
DEFINE ('DB_HOST', 'db1.srv.host-it.co.uk');
DEFINE ('DB_NAME', 'aberdigsleague');

Now, in your main script, change this:

require_once 'Connections/AberDigs.php';  // Connect to DB

to:

require_once 'Connections/AberDigs.php';
$AberDigs = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($AberDigs->connect_error) {
    die('Could not connect to MySQL: ' . $AberDigs->connect_error);
}

If you can, move your 'Connections/AberDigs.php' file to a location outside of your directory root (e.g., outside of your public_html directory). This slightly increases security by making your database connection credentials unavailable even in the event that the PHP interpreter is not invoked to parse your PHP code.

[LTJason]

laserlight;10911140 wrote:

I propose that you change your code to:

<?php

DEFINE ('DB_USER', 'xxxxx');
DEFINE ('DB_PASSWORD', 'xxxxxxx');
DEFINE ('DB_HOST', 'db1.srv.host-it.co.uk');
DEFINE ('DB_NAME', 'aberdigsleague');

Now, in your main script, change this:

require_once 'Connections/AberDigs.php';  // Connect to DB

to:

require_once 'Connections/AberDigs.php';
$AberDigs = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($AberDigs->connect_error) {
    die('Could not connect to MySQL: ' . $AberDigs->connect_error);
}

If you can, move your 'Connections/AberDigs.php' file to a location outside of your directory root (e.g., outside of your public_html directory). This slightly increases security by making your database connection credentials unavailable even in the event that the PHP interpreter is not invoked to parse your PHP code.

I've tried this and it's coming up as an 'error!' still, it doesn't seem to be connecting still

Also, the information I type into Author dissappears after I submit and then replaces the information I have typed into Title

I have screen dumped the result as an example

[laserlight]

Oh, notice that in your form, you named the "Author" form control "Title".

[LTJason]

laserlight;10911149 wrote:

Oh, notice that in your form, you named the "Author" form control "Title".

Oh sweet God, I hope that wasn't what the problem was all along

That last one submitted 😃

You've been magnificent help and I will forever love you for taking time out to help me!

Thank you a million times over!

[laserlight]

LTJason wrote:

Oh sweet God, I hope that wasn't what the problem was all along

It might have been, but I hope you have been prompted to look into the use of prepared statements and such 🙂

LTJason wrote:

Thank you a million times over!

You're welcome 🙂

Oh, and you can mark this thread as resolved by using the thread tools, if you didn't know.

[LTJason]

laserlight;10911153 wrote:

It might have been, but I hope you have been prompted to look into the use of prepared statements and such 🙂

You're welcome 🙂

Oh, and you can mark this thread as resolved by using the thread tools, if you didn't know.

I will mark it in a second thanks!

Also, do you know any good online resources which can help me learn the best way to use prepared statements?

Thanks again

[laserlight]

LTJason wrote:

Also, do you know any good online resources which can help me learn the best way to use prepared statements?

One place is to read the PHP manual's entries on mysqli as some examples are provided.

[LTJason]

laserlight;10911157 wrote:

One place is to read the PHP manual's entries on mysqli as some examples are provided.

thanks