importing my database

[dazz_club]

Hi there,

 

I have been trying to import my database from my local computer to my hosts server using phpmyadmin.

When i paste in the database file and then run query I get this error.

 

#1064 - 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 'collate utf8_bin default NULL,

  `username` varchar(50) charac

 

I then tried to run each table query seperately but still got more or less the same response, only thing to change was the username.

 

On my local computer i have:

php:5.2.5 

mysql:5.0.45

 

My host:

php:4.4.3

mysql4.0.27

 

If anyone can shed any light on this i would be very gratefull.

 

kind regards

Dazzclub

[rhodesa]

You will have that problem going from MySQL 5 -> MySQL 4

 

In phpMyAdmin, when exporting, there should be an option to export as MYSQL4. If not, try removing the "collate utf8_bin"s from your SQL

[dazz_club]

Hi rhodesa,

 

Thanks for that, I simply experted compatible with MySQL4 and it works fine thank you.

 

May I ask you one more question please?

 

My database contains over 3,000 members, and up to a certain point say 2712, members beyond that cant login??

 

What could cause this besides the possibilities that a wrong username/password is to blame??

 

could it be the size of my database that my server provides, or i have set up the table that contains these members right??

 

kind regards

Dazzclub

[gluck]

If you have space issues then this should show on the error logs of the server. Have you checked the logs?

When you say they can't login - does the server display an error? What message do they get?

 

[dazz_club]

I have checked my error logs and shows no report of disc quota of space reached for my database, just some pages and images that aren't present.

 

The login is created by me, there is no error that is displayed, say if i had some broken php/sql script that stopped the form/login from working it just displays an error, informing the user they have incorrectly completed the login.

 

if(isset($errors) && is_array($errors))

    {

        echo 'Errors: <ul><li>' . implode('</li><li>', $errors) . '</li></ul>';

    }

 

My database engine is MYISAM, and my tables are InnoDB, could that be a problem??Also I know auto_crement shouldnt have a problem with storing members, are large amount of members.

 

Im stumped to what the problem is.

 

The users are given all the same password and the only thing different is their username, which is their email.

 

hmm, i am gonna have a coffe and look at it with fresh eyes.

 

if this helps, here is the script for the login;

<?php
session_start();
require_once("includes/connection.php"); 

// check that the form is submitted
if(isset($_POST['submit']))
{
    // validate username
    if(isset($_POST['username']) && !empty($_POST['username']))
    {
        // use the built in mysql real escape string function to protect agains SQL Injection
        $username = mysql_real_escape_string($_POST['username']);
    }
    else
    {
        // username does not validate, define an error
	$no_username = '<div class=\"login\" >incorrrect username</div>';
        
    }

    // we apply the same for the password field.
    if(isset($_POST['password']) && !empty($_POST['password']))
    {
        $password = md5($_POST['password']);
    }
    else
    {
        $no_password = 'Password not provided';
    }

    // chekc that no errors have been set, if so display them
    if(isset($errors) && is_array($errors))
    {
        echo 'Errors: <ul><li>' . implode('</li><li>', $errors) . '</li></ul>';
    }
    // no errors are set so we'll continue
    else
    {
        $sql= " SELECT * FROM members WHERE username = '$username' AND password = '$password' ";
        $result = mysql_query($sql) or die('Query Error:<br />Query: <tt>'.$sql.'</tt><br />Error: ' . mysql_error($db));

        // check that the query return only ONE result
        if(mysql_num_rows($result) == 1)
        {
            $_SESSION['is_logged_in'] = true;

            // get result set from the query and assign it to the 'user' session.
            $row = mysql_fetch_assoc($result);
            $_SESSION['user'] = $row;

            // redirect to the login_success.php
            header('Location: free-sample/');
            exit;
        }

        // query failed, display error
        $both_pw_unme = "<img src=\"../images/warning_triangle.png\" /> Error";
    }
}
// for was not submitted, display error
else
{
    $form_login = '<div class=\"login\" >Please use this form to login </div>';
}
?>

 

and this is the script for the page they will see if they have logged in correctly;

