Table has error

[Html]

Hi,

This error is given when I try to create a table for a db.

$sql='CREATE TABLE IF NOT EXISTS forum('
'post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,'.
'first_name VARCHAR(20) NOT NULL,'.
'last_name VARCHAR(40) NOT NULL,'.
'first_name VARCHAR(20) NOT NULL,'.
'subject VARCHAR(60) NOT NULL,'.
'message TEXT NOT NULL,'.
'post_date DATETIME NOT NULL,'.
'PRIMARY KEY (post_id))';

Quote

#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 '$sql='CREATE TABLE IF NOT EXISTS forum(' 'post_id INT UNSIGNED NOT NULL AUTO_IN' at line 1

Thanks.

[Funce]

Where are you pasting this code? That error doesn't look like a PHP error. (Which I'm assuming that is being written in)

 

If you've pasted that in directly to phpMyAdmin for instance, you'll need to try something like this instead. Please let me know how it goes!

CREATE TABLE IF NOT EXISTS forum(
  post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(20) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  first_name VARCHAR(20) NOT NULL,
  subject VARCHAR(60) NOT NULL,
  message TEXT NOT NULL,
  post_date DATETIME NOT NULL,
  PRIMARY KEY (post_id)
)

 

[Html]

I now get this from your sql table code

Quote

#1060 - Duplicate column name 'first_name'

The example I used was from a book, which is a beginners book for php 7.

Thanks.

[Html]

CREATE DATABASE accounts;

CREATE TABLE `accounts`.`users`
(
    `id` INT NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL,
     `last_name` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `password` VARCHAR(100) NOT NULL,
    `hash` VARCHAR(32) NOT NULL,
    `active` BOOL NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
);

This is from an example php files i have, this works, without the db section. Why wouldn't the book example work, I am using a free web host, but that shouldn't matter.

Quote

