A few PHP/MySQL issues

[Dr. Watson]

Hey,

Just a few issues, I'd like to resolve with the help of willing fellow SitePoint members

Issue 1

I am working on a content management system to make it easy to manage the websites in my new Icelandic free webmaster resources and tips website. So I made up a form that collects information about a website and posts it into the database.

The problem is, that when I make an entry, sometimes the page or MySQL or whatever it is, decides to make 1-3 empty rows in my database and then post the data.

How can I prevent that?
This is the code for the form, and I am using MySQL.

<form action="insert.php" method="post">
 
<TABLE>
 
<TR><TD>Lýsing síðu</TD><TD><input type="text" name="description" maxchar="255"></TD></TR>
 
<TR><TD>Lykilorð</TD><TD><input type="text" name="keywords" maxchar="255"></TD></TR>
 
<TR><TD>Titill</TD><TD><input type="text" name="title" maxchar="20"></TD></TR>
 
<TR><TD>Efni</TD><TD><textarea name="content"></textarea></TD></TR>
 
<TR><TD>Fótur</TD><TD><input type="text" name="footer" maxchar="200"></TD></TR>
 
<TR><TD>Auglýsing</TD><TD><input type="text" name="advertisement" maxchar="70"></TD></TR>
 
<TR><TD>Mynd</TD><TD><input type="text" name="image" maxchar="50"></TD></TR>
 
<TR><TD></TD><TD><input type="Submit" value="Vista"></TD></TR>
 
</TABLE>
 
</form>
 
<?
 
$username="snilldar_vefur";
 
$password="----beeep-----";
 
$database="snilldar_vefsidugerd";
 
$description=$_POST['description'];
 
$keywords=$_POST['keywords'];
 
$title=$_POST['title'];
 
$content=$_POST['content'];
 
$footer=$_POST['footer'];
 
$advertisement=$_POST['advertisement'];
 
$image=$_POST['image'];
 
mysql_connect(localhost,$username,$password);
 
@mysql_select_db($database) or die( "Villa við að velja gagnagrunn");
 
$query = "INSERT INTO vefsida VALUES ('','$description','$keywords','$title','$content','$footer','$advertisement','$image')";
 
mysql_query($query);
 
mysql_close();
 
?>

Issue 2

Issue 2 mainly consist of my lack of knowledge in PHP and MySQL. I'd like to know how I can select invidual rows and make 1 page display all information from a row, so here is my attempt, that doesn't really work:

 <? 
 
$id=$_GET['id'];
$username="snilldar_vefur";
$password="----beep------";
$database="snilldar_vefsidugerd";
mysql_connect(localhost,$username,$password);
 
@mysql_select_db($database) or die( "Gat ekki valið gagnagrunn");
 
$query="SELECT * FROM vefsida WHERE id='$id'";
 
$result=mysql_query($query);
 
mysql_close();
 
$description=mysql_result($result,$i,"description");
 
$keywords=mysql_result($result,$i,"keywords");
 
$id=mysql_result($result,$i,"id");
 
$title=mysql_result($result,$i,"title");
 
$content=mysql_result($result,$i,"content");
 
$footer=mysql_result($result,$i,"footer");
 
$advertisement=mysql_result($result,$i,"advertisement");
 
$image=mysql_result($result,$i,"image");
 
?>
 
Title: <?php $title ?>

[samsm]

Issue #1 is kind of two issues.

The first, and the reason you are seeing the empty rows problem, is that the insert code is running every time the page is loaded, not every time the form is submitted.

An "if" condition will fix that problem.

if ($_POST['Submit'])
{
   // put your php for dealing with the form data here
} 

The second problem is that there is nothing keeping incomplete forms from being added. Even if you add the if condition above, if someone were to hit the submit button without filling out anything you'd get the empty row.

So, you might consider testing each variable for minimum requirements:

if (strlen($_POST['description'])>2)
{
   $description= $_POST['description'];
} 
else
{
   $incomplete_form = true;
   echo '<p>Description must be longer than 2 characters</p>';
} 

Later in the code, before the additions are made to the database, you'd use another "if" to check to see if $incomplete_form was true.

Also, if magic_quotes are off, you will need to add something that deals with funky characters in the user entries:

$description= mysql_escape_string($_POST['description']); 

Ok, that's why you have issue #1, to my knowledge. Holler back if you don't understand anything.

[samsm]

Issue #2:

$query="SELECT * FROM vefsida WHERE id='$id'";
$result=mysql_query($query);
$r = mysql_fetch_row($result);
echo $r['description'];
echo '<br />';
echo '<pre>';
print_r($r);
echo '</pre>'; 

Try that, tell me if that answers your second question.

[Dr. Watson]

Thank you, you successfully explained to me, the PHP newbie, what the problem is in Issue 1. Thank you so much!

Secondly, I changed the code, the page now returns the following:

Array
(
[0] => 2
[1] => erwer
[2] => werkælqwerkæ
[3] => kæ
[4] => kælkæ
[5] => kæk
[6] => ælkæk
[7] => ækæk
)
Title:

By the way the nonsense (erwer, and so on) is what was entered into row nr. 2 when testing.

So basically, that half-way does it, it shows me the content, but it won't print anything in "Title: $title". So, it's reading the database, but not showing up correctly. That makes me wonder, I got that "$i" in the code from a tutorial website that used loops, do you think removing that unnecessary $i for every attribute would solve the problem?

[samsm]

I made a small error in my second post.

I meant to use mysql_fetch_assoc($result) instead of mysql_fetch_row.

But first, all of those functions do the same thing. They take a row (as requested by your query) and place it into an array where it is available for you to use however. Once you get the data from mysql this way, there is no need to additionally use mysql_fetch().

You can then access the information in the array as $array_name['key_name'].

So, picking up in your code from:
$r = mysql_fetch_assoc($result);

After that point, you can access that row as $r['column-name'].
If you feel more comfy with $column-name, you could do this:
$column-name = $r['column-name'];
... but the difference is mostly superficial.

Does that make sense?

[Dr. Watson]

Both problems have been successfully solved thanks to you! Thanks a lot