Simple query

Anon

Hi guys,

I'm a complete newbie and desperatly need to do a very simple query from a MySQL database using PHP. Here's the situation:

I have a .php page that has a blank table which needs to be filled in with a block of text stored in the database.

Question1. How do I pull that block of text from the database and print them dynamically on the page?

Question2. How do I create a page (interface) which contains a form such that the text field of the form shows that block of text currently stored in the database? This is to allow users to change the text and then post and store it back in the database (replacing the previous contents).

Thanks heaps guys. I am really banking on this forum to help me out of my misery... Detailed instructions would be much appreciated.

calevans

Ed,

Neither of these are simple questions. Here's a snip of code from a VERY simple page I did that pulls a record out of a database and displays it.

<?PHP
// Make database connection
$dbcon = mysql_pconnect ($serverName,$userName,$userPassword) or die('Cannot connect to the server');
mysql_select_db ($databaseName,$dbcon) or die('Cannot connect to the database');

$poemArray = Array();

$sqlStatement = 'Select * from poem where poemID = '.$poemID;

// retrieve poem
$x=mysql_query($sqlStatement,$dbcon) or die('error in the query');
$poemArray = mysql_fetch_array($x,MYSQL_ASSOC);
// display.
?>
<pre><?echo stripslashes($poemArray['poem']);?></pre>

Now this is a VERY simple page. It only retrieves one row and displays the contents of one field but you can use it to get a feel for what is necessary.

Cal Evans
- Senior Internet Dreamer
- Techno-Mage
- Last of the great Internet Burma-Shave poets.
http://www.calevans.com
*

Anon

Thanks Cal.

Is there a limit on the array length?

If I wanted to retrieve and print out a few paragraphs of text, would it be better to use only one variable (if possible) to keep them, or a few?

Thanks again...

Anon

Thanks for that... I've now created a simple table in the database and am trying to load a data file into the table. But I'm not sure where to place the file data.txt.

The client software that I'm using the administer the database on the database server itself and I telnet to this machine. (My home directory is different from the mysql directory though). And I do not have the rights to place my file in the directory where my table is in.

Should I use LOCAL INFILE or not?

What's the syntax that I should use if I want to load from files in my home directory?

Thanks heaps!!

[deleted]

Are you talking about printing just ONE block of text on ONE page?

Anon

Yes. I'm using LONGTEXT and will be changing the default end of line from /n to something else (because my block of text contains /n as new lines).

[deleted]

Hold on, what exactly do you want to do. Exactly.

Anon

I would like to do the following ultimately:

Create a database with tables containing 3 columns each (id, title, textBody). Populate tables by loading in data files. The data files contains a large amount of text, including /n (this is why I have to change the LINE TERMINATED BY '/N').
Create a .php page to retrieve the data and print it on the web browser.
(This is for later and not so urgent) Create a page where users can change the contents of the tables from a web browser, using forms.

Anon

Also, would you know where I could get phpAdmin or something similar? Tried searching for phpAdmin but only got to numerous broken links.

Thanks!

[deleted]

1.
You don't have tht change anything about the files. You're going to need a PHP script to lead the data anyway, and when you do, you will use 'addslashes()' to make the text binary-safe.

Read this article:
http://www.phpbuilder.com/columns/florian19991014.php3

Again, read the article above, it explains a lot of it.
That is just a combination of 1 and 2.
Fetch the data from the database, and print it into a form that will submit it back to the database.

[deleted]

look for it on www.sourceforge.net

Anon

Thanks, what about loading in the file? At the moment, when I try to load a file into my tables, an error occurs saying that I do not have permission to do so. I'm supposed to have full permissions on my own database according to my administrator. Is there somes special config that should be done for loading in data files?

Anon

Thanks for that. I've actually come across the article before.

However, I'm not sure if that's what I want to do as I do not intend to store the entire html or php file in the database, I only intend to store the text content of a web page in the database. This text will be called from a template which is essentially a webpage with the text contents from the database.

So, the easiest way to do this is still 'LOAD DATA INFILE...' with a change in the line termination, isn't it?

Thanks...

Anon

Regarding 'addslashes()', could you please elaborate a little on that? I guess its something which adds slashes '/' to a text block...

Anon

Just one block...

I've managed to get the following to work ON ITS OWN:

<?
$dc = mysql_connect("localhost","user","pass")
or die ("Unable to connect to server");
mysql_select_db("myDB")
or die ("Unable to open database");
$QRETURN = @("select textBody from myTable")
or die ("Query failed");
while($line = mysql_fetch_array($QRETURN))
{
while(list($col_name, $col_value) = each($line))
print "$col_value";
}
mysql_close($dc);
?>

But when I pop the exact code into a table cell, the page returns an error.

How to I get it to just return the data? I tried

echo "$col_value";

instead of

print "$col_value";

and it doesn't work either. Please help me out... Ta!

[deleted]

Read this:
http://www.hvt-automation.nl/yapf/database/mysql_starterguide.php

Then read about what mysql_fetch_array() does.

Then re-think your script a little.

ps and stop quoting variabls, it is of no use whatsoever.

Anon

I got it to print out alright now, but yes, I do realize that it prints the same data twice in the script.

I'll read the article but if you could just point out what should be done, then that would save me time.

I'm sorry for quoting the variables. Like I said before, I am a complete newbie. And if you didn't know, I do appreciate your help.

[deleted]

Basically you should change it to this:
<?
error_reporting(E_ALL);

if (!$rDatabaseConnection = mysql_connect("localhost","user","pass"))
{
  echo 'Could not connect to database server';
}
else
{
  if (!mysql_select_db("myDB"))
  {
    echo 'Could not select database: '.mysql_error($rDatabaseConnection);
  }
  else
  {
    $sQuery = "select textBody from myTable";
    if (!$rResult = mysql_query($sQuery , $rDatabaseConnection))
    {
      echo 'Query failed: '.mysql_error($rDatabaseConnection);
    }
    else
    {
      while($aRow = mysql_fetch_array($rResult, MYSQL_ASSOC)) 
      { 
        echo $aRow['textBody'];
      }
    };
  };
  mysql_close($rDatabaseConnection); 
};
?>

Anon

The article was helpful and I got it to work. Thanks for taking time to reply too.

I guess this will be my last question, for tonight at least...

The data in my table is a block of LONGTEXT and eventhough it contains various paragraphs (\n) the result that is printed omits them. How do I get the text to be displayed the way they are?

Thanks mate!

[deleted]

That's usually because the linebrrak character has no effect in HTML.

use 'nl2br()' to replace the newlines with BR tags:

echo nl2br($aRow['field']);