Database Query Through PHP...

[jabird]

I have this script:

<?php
include('../includes/db.php');
echo('<p>This page will attempt to create the MySQL Tables for you.<br />');
$userstable = 'CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username TEXT,
    password TEXT,
    registerdate DATE NOT NULL,
    admin TEXT,
    author TEXT
    )';
if (@mysql_query($usertable)) {
    echo 'User table successfully created!<br />';
} else {
    exit('Error creating the User table: ' .
        mysql_error() . '<br />Please use your database management application to add this to your database:<br />
        <textarea>CREATE TABLE users (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        username TEXT,
        password TEXT,
        registerdate DATE NOT NULL,
        admin TEXT,
        author TEXT
        )</textarea><br />');
}
$newstable = 'CREATE TABLE news (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    author TEXT,
    date DATE NOT NULL,
    newstext TEXT,
    category TEXT,
    )';
if (@mysql_query($newstable)) {
    echo 'News table successfully created!<br />';
} else {
    exit('Error creating the News table: ' .
        mysql_error() . '<br />Please use your database management application to add this to your database:<br />
        <textarea>CREATE TABLE news (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        author TEXT,
        date DATE NOT NULL,
        newstext TEXT,
        category TEXT</textarea><br />');
}
if (@mysql_query($usertable, $newstable)) {
    header("Location: install2.php");
} 
?>

As you can tell that is supposed to create 2 tables, users and news.

when I run it, I get:

This page will attempt to create the MySQL Tables for you.
Error creating the User table: Query was empty
Please use your database management application to add this to your database:

How can the query be empty? when it clearly has stuf in it...

Does anyone see what I did wrong?

Thanks,

[Imprint]

Try this:

<?php 
include('../includes/db.php'); 
echo('<p>This page will attempt to create the MySQL Tables for you.<br />'); 
$usertable = 'CREATE TABLE users ( 
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    username TEXT, 
    password TEXT, 
    registerdate DATE NOT NULL, 
    admin TEXT, 
    author TEXT 
    )'; 
if (@mysql_query($usertable)) { 
    echo 'User table successfully created!<br />'; 
} else { 
    exit('Error creating the User table: ' . 
        mysql_error() . '<br />Please use your database management application to add this to your database:<br /> 
        <textarea>CREATE TABLE users ( 
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        username TEXT, 
        password TEXT, 
        registerdate DATE NOT NULL, 
        admin TEXT, 
        author TEXT 
        )</textarea><br />'); 
} 
$newstable = 'CREATE TABLE news ( 
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    author TEXT, 
    date DATE NOT NULL, 
    newstext TEXT, 
    category TEXT, 
    )'; 
if (@mysql_query($newstable)) { 
    echo 'News table successfully created!<br />'; 
} else { 
    exit('Error creating the News table: ' . 
        mysql_error() . '<br />Please use your database management application to add this to your database:<br /> 
        <textarea>CREATE TABLE news ( 
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        author TEXT, 
        date DATE NOT NULL, 
        newstext TEXT, 
        category TEXT</textarea><br />'); 
} 
if (@mysql_query($usertable, $newstable)) { 
    header("Location: install2.php"); 
} 
?>

All I did was fix a typo in your script. You adressed the variable name in the query as $usertable, while you defined it as $userstable.

[jabird]

hehe, that worked, and I also had to fix an erro in the sql syntax for the newstable, now, towards the bottom, I have:

if (@mysql_query($usertable, $newstable)) {
header("Location: install2.php");

This isn't redirecting me to install2.php how would I make it check if both where successful, and if they were, redirect?

Thanks,

EDIT:

install2.php is:

<center>
<?php
include('../includes/db.php');
$checkmysql = mysql_query('SHOW TABLES;')
if (!@$checkmysql) {
    header("Location: install1.php");
} else {
    echo('<p>It looks like your tables where created successfully, so lets create the admin account, so you can login:</p>');
}
?>
<form method='post' action='install3.php'>
A <font color='#FF0000'>*</font> indicates a required field.<br />
Username: <input name='username' type='text' value='' /><font color='#FF0000'>*</font><br />
Password: <input name='password' type='password' value='' /><font color='#FF0000'>*</font><br />
<input type='submit' value='Submit' />
</form>
</center>

I get:
Parse error: parse error, unexpected T_IF in /usr/local/apache/htdocs/jabirdcms/install/install2.php on line 5

if (!@$checkmysql) {
is line 5...

I was using that to see if install1.php was run correctly, and if it wasn't, fail, and return to install1.php...

[Imprint]

Not being the biggest expert at PHP/MySQL, I gave it a shot anyways. I faced the same problem a couple of days ago and solved it by changing around the order of the if-else structure in the script.

I did the same with your script. I added tabs so you could see what has changed and what it is supposed to do. Let's hope it works at once:

<?php
include('../includes/db.php');

echo('<p>This page will attempt to create the MySQL Tables for you.<br />');
$usertable = 'CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username TEXT,
    password TEXT,
    registerdate DATE NOT NULL,
    admin TEXT,
    author TEXT
    )';
if (@mysql_query($usertable)) {
    echo 'User table successfully created!<br />';

    $newstable = 'CREATE TABLE news (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    author TEXT,
    date DATE NOT NULL,
    newstext TEXT,
    category TEXT,
    )';

    if (@mysql_query($newstable)) {
    echo 'News table successfully created!<br />';
    header("Location: install2.php");
    }
    
    else {
    exit('Error creating the News table: ' .
        mysql_error() . '<br />Please use your database management application to add this to your database:<br />
        <textarea>CREATE TABLE news (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        author TEXT,
        date DATE NOT NULL,
        newstext TEXT,
        category TEXT</textarea><br />');
    }
}

else {
    exit('Error creating the User table: ' .
        mysql_error() . '<br />Please use your database management application to add this to your database:<br />
        <textarea>CREATE TABLE users (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        username TEXT,
        password TEXT,
        registerdate DATE NOT NULL,
        admin TEXT,
        author TEXT
        )</textarea><br />');
}
?>

[jabird]

Ok, on install1.php, I get:

This page will attempt to create the MySQL Tables for you.
User table successfully created!
News table successfully created!

Warning: Cannot modify header information - headers already sent by (output started at /usr/local/apache/htdocs/jabirdcms/install/install1.php:4) in /usr/local/apache/htdocs/jabirdcms/install/install1.php on line 26

and on install2.php I get:

Warning: Cannot modify header information - headers already sent by (output started at /usr/local/apache/htdocs/jabirdcms/install/install2.php:2) in /usr/local/apache/htdocs/jabirdcms/install/install2.php on line 6
A * indicates a required field.
Username: *
Password: *

[Imprint]

Hmm... I remember reading somewhere that header information needs to be sent at the top of the page. This is well out of my league, I never used headers, but I will see what I can find.

- Edit -

I just found something which will probably work. You should add this just beneath the PHP opening tags.

ob_start();

As far as I could gather it triggers the output buffering setting (which is the thing preventing you from sending headers). By doing this, it should be switched to 'off' which should allow you to send the header, after having sent out the content with your echo statements.

[jabird]

Well, that fixed that problem...

now, in install3.php I have:

<?php
ob_start();
include('../includes/db.php');
$username = ($_POST['username']);
$password = (md5($_POST['password']));

$sql = "INSERT INTO author SET
    username='$username',
    password='$password',
    admin='1'
    registerdate='curdate()'"
if ($sql) {
    header("Location: install4.php");
} else {
    exit('<p>We where unable to create your admin account.</p>');
?>

the error I get is:

Parse error: parse error, unexpected T_IF in /usr/local/apache/htdocs/jabirdcms/install/install3.php on line 12

[Imprint]

The problem is you forgot to close the $sql variable. Add a ; at the end of the declaration of that variable and it should work.

[jabird]

Heh, now I have:

Parse error: parse error, unexpected $ in /usr/local/apache/htdocs/jabirdcms/install/install3.php on line 16

The entire code is:

<?php
ob_start();
include('../includes/db.php');
$username = ($_POST['username']);
$password = (md5($_POST['password']));

$sql = "INSERT INTO author SET
    username='$username',
    password='$password',
    admin='1'
    registerdate='curdate()'";
if ($sql) {
    header("Location: install4.php");
} else {
    exit('<p>We where unable to create your admin account.</p>');
?>

See any problems there?

Line 16 is:

?>

[frezno]

$sql = "INSERT INTO author SET
    username='$username',
    password='$password',
    admin='1'
    registerdate='curdate()'"; 

missing comma after admin='1'

[jabird]

Still getting an error

Thanks for pointing that out though

[frezno]

Still getting an error

sure, you have to close the curly brace at the end as well:

if ($sql) {
    header("Location: install4.php");
} else {
    exit('<p>We where unable to create your admin account.</p>');
} // <-- That's missing 

[jabird]

Great! Thanks alot, it works now

~jabird

[jabird]

Eh, spoke to soon, everything appears to be working, but it doesn't add a user to the MySQL database when I hit submit...

edit... it'd probly help if I made an sql query to use that variable huh?

=\

edit2: well, now it gives me an unable to create your admin account error... so thats progress...

[frezno]

hmpf
you're starting with ob_start(), which means that you are buffering the content: ob_start()
Now you have to flush it somewhen as well, since it remains buffered otherwise: ob_end_flush()

And make sure you're passing value(s) for your variable(s).

[jabird]

Alright... heres my signup form:

<?php
echo('<center><p>Now lets create the admin account, so you can login:</p>');
?>
<form method='post' action='install3.php'>
A <font color='#FF0000'>*</font> indicates a required field.<br />
Username: <input name='username' type='text' value='' /><font color='#FF0000'>*</font><br />
Password: <input name='password' type='password' value='' /><font color='#FF0000'>*</font><br />
<input type='submit' value='Submit' />
</form>
</center>

and here's install3.php:

<?php
ob_start();
include('../includes/db.php');
$username = $_POST['username'];
$password = md5($_POST['password']);

$sql = "INSERT INTO author SET
    username='$username',
    password='$password',
    admin='1',
    registerdate='curdate()'";
if (@mysql_query($sql)) {
    header("Location: install4.php");
} else {
    exit('<p>We where unable to create your admin account.</p>');
}
ob_end_flush()
?>

I'm not so great at forms, incase you can't tell

[frezno]

BTW, what are you doing with your sql? NOTHING
so add a

$result = mysql_query($sql);
if ($result) // instead of if ($sql)
{
   // ...
} 

[jabird]

K... added that...

so now I have:

<?php
ob_start();
include('../includes/db.php');
$username = $_POST['username'];
$password = md5($_POST['password']);

$sql = "INSERT INTO author SET
    username='$username',
    password='$password',
    admin='1',
    registerdate='curdate()'";
$result = mysql_query($sql);
if ($result) {
    header("Location: install4.php");
} else {
    exit('<p>We were unable to create your admin account.</p>');
}
ob_end_flush();
?>

as install3.php... it still gives unable to create admin account.

so first, am I even getting the data from the form correctly? or is the another way to do it?

[frezno]

ok, what do you get passed as username and password?

if (@mysql_query($sql)) {} 

why are you disabling the display of error(s)?

[jabird]

Don't understand what you mean by the first question,

and I disabled the non error display already:

<?php
ob_start();
include('../includes/db.php');
$username = $_POST['username'];
$password = md5($_POST['password']);

$sql = "INSERT INTO author SET
    username='$username',
    password='$password',
    admin='1',
    registerdate='curdate()'";
$result = mysql_query($sql);
if ($result) {
    header("Location: install4.php");
} else {
    exit('<p>We were unable to create your admin account.</p>');
}
ob_end_flush();
?>

[frezno]

ok, let's simplify all this.

Let's take just this chunk of code and try whther it works or not.
Pretty q'n'd but it's working!
You have to change curdate() to NOW(), btw.

<form method='post' action='<?php echo $_SERVER['PHP_SELF']; ?>'>
A <font color='#FF0000'>*</font> indicates a required field.<br />
Username: <input name='username' type='text' value='' /><font color='#FF0000'>*</font><br />
Password: <input name='password' type='password' value='' /><font color='#FF0000'>*</font><br />
<input type='submit' value='Submit' />
</form>

<?php 
// database access data, enter yours !
$dbhost = "localhost";
$dbname = "root";
$dbpass = "";
$database = "";
 
$link = @mysql_connect($dbhost, $dbname, $dbpass);
if (! $link) {
    die("error connecting to Server.");
}
 
if (! mysql_select_db($database, $link)) {
    die("error connecting to database: $database");
}

ob_start(); 
$username = $_POST['username'];
$password = md5($_POST['password']);
 
echo $username ."<br>";
echo $password ."<br>";
 
$sql = "INSERT INTO author SET 
    username='$username', 
    password='$password', 
    admin='1', 
    registerdate=NOW()";
 
    $result = mysql_query($sql);
 
if ($result)
{
    echo "Sucessfully inserted in database"; 
} else { 
    exit('<p>We where unable to create your admin account.</p>'); 
} 
 
ob_end_flush() 
?>

[jabird]

I'm going to edit all the db connect stuff out, because I already have db.php and conf.php that does all the mysql connect and selecting...

db.php:

<?php
include('conf.php');
// Connect to the MySQL Server
$dbcnx = @mysql_connect($ip, $mysql_user, $mysql_pass);
if (!$dbcnx) {
    echo '<p>Unable to connect to the ' .
        'database server at this time.</p>';
    exit();
}
// Select the MySQL Database
mysql_select_db($db, $dbcnx);
if (!@mysql_select_db($db)) {
    exit('<p>Unable to locate the ' .
        'database at this time.</p>');
}
?>

conf.php

<?php
// docs/configuration.html
// Explains everything in this script.
$title = 'JabirdCMS';
$path = 'jabirdcms';
$domain = 'localhost';
// MySQL Stuff:
$ip = 'localhost'; // This will usually work...
$mysql_user = 'root'; // The username used to connect to your MySQL Database
$mysql_pass = ''; // The password used to connect to your MySQL Database
$db = '' // Your database name.
?>

[jabird]

Alright, that worked, and I checked the database, and it had the username and md5hashed password, and the register date!

Is there any real purpose for:

echo $username ."<br>";
echo $password ."<br>";
? I just see it showing the password as a security threat, it may not be wise to show off the md5hash right on the website... so if your just showing that to make sure the md5 and all works, I'm just gonna take it out.. is that ok?

Edit: I also want sometihng like:

if (mysql_query($usertable, $newstable)) {
echo("<a href='install2.php'>Next</a>");
}

but thats not working... is there a way to check if both query's worked, and if they did, show a next link?

[frezno]

Is there any real purpose for:
echo $username ."<br>";
echo $password ."<br>";

no, it's just for checking whether a value is passed and - of course - must not be included in your official page.

Edit: I also want sometihng like:

no
but not sure what you'r etrying to achieve

[jabird]

In install1.php, I create some database tables, I want to check if the 2 variables I made BOTH worked, and if they did, show a next link, and if 1 of the database query's failed, don't show then next link, and if both failed, don't show the next link...

So I need an if statement that checks for failure, if there was none, it shows a link to the next isntall page