Page 1 of 1

creating table from php

Posted: Sun Sep 19, 2010 7:18 pm
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!
?>

Re: creating table from php

Posted: Sun Sep 19, 2010 7:20 pm
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.

Re: creating table from php

Posted: Sun Sep 19, 2010 11:50 pm
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);
?>

Re: creating table from php

Posted: Tue Sep 21, 2010 8:28 am
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!

Re: creating table from php

Posted: Tue Sep 21, 2010 2:08 pm
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().

Re: creating table from php

Posted: Mon Sep 27, 2010 12:02 am
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!!

Re: creating table from php

Posted: Mon Sep 27, 2010 8:48 am
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?

Re: creating table from php

Posted: Mon Sep 27, 2010 10:27 am
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).

Re: creating table from php

Posted: Mon Sep 27, 2010 10:59 am
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();

?> 

Re: creating table from php

Posted: Tue Sep 28, 2010 8:19 am
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.

Re: creating table from php

Posted: Tue Sep 28, 2010 8:34 am
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!

Re: creating table from php

Posted: Tue Sep 28, 2010 10:10 am
by DigitalMind
lilpenguin wrote:'$_post[offer] ',
'$_post[music] ',
use $_POST instead of $_post

Re: creating table from php

Posted: Tue Sep 28, 2010 10:21 am
by internet-solution
and remove coma after $_POST[music]
'$_post[music]',
)";