CREATE TABLE .`forum`
(
    `id` INT NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL,
     `last_name` VARCHAR(50) NOT NULL,
    `first_name VARCHAR(20) NOT NULL,
    `subject VARCHAR(60) NOT NULL,
    `message TEXT NOT NULL,
    `post_date DATETIME NOT NULL,
PRIMARY KEY (`id`)

 

I tried this,

Quote

#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 'subject VARCHAR(60) NOT NULL, `message TEXT NOT NULL, `post_date DATET' at line 6

 

Edited September 19, 2018 by Html

[Funce]

I didn't read it too thoroughly. It might've been a transcription error.

CREATE TABLE IF NOT EXISTS forum(
  post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(20) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  subject VARCHAR(60) NOT NULL,
  message TEXT NOT NULL,
  post_date DATETIME NOT NULL,
  PRIMARY KEY (post_id)
)

Try this. It doesn't have multiple `first_name` columns.

[Html]

The example has two first_name in the book.

[Funce]

Just now, Html said:

The example has two first_name in the book.

I wouldn't have an answer for why that is. Have you read further? Is it a chapter on error checking? I know some examples in books do that on purpose to teach the value of debugging errors. I have a C# book that did just that.

[dsonesuk]

You have  mismatched backticks, one starts beginning of field name but does not finish enclosing that field name, but the next.

You don't actually require backticks unless you are using a MySQL keyword.

No! field name can't be duplicated within the same table, it can be used in another table, and if you need to refer to both in SQL that is where backticks are used also.

Edited September 20, 2018 by dsonesuk

[Html]

I have a copy of Php 7 a basic book on creating php files, this is a basic forum page where the following will be sent to a db.

I'm just doing what the book has shown.

I decided to just try this, as the other book Learning Php doesn't make much sense to me, and I can't get anything done for free what I'm trying to get working, a user login system, post comments and so on. By trying out the chapter in the easy book, I hope to use that as a stepping stone for a user to post comments on their own profile index page.

Page 162 creating a forum, so it states

 

require.php

<?php
require ('/connect_db.php');
if (mysqli_ping($dbc))
{
echo 'MySQL Server', mysqli_get_server_info($dbc)
'on', mysqli_get_host_info($dbc);
}

<?php
require ('/connect_db.php');
if (mysqli_ping($dbc))
{
echo 'MySQL Server', mysqli_get_server_info($dbc)
'on', mysqli_get_host_info($dbc);
}

connect_db

Then the step two of the forum chapter, assign SQL so then the following in my first post.

Then states after now add a test report, whether the table was created successfully or describe the error on failure

if(mysqli_query($dbc, $sql)=== TRUE)

{

echo 'table "forum" created successfully and so on.

[Funce]

Apologies for the abruptness, but do you have a new question?

I'm not able to figure it out from your last posts.

[Html]

No,  but this example in the book won't create a table, unless it means out the sql instructions in the page? I doubt it. I've continued with making the php files, still have to finish step 5 of one, forum.php or create_forum.php

 

[Ingolme]

The code from your first post is PHP code, not SQL, but you seem to be running it in an SQL environment. It should be in a PHP file.

The code also is missing a "." at the end of the first line to concatenate it with the next line. I'm under the impression that the duplicate first_name is a result of not copying what was in the book correctly.

[Html]

So that is for a php file then, not to use in the SQL section of the phpmyadmin, that is what you are stating.

[Html]

I created the files, except for the last which is called process.php, the tutorial uses a localhost, so not sure if these files would work fine on any host. The forum.php, nothing does display.

It displays an index.html, but there is no step on that, require.php outside of a includes folder along with index.html, and before that htdocs which is on any free host or paid host, and that has connect_db.php

includes contains a header.html, and footer, as well css file.

 

I also tried to create the table in the database. So I can't create the table manually or using the page create_forum, nothing loads for that either.

Quote

Error

SQL query:

CREATE TABLE `epiz_22739806_text`.`forum` (

`id ` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARCHAR( 20 ) NOT NULL ,
`last_name` VARCHAR( 40 ) NOT NULL ,
`subject` VARCHAR( 60 ) NOT NULL ,
`message` TEXT NOT NULL ,
`post_date` DATE NOT NULL ,
PRIMARY KEY ( `id ` )

) ENGINE = MYISAM ;

 

 

 

MySQL said:

#1166 - Incorrect column name 'id '

 

Edited September 21, 2018 by Html
Tried to create table

[dsonesuk]

You don't usually have a space after the column name, you use backticks with column names that have a space between them, if you have a space after you will course confusion if someone new works with the same code, as they won't know the space exist, looking through column names unless they specifically select and highlight it.

[Html]

What column?

It was Post_id, so either way it doesn't work. The php pages don't display anything, I copied from the book, so not sure what is going on here. The book is from the series by Mike Mcgrath php 7 in easy steps.

As for the index.html, what is that about? forum.php should load without the need for a db?

[dsonesuk]

CREATE TABLE `epiz_22739806_text`.`forum` (

`id-` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARCHAR( 20 ) NOT NULL ,
`last_name` VARCHAR( 40 ) NOT NULL ,
`subject` VARCHAR( 60 ) NOT NULL ,
`message` TEXT NOT NULL ,
`post_date` DATE NOT NULL ,
PRIMARY KEY ( `id-` )

) ENGINE = MYISAM ;

Where '-' equals a space

[Html]

Okay I tried using func code that created a table.

<?php
$page_title = 'forum'
include('includes/header.html');
require ('\connect_db.php');
$sql = 'SELECT * FROM forum';
$result = mysql_query($dbc, $sql);
if ( mysqli_num_rows($result)>0)
{
echo '<table><tr><th>Posted By</th>
<th>Subject</th><th id="msg">Message</th></tr>';
while ($rows=mysqli_fetch_array($result, MYSQLI_ASSOC))
{
echo '<tr><td>'.
$row['first_name'].''.
$row['last_name'].'<br>'.
$row['post_date'].'</td><td>'.
$row['subject'].''.
$row['message'].''.
}
echo'</table>';
else
{
echo '<p>There are currently no messages.</p>';
}

echo'<p><a href="post.php">Post Message</a></p>';
mysqli_close($dbc);
incluide('includes/footer.html');
?>

So this is forum.php

doesn't display anything.

<?php
require ('/connect_db.php');
if (mysqli_ping($dbc))
{
echo 'MySQL Server', mysqli_get_server_info($dbc)
'on', mysqli_get_host_info($dbc);
}
?>

require.php

And create_forum.php

<?php
require('\connect_db.php');

$sql='CREATE TABLE IF NOT EXISTS forum('
'post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,'.
'first_name VARCHAR(20) NOT NULL,'.
'last_name VARCHAR(40) NOT NULL,'.
'first_name VARCHAR(20) NOT NULL,'.
'subject VARCHAR(60) NOT NULL,'.
'message TEXT NOT NULL,'.
'post_date DATETIME NOT NULL,'.
'PRIMARY KEY (post_id))';

if(mysqli_query($dbc, $sql)===True)
{
echo'Table "forum" created successfully';
}
else
{
echo 'Error Creating table:'.mysqli_error($dbc);
}
mysqli_close($dbc);
?>

[dsonesuk]

You still haven't fixed the error from ingolme post

To join two separate strings in php

'string no1' and 'string no2' you need a period between them

So having

$sql= 'string no1'
'string no2';

IS INVALID, look at above and reading the statement above that! WHY DID IT FAIL?

php is joining each line of a string into one line of a sql string statement and storing it to php variable $sql.

 

 

[Html]

Right okay,

So this is what it should be,

<?php
require('\connect_db.php');

$sql='CREATE TABLE IF NOT EXISTS forum('.
'post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,'.
'first_name VARCHAR(20) NOT NULL,'.
'last_name VARCHAR(40) NOT NULL,'.
'first_name VARCHAR(20) NOT NULL,'.
'subject VARCHAR(60) NOT NULL,'.
'message TEXT NOT NULL,'.
'post_date DATETIME NOT NULL,'.
'PRIMARY KEY (post_id))';

if(mysqli_query($dbc, $sql)===True)
{
echo'Table "forum" created successfully';
}
else
{
echo 'Error Creating table:'.mysqli_error($dbc);
}
mysqli_close($dbc);
?>

The page doesn't do anything when i try to load it, which is obviously how the table would be loaded into the db.

[Ingolme]

This line is probably causing the program to halt because it cannot find the file:

require('\connect_db.php');

Starting with a backslash on Linux will not work correctly, and on Windows it will look for the file at the root of your computer, which is probably directly under C:\ depending on how Windows was set up.

[dsonesuk]

Windows local server is more forgiven than Linux in regards when you mistakenly use '\' instead of '/', the problem is highlighted when you upload to hosts Linux server.

[Html]

Ah, I see.

I tried editing both pages, nothing.

<?php
$page_title = 'forum'
include('includes/header.html');
require ('/connect_db.php');
$sql = 'SELECT * FROM forum';
$result = mysql_query($dbc, $sql);
if ( mysqli_num_rows($result)>0)
{
echo '<table><tr><th>Posted By</th>
<th>Subject</th><th id="msg">Message</th></tr>';
while ($rows=mysqli_fetch_array($result, MYSQLI_ASSOC))
{
echo '<tr><td>'.
$row['first_name'].''.
$row['last_name'].'<br>'.
$row['post_date'].'</td><td>'.
$row['subject'].''.
$row['message'].''.
}
echo'</table>';
else
{
echo '<p>There are currently no messages.</p>';
}

echo'<p><a href="post.php">Post Message</a></p>';
mysqli_close($dbc);
incluide('includes/footer.html');
?>

There is no actual footer.html or anything, I doubt that would matter.

 

<?php
require ('/connect_db.php');
if (mysqli_ping($dbc))
{
echo 'MySQL Server', mysqli_get_server_info($dbc)
'on', mysqli_get_host_info($dbc);
}
?>

That was correct already.

Edited September 22, 2018 by Html
Added require.php code

[dsonesuk]

You need to enable display of errors, any errors will cause the page to fail I'm surprised your editor did not pick it up.

The spelling of include is wrong in this case, and yes if you include a none existent file, that too will cause an error causing the code to fail.

[Html]

Well there is no includes folder or footer.html. There is no tutorial on that. I guess I'll have to just try something.