[SOLVED] PHP forms and databases

[DangerM0use]

Hi,

firstly hello and sorry if this has been asked and answered before. I did a search but couldn't find anything.

Anyway, I am just about a total newb to PHP and am struggling with a project I have taken on. I am creating a site that contains an admin page and the site has a database using MySql. There are quite a few things I want to do, but what I am about to ask is the important bit I am struggling with.

My admin page will have a form containing a text box, two text areas and a submit and reset buttons. What I want to do is get the data that is typed into the form and save it in the relevent databse fields. Example being, the text box in the form would be connected to the title field in the database.

At the moment this is what I am struggling with, I am sure there will be plenty more once this is done!

Hope someone can help and knows what I am talking about.

Thanks.

[mbtaylor]

This is basic stuff mate, I suggest you read the Zend 101 beginners tutorial and skim through appropriate sections of the PHP Manual.

 

devzone.zend.com/node/view/id/627

 

[DangerM0use]

Ta will have a look through!

[DangerM0use]

That was great thanks. Learnt a lot and that sorted out the first of the issues I was having.

What my site is going to be is a games news site. I and some other guys will be regularly posting news articles using the admin page that I can now create.

What I want to now happen, and I think this is where it gets complicated, is publish the article. But the way I want it to happen is by publishing the preview text onto the home page. Clicking the article title will link to the actual article on a new page. This new page will be created when the article is published. Can anyone help me with this one??!!  ???

I don't think I explained it very well so please do ask if you have no idea what I'm on about, and thanks again in advance if anyone can help!!

 

[nloding]

No, you explained it fine.  But go do some homework and come back with code when it's not working.

 

Basically, you're going to do a query on your database ("SELECT"), then using a while loop, print out those results in the format you want with a link to the proper PHP page.  On that PHP page, you do the same query, only limiting yourself to a particular article, then echo all the variables onto the page.  Each article should have it's own ID, and it would be easy to make an article.php page using $_GET variables (article.php?articleid=1029).

 

If any of that you didn't understand, you'll need to start hitting the books.

 

This is again simple stuff.  Here's a place to start:

 

http://www.w3schools.com/php/php_mysql_select.asp

 

There are tons of tutorials on this stuff though.  If you're looking for a book, anything that has "PHP & MySQL" in the title will cover this.

[DangerM0use]

I thought I might have been making it more complicated than it was, or at least thinking it was more complicated then it is. What you put makes sense, I will have a look at the books I have and fiddle around with what I already know and see what I get.

Thanks for the help so far, will hopefully be able to come on and say that's all sorted!! 

[nloding]

Try several things after reading some stuff, and if you can't get the code working, bring it back here and we'll take a look at it.

 

