Html Posted September 19, 2018 Share Posted September 19, 2018 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. Link to comment Share on other sites More sharing options...
Funce Posted September 19, 2018 Share Posted September 19, 2018 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) ) Link to comment Share on other sites More sharing options...
Html Posted September 19, 2018 Author Share Posted September 19, 2018 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. Link to comment Share on other sites More sharing options...
Html Posted September 19, 2018 Author Share Posted September 19, 2018 (edited) 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 Link to comment Share on other sites More sharing options...
Funce Posted September 19, 2018 Share Posted September 19, 2018 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. Link to comment Share on other sites More sharing options...
Html Posted September 19, 2018 Author Share Posted September 19, 2018 The example has two first_name in the book. Link to comment Share on other sites More sharing options...
Funce Posted September 19, 2018 Share Posted September 19, 2018 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. Link to comment Share on other sites More sharing options...
dsonesuk Posted September 20, 2018 Share Posted September 20, 2018 (edited) 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 Link to comment Share on other sites More sharing options...
Html Posted September 20, 2018 Author Share Posted September 20, 2018 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. Link to comment Share on other sites More sharing options...
Funce Posted September 20, 2018 Share Posted September 20, 2018 Apologies for the abruptness, but do you have a new question? I'm not able to figure it out from your last posts. Link to comment Share on other sites More sharing options...
Html Posted September 20, 2018 Author Share Posted September 20, 2018 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 Link to comment Share on other sites More sharing options...
Ingolme Posted September 21, 2018 Share Posted September 21, 2018 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. Link to comment Share on other sites More sharing options...
Html Posted September 21, 2018 Author Share Posted September 21, 2018 So that is for a php file then, not to use in the SQL section of the phpmyadmin, that is what you are stating. Link to comment Share on other sites More sharing options...
Html Posted September 21, 2018 Author Share Posted September 21, 2018 (edited) 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 Link to comment Share on other sites More sharing options...
dsonesuk Posted September 21, 2018 Share Posted September 21, 2018 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. Link to comment Share on other sites More sharing options...
Html Posted September 21, 2018 Author Share Posted September 21, 2018 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? Link to comment Share on other sites More sharing options...
dsonesuk Posted September 21, 2018 Share Posted September 21, 2018 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 Link to comment Share on other sites More sharing options...
Html Posted September 21, 2018 Author Share Posted September 21, 2018 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); ?> Link to comment Share on other sites More sharing options...
dsonesuk Posted September 21, 2018 Share Posted September 21, 2018 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. Link to comment Share on other sites More sharing options...
Html Posted September 21, 2018 Author Share Posted September 21, 2018 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. Link to comment Share on other sites More sharing options...
Ingolme Posted September 21, 2018 Share Posted September 21, 2018 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. Link to comment Share on other sites More sharing options...
dsonesuk Posted September 22, 2018 Share Posted September 22, 2018 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. Link to comment Share on other sites More sharing options...
Html Posted September 22, 2018 Author Share Posted September 22, 2018 (edited) 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 Link to comment Share on other sites More sharing options...
dsonesuk Posted September 22, 2018 Share Posted September 22, 2018 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. Link to comment Share on other sites More sharing options...
Html Posted September 22, 2018 Author Share Posted September 22, 2018 Well there is no includes folder or footer.html. There is no tutorial on that. I guess I'll have to just try something. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now