Database Problems

staticfire · #1 (**permalink**) 05-15-07, 04:35 PM

Well it seems that my databases are slightly buggy. I've tried everything to resolve the problems but now i do what seems best in these situation, come here for help

The login system for this is actually fully working so i'm hoping to sort the database out now.

So let me explain. I have this script which is the registration form for my site:

register.php

PHP Code:


			
<?php

session_start();



$_SESSION['loggedin']=false;

?>





<!--background and link settings-->



<body link="#B8B8B8" vlink="#B8B8B8" alink="#B8B8B8">



<?php

require 'link_style.css';

?>



<!--open scrollbar-->



<?php

require 'scrollbar.css';

?>





<!--open page header-->



<?php

require 'banner.php';

?>





<table width="817" align="center">



<tr>



<td width="215" background="http://staticfire.co.uk/images/sidebar_back.png" 

align="center" valign="top">



<br>



<?php

require 'login.php';

?>



<br>



<?php

require 'links.php';

?>



<br>



<?php

require 'active_list.php';

?>



</td>





<td width="602" valign="top">



<table width="100%" align="center" border="1" bordercolor="#CD0000" cellpadding="10">

<tr>

<td>



<center>



<html>



<form action="insert.php" method="post">



<table>



<tr> <td> <font color="#707070"> loginname: </font> </td> <td><input type="text" name="loginname" maxlength="20"></td> </tr>

<tr> <td> <font color="#707070"> password: </font> </td> <td><input type="text" name="password" maxlength="20"> </td> </tr>

<tr> <td> <font color="#707070"> name: </font> </td> <td><input type="text" name="name" maxlength="30"> </td> </tr>

<tr> <td> <font color="#707070"> Age: </font> </td> <td><input type="text" name="age" maxlength="2"> </td> </tr>

<tr> <td> <font color="#707070"> Location: </font> </td> <td><input type="text" name="location" maxlength="30"> </td> </tr>

<tr> <td> <font color="#707070"> Description: </font> </td> <td><textarea cols="40" rows="5" name="description">

Text here

</textarea>

<tr> <td> <font color="#707070"> email: </font> </td> <td><input type="text" name="email"> </td> </tr>

 

<tr> <td></td>



<td> <font color="#707070"><input type="checkbox" name="option1" value="age_check"> 

I am 13+ or have a parents permission. </font></td></tr><br>



<tr> <td></td>



<td> <font color="#707070"><input type="checkbox" name="option1" value="age_check"> 

I agree to the Terms of Service </font></td></tr><br><br>



</td></tr>

</table>



<input type="Submit" value="Register">



</form>



</html>



</center>



</td>

</tr>

</table>







</td>

</tr>



</table>



<?php

require 'footer.php';

?>

...and as you can probably see from the form action it is processed by insert.php which will insert the fields into the user database:

insert.php

PHP Code:


			
<?php

$username="staticfire";

$password="xxxxxxx";

$database="staticfire";



mysql_connect('mysql10.streamline.net',$username,$password);

@mysql_select_db($database) or die("Unable to select database");



$loginname=$_POST['loginname'];

$createdate=date("m/d/y"); 

$password=$_POST['password'];

$name=$_POST['name'];

$age=$_POST['age'];

$location=$_POST['location'];

$description=$_POST['description'];

$email=$_POST['email'];

$warn=0;



$query="INSERT INTO members VALUES(



'id',

'$loginname',

'$createdate',

'$password',

'$name',

'$age',

'$location',

'$description',

'$email',

'$warn'



)";



$result=mysql_query($query);

?>





</td>

</tr>



</table>





<!--background and link settings-->



<body bgcolor="#000000" link="#707070" vlink="#707070" alink="#707070">





<!--open scrollbar-->



<?php

require 'scrollbar.css';

?>





<!--open page header-->



<?php

require 'banner.php';

?>





<table width="817" align="center">



<tr>



<td width="215" background="http://staticfire.co.uk/images/sidebar_back.png" 

align="center" valign="top">



<br>



<?php

require 'login.php';

?>



<br>



<?php

require 'links.php';

?>



<br>



<?php

require 'top5.php';

?>





<br>



<?php

require 'active_list.php';

?>



</td>                





<!--Start main section-->





<td width="602" valign="top">



<table width="100%" align="center" cellpadding="10">

<tr>

<td>



<center><font color="#707070">

<?php

echo"Thank you $loginname for registering on StaticFire. 

An email has been sent too $email which contains your account information. 

You may now use the login to the left.";

?>

</font></center>



</td>

</tr>

</table>



</td>

</tr>



</table>



<?php

require 'footer.php';

?>

...ok so the problems with that are as follows:

-The id doesn't insert
-The create date doesn't either
-Neither does the warn

Oh and everyones warn level is 0 (untill i warn them

)

...finally we have the actual database which shows all the members:

db_members.php

PHP Code:


			
<?php session_start(); ?>



