The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> 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 7
tyler.watkins
post Apr 10 2012, 03:24 PM
Post #1


Novice
**

Group: Members
Posts: 27
Joined: 6-March 12
Member No.: 16,654



<?php
$mysql_host = "********";
$mysql_database = "********";
$mysql_user = "*************";
$mysql_password = "*********";


$fname=$_POST['fname'];
$lname=$_POST['lname'];
$user=$_POST['username'];
$password=$_POST['password'];
$verify=$_POST['verify'];
$email=$_POST['email'];
$country=$_POST['country'];
$month=$_POST['month'];
$day=$_POST['day'];
$year=$_POST['year'];


$con = mysql_connect( $mysql_host, $mysql_user, $mysql_password );

if ( !$con )
{
die( 'Could not connect: ' . mysql_error() );
}

mysql_select_db( "$mysql_database",$con );


$table = "CREATE TABLE `$user`
(
`fname` varchar(30),
`lname` varchar(30),
`username` varchar(30),
`password` varchar(30),
`email' varchar(30),
'country' varchar(30),
'month' varchar(30),
'day' int(2),
'year' int(4),
PRIMARY KEY '$user'
)" ;

echo "Table Created!";

mysql_query($table,$con)
or die (mysql_error());

mysql_close($con);
?>



Above is my php code. I am trying to make a login system. I keep getting this error message "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 7"

I have no idea what this means because i have no code on line 7. Any help is greatly appreciated.

Also if you have any suggestions, please pm me.

The form for this php code is on the page "http://blocks.netne.net/register.html "

Please visit and give me any suggestions as to what to do! Thanks!
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Ephraim F. Moya
post Apr 10 2012, 08:46 PM
Post #2


Advanced Member
****

Group: Members
Posts: 167
Joined: 2-September 07
From: New Mexico
Member No.: 3,702



QUOTE(tyler.watkins @ Apr 10 2012, 01:24 PM) *

<?php
$mysql_host = "********";
$mysql_database = "********";
$mysql_user = "*************";
$mysql_password = "*********";


$fname=$_POST['fname'];
$lname=$_POST['lname'];
$user=$_POST['username'];
$password=$_POST['password'];
$verify=$_POST['verify'];
$email=$_POST['email'];
$country=$_POST['country'];
$month=$_POST['month'];
$day=$_POST['day'];
$year=$_POST['year'];


$con = mysql_connect( $mysql_host, $mysql_user, $mysql_password );

if ( !$con )
{
die( 'Could not connect: ' . mysql_error() );
}

mysql_select_db( "$mysql_database",$con );


$table = "CREATE TABLE `$user`
(
`fname` varchar(30),
`lname` varchar(30),
`username` varchar(30),
`password` varchar(30),
`email' varchar(30),
'country' varchar(30),
'month' varchar(30),
'day' int(2),
'year' int(4),
PRIMARY KEY '$user'
)" ;

echo "Table Created!";

mysql_query($table,$con)
or die (mysql_error());

mysql_close($con);
?>


This line:
$user=$_POST['username'];
is the first error. You rename username to user.
This error cascades to make the rest of the program not work.
Note that this error is valid code, the error is with your use.
Note also that you're confused with a variable ($v) and a literal string (v) even with the same name.

This is the first cascade error:
$table = "CREATE TABLE `$user`
The name of the table is not user as you seem to want but some name that $user contains.

