MS Access to MySQL conversion - need help!

croix · May 18, 2007

OK, I have a new host, and thus I now have the ability to use MySQL instead of Access.

I have a simple script, which I need to convert the code to utilize the new database. I will worry about migrating the content aft er I get teh script working.

OK, here's the current code:

<?php

if (isset($_POST['company'])) {
$company = $_POST['company'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address1 = $_POST['address1'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$country = $_POST['country'];
$outside_us = $_POST['outside_us'];
$phone = $_POST['phone'];
$website = $_POST['website'];
$fax = $_POST['fax'];
$email = $_POST['email'];
$store_type = $_POST['store_type'];
$comments = $_POST['comments'];
$referral = $_POST['referral'];

$msg = "Attention Sliquid! You have a new vendor request!
Company Name = {$company} 
First Name = {$firstname}
Last Name = {$lastname}
Address = {$address1}
Address 2 = {$address2}
City = {$city}
State = {$state}
Zip = {$zip}
Country = {$country}
Shipping Info = {$outside_us}
Phone = {$phone}
Website = {$website}
Fax = {$fax}
Email = {$email}
Store Type = {$store_type}
Comments = {$comments}
Referred By = {$referral}";

Enter_New_Entry($company,$firstname,$lastname,$address1,$address2,$city,$state,$zip,$country,$outside_us,$phone,$website,$fax,$email,$store_type,$comments,$referral);

//ini_set(SMTP, "smtp.sliquid.com");
ini_set(SMTP, "mail.sliquid.com");
ini_set(smtp_port, 25);
ini_set(sendmail_from, "[email protected]");
$headers = "From: [email protected]\r\n";
$headers .= "BCC: [email protected]\r\n";
//mail(To, subject, content, header);
$to = "[email protected]";
$result  = mail($to, "New Vendor Request", $msg, $headers);
if( $result == 0 ) {
	echo "error <br>";

}
}
function Enter_New_Entry 
($company,$firstname,$lastname,$address1,$address2,$city,$state,$zip,$country,$outside_us,$phone,$website,$fax,$email,$store_type,$comments,$referral) {

$cnx = odbc_connect( 'records_db' , '******', '********' );
    if (!$cnx) {
        Error_handler( "Error in odbc_connect" , $cnx );
    }

    $SQL_Exec_String = "Insert Into distributors (company, firstname, lastname, address1, address2, city, state, zip, country, outside_us, phone, website, fax, email, store_type, comments, referral)
            Values ('$company', '$firstname', '$lastname', '$address1', '$address2', '$city', '$state', '$zip', '$country', '$outside_us', '$phone', '$website', '$fax', '$email', '$store_type', '$comments', '$referral')";

    $cur= odbc_exec( $cnx, $SQL_Exec_String );
    if (!$cur) {
        Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
}

odbc_close($cnx);  
header("location: http://www.sliquid.com/verify.htm"); 

}

?>

I found out how to change my db connection to mysql, but im lost on teh other parts (mysql_connect, mysql_exec?)

any ideas, solutions, or links to something that will show me all the equivalent functions between access and mysql?

Thanks

\colin

hitman6003 · May 18, 2007

If you are using odbc, then just set up a new odbc connection to the mysql server and change your scripts to use that.

You'll need the mysql odbc driver from http://dev.mysql.com.

corbin · May 18, 2007

The syntax for all the SQL queries looks correct for mysql, so you could just trying using diff functions...

Ex:

mysql_connect instead of odbc_connect

mysql_query instead of odbc_exe.... etcetera

croix · May 18, 2007

whats the equivalent of odbc_close?

hitman6003 · May 18, 2007

I'm sure if you even glance at the manual you can figure it out...

http://www.php.net/mysql

croix · May 18, 2007

ya i just found that....

thanks!

croix · May 18, 2007

OK, i got a test db connection page to work...

<html>
<head>
<title>MySQL database test</title>
</head>
<body>

<?php

if (!($db = 
mysql_connect("localhost","user_name","my_password"))) {
        print ("Unable to connect to database host: $_db_host");
        errorEmail();
        exit;
}

if (!(mysql_select_db("db_name",$db)))  {
        print ("Unable to select database: $_db_name");
        errorEmail();
        exit;
}

print "<p>Database connection successful.<br /></p>";


?>

</body>
</html>

when I access this page, it echos "Database connection successful"

however, when I run my original script:

function Enter_New_Entry 
($company,$firstname,$lastname,$address1,$address2,$city,$state,$zip,$country,$outside_us,$phone,$website,$fax,$email,$store_type,$comments,$referral) {

$cnx = mysql_connect("localhost","user_name","my_password");
    if (!$cnx) {
        Error_handler( "Error in mysql_connect" , $cnx );
    }

    $SQL_Exec_String = "Insert Into distributors (company, firstname, lastname, address1, address2, city, state, zip, country, outside_us, phone, website, fax, email, store_type, comments, referral)
            Values ('$company', '$firstname', '$lastname', '$address1', '$address2', '$city', '$state', '$zip', '$country', '$outside_us', '$phone', '$website', '$fax', '$email', '$store_type', '$comments', '$referral')";

    $cur= mysql_query( $cnx, $SQL_Exec_String );
    if (!$cur) {
        Error_handler( "Error in mysql_query( no cursor returned ) " , $cnx );
}

mysql_close($cnx);  
header("location: http://www.sliquid.com/verify.htm"); 

}

?>

nothing is inserted into the database...

no error message is returned...

and because of my submit to and my header redirect, it takes me to my old host...

looking at it, it seems that the dbtest script has a line that references the database name, where the form does not.

how can i modify the script to contain the reference to the database name, or is there a better way to accomplish this?

croix · May 18, 2007

i cant find a way to edit my post, so Im just going to have to add new information.

I changed my script to point back to itself instead of the old host, as well as my header.

Now I am getting an error

<b>

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/sliquidc/public_html/become.php on line 47

Fatal error: Call to undefined function error_handler() in /home/sliquidc/public_html/become.php on line 49</b>

This is where the error is reporting:

    $cur= mysql_query( $cnx, $SQL_Exec_String );
    if (!$cur) {
        Error_handler( "Error in mysql_query( no cursor returned ) " , $cnx );

here is the whole code:

<?php

if (isset($_POST['company'])) {
$company = $_POST['company'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address1 = $_POST['address1'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$country = $_POST['country'];
$outside_us = $_POST['outside_us'];
$phone = $_POST['phone'];
$website = $_POST['website'];
$fax = $_POST['fax'];
$email = $_POST['email'];
$store_type = $_POST['store_type'];
$comments = $_POST['comments'];
$referral = $_POST['referral'];

$msg = "Attention Sliquid! You have a new vendor request!
Company Name = {$company} 
First Name = {$firstname}
Last Name = {$lastname}
Address = {$address1}
Address 2 = {$address2}
City = {$city}
State = {$state}
Zip = {$zip}
Country = {$country}
Shipping Info = {$outside_us}
Phone = {$phone}
Website = {$website}
Fax = {$fax}
Email = {$email}
Store Type = {$store_type}
Comments = {$comments}
Referred By = {$referral}";

Enter_New_Entry($company,$firstname,$lastname,$address1,$address2,$city,$state,$zip,$country,$outside_us,$phone,$website,$fax,$email,$store_type,$comments,$referral);

//ini_set(SMTP, "smtp.sliquid.com");
ini_set(SMTP, "mail.sliquid.com");
ini_set(smtp_port, 25);
ini_set(sendmail_from, "[email protected]");
$headers = "From: [email protected]\r\n";
$headers .= "BCC: [email protected]\r\n";
//mail(To, subject, content, header);
$to = "[email protected]";
$result  = mail($to, "New Vendor Request", $msg, $headers);
if( $result == 0 ) {
	echo "error <br>";

}
}
function Enter_New_Entry 
($company,$firstname,$lastname,$address1,$address2,$city,$state,$zip,$country,$outside_us,$phone,$website,$fax,$email,$store_type,$comments,$referral) {

$cnx = mysql_connect("localhost","user_name","password");
    if (!$cnx) {
        Error_handler("Error in mysql_connect",$cnx);
    }

    $SQL_Exec_String = "Insert Into distributors (company, firstname, lastname, address1, address2, city, state, zip, country, outside_us, phone, website, fax, email, store_type, comments, referral)
            Values ('$company', '$firstname', '$lastname', '$address1', '$address2', '$city', '$state', '$zip', '$country', '$outside_us', '$phone', '$website', '$fax', '$email', '$store_type', '$comments', '$referral')";

    $cur= mysql_query( $cnx, $SQL_Exec_String );
    if (!$cur) {
        Error_handler( "Error in mysql_query( no cursor returned ) " , $cnx );
}

mysql_close($cnx);  
header("location: http://69.89.31.71/~sliquidc/verify.htm"); 

}

?>

croix · May 21, 2007

no one?

TEENFRONT · May 21, 2007

is error_handler() a valid php funtion??

croix · May 21, 2007

OK, Im not sure why error_handler was working on the old server, but I changed it.

Here's the error Im getting now when I submit the form:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/sliquidc/public_html/become.php on line 47

Invalid query:

$cnx = mysql_connect("localhost","user_name","user_pass");
    if (!$cnx) {
        die('Could not connect: ' . mysql_error());
    }

    $SQL_Exec_String = "Insert Into distributors (company, firstname, lastname, address1, address2, city, state, zip, country, outside_us, phone, website, fax, email, store_type, comments, referral)
            Values ('$company', '$firstname', '$lastname', '$address1', '$address2', '$city', '$state', '$zip', '$country', '$outside_us', '$phone', '$website', '$fax', '$email', '$store_type', '$comments', '$referral')";

    $cur = mysql_query($cnx, $SQL_Exec_String);
    if (!$cur) {
        die('Invalid query: ' . mysql_error());
}

mysql_close($cnx);  
header("location: http://69.89.31.71/~sliquidc/verify.htm"); 

}

per1os · May 21, 2007

$cur = mysql_query($SQL_Exec_String);

The connection portion is only required for mysqli as the first parameter.

www.php.net/mysql_query suggest looking over the functions usage.

Sign In

MS Access to MySQL conversion - need help!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information