Page 1 of 1

PHP Mysql syntax and logic question

Posted: Mon Dec 06, 2004 11:04 pm
by rmul1966
Hello everyone,
** I made some changes to correct and obvious error I had
New Code listed below ****
Thanks again for any assistance
I have created some code to do the following things and I have a few bugs in it that I will describe as I go.
What I am trying to accomplish is
1. read variables passed from a Form which contains the Database , Table name , and Data File for load,.

2. Check to see if table exists If so then drop it

3 Create new table

4 Load new Table with data file which is Pipe delimeted newline terminated

I think I have pieced together a somewhat close rendition to what I have described above but I am having problems with
(1) syntax around line 81 which is the Drop table $DB_NAME
and
(2) I think I may have a problem if the section that checks if the TB_name is returnd when the tables are listed has more than 1 table returned... not real sure about this.

I am Very new to this and thought I would ask now that I have pieced what I could together.

Any help with this would be greatly apreciated.
Thank You
Robert

Code: Select all

<?php

// Connect to database 
include $_SERVER['DOCUMENT_ROOT']."/php/connectdor.php";

$sqlstep = mysql_list_tables($DB_NAME);
if (!$sqlstep) {
  echo "Error, could not list tables\n";
  echo 'MySQL Error code: ' . mysql_error();
  exit;
}

// If DORLIST TABLE EXISTS Drop it 
if(mysql_list_tables($DB_NAME) == $TB_NAME){
$sqlstop = Drop table '$TB_NAME'; } 
$result = mysql_query($sqlstep);
if (!$result) {		
	 						echo("<p>Error performing query: $sqltep " .		mysql_error() . "</p>"); 
							exit();   
							}
echo "$DB_NAME.$TB_NAME dropped\n";

// If DORLIST Table does not exist create it
if(mysql_list_tables($DB_NAME) != $TB_NAME){
$sqlstop = CREATE TABLE '$TB_NAME'(
id int(6) NOT NULL auto_increment,
Title varchar(5) NOT NULL,
Last_Name varchar(25) NOT NULL,
First_Name varchar(15) NOT NULL,
Middle_Name varchar(10) NOT NULL,
DFD DATETIME NOT NULL,
DOB DATETIME NOT NULL,
DOD DATETIME NOT NULL,
Spouse varchar(15) NOT NULL,
Addr1 varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State varchar(2) NOT NULL,
ZIP varchar(5) NOT NULL,
Phone varchar(20) NOT NULL,
PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id)
) } 
$result = mysql_query(  $sqlstep	); 

$sqlstep="LOAD DATA LOCAL INFILE '$Infile_name' INSERT INTO TABLE $TB_Name.$TB_NAME FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'";

$result = mysql_query($sqlstep);  
if (!$result) {		
	 						echo("<p>Error performing query: $sqltep " .		mysql_error() . "</p>"); 
							exit();   
							}
echo (mysql_affected_rows() .  " rows inserted into $DB_NAME.$TB_NAME \n\r");

mysql_close($db);
?> 
?>

Posted: Tue Dec 07, 2004 3:50 am
by timvw
you can shorten some code... mysql has an exists function

Code: Select all

IF EXISTS tables DROP table;
CREATE table (
);

Posted: Tue Dec 07, 2004 9:19 am
by rmul1966
Tim Thank you for your tip, I will use that to clean up the code but before I can even do that I have a problem with my logic even before getting to that point.
I have been changing the code around to display variables at different stages of the entire script to make sure that everything is correct.

For instance when I click on the submit button on the form which passes the Database name and Table names to the Validation script I echo out all the variables to ensure that they are being passed correctly. I echo them on on Lines 14- 28 Below in the Validate_new2.php script.

At that partticular step everything looks great

I then do a connect to database using a connectdor.php script which
connects to the database, Lines 56-58 Validate_new2.php
At this point I am noticing is that when I echo the variables after I do the Connect to database the Variables are no longer correct. ie the variable $DB_NAME is blank or whitespace

I am not sure why the variable is not staying intact. can you reccomend a solution ?
here is my updated code
the form php file

Code: Select all

