mySQL Error: 1064

[fatnslow]

Hey, I'm trying to create a table with this:



CREATE TABLE guests (
id int(10) NOT NULL auto_increment,
name varchar(50) NOT NULL,
message varchar(255) NOT NULL,
date timestamp(14) NOT NULL,
PRIMARY KEY (id) * );


But i just get this error:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* )' at line 5

How can I get the table to be created?

I know as much as nothing about databases, so please help if you can.

Thanks,
fatnslow

EDIT: Problem solved

ummmm you don't need the * do you :S


Still need some help on later replies. Thanks

[fatnslow]

Btw, I tried creating it manually, but no luck. I can get it to be like this:


CREATE TABLE guests (
id int(10) NOT NULL auto_increment,
name varchar(50) NOT NULL,
message varchar(255) NOT NULL,
date timestamp(14) NOT NULL,
PRIMARY KEY (id)
);

But I cant get the little *

Thanks for any replies.

[RushScripting]

ummmm you don't need the * do you :S

[fatnslow]

Hmm, I have no idea actually. Lol, I'll try without

Thanks

[fatnslow]

Ok, I got it working now.

But; since this is a php guestbook script, I wonderd how do I delete entries? or add some kind of page system. Or maybe the script could delete old entries, like when it have been posted 20 entries and I post one more, it will delete the oldest, so it will never be more than 20 entries on the page...?

Anyone who knows php could help me here? Or sugest another way.

Here is my php script:


mysql_connect($host, $user, $pass) OR die ("Could not connect tothe server.");
mysql_select_db($db) OR die("Could not connect to the database.");

$name = stripslashes($_POST['txtName']);
$message = stripslashes($_POST['txtMessage']);
if (!isset($_POST['txtName'])) {
$query = "SELECT id, name, message, DATE_FORMAT(date, '%d.%m.%y kl. %H:%i') as newdate FROM guests ORDER BY id DESC";
$result = mysql_query($query);

while ($row = mysql_fetch_object($result)) {
?>
<p><br /><b>Skrevet av <?php echo $row->name; ?> den <?php echo $row->newdate; ?></b><br><?php echo $row->message; ?></p>
<?php

}

?>

<?php
}
else {
// Adds the new entry to the database
$query = "INSERT INTO guests SET message='$message', name='$name', date=NOW()";
$result = mysql_query($query);
// Takes us back to the entries
$ref = $_SERVER['HTTP_REFERER'];
header ("Location: $ref");
}
?>


Edit: And I also get this error everytime I post a message:

Warning: Cannot modify header information - headers already sent by (output started at /home/www/vsffc.freeprohost.com/Test/guestbook.php:9) in /home/www/vsffc.freeprohost.com/Test/guestbook.php on line 82
What I have done is I have just put the form in another cell, so it doesn't work. But is it possible to just add a refresh script/line instead of this

$ref = $_SERVER['HTTP_REFERER'];
header ("Location: $ref");

Thanks

[fatnslow]

Anyone could help?

Thanks

[fatnslow]

Anyone able to help?

[terlan]

ok first the headers problem.


web pages are sent to browser like this. (*very simply put)

<headers>
<page contents>

Once Page content begins, header content cant be changed. the PHP location command doesnt work like javascript equivilent. It is sent before ANY page content.

before the SECOND <?php in your code, you output text. so you cant change the headers at that point.

Ok work around is easy your code is near enough correct.

try the following (from your while loop)

while ($row = mysql_fetch_object($result)) {
echo'<p><br /><b>Skrevet av ';
echo $row->name;
echo' den ';
echo $row->newdate;
echo '</b><br';
echo $row->message;
echo '</p>';
}
}else {
// Adds the new entry to the database
$query = "INSERT INTO guests SET message='$message', name='$name', date=NOW()";
$result = mysql_query($query);
// Takes us back to the entries
$ref = $_SERVER['HTTP_REFERER'];
header ("Location: $ref");
}
?>

There might be more errors but try this first see if it helps.

try adding exit;
below header ("Location: $ref"); just to be sure no other code is executed.


Adding the LIMIT option in your SQL line allows you to code in some page control sorta like this.
$start=0;
$max=10;
$query =" SELECT * FROM table where a='$sumthing' ORDER BY id DESC LIMIT $start,$max"
$result=mysql_query($query);
This would grab rows from table but only return results $start to $max. (0 to 10 in this case)

hope this helps a little.

[fatnslow]

Hmm, I tried your script and I just inserted it under while (....) etc, but now I got this error:

Warning: Cannot modify header information - headers already sent by (output started at /home/www/vsffc.freeprohost.com/Test/guestbook.php:9) in /home/www/vsffc.freeprohost.com/Test/guestbook.php on line 80

Im not sure what this code does, because I still get the header error after posting a message in the guestbook. And now only the headtitles of the guestbook entries(name,date) are shown, not the message. Isn't it some easier way just to make the page refresh or something?

