PHP/MySQL code not working!

[ttttt]

I'm trying to create the tables I need in my MySQL database with this code. For some reason, it won't work (PHP error something to do with T_ECHO?). Here's the code:

<?php
$conn = mysql_connect($_POST['db_host'], $_POST['db_username'], $_POST['db_password']) or die
('Error connecting to mysql');
mysql_create_db('mydb') or die
('Error creating the database');
mysql_select_db('mydb') or die('Cannot select database');

$query = 'CREATE TABLE users( '.
'uid INT NOT NULL AUTO_INCREMENT, '.
'uname VARCHAR(20) NOT NULL, '.
'upass VARCHAR(20) NOT NULL, '.
'uemail VARCHAR(50) NOT NULL, '.
'urank INT NOT NULL, '.
'PRIMARY KEY(uid))';

$result = mysql_query($query);

$username = $_POST['username'];
$password = $_POST['password'];

$queryaddadmin = "INSERT INTO user (uname, upass, uemail, urank)
VALUES ('$username', PASSWORD('$password'), '$user_email', '1')";

mysql_query($queryaddadmin) or die('Error, insert query failed');

$queryreload = "FLUSH PRIVILEGES";
mysql_query($queryreload) or die('Error, insert query failed');

mysql_select_db('mydb') or die('Cannot select database');

$query = 'CREATE TABLE meta( '.
'mid INT NOT NULL AUTO_INCREMENT, '.
'mname VARCHAR(20) NOT NULL, '.
'mcontent TEXT NOT NULL, '.
'PRIMARY KEY(mid))';

mysql_select_db('mydb') or die('Cannot select database');

$query = 'CREATE TABLE content( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'ccat VARCHAR(20) NOT NULL, '.
'ctitle VARCHAR(150) NOT NULL, '.
'cauthor VARCHAR(20) NOT NULL, '.
'cemail VARCHAR(50) NOT NULL, '.
'ccontent TEXT NOT NULL, '.
'PRIMARY KEY(cid))';

mysql_select_db('mydb') or die('Cannot select database');

$query = 'CREATE TABLE comments( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'carticle VARCHAR(20) NOT NULL, '.
'ctitle VARCHAR(150) NOT NULL, '.
'cauthor VARCHAR(20) NOT NULL, '.
'cemail VARCHAR(50) NOT NULL, '.
'ccontent TEXT NOT NULL, '.
'PRIMARY KEY(cid))';

mysql_select_db('mydb') or die('Cannot select database');

$query = 'CREATE TABLE menu( '.
'mid INT NOT NULL AUTO_INCREMENT, '.
'mname VARCHAR(20) NOT NULL, '.
'murl VARCHAR(150) NOT NULL, '.
'PRIMARY KEY(mid))';

mysql_select_db('mydb') or die('Cannot select database');

$query = 'CREATE TABLE title( '.
'tid INT NOT NULL'.
'tname VARCHAR(20) NOT NULL, '.
'PRIMARY KEY(tid))';

$query = "INSERT INTO title (tname)
VALUES ('$tname')";

mysql_query($query) or die('Error, insert query failed');

$queryreload = "FLUSH PRIVILEGES";

mysql_select_db('mydb') or die('Cannot select database');

$query = 'CREATE TABLE paths( '.
'pid INT NOT NULL'.
'pname VARCHAR(20) NOT NULL'.
'purl VARCHAR(150) NOT NULL, '.
'PRIMARY KEY(hurl))';

mysql_close($conn);
?>

Thanks for any help,

[vinyl-junkie]

It would help if you'd post the exact error message you're getting. Thanks.

[ttttt]

