creating table from php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lilpenguin
Forum Newbie
Posts: 18
Joined: Sun Sep 19, 2010 7:06 pm

creating table from php

Post by lilpenguin »

Here is my code and it's telling me expected t_string at line 1. But table is not a string and I've tried it without and with the ' around it.

This is the code after successful connection to the databse.

<?


CREATE TABLE 'users' (
'id_user' INT NOT NULL AUTO_INCREMENT ,
'name' VARCHAR( 128 ) NOT NULL ,
'username' VARCHAR( 16 ) NOT NULL ,
'password' VARCHAR( 32 ) NOT NULL ,
'confirm' VARCHAR( 32 ) NOT NULL ,
'email' VARCHAR( 64 ) NOT NULL ,
'offer' VARCHAR( 64 ) NOT NULL ,
'music' VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( 'id_user' )
)


Thank you!
?>
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: creating table from php

Post by Jonah Bron »

lilpenguin wrote:This is the code after successful connection to the databse.
You have a major problem here. Can you give us the entire code? (just remove any passwords before posting, and please use the PHP Code button.
almedajohnson
Forum Newbie
Posts: 7
Joined: Sun Sep 19, 2010 11:21 pm

Re: creating table from php

Post by almedajohnson »

Hi
I think this is may be because you have not put the semicolon at last curlybracket as per the syntax.
Use following code and try

CREATE TABLE 'users' (
'id_user' INT NOT NULL AUTO_INCREMENT ,
'name' VARCHAR( 128 ) NOT NULL ,
'username' VARCHAR( 16 ) NOT NULL ,
'password' VARCHAR( 32 ) NOT NULL ,
'confirm' VARCHAR( 32 ) NOT NULL ,
'email' VARCHAR( 64 ) NOT NULL ,
'offer' VARCHAR( 64 ) NOT NULL ,
'music' VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( 'id_user' )
);


Thank you!
?>
If still there is problem then I suggest you to following code so you can figure out the problem occurring:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// Create database
if (mysql_query("CREATE DATABASE my_db",$con))
{
echo "Database created";
}
else
{
echo "Error creating database: " . mysql_error();
}

// Create table
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE tablename
(
Field1 varchar(15),
field2 varchar(15),
field3 int
)";

mysql_close($con);
?>
lilpenguin
Forum Newbie
Posts: 18
Joined: Sun Sep 19, 2010 7:06 pm

Re: creating table from php

Post by lilpenguin »

Jonah Bron wrote:
lilpenguin wrote:This is the code after successful connection to the databse.
You have a major problem here. Can you give us the entire code? (just remove any passwords before posting, and please use the PHP Code button.
Here's the code I got. I'll try the other code when I get back tonight.

<?php


$servername='localhost';

$dbusername='usernam';
$dbpassword='pw';

$link=mysql_connect ("$servername","$dbusername","$dbpassword")
or die ( " Not able to connect to server ");


CREATE TABLE 'users' (
'id_user' INT NOT NULL AUTO_INCREMENT ,
'name' VARCHAR( 128 ) NOT NULL ,
'username' VARCHAR( 16 ) NOT NULL ,
'password' VARCHAR( 32 ) NOT NULL ,
'confirm' VARCHAR( 32 ) NOT NULL ,
'email' VARCHAR( 64 ) NOT NULL ,
'offer' VARCHAR( 64 ) NOT NULL ,
'music' VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( 'id_user' )
);

?>

Thanks!
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: creating table from php

Post by Jonah Bron »

You can't just put SQL inside of PHP. Read this: http://www.w3schools.com/php/php_mysql_intro.asp (the whole thing)

You need to put your query into a mysql_query().
lilpenguin
Forum Newbie
Posts: 18
Joined: Sun Sep 19, 2010 7:06 pm

Re: creating table from php

Post by lilpenguin »

Thanks a lot for that! I feel a bit silly now:) I'm more used to phpmyadmin, but I want to do all coding now.
What is confusing me is that it appears that some of the syntax even if you run a query in phpmyadmin is different than running it in php/mysql code.
For example, I inputted info into a table in phpmyadmin and copied the mysql query into php and mysql code and it comes out with error. Obviously I edited the code somewhat, but cant find my latest syntax error:/

When trying to insert data into a table, I receive Error: 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 21

<?php

$server='servername';
$dbusername='username';
$dbpassword='pw';

$conn=mysql_connect ($server, "$dbusername","$dbpassword")
or die ( " Not able to connect to server ");

mysql_select_db("db_name", $conn);

$sql="INSERT INTO `db_name`.`users` (
id_user ,
name ,
username ,
password ,
confirm ,
email ,
offer ,
music
)
VALUES
(
'$_POST[id_user]' ,
'$_POST[name]',
'$_POST[username]',
'$_POST[password]',
'$_POST[confirm]',
'$_POST[email]',
'$_post[offer]',
'$_post[music]',
)";

