Get information from MySQL database (PHP)

Question

I am building an online WYSIWYG editor, and I have constructed a method for clients to edit their own webpages through a <textarea> method in HTML. Now the only issue is that I have never dealt directly with databases (when I say 'never' I mean... never.)

I am seriously stuck on a few things about this... I'm hoping someone knows how to fix this.

So, code is as follows:
(all of this is within the body tags...)

<!-- Database Connection -->
<?php
    if ($_POST['submit']) {
        // MySql Database Connection, connecting at ("?", with details "user", "password") or spit error
        mysql_connect ("localhost", "user", "password") or die ('Error: ' . mysql_error());
        // Select database named ("dbName") or spit error
        mysql_select_db("dbName") or die ('Data error:' . mysql_error());
        // Select text to input, from textarea, name="textName"
        $text = mysql_real_escape_string($_POST['textName']); 
        // Input ^ into table "TableName", field (field)
        $query="INSERT INTO TableName (field) VALUES ('$text')";
        // Submit query, or spit error
        mysql_query($query) or die ('Error updating database' . mysql_error());
    }
?>

<!-- Actual Text Input -->
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <textarea name="textName">INITIAL TEXT</textarea>
    <input name="submit" type="submit" value="submit" />
</form>

The actual code for pushing information into the database is working, I can very easily input text into the textarea and click submit and then look at it inside the database I have setup- however I now need to take the information out of the database.

I need to pull it out in two ways:

1. To display it onto the webpage (which will be done on another document to this) AND
2. To display it inside the textarea, replacing the "INITIAL TEXT".

(If any of the code looks like it shouldn't work then it is probably because I have quickly removed information about the actual server).

Answer 1

In the words of Obi-Wan Kenobi, "you have much to learn about the ways of the force." But that's OK ... we all need to learn more if we want to go anywhere in life ;)

Nonetheless, I'll try and offer some help. First off, read up a bit on DB design. You should probably be putting more than one datum into that data table (things like timestamps, user ID and perhaps a unique ID [which the DBMS can do for you] come to mind).

The primary thing you'll notice about mysqli_* vs. mysql_* is the inclusion of the required data object:

$connection = mysqli_connect("localhost","root","","test");

$sql = "select * from myTable;";
$result = mysqli_query($connection,$sql);

And the above code also acts as a primer for getting a "result resource" from MySQL. But a "result resource" isn't actual data; it's more like a "pointer". If you did this:

echo $result;

You'd just get "1" if the query was successful.

Here's a brief bit of code that might help you understand what to do to get data from a database. I'm going to make some assumptions about your DB table structure, and I'm going to assume you know just which bit you want by including a contrived "$foo_id" variable.

$sql = "select timestamp, user_id, textfieldname from myTable where post_id = $foo_id;";
$res = mysqli_query($connection,$sql);
if ($res && $res->num_rows) { //we got a result, and the result contains data (at least 1 row)

  $return = array();   //this array will hold data for our script

  while ($row = $res->fetch_assoc()) {
     $return['text'] = $row['textfieldname'];
     $return['ts'] = $row['timestamp'];
     $return['user'] = $row['user_id'];
  }
}

At this point, the array $return should hold three variables of interest, one of which is the text you stored previously.