well,
I have since changed the code to this:
mysql_connect ("localhost", "db_name", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db");

$query = 'CREATE TABLE users( '.
'uid INT NOT NULL AUTO_INCREMENT, '.
'uname VARCHAR(20) NOT NULL, '.
'upass VARCHAR(20) NOT NULL, '.
'uemail VARCHAR(50) NOT NULL, '.
'urank INT NOT NULL, '.
'PRIMARY KEY(uid))'.
"INSERT INTO users
(uname, upass, urank)
VALUES('admin', 'password', '1')"
or die(mysql_error());

$result = mysql_query($query);
It did work, but now no errors are produced but the table isn't creates
Thanks,

[vinyl-junkie]

For one thing, you have two queries here. If you're going to execute them both at once, at least separate them with a semicolon.

$query = 'CREATE TABLE users( '.
'uid INT NOT NULL AUTO_INCREMENT, '.
'uname VARCHAR(20) NOT NULL, '.
'upass VARCHAR(20) NOT NULL, '.
'uemail VARCHAR(50) NOT NULL, '.
'urank INT NOT NULL, '.
'PRIMARY KEY(uid));'.
"INSERT INTO users
(uname, upass, urank)
VALUES('admin', 'password', '1')"
or die(mysql_error());

Also, check the error log on your server. That may contain an error message related to your script. If it doesn't, add this piece of code to the top of your script and run it again:

ini_set("error_reporting","E_ALL");

[GJay]

am I wrong in thinking mysql will only accept one query through php?

[ttttt]

Well, I've tried adding in the extra line of code and also I have slip the two queries up into separate variables and even only using the file for one of the queries. The code at the moment is:
$query1 = 'CREATE TABLE users( '.
'uid INT NOT NULL AUTO_INCREMENT, '.
'uname VARCHAR(20) NOT NULL, '.
'upass VARCHAR(20) NOT NULL, '.
'uemail VARCHAR(50) NOT NULL, '.
'urank INT NOT NULL, '.
'PRIMARY KEY(uid));'.
$query2 = "INSERT INTO users
(uname, upass, urank)
VALUES('admin', 'password', '1')"
or die(mysql_error());

$result1 = mysql_query($query1);
$result2 = mysql_query($query2);
I am only using php4.
Thanks for all of the help,

[vinyl-junkie]

Are you getting any error message? If not, did you check the error messages on your server log?

[ttttt]

There are no errors and the error log shows nothing about this.

[vinyl-junkie]

Post the whole script as it currently exists, so we're sure of what we're trying to help you debug.

[ttttt]

Thanks,
Here it is:

<?php
mysql_connect ("localhost", "user", "pass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db");

$query1 = 'CREATE TABLE users( '.
'uid INT NOT NULL AUTO_INCREMENT, '.
'uname VARCHAR(20) NOT NULL, '.
'upass VARCHAR(20) NOT NULL, '.
'uemail VARCHAR(50) NOT NULL, '.
'urank INT NOT NULL, '.
'PRIMARY KEY(uid));'.
$query2 = "INSERT INTO users
(uname, upass, urank)
VALUES('admin', 'password', '1')"
or die(mysql_error());

$result1 = mysql_query($query1);
$result2 = mysql_query($query2);
mysql_close;
?>

[GJay]

'PRIMARY KEY(uid));'.
$query2 = "INSERT INTO users

is nonsense.
-queries sent from php shouldn't have a ;
-the first variable definition should end with a ;, not a .

[ttttt]

So my code should be:

$query1 = 'CREATE TABLE users( '.
'uid INT NOT NULL AUTO_INCREMENT, '.
'uname VARCHAR(20) NOT NULL, '.
'upass VARCHAR(20) NOT NULL, '.
'uemail VARCHAR(50) NOT NULL, '.
'urank INT NOT NULL, '.
'PRIMARY KEY(uid))';
$query2 = "INSERT INTO users
(uname, upass, urank)
VALUES('admin', 'password', '1')"
or die(mysql_error());

$result1 = mysql_query($query1);
$result2 = mysql_query($query2);

[vinyl-junkie]

Good catch, GJay!

Yes, the way you've changed your code should be correct. You might want to also change your two queries, like so:

$result1 = mysql_query($query1) or die("query1: " & mysql_error());
$result2 = mysql_query($query2) or die("query2: " & mysql_error());

[ttttt]

Well, that worked, however, I added in the rest of the code I need, and I'm getting more errors. I have checked for the same problems as before, but I can't find anything wrong with the code. I have probably done something stupid. Could you please help?
//Connect to the database
mysql_connect ("localhost", "user", "pass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db");

//Create the queries
$query1 = 'CREATE TABLE users( '.
'uid INT NOT NULL AUTO_INCREMENT, '.
'uname VARCHAR(20) NOT NULL, '.
'upass VARCHAR(20) NOT NULL, '.
'uemail VARCHAR(50) NOT NULL, '.
'urank INT NOT NULL, '.
'PRIMARY KEY(uname))';
$query2 = 'CREATE TABLE admin( '.
'aid INT NOT NULL AUTO_INCREMENT, '.
'aname VARCHAR(20) NOT NULL, '.
'apass VARCHAR(20) NOT NULL, '.
'aemail VARCHAR(50) NOT NULL, '.
'PRIMARY KEY(aname))';
$query3 = "INSERT INTO admin
(uname, upass, urank)
VALUES('admin', 'password', '1')"
$query4 = 'CREATE TABLE meta( '.
'aid INT NOT NULL AUTO_INCREMENT, '.
'aname VARCHAR(20) NOT NULL, '.
'apass VARCHAR(20) NOT NULL, '.
'aemail VARCHAR(50) NOT NULL, '.
'PRIMARY KEY(aname))';
$query5 = 'CREATE TABLE content( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'ccat VARCHAR(20) NOT NULL, '.
'ctitle VARCHAR(150) NOT NULL, '.
'cauthor VARCHAR(20) NOT NULL, '.
'cemail VARCHAR(50) NOT NULL, '.
'ccontent TEXT NOT NULL, '.
'PRIMARY KEY(cid))';
$query6 = 'CREATE TABLE comments( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'carticle VARCHAR(20) NOT NULL, '.
'ctitle VARCHAR(150) NOT NULL, '.
'cauthor VARCHAR(20) NOT NULL, '.
'cemail VARCHAR(50) NOT NULL, '.
'ccontent TEXT NOT NULL, '.
'PRIMARY KEY(cid))';
$query7 = 'CREATE TABLE menu( '.
'mid INT NOT NULL AUTO_INCREMENT, '.
'mname VARCHAR(20) NOT NULL, '.
'murl VARCHAR(150) NOT NULL, '.
'PRIMARY KEY(mid))';
$query8 = 'CREATE TABLE title( '.
'tid INT NOT NULL'.
'tname VARCHAR(20) NOT NULL, '.
'PRIMARY KEY(tid))';
$query9 = "INSERT INTO title (tname)
VALUES ('tname')";
$query10 = 'CREATE TABLE paths( '.
'pid INT NOT NULL'.
'pname VARCHAR(20) NOT NULL'.
'purl VARCHAR(150) NOT NULL, '.
'PRIMARY KEY(hurl))';

//Run the queries
$result1 = mysql_query($query1) or die("query1: " & mysql_error());
$result2 = mysql_query($query2) or die("query2: " & mysql_error());
$result3 = mysql_query($query3) or die("query3: " & mysql_error());
$result4 = mysql_query($query4) or die("query4: " & mysql_error());
$result5 = mysql_query($query5) or die("query5: " & mysql_error());
$result6 = mysql_query($query6) or die("query6: " & mysql_error());
$result7 = mysql_query($query7) or die("query7: " & mysql_error());
$result8 = mysql_query($query8) or die("query8: " & mysql_error());
$result9 = mysql_query($query9) or die("query9: " & mysql_error());
$result10 = mysql_query($query10) or die("query10: " & mysql_error());

//Close the connection
mysql_close;

[Mwnciau]

There's no ; at the end of the $query3 declaration.

[ttttt]

I added that in, but on the page I get this: Adabp0
None of the tables are created either.
Thanks,

[vinyl-junkie]

I echo'd out your queries, then copy/pasted them into phpMyAdmin and ran them from there. The first error that I got was this:

SQL query:

CREATE TABLE admin(

aid INT NOT NULL AUTO_INCREMENT ,
aname VARCHAR( 20 ) NOT NULL ,
apass VARCHAR( 20 ) NOT NULL ,
aemail VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( aname )
);



MySQL said:

#1075 - Incorrect table definition; there can be only one auto column and it
must be defined as a key


Do the same with the rest of your queries, and I'm sure you'll find all the errors.

[Fumigator]

Do the same with the rest of your queries, and I'm sure you'll find all the errors.

That is a rather optimistic view don't ya think? :D

[vinyl-junkie]

That is a rather optimistic view don't ya think? :D
I'm an optimistic kind of person. ;)

[ttttt]

Well, I did that, and the first two queries work, but the third query (the INSERT query) still doesn't. I have absolutely no idea why. Can you help?
Thanks,

[GJay]

your admin columns begin 'a', your query is inserting to 'u'
(re-reading the query, i think it's the table name that's wrong, rather than the columns...)
Try taking a bit more time with things, lots of sloppy mistakes are creeping in, that a bit of care and a logical debugging process would help solve- running queries outside of PHP is a really good thing to get into the abit of doing, the commandline and phpmyadmin both provide much better error messages than you get from mysql_error();

[vinyl-junkie]

When you created your admin table, you created it with variables names of aname and apass, but you're trying to insert values for uname and upass. There is no variable named urank.

Edit: Sorry, I didn't see GJay's reply.