There's a book called PHP and MySQL Web Development but I'm blanking on the publisher right now (it's a white cover with a B/W photo and purple).  Very good starter book.  Another option is to grab a coffee and chill in the back of Barnes And Nobel or Borders and just read the books, taking notes as you go.

[DangerM0use]

I have PHP & MySQL for dummies but I don't think it is that good to be honest. Although, it may well be that I don't really know what I'm looking for! But i'll check out the uni library as well. One of the reasons i'm doing this as well is it will help with my assignment coming up.

[DangerM0use]

No, you explained it fine.  But go do some homework and come back with code when it's not working.

 

Basically, you're going to do a query on your database ("SELECT"), then using a while loop, print out those results in the format you want with a link to the proper PHP page.  On that PHP page, you do the same query, only limiting yourself to a particular article, then echo all the variables onto the page.  Each article should have it's own ID, and it would be easy to make an article.php page using $_GET variables (article.php?articleid=1029).

 

If any of that you didn't understand, you'll need to start hitting the books.

 

This is again simple stuff.  Here's a place to start:

 

http://www.w3schools.com/php/php_mysql_select.asp

 

There are tons of tutorials on this stuff though.  If you're looking for a book, anything that has "PHP & MySQL" in the title will cover this.

 

Just had another look at what was put, would any of that automatically generate the new page for the article?

[nloding]

The new page is created using the $_GET variable.  I used this "article.php?articleid=1029" ... when that is called, your PHP code would extract from your database the article with the ID 1029.  Think of it as an automatically filled template using the keyword "1029".

[DangerM0use]

I'm afraid I haven't really managed to get anywhere! Let me tell you what I have managed to achieve.

I created an admin home page. On this page are 2 links. 1 to view the current articles stored in the DB. The second to create a new article.

The second page works fine. It takes you to a page with a form with the relevent areas needed to fill in. Code is as follows;

 

<?php

 

if (!isset($_POST['submit'])) {

// form not submitted

?>

 

<form action="<?=$_SERVER['PHP_SELF']?>" method="post">

Title: <input type="text" name="title">

Author: <input type="text" name="author">

Intro: <input type="text" name="intro">

Text: <input type="text" name="text">

<input type="submit" name="submit">

</form>

 

<?php

}

else {

//include config file

include "config.php";

 

// get form input

// check to make sure it's all there

// escape input values for greater safety

$title = empty($_POST['title']) ? die ("ERROR: Enter a title") : mysql_escape_string($_POST['title']);

$author = empty($_POST['author']) ? die ("ERROR: Enter an author") : mysql_escape_string($_POST['author']);

$intro = empty($_POST['intro']) ? die ("ERROR: Enter an intro") : mysql_escape_string($_POST['intro']);

$text = empty($_POST['text']) ? die ("ERROR: Enter the text") : mysql_escape_string($_POST['text']);

 

// open connection

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

 

// select database

mysql_select_db($db) or die ("Unable to select database!");

 

// create query

$query = "INSERT INTO news (title, author, intro, text) VALUES ('$title', '$author', '$intro', '$text')";

 

// execute query

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

 

// print message with ID of inserted record

echo "New record inserted with ID ".mysql_insert_id();

 

// close connection

mysql_close($connection);

}

?>

Not brilliant I know, I have made a better page but I thought I would give this as an example.

So the article is saved in the DB, the first page that I mentioned above should display titles of all the articles in the DB. The title is a link to the article which will be able to be edited from this point. This is where I am getting stuck.

I can display the titles on the view articles page. No problem. But I cannot get the title to become a link to the edit page for that article. This is what I have;

 

<?php

 

//include config file

include "config.php";

 

// create query

$query = "SELECT title, id FROM news";

 

// execute query

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

 

// see if any rows were returned

if (mysql_num_rows($result) > 0) {

// yes

// print them one after another

echo "<table cellpadding=10 border=1>";

while($row = mysql_fetch_row($result)) {

echo "<tr>";

echo "<td>".$row[0]."</td>";

echo "</tr>";

}

echo "</table>";

}

else {

// no

// print status message

echo "No rows found!";

}

 

// free result set memory

mysql_free_result($result);

 

// close connection

mysql_close($connection);

 

?>

 

I know I am being really simple, but I know once this part is done, I will be able to use the knowladge I now have (or should have had before) to finish off the rest of the site.

Anyone help? Ta.

[nloding]

You're not including the <a href...> HTML tags ...

 

<?php

while($row = mysql_fetch_assoc($result)) { // I like fetch_assoc better
        echo "<tr>";
        echo "<td><a href='article.php?id=".$row['id']."'>".$row['title']."</a></td>";
        echo "</tr>";
    }

?>

 

That example works if "id" and "title" are columns in your table ... if not, edit the code so it fits.  I told you that each article needed a unique ID ... I hope you did that.  Then on article.php, you'd have something like this:

 

<?php

$articleid = $_GET['id']; // fetch the artcle id from the URL

$query = "SELECT * FROM article WHERE id=".$articleid;
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result)) {
  // format your results here
}

?>

 

That's pretty much it.  Except for styling and formatting the output in the WHILE loop, you're done.

[DangerM0use]

So where you have put the comment format results here would you put the form fields?

[nloding]

What form fields are you talking about?

 

I think you need to do some more studying on the basics of using PHP and MySQL together.  Regardless of what you know though, here's the exact breakdown of what I'm proposing you do:

 

1) Make sure your database table has an auto_increment integer field as the primary key, so that every article in the database has a unique number associated with it.

2) index.php (or whatever your main page is): You do what I think you're already doing.  You query your database, and using a while loop, print out the results (which you did).  These results must include the <a href...> link, and the page it points to is "article.php?id=" + whatever the article id is.  This is what I wrote in my last post.

3) article.php: This page uses the PHP $_GET variables to get the id of the article requested from the URL itself (article.php?id=1029, $_GET['id'] == 1029).  You use this id to query the database for ONLY the row containing that article (WHERE id=).  Then, again using the WHILE loop just so you code looks the same every where, you can print out those results.  In reality, you don't need to use the WHILE loop on article.php, since you're only pulling one result -- you would just do "$row = mysql_fetch_assoc($result);"