I didn't try your limit sql script, because I have found out that I will in the end get alot of numbers, and my database is limited to 2MB. So is it any way I just can make the database delete the entries when it get to a certain amount? (So the page doesnt get to long)

I appreciate your help, but I need some more of it :P

Thanks for any replies.

[terlan]

hmm. let me code some and get back to you. I replied a bit quick the last time. let me work on it for a bit, then i'll get back to you.

[fatnslow]

Ok, thanks for looking into it.

[terlan]

Hi.
The code below should sort it out for you.
paste it into a blank file, save it as guestbook.php and it'll do what it says on the can. I'll leave the formating to you to sort out :)

Basically instead of using any redirect it's all done in one page it works like this.

is there a new entry.
add it to database
start html code
display guestbook form
display previous entries using php
end html

The code isn't perfect or as refined as it could be. But it should be easy to expanded upon.

but hey, good code doesn't need to be elegant......it just needs to work


<?php
// CONNECT TO DATABASE
// put your details here
define ('HOST','localhost');
define ('USER', '' );
define ('PASS', '' );
define ('DB', 'testing');
$connection=mysql_connect(HOST, USER, PASS);
if ($connection){
mysql_select_db(DB);
}else{
echo ('Could Not Connect to Database');
exit;
}

// if name variable exists add it to database THEN display all entries.
if ($HTTP_POST_VARS['txtName']) {
// add to database
$name = stripslashes($HTTP_POST_VARS['txtName']);
$message = stripslashes($HTTP_POST_VARS['txtMessage']);
$query = "INSERT INTO guests SET message='$message', name='$name', date=NOW()";
$result = mysql_query($query);
}
// ADD SOME HTML
?>
<HTML>
<HEAD>
<TITLE>test guestbook</TITLE>
</HEAD>
<BODY BGCOLOR=#222222 text="#EFEFEF" LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0>
<form name="guest" method="post" action="guestbook.php">
<p>name
<input name="txtName" type="text" id="txtName">
</p>
<p>message
<textarea name="txtMessage" id="txtMessage">add your comments</textarea>
</p>
<p>
<input type="submit" name="Submit" value="Submit">
</p>
</form>


<?
// DISPLAY CODE GOES HERE
$query = "SELECT id, name, message, DATE_FORMAT(date, '%d.%m.%y kl. %H:%i') as newdate FROM guests ORDER BY id DESC";
$result = mysql_query($query);
while ($row=mysql_fetch_object($result)) {
$date=date($row['date'], '%d.%m.%y kl. %H:%i');
echo '<p><br /><b>Skrevet av '.$row->name.' den '.$row->newdate.'</b><br>'.$row->message.'</p>';
}
?>
</body></html>

[fatnslow]

Ok, thanks. I'll try it out asap.

[fatnslow]

Hey terlan, it works great. But I don't really get how I get the limit thing to work. What do I add in the php, and what do I add in the database.

A short question: Does the entries be saved in the database? Because at the moment I'm on a free host, and I have a 2MB limit on the database, and it would be great not using all of it.

Isn't it some way to tell database to delete entries when it is added more than like 25? So it only would be shown 25 entries at all times.

Need some help as you can see.

Thanks,
fatnslow

[terlan]

Hi again.

It'll take a lot of entries before your 2mb limit is reached. So don't stress out on it too much. Your guest book entries are limited to 255 characters, which is 255 bytes. add another characters for the users name and a couple more for the index and date record and just to get a nice number lets say it all comes to 300 characters.
Thats 300 bytes. Now 2mb is 2,097,152 bytes, so you'd need roughly 7000 entries in your guestbook to fill your database. Probably more as not everyone would use the full 255 characters. So you dont need to worry about overloading your database too quickly. unless you get about 15,000 visitors a day, in which case get some PPC ads on quick ;)
So what you want is not an automatic script to delete entries. To be honest some idiot would come along notice this happening enter 25 entries into your guestbook saying silly things and effectively wipe your legitimate entries. It would be easier to go in once a month and manually trim the database with some sort of admin interface.(phpmyadmin is the common one).

[fatnslow]

Ok, I agree it will take some time to fill my database :)

I do have phpmyadmin on the database, but I don't really understand how I can delete entries from there. Could you guide me alitle?

But again, I need som sort of limiting, maybe just adding like pages (1,2,3 ...) for the entries, or else the guestbook page would be very long.

EDIT: Started to wonder, what is PPC ads?

Thanks for helping.

[fatnslow]

Ok, I agree it will take some time to fill my database :)

I do have phpmyadmin on the database, but I don't really understand how I can delete entries from there. Could you guide me alitle?

But again, I need som sort of limiting, maybe just adding like pages (1,2,3 ...) for the entries, or else the guestbook page would be very long.

EDIT: Started to wonder, what is PPC ads?

Thanks for helping.

[fatnslow]

Bump