Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Server-side Scripting _ 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

Posted by: tyler.watkins Apr 10 2012, 03: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);
?>



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!

Posted by: Ephraim F. Moya Apr 10 2012, 08:46 PM

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.

Posted by: Brian Chandler Apr 11 2012, 07:28 AM

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.


Posted by: tyler.watkins Apr 11 2012, 08:11 AM

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

Posted by: Brian Chandler Apr 11 2012, 09:02 AM

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.

Posted by: tyler.watkins Apr 11 2012, 10:07 AM

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

Posted by: Brian Chandler Apr 11 2012, 10: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...

Posted by: Ephraim F. Moya Apr 11 2012, 11:45 AM

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-administration/ten-common-database-design-mistakes/

Posted by: tyler.watkins Apr 11 2012, 02:03 PM

What book?

Posted by: Brian Chandler Apr 11 2012, 02:07 PM

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...

Posted by: tyler.watkins Apr 11 2012, 02:12 PM

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?

Posted by: Darin McGrew Apr 11 2012, 05:26 PM

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.

Posted by: tyler.watkins Apr 11 2012, 05:39 PM

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!!

Posted by: Ephraim F. Moya Apr 11 2012, 11:15 PM




+----------+ . . . . . . . .+--------+
| 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?

Posted by: tyler.watkins Apr 11 2012, 11:50 PM

You lost me?

Posted by: Ephraim F. Moya Apr 12 2012, 12:02 AM

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.

Posted by: tyler.watkins Apr 12 2012, 05:38 AM

Thanks!

Posted by: Ephraim F. Moya Apr 12 2012, 10:51 AM

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.


Posted by: Christian J Aug 31 2012, 09:08 AM

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

Posted by: mde27 Apr 20 2013, 08:31 PM

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~

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)