This is the next error cluster:
`email' varchar(30),
'country' varchar(30),
'month' varchar(30),
'day' int(2),
'year' int(4),
PRIMARY KEY '$user'
Note that you use an apostrophe instead of a backtick.
The PRIMARY KEY statement belongs with the definition for user (which doesn't exist).

You need to pay more attention to all the nitty gritty.
I didn't go into any other errors.


--------------------
Ephraim F. Moya
"Los pintos y los pendejos
Se conocen desde lejos."
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Apr 11 2012, 07:28 AM
Post #3


Jocular coder
********

Group: Members
Posts: 2,241
Joined: 31-August 06
Member No.: 43



QUOTE
Above is my php code. I am trying to make a login system. I keep getting this error message "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 7" I have no idea what this means because i have no code on line 7. Any help is greatly appreciated.


I'm pretty sure that the MySQL error message means line 7 of the chunk of SQL you have thrown at it. It looks as though this might be the first line on which you have used a quote (') in place of a backtick, so the confusing '' might somehow be meant to be ''', i.e. a quote inside quotes. Anyway, SQL error messages are not the most helpful, but as Ephraim said, you have to be *much* more careful.

I'm not sure I understand his comment about the $user variable, though. Maybe it's a mistake, or maybe there's a more major confusion and you are trying to create a table for each user, which is not what you want.



--------------------
Brian Chandler
Nothing in this post constitutes "commercial solicitation". PayPal does not solicit residents of Japan. Contents may settle in transit. "Legal mind" may or may not be brain-damaged.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tyler.watkins
post Apr 11 2012, 08:11 AM
Post #4


Novice
**

Group: Members
Posts: 27
Joined: 6-March 12
Member No.: 16,654



What would I do if I wanted to create a table with the name of the user? Like they have their own individual table.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Apr 11 2012, 09:02 AM
Post #5


Jocular coder
********

Group: Members
Posts: 2,241
Joined: 31-August 06
Member No.: 43



QUOTE
What would I do if I wanted to create a table with the name of the user? Like they have their own individual table.


If you think you want to do this, you have almost certainly missed the whole point of using a database. (You could try reading the first three chapters of the book again!)

You need one table for each *class* of entity (e.g. 'customer'), then you need one entry in the customer table for each customer. Nothing else makes any sense.

So generally it's simpler to use phpmyadmin (e.g.) to create the table, since you only need to do this once.


--------------------
Brian Chandler
Nothing in this post constitutes "commercial solicitation". PayPal does not solicit residents of Japan. Contents may settle in transit. "Legal mind" may or may not be brain-damaged.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tyler.watkins
post Apr 11 2012, 10:07 AM
Post #6


Novice
**

Group: Members
Posts: 27
Joined: 6-March 12
Member No.: 16,654



So I would make a table called users and everyone's information will be stored in that?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Apr 11 2012, 10:38 AM
Post #7


Jocular coder
********

Group: Members
Posts: 2,241
Joined: 31-August 06
Member No.: 43



QUOTE
So I would make a table called users and everyone's information will be stored in that?


Yes. (This really should be in Chapter 1 of the book!)

Because every 'user' has the same properties: name, id, date joined, whatever, something else...


--------------------
Brian Chandler
Nothing in this post constitutes "commercial solicitation". PayPal does not solicit residents of Japan. Contents may settle in transit. "Legal mind" may or may not be brain-damaged.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Ephraim F. Moya
post Apr 11 2012, 11:45 AM
Post #8


Advanced Member
****

Group: Members
Posts: 167
Joined: 2-September 07
From: New Mexico
Member No.: 3,702



QUOTE(Brian Chandler @ Apr 11 2012, 08:38 AM) *

QUOTE
So I would make a table called users and everyone's information will be stored in that?


Yes. (This really should be in Chapter 1 of the book!)

Because every 'user' has the same properties: name, id, date joined, whatever, something else...


See: http://www.simple-talk.com/sql/database-ad...esign-mistakes/


--------------------
Ephraim F. Moya
"Los pintos y los pendejos
Se conocen desde lejos."
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tyler.watkins
post Apr 11 2012, 02:03 PM
Post #9


Novice
**

Group: Members
Posts: 27
Joined: 6-March 12
Member No.: 16,654



What book?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Apr 11 2012, 02:07 PM
Post #10


Jocular coder
********

Group: Members
Posts: 2,241
Joined: 31-August 06
Member No.: 43



Whichever book you're using. (I suppose it might be an online tutorial)

Incidentally there's a very delicate question: should you call the table "users" (because it's something that holds all the users, or "user", because what you get from it is a table row representing a user. I tend to think the singular is better...


--------------------
Brian Chandler
Nothing in this post constitutes "commercial solicitation". PayPal does not solicit residents of Japan. Contents may settle in transit. "Legal mind" may or may not be brain-damaged.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tyler.watkins
post Apr 11 2012, 02:12 PM
Post #11


Novice
**

Group: Members
Posts: 27
Joined: 6-March 12
Member No.: 16,654



But if I make a table for all the users wouldn't all the usernames and passwords be stored there? And when someone goes to login how would I know if they use the right password that corresponds to their username?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Darin McGrew
post Apr 11 2012, 05:26 PM
Post #12


WDG Member
********

Group: Root Admin
Posts: 8,017
Joined: 4-August 06
From: Mountain View, CA
Member No.: 3



QUOTE
But if I make a table for all the users wouldn't all the usernames and passwords be stored there?
Yes. That's the point.

QUOTE
And when someone goes to login how would I know if they use the right password that corresponds to their username?
Don't look for an entry that merely has the password they entered. Instead, look for an entry that has the username they entered AND the password they entered.


--------------------
Darin McGrew
WDG Member since 1998
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tyler.watkins
post Apr 11 2012, 05:39 PM
Post #13


Novice
**

Group: Members
Posts: 27
Joined: 6-March 12
Member No.: 16,654



I'm sorry for all the probably amateurish questions, but I'm new to this. But do you have any examples of the code? Or what it would look like? I'm not asking for a full page of code, just a small snippet. And thanks a lot for all the help everyone!!

This post has been edited by tyler.watkins: Apr 11 2012, 05:40 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Ephraim F. Moya
post Apr 11 2012, 11:15 PM
Post #14


Advanced Member
****

Group: Members
Posts: 167
Joined: 2-September 07
From: New Mexico
Member No.: 3,702






+----------+ . . . . . . . .+--------+
| database +-+--------------+ table2 |
+----------+ |. . . . . . . +-----+--+
. . . . . . .|. .+--------+ . . . |
. . . . . . .+---+ table1 + . . . |
. . . . . . . . .+-----+--+ . . . |
. . . . . . . . . . . .|. . . . . | +----+----------+----------+------+
. . . . . . . . . . . .|. . . . . +-+ id + username + password + more +
. . . . . . . . . . . .|. . . . . | +----+----------+----------+------+
. . . . . . . . . . . .|. . . . . | +----+----------+----------+------+
. . . . . . . . . . . .|. . . . . +-+ id + username + password + more +
. . . . . . . . . . . .|. . . . . . +----+----------+----------+------+
. . . . . . . . . . . .|. . . . . . . . . . . . . . etc
. . . . . . . . . . . .|
. . . . . . . . . . . .| +----+----------+----------+------+
. . . . . . . . . . . .+-+ id + column 1 + column 2 + more +
. . . . . . . . . . . . .+----+----------+----------+------+
. . . . . . . . . . . . . . . . . . . . etc



IGNORE THE DOTS

Does anyone know how to keep spaces from collapsing in this board?

This post has been edited by Ephraim F. Moya: Apr 11 2012, 11:50 PM


--------------------
Ephraim F. Moya
"Los pintos y los pendejos
Se conocen desde lejos."
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tyler.watkins
post Apr 11 2012, 11:50 PM
Post #15


Novice
**

Group: Members
Posts: 27
Joined: 6-March 12
Member No.: 16,654



You lost me?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Ephraim F. Moya
post Apr 12 2012, 12:02 AM
Post #16


Advanced Member
****

Group: Members
Posts: 167
Joined: 2-September 07
From: New Mexico
Member No.: 3,702



QUOTE(tyler.watkins @ Apr 11 2012, 10:50 PM) *

You lost me?


first you select the database using the database's name. In this case the database name is database.

Next you use a sql query to select the data you want.

mysql_query( SELECT * FROM `table2` FOR `id` = 7 );

In English: give me all (*) the data for the row with id = 7.

What you'll get back is an array with all the data in row 7 or you'll get back a false if there's no row 7.

Study the diagram I posted. It describes a simple database like I think you want.

This post has been edited by Ephraim F. Moya: Apr 12 2012, 12:06 AM


--------------------
Ephraim F. Moya
"Los pintos y los pendejos
Se conocen desde lejos."
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tyler.watkins
post Apr 12 2012, 05:38 AM
Post #17


Novice
**

Group: Members
Posts: 27
Joined: 6-March 12
Member No.: 16,654



Thanks!
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Ephraim F. Moya
post Apr 12 2012, 10:51 AM
Post #18


Advanced Member
****

Group: Members
Posts: 167
Joined: 2-September 07
From: New Mexico
Member No.: 3,702



QUOTE(tyler.watkins @ Apr 12 2012, 04:38 AM) *

Thanks!


I made a mistake in the query string for reading a row. FOR should be WHERE.

$query = "SELECT * FROM `table2` WHERE `id` = '7'"; and then I noticed that you're using mysqli

This fits in the $resultArray = mysqli_query( $con, "{$query}", MYSQLI_STORE_RESULT ); instruction like this.

Of course there are supporting instructions all around these.



--------------------
Ephraim F. Moya
"Los pintos y los pendejos
Se conocen desde lejos."
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Aug 31 2012, 09:08 AM
Post #19


.
********

Group: WDG Moderators
Posts: 6,141
Joined: 10-August 06
Member No.: 7



I moved one post to its own thread: http://forums.htmlhelp.com/index.php?showtopic=16900


--------------------
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
mde27
post Apr 20 2013, 08:31 PM
Post #20





Group: Members
Posts: 1
Joined: 20-April 13
Member No.: 19,046



Please help! I'm having the same problem.. Here is my php code

<?php
$id = @$_REQUEST['id'];
session_start();
include("../admin/connections.php");

$query = mysql_query("SELECT * FROM album WHERE id = $id");
$fetch = mysql_fetch_array($query);

if(isset($_SESSION['account'])) {
$acct = $_SESSION['account'];
}

$sql = "SELECT a.name as name, r.id as id, r.account_id as act_id, r.album_id as albm_id, r.review as review, r.rating as rating, r.date as date FROM reviews r ";
$sql .= "LEFT JOIN accounts a ON a.acc_id = r.account_id WHERE r.album_id = $id";
$res = mysql_query($sql) or die(mysql_error());

if($_POST) {
$comment = $_POST['comment'];
$acct_id = $acct['id'];
$album_id = $fetch['id'];
$rating = @$_POST['rate'];
$date = date('Y-m-d H:i:s');

$sql = "INSERT INTO reviews(account_id, album_id, review, rating, date)";
$sql .= "VALUES($acct_id, $album_id, '$comment', $rating, '$date')";
$result = mysql_query($sql) or die(mysql_error());
header("Location: viewalbum.php");
}
?>

thank you in advance~
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 23rd October 2014 - 11:30 AM