<!--begin the database page-->



<!--create favicon-->



<LINK REL="SHORTCUT ICON" HREF="http://staticfire.co.uk/favicon.ico"> <br><br>





<!--background and link settings-->



<body bgcolor="#000000" link="#707070" vlink="#707070" alink="#707070">





<!--open scrollbar-->



<?php

require 'scrollbar.css';

?>



<?php



$username="staticfire";

$password="xxxxxxx";

$database="staticfire";



mysql_connect('mysql10.streamline.net',$username,$password);

@mysql_select_db($database) or die("Unable to select database");



$query="CREATE TABLE members(



id          INT           NOT NULL   AUTO_INCREMENT,

loginname   VARCHAR(20)   NOT NULL,

createdate  DATE          NOT NULL,

password    CHAR(255)     NOT NULL,

name        VARCHAR(40),

age         INT(2),

location    VARCHAR(40),

description CHAR(255),

email       VARCHAR(40)   NOT NULL,

warn        INT



PRIMARY KEY(id))";



$result=mysql_query($query);



?>





<!--open page header-->



<?php

require 'banner.php';

?>





<table width="817" align="center">



<tr>



<td width="215" background="http://staticfire.co.uk/images/sidebar_back.png" 

align="center" valign="top">



<br>



<?php

require 'login.php';

?>



<br>



<?php

require 'links.php';

?>



<br>



<?php

require 'top5.php';

?>





<br>



<?php

require 'active_list.php';

?>



</td>                





<!--Start main section-->





<td width="602" valign="top">



<table width="100%" align="center" border="1" bordercolor="#CD0000" cellpadding="10">

<tr>

<td>



<center><font color="#707070">



<?php



$username="staticfire";

$password="xxxxxxx";

$database="staticfire";



mysql_connect('mysql10.streamline.net',$username,$password);

@mysql_select_db($database) or die("Unable to select database");



$query = "SELECT * FROM members"; 

     

$result = mysql_query($query) or die(mysql_error());





while($row = mysql_fetch_array($result)){



echo 

$row['id']. " - ". 

$row['loginname']. " - ".

$row['createdate']. " - ".

$row['password']. " - ". 

$row['name']. " - ".

$row['age']. " - ".

$row['location']. " - ". 

$row['description']. " - ".

$row['email']. " - ".

$row['warn']. " - ";



echo "<br />";

}

?>







</font></center>



</td>

</tr>

</table>







</td>

</tr>



</table>



<?php

require 'footer.php';

?>

..I'm not actually going to show peoples passwords, it just shows everything at the moment for testing purposes.

Well that's just about it. I thank anyone in advance who takes the time to help with that. I know there's quite a bit there but i think it's just minor errors.

phpdoctor · #2 (**permalink**) 05-15-07, 05:50 PM

For inserting use something like this: (UNTESTED)

PHP Code:


			
// Get posted data and turn them into variables

// Also dont replace existing variables (for extra security)

// $<field name> = <field value> ;

extract($_POST, EXTR_SKIP) ;



// Insert member

$sql = "INSERT members SET loginname='".mysql_real_escape_string($loginname)."', createdate='".mysql_real_escape_string($createdate)."'" ;

/* PUT IN THE REST OF THE FIELDS INTO THIS QUERY */



    if (!mysql_query ($sql))

    {

    echo mysql_error() ;

    exit ;

    }

Hope that helps

Tell us if theres any errors.
Also the id field is a auto increment so you dont need to enter anything in it.

staticfire · #3 (**permalink**) 05-16-07, 09:37 AM

oops i dropped the table to get rid of the entries that were already there and now it says it doesn't exist

job0107 · #4 (**permalink**) 05-16-07, 10:23 AM

In insert.php you might try this:

PHP Code:


			
$query="INSERT INTO members (loginname, createdate, password, name, age, location, description, email, warn) VALUES('$loginname', '$createdate', '$password', '$name', '$age', '$location', '$description', '$email', '$warn')";

There is no need to try to insert the ID because the ID field is an auto-increment field.

staticfire · #5 (**permalink**) 05-16-07, 10:29 AM

it still says the table doesn't exist

Nico · #6 (**permalink**) 05-16-07, 10:34 AM

Then re-create it. I guess this is the code you used to create it?

PHP Code:


			
<?php

$username="staticfire";
$password="xxxxxxx";
$database="staticfire";

mysql_connect('mysql10.streamline.net',$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query="CREATE TABLE members(

id          INT           NOT NULL   AUTO_INCREMENT,
loginname   VARCHAR(20)   NOT NULL,
createdate  DATE          NOT NULL,
password    CHAR(255)     NOT NULL,
name        VARCHAR(40),
age         INT(2),
location    VARCHAR(40),
description CHAR(255),
email       VARCHAR(40)   NOT NULL,
warn        INT

PRIMARY KEY(id))";

$result=mysql_query($query);

?>