<?php
session_start();
require_once '../includes/connection.php';
// check that the  user has logged in
if(isset($_SESSION['is_logged_in']) && $_SESSION['is_logged_in'] !== true || !isset($_SESSION['is_logged_in']))
{
    die ("<div style=\"text-align:center;color:black;font-family:arial;\"><img src=\"../../images/padlock.png\" />You must <a href=\"../admin/..\" style=\"color:red;\">login</a> to view this page!</div>");
}
// no need to query members table, query the contacts table as you already have the username in the 'user' session
$sql  = "SELECT * FROM contacts WHERE username='{$_SESSION['user']['username']}'";
// perform the query
$result = mysql_query($sql);
// as you are only returning 1 row from the contact tables you don't need a while loop
$row = mysql_fetch_assoc($result);
// display contact data for user
	$sample = $row['sample'];
	$username = $row['username'];
	$type = $row['type'];
	$image = $row['image'];
	$title = $row['title'];
	$first_name = $row['first_name'];
	$surname = $row['surname'];
	$position= $row['position'];
	$company_name = $row['company_name'];
	$company_email = $row['company_email'];
	$company_fax = $row['company_fax'];
	$company_telephone_number = $row['company_telephone_number'];
	$company_fax = $row['company_fax'];
	$address_1 = $row['address_1'];
	$address_2 = $row['address_2'];
	$address_3 = $row['address_3'];
	$town = $row['town'];
	$county = $row['county'];
	$post_code = $row['post_code'];
/////
if(isset($_POST['submit']))
{
$sample=$_POST['sample'];
$type=$_POST['type'];
$title=$_POST['title'];
$first_name=$_POST['first_name'];
$surname=$_POST['surname'];
$position=$_POST['position'];
$company_name=$_POST['company_name'];
$company_email=$_POST['company_email'];
$company_fax=$_POST['company_fax'];
$company_telephone_number=$_POST['company_telephone_number'];
$address_1=$_POST['address_1'];
$address_2=$_POST['address_2'];
$address_3=$_POST['address_3'];
$town=$_POST['town'];
$county=$_POST['county'];
$post_code=$_POST['post_code'];
///spacer
$errors .= (empty($post_code)) ? "<span class=\"emptyFields\">postcode</span>" : "";
$errors .= (empty($company_name)) ? "<span class=\"emptyFields\">company name</span>" : "";
$errors .= (empty($company_telephone_number)) ? "<span class=\"emptyFields\">telepone number</span>" : "";
$errors .= (empty($company_email)) ? "<span class=\"emptyFields\">email</span>" : "";
//need to add country, product and state
if (!$errors)
{
if(!get_magic_quotes_gpc())
{
$sample = addslashes($sample);
$type = addslashes($type);
$title = addslashes($title);
$first_name = addslashes($first_name);
$surname = addslashes($surname);
$position= addslashes($position);
$company_name = addslashes($company_name);
$company_email = addslashes($company_email);
$company_fax = addslashes($company_fax);
$company_telephone_number= addslashes($company_telephone_number);
$address_1= addslashes($address_1);
$address_2= addslashes($address_2);
$address_3= addslashes($address_3);
$town= addslashes($town);
$county= addslashes($county);
$post_code= addslashes($post_code);
}
@ $db = mysql('localhost', 'root', 'DARREN', 'bulletins2');
if (mysql_error())
{
echo 'error';
}
$query = " INSERT INTO order_enquiries  (sample, type, title, first_name, surname, position, company_name, company_email,  company_fax, company_telephone_number, address_1, address_2, address_3, town, county, post_code ) VALUES ('".$sample."', '".$type."', '".$title."', '".$first_name."', '".$surname."', '".$position."','".$company_name."', '".$company_email."', '".$company_fax."','".$company_telephone_number."', '".$address_1."', '".$address_2."', '".$address_3."', '".$town."', '".$county."', '".$post_code."' )";
$result = mysql_query($query);
////spacer
$success .= "<div class=\"success\" >Thank you,<br /><br />Your free sample will be dispatched as soon as possible.<br /><br />Please <a href=\"logout.php\" style=\"font-weight:bold;color:black;\">Logout</a></span></div>";
//need to add country, product and state
if ($success);
}
}//else//{/
//send email aswell
//$sendTo  =  "[email protected]";
//$subject = "Free order sample of $sample";

//$headers  =  "From:$company_email \n ";
//$headers .= " $first_name $surname \r\n";
//$headers .= "Reply-To: $company_email \r\n";
//$message = " Sample order enquiry from:
		 //$title $first_name $surname has requested a free sample.

		 //TYPE:$type
		 //ADDRESS
		 //$company_name
		 //$address_1
		 //$address_2
		 //$address_3
		 //$town
		 //$county
		 //$post_code\n
		 //CONTACT DETAILS:
		 //tel:$company_telephone_number
		 //fax:$company_fax
		 //email:$company_email
		 //";

//mail($sendTo, $subject, $message, $headers);
//}
?>

 

kind regards

Dazzclub

 

[dazz_club]

hello back again, I was reading somewhere that innoDB, the way it handles auto incrementing columns can cause issues,

"... it certainly does limit scalability."

 

The problem is the members table. considering it has over 3 thousands users, this could the problem.

 

So my plan of action is to change over (the table engine) innoDB to Myisam and see what comes of it.