if (!mysql_query($sql,$conn))
{
die('Error: ' . mysql_error());
}
echo "1 record added";

mysql_close($conn);



?>

Perhaps I just need extra eyes:) Thanks!!
lilpenguin
Forum Newbie
Posts: 18
Joined: Sun Sep 19, 2010 7:06 pm

Re: creating table from php

Post by lilpenguin »

I thought I fixed it. No more syntax errors. However when the script is executed, nothing shows up on the page. What is the most common cause of this?
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: creating table from php

Post by Jonah Bron »

Probably a fatal error. Turn on error reporting in your php.ini file

http://www.phoca.cz/documents/16-joomla ... rs-on-site

After you do that, post your code (don't forget the [ syntax=php][/syntax] tags) and tell us what the error is (that is, if you can't fix it).
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

Re: creating table from php

Post by DigitalMind »

Why do you set id_user in values list? It's auto_increment.

Code: Select all

<?php

$server='servername';
$dbusername='username';
$dbpassword='pw';

$conn = new mysqli($server, $dbusername, $dbpassword, $db_name) or die ('Could not connect!');
$stmt = $conn->prepare('insert into users (name, username, password, confirm, email, offer, music) values (?, ?, ?, ?, ?, ?, ?)');
$stmt->bind_param('sssssss', $_POST['name'], $_POST['username'], $_POST['password'], $_POST['confirm'], $_POST['email'], $_POST['offer'], $_POST['music']);
$stmt->execute() or die ('Query error!');
$stmt->close();

?> 
lilpenguin
Forum Newbie
Posts: 18
Joined: Sun Sep 19, 2010 7:06 pm

Re: creating table from php

Post by lilpenguin »

DigitalMind wrote:Why do you set id_user in values list? It's auto_increment.

Code: Select all

<?php

$server='servername';
$dbusername='username';
$dbpassword='pw';

$conn = new mysqli($server, $dbusername, $dbpassword, $db_name) or die ('Could not connect!');
$stmt = $conn->prepare('insert into users (name, username, password, confirm, email, offer, music) values (?, ?, ?, ?, ?, ?, ?)');
$stmt->bind_param('sssssss', $_POST['name'], $_POST['username'], $_POST['password'], $_POST['confirm'], $_POST['email'], $_POST['offer'], $_POST['music']);
$stmt->execute() or die ('Query error!');
$stmt->close();

?> 
You are right. I dont think I should need that in my query. I left it in there initially because that was the mysql output from phpmyadmin wen I was testing. I took it out now though.
lilpenguin
Forum Newbie
Posts: 18
Joined: Sun Sep 19, 2010 7:06 pm

Re: creating table from php

Post by lilpenguin »

Now that I removed md5 from the password fields temporarily (was receiving long alphanumeric error code....) I'm back to my regular syntax errors.

Code: Select all


<?

//variables are defined in previous code not copied in this snippet
mysql_select_db($dbname, $conn);

$sql="INSERT INTO users (

name ,
username ,
password ,
confirm ,
email ,
offer ,
music
)
VALUES 
(
'$_POST[name]',
'$_POST[username] ',
'$_POST[password] ',
'$_POST[confirm]  ',
'$_POST[email]  ',
'$_post[offer]  ',
'$_post[music] ',
)";

if (!mysql_query($sql,$conn))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

?>

New result is

Code: Select all

Error: 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 21

Thanks!
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

Re: creating table from php

Post by DigitalMind »

lilpenguin wrote:'$_post[offer] ',
'$_post[music] ',
use $_POST instead of $_post
internet-solution
Forum Contributor
Posts: 220
Joined: Thu May 27, 2010 6:27 am
Location: UK

Re: creating table from php

Post by internet-solution »

and remove coma after $_POST[music]
'$_post[music]',
)";
Post Reply