&lt;form method="POST" action="Validate_new2.php"&gt;
 							 &lt;p&gt;Database to Use:&lt;input type="text" name= "DB_Name"&gt;&lt;/p&gt;
							 &lt;p&gt;Table to Load:&lt;input type="text" name= "TB_Name"&gt;&lt;/p&gt;
							 &lt;p&gt;Input File Name:&lt;input type="text" name= "Infile_name"&gt;&lt;/p&gt;
							 &lt;p&gt;User Name:&lt;input type="text" name= "User_nm"&gt;&lt;/p&gt;
							 &lt;p&gt;User Password:&lt;input type="password" name= "U_passwd"&gt;&lt;/p&gt;
							 &lt;input type="submit" name="submit" value="Update DB!"&gt;
							 &lt;/form&gt;
The validate_new2.php file

Code: Select all

<?php
<?php include $_SERVER['DOCUMENT_ROOT']."/php/header.php"; ?>
	
<?php include $_SERVER['DOCUMENT_ROOT']."/php/nav.php"; ?>

  <div id="sidebar"> 
    <h1>Validate.php</h1>
		<p>This page is to Validate the Form coming from Load Database</p>
  </div>
	
  <div id="content"> 
				<h2>Validate Form Variables</h2>
				<?
				echo "The name of the Database you are using is      ..................... ";
				echo"($DB_Name)<br>";

				echo "The name of the table you are trying to load is..................... ";
				echo"($TB_Name)<br>";

				echo "The name of the Input file to be read into the data loader is.......... ";
				echo "($Infile_name)<br>";
				
				echo "Autorized or Non Authorized User ?<br>";
				echo "The User name your using to Load the Database is ................... ";
				echo "($User_nm)<br>";
				
				echo "The User password your using to Load the Database is ............. ";
				echo "($U_passwd)<br>";
				// the following code is used to validate the existance of the infile
				if (file_exists($_POST['Infile_name']))
					 {
					 echo "($Infile_name) File exists! in the proper location<br>";
					 }
				else
  				 {
				   echo "($Infile_name) File does not exist! or is not in correct directory";
					 }
				
				if (is_readable($_POST['Infile_name']))
				  {
  				echo "($Infile_name) = File is readable <br><hr>";
					 }
				else
						{
						echo "($Infile_name) is not readable<br><hr>";
						}
				?>
								
				
<!-- The Following Code is used to drop the Table then create a new table 
and then Load the table with the correct data -->				
<?php

// Connect to database 

include $_SERVER['DOCUMENT_ROOT']."/php/connectdor.php";
echo "Returned from connectdor.php with no problems <br />";
echo "Variable passed from Form after connect to database:$DB_NAME<br />";

$sqlstep = mysql_list_tables($DB_NAME);

if (!$sqlstep) {
   echo "DB Error, could not list tables\n";
   echo 'MySQL Error: ' . mysql_error();
   exit;
}

while ($row = mysql_fetch_row($sqlstep)) {
   echo "Table: $row[0]\n";
}

echo "returned from mysql list tables $DB_NAME\n";

mysql_close($dbh);
?> 

				<hr />
  </div>
	
<?php include $_SERVER['DOCUMENT_ROOT']."/php/footer.php"; ?>

?>
The connectdor.php file contains the following information

Code: Select all

<?php
<?php
include("/my/secure/path/keydor.php");

$dbh=mysql_connect ($host, $user, $passwd) or die ('I cannot connect to the database because: ' . mysql_error());

mysql_select_db ("$dbh");

?>
?>
the keydor.php file contains the Database name user id and passwords

Code: Select all

<?php
<?php
$host="localhost";
$db="mydatabase";
$user="myuserid";
$passwd="mypassword";
?>
?>
I have tested the connectdor with the keydor php files utilizing a select statement that returned the correct results.

Posted: Tue Dec 07, 2004 9:52 am
by timvw
Try:

Code: Select all

echo"{$_POST['DB_Name']}<br>";

print_r($_POST);

Posted: Tue Dec 07, 2004 9:59 am
by rmul1966
Tim Thank you, CASE SENSITIVE was killing me.... after staring at it for what seems like hours to find out that it was a simple thing like that sure makes a person feel like UGHHHHHHHHHHHHHHH


Thanks for your help

Posted: Tue Dec 07, 2004 3:49 pm
by timvw
actually, i was hoping you would see i used " " instead of your ' '