4) Where my comment was "//format your results here" -- I meant however you want the data DISPLAYED!

 

If you're still not getting, sorry, but it's not any easier than that.  If you don't know what I'm talking about, you'll need to grab a PHP/MySQL book and read the entire thing before coming back.

[DangerM0use]

Sorry, I believed that is what you meant regarding that is where you format, just wanted to make sure. Regarding your checkpoints, yes, I have done those steps and will now attempt to do it. I think I figured it out while I was at work stood around doing nothing! Us students ey!

[DangerM0use]

Right, i've probably made a complete hash of it somewhere and confused my self but I cannot get it to work. I've come close to getting a version to work.

 

This version gets the data from the DB and puts it in the correct text boxes, but when I click update it doesn't. I'm not sure where to put the update query as I have placed it in different parts and it just didn't seem to either load the page or have an effect. The code is;

 

<?php 
if(empty($_GET['id'])) 
{ 
   echo "<p class='error'>No article ID supplied.</p>\n"; 
} 
else 
{ 

   //include config file 
   include "config.php"; 
   
   //The following code displays the data, but doesn't edit it
  $title = ($_POST['title']);
   $query = "SELECT title, author, intro, text FROM news WHERE id=".$_GET['id']; 
   $result = mysql_query($query) or die("Query failed: ".mysql_error()); 
   
   if(mysql_num_rows($result))  // found something 
   { 
      $row = mysql_fetch_assoc($result); 
      echo "<form action='admin.php' method='post'>\n<p>\n"; 
      echo "<input name='id' type='hidden' value='".$_GET['id']."'>\n"; 
      echo "Title: <input name='title' type='text' value='".$row['title']."'><br>\n"; 
      echo "Author: <input name='author' type='text' value='".$row['author']."'><br>\n"; 
      echo "Intro: <input name='intro' type='text' value='".$row['intro']."'><br>\n"; 
      echo "Text: <input name='text' type='text' value ='".nl2br($row['text'])."'><br>\n"; 
      echo "<input type='submit' value='Submit'>\n"; 
      echo "</p>\n</form>\n"; 
      } 
   else 
   { 
      echo "<p class='error'>Invalid article ID.</p>\n"; 
   } 
} 
?> 

 

And this code i made from help from people and from reading up using tutorials, books etc;

 

<?php
include "config.php";

$query=" SELECT * FROM news WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {

$atitle=mysql_result($result,$i,"atitle");
$aauthor=mysql_result($result,$i,"aauthor");
$aintro=mysql_result($result,$i,"aintro");
$atext=mysql_result($result,$i,"atext");
++$i;
}

$id=$_POST['id'];
$title=$_POST['title'];
$author=$_POST['author'];
$intro=$_POST['intro'];
$text=$_POST['text'];


$query="UPDATE news SET atitle='$title', aauthor='$author', aaintro='$intro', atext='$text' WHERE id='$id'";
mysql_query($query);
//echo "Record Updated";
//mysql_close();
?>

<form action="admin.php" method="post">
<input type="hidden" name="id" value="<? echo $id; ?>">
Title: <input type="text" name="title" value="<? echo $atitle; ?>"><br>
Author: <input type="text" name="author" value="<? echo $aauthor; ?>"><br>
Intro: <input type="text" name"author" value="<? echo $aintro; ?>"><br>
Text: <input type="text" name="text" value="<? echo $atext; ?>"><br>
<input type="submit" value="update">
</form>

 

I know I am probably frustrating a lot of you, but I know I am so close. I understood the last tutorial I did, but got confused where to put the update queries. That is all I am struggling with now as I understand everything else (i think!!!), or I at least have more of an understanding than I did before I started this project.

 

Anyone know where I am going wrong?

Thanks again for all the help you have given, I do really appreciate it, it just seems the hours and days I have spent reading and searching doesn't provide the understanding and answers I am looking for.

[nloding]

I don't see anything wrong with either code ... so you need to make it show you the error.

 

mysql_query($query) or die(mysql_error());

 

use that, and then come back with the error it shows.

[DangerM0use]

No error comes up for the first code, i click on the update button and it goes back to the admin page like it should. But the data that was changed in any of the text boxes does not update in the database like it should.

The second lot of code doesn't actually show anything in any of the text boxes. And I don't get any errors.

[nloding]

Correct me if I'm wrong: the 1st code posted works fine, as it should, no problems.  The second, which is admin.php (or part of it), doesn't work?

 

Tackle the database part first.  It's because you closed your connection before the update query.  Don't use "mysql_close()" until the bottom of the page, after everything else.  Then close it.  Try that, see if it updates.

 

To check for the values of the text boxes, view the source when it's open in a browser to see if it's getting output.  Also try just echoing the variables alone (ie, "echo $variable;") to make sure they are correct values and getting passed along correctly.

[DangerM0use]

These are two seperate pieces of code, that are meant to do the same thing. I was playing around with different methods that I have read about to see if I can understand one better than the other, but alas cannot get either to work.

The first piece of code I posted half works. It puts the database data that I want in the text boxes. But if I change anything in those text boxes and click update it doesn't do that. It simply goes to the admin home page which is what I put in the action method. I did have a thought last night that I maybe should have the action linking to another php page that has the update query in it. Would that be correct?

The second piece of code I showed didn't really work at all. It displayed the text boxes, but did not display the relevent data from the database in it so at the moment I don't know if the updat query actually works in that code.

[nloding]

OK, wow, you should have just said that the first time around.  Scrap the second code.  Stick with the first.  If everything works but the update, it's because you haven't told it to update the tables.  So you need to make it do that.  Here is a complete version of the first posted code that should work ...

 

update.php

<?php 
if(empty($_GET['id'])) 
{ 
   echo "<p class='error'>No article ID supplied.</p>\n"; 
} 
else if(!$_POST['id'])
{ 

   //include config file 
   include "config.php"; 
   
   //The following code displays the data, but doesn't edit it
  $title = ($_POST['title']);
   $query = "SELECT title, author, intro, text FROM news WHERE id=".$_GET['id']; 
   $result = mysql_query($query) or die("Query failed: ".mysql_error()); 
   
   if(mysql_num_rows($result))  // found something 
   { 
      $row = mysql_fetch_assoc($result); 
      echo "<form action='update.php' method='post'>\n<p>\n"; 
      echo "<input name='id' type='hidden' value='".$_GET['id']."'>\n"; 
      echo "Title: <input name='title' type='text' value='".$row['title']."'><br>\n"; 
      echo "Author: <input name='author' type='text' value='".$row['author']."'><br>\n"; 
      echo "Intro: <input name='intro' type='text' value='".$row['intro']."'><br>\n"; 
      echo "Text: <input name='text' type='text' value ='".nl2br($row['text'])."'><br>\n"; 
      echo "<input type='submit' value='Submit'>\n"; 
      echo "</p>\n</form>\n"; 
      } 
   else 
   { 
      echo "<p class='error'>Invalid article ID.</p>\n"; 
   } 
}
else
{
   $title = $_POST['title'];
   $author = $_POST['author'];
   $intro = $_POST['intro'];
   $text = $_POST['text'];

   $query = "UPDATE news SET atitle='$title', aauthor='$author', aaintro='$intro', atext='$text' WHERE id='$id'"
   if(!mysql_query($query)) {
      echo "Database did not update correctly:" . mysql_error();
   }
}
?>

 

Notice the form points to the same page it is on (keeps your scripts easier in my opinion.  Without running the update, the code will never update.  Try that, tweak it so you like the way it's layed out or whatever.  But that should work.

[DangerM0use]

Right, tried this code, I haven't tweaked the style yet as I just want to get it to work, but it still doesn't update the record.

I changed a bit of the code you put

$query = "UPDATE news SET atitle='$title', aauthor='$author', aaintro='$intro', atext='$text' WHERE id='$id'"

to this;

 

   $query = "UPDATE news SET title='$title', author='$author', intro='$intro', text='$text' WHERE id='$id'";

 

and also changed the form action to the admin home page. Does this have to be to an update.php page that contains an update query as well?

[nloding]

As with any programming, if the code is not on the page, it won't do it.  If you don't put the update query on whatever page the form action is, it won't do it.

 

Also, you may want to rename your "text" column ... TEXT is a MySQL data type, and it may be confusing later on ... I personally try to stay away from things like that.

[DangerM0use]

But there is an update query on the article page. Would there have to be an update query on the page that the submit button is directed to?

[nloding]

Yes.  How else does the server know to update the database?