DB Insert problem

cbrknight

I am using two pieces of script...

here is the database class

class sql_wrapper{

var $dbhost;
var $dbuser;
var $dbpass;
var $dbase;
var $sql_query;
var $mysql_link;
var $sql_result;
var $query_count;	

	function sql_wrapper($dbhost,$dbuser,$dbpass,$dbase)
	{

		$this->dbhost = $dbhost;
    	$this->dbuser = $dbuser;
    	$this->dbpass = $dbpass;
    	$this->dbase = $dbase; 
    	$this->mysql_link = '0';
    	$this->query_count = '0';
    	$this->sql_result = '';
    	$this->connection();

	}

	//make connection to the database
	function connection(){

    	$this->mysql_link = @mysql_connect( $this->dbhost, $this->dbuser, $this->dbpass ) or $this->error( mysql_error( $this->mysql_link ), $this->sql_query, mysql_errno( $this->mysql_link ) );


    	@mysql_select_db( $this->dbase ) or $this->error( mysql_error( $this->mysql_link ), $this->sql_query, mysql_errno( $this->mysql_link ) );

	}

	//display any errors in readable format
	function error( $error_msg, $sql_query, $error_no )
	{

    	$error_date = date("F j, Y, g:i a");

//Heredoc Strings must be on the far left lines        

$error_page = <<<EOD
<html><head><title>mySQL database Error</title>
<style>P,BODY{ font-family:arial,sans-serif; font-size:11px; }</style></head><body>
&nbsp;<br><br><blockquote><b>There appears to be an error while trying to complete your request.</b><br>
You can try to go back and try again by clicking <a href="javascript:history.go(-1);">here</a>, if you
still get the error you can contact the site administrator by clicking <a href='mailto:haiden@asciant.net?subject=SQL+Error'>here</a>
<br><br><b>Error Returned</b><br>
<form name='mysql'><textarea rows="15" cols="60">mySQL query error: {$sql_query}
mySQL error: {$error_msg}
mySQL error code: {$error_no}
Date: {$error_date}</textarea></form><br>We apologise for any inconvenience</blockquote></body></html>
EOD;

		echo $error_page;

	} 

	function sql_query( $sql_query )
	{

    	$this->sql_query = $sql_query;

    	$this->sql_result = @mysql_query( $sql_query, $this->mysql_link );

    	if (!$this->sql_result)

    	{

        	$this->error( mysql_error( $this->mysql_link ), $this->sql_query, mysql_errno( $this->mysql_link ) );

    	}


        $count = $this->query_count;

        $count = ($count + 1);

        $this->query_count = $count;

	}

	//close database link
	function close()
	{

    	mysql_close( $this->mysql_link );

	}

    function num_rows()
	{

    	$mysql_rows = mysql_num_rows( $this->sql_result );

   		if (!$mysql_rows)

    	{

        	$this->error( mysql_error( $this->mysql_link ), $this->sql_query, mysql_errno( $this->mysql_link ) );

    	}

    	return $mysql_rows;

	}

	function affected_rows()
	{

    	$mysql_affected_rows = mysql_affected_rows( $this->mysql_link );

    	return $mysql_affected_rows;

	}

	function fetch_array()
	{

    	if ( $this->num_rows() > 0 )

    	{

        	$mysql_array = mysql_fetch_assoc( $this->sql_result );

        		if (!is_array( $mysql_array ))
        		{
            		return FALSE;
        		}

        	return $mysql_array;
    	} 
	}

	function fetch_rows()
	{
    	if ( $this->num_rows() > 0 )

    	{

        	$mysql_array = mysql_fetch_row( $this->sql_result );

        if (!is_array( $mysql_array ))
        {
            return FALSE;
        }

        return $mysql_array;
    	}
    }

    function query_count()
	{

    	return $this->query_count;

	}

}

and here is the script

include ("includes/sql_db_wrapper.php");
include ("includes/functions.php");
$dbhost	= 'localhost';
$dbuser	= 'root';
$dbpass	= 'urnid10t';
$dbase	= 'ctfstores';
// Make Database connection
$database = new sql_wrapper($dbhost,$dbuser,$dbpass,$dbase); 

$select_users = "SELECT * FROM user_upload";
$database->sql_query($select_users);
while($data = $database->fetch_array()){
/*echo "<pre>";
print_r($results);
echo "</pre>";*/

/* zen cart customers fields */

$customers_id					= "";
$customers_gender				= "";
$customers_firstname			= addslashes($data['firstname']);
$customers_lastname				= addslashes($data['lastname']);
$customers_dob					= "";
$customers_email_address		= $data['email'];
$customers_nick					= "";
$customers_default_address_id	= addslashes($data['address1']);
$customers_telephone			= addslashes($data['phonenumber']);
$customers_fax					= "";
$customers_password				= addslashes($data['password']);
$customers_newsletter			= "";
$customers_group_pricing		= "";
$customers_email_format			= "";
$customers_authorization		= "";
$customers_referral				= "";
$customers_paypal_payerid		= "";
$customers_paypal_ec			= "";
$customers_username				= $data['username'];

echo '<br/>Firstname:'.$customers_firstname.'<br/>Lastname:'.$customers_lastname.'<br/>Email:'.$customers_email_address;

$data_import = "INSERT IGNORE INTO zen_customers (`customers_username`) VALUES ('$customers_username')";
$database->sql_query($data_import);


}

I am getting this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2triad\htdocs\vhosts\customer_uploader\includes\sql_db_wrapper.php on line 103

anyone able to tell me why? I have used this wrapper to do insert statements before. Dont know why it isnt working this time.

Thanks

tomhath

Doesn't look like it ever got to the INSERT. My guess is that it had some problem with the "SELECT * FROM user_upload", but kept going instead of stopping when the error function was called.

cbrknight

tomhath wrote:
Doesn't look like it ever got to the INSERT. My guess is that it had some problem with the "SELECT * FROM user_upload", but kept going instead of stopping when the error function was called.

I think it did get to the insert. As a matter of fact it inserts the first record twice before throwing the error.

Sxooter

From the docs:

Description
int mysql_num_rows ( resource $result )

Retrieves the number of rows from a result set. This command is only valid for statements like SELECT or SHOW that return an actual result set. To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use mysql_affected_rows().

You can't use num_rows() to get back a count from an insert update etc...

It looks to me like your class is trying to use mysql_num_rows() to get the affected rows from an insert statement. If that's so, I can't believe it's worked, as is, in the past on insert statementss.

cbrknight

The code i used that worked was to pull information from a csv file.. parsed it then inserted it into a database.

This code works just fine.

include ("includes/sql_db_wrapper.php");
include ("includes/functions.php");

// Make Database connection
$database = new sql_wrapper(); 

$filename = $_FILES['userfile']['name'];

//clear bmi_products table.
$sql_clear_tblBMI = "TRUNCATE user_upload";
$database->sql_query($sql_clear_tblBMI);
//$uploaddir = 'tab/';
$uploaddir = 'tab/';
$uploadfile = $uploaddir . $filename;

echo $uploadfile;

if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile)){
	$fcontents = file($uploadfile); 

//print_r ($fcontents);

for($i=0; $i<sizeof($fcontents); $i++) { 
		$rand_password = randPassword(8);
		$file_exp = explode("_", $filename);
		$line = trim($fcontents[$i]); 
		$data = explode(",", $line);
		$data = str_replace("\"","",$data); 
		echo '<pre>';
		print_r ($data);
		echo '</pre>';
//		echo '<pre>';
//		echo $rand_password;
//		echo '</pre>';		
		//$data = addslashes($data);
		$phonenumber = addslashes($data['0']);
		$firstname = addslashes($data['1']);
		$lastname = addslashes($data['2']);
		$address1 = addslashes($data['3']);
		$city = addslashes($data['4']);
		$stateprovince = addslashes($data['5']);
		$zip = addslashes($data['6']);
		$username = addslashes($phonenumber);
		$pass1 = $data['8'];
		$password = zen_encrypt_password($data['8']);
		$email = $username.'@'.$file_exp[0].'.com';
		$country = addslashes($data['10']);

	$data_import_sql = "INSERT IGNORE INTO user_upload (`phonenumber`,`firstname`,`lastname`,`address1`,`city`,`stateprovince`,`zip`,`username`,`pass1`,`password`,`email`,`country`) VALUES ('$phonenumber','$firstname','$lastname','$address1','$city','$stateprovince','$zip','$username','$pass1','$password','$email','$country')";
	$database->sql_query($data_import_sql);
	//$results_import = $database->result;


	}
echo '<p><strong>File Inport Complete.</strong></p>';
echo '<a href=index.php>Back To Menu</a>';
}else{
	echo "<p>Possible file upload attack: </p>";
	echo "<p>filename '". $_FILES['userfile']['tmp_name'] . "'.</p>";
}

Sxooter

OK, if you print out your query right before it's executed, and run it from the mysql command prompt, does it work?

cbrknight

Sxooter wrote:
OK, if you print out your query right before it's executed, and run it from the mysql command prompt, does it work?

never tried that... how do I do it?

Sxooter

OK, I grabbed the sql_wrapper code and threw it in an editor, and the line 103 is somewhere in the middle of the function num_rows(). But the standard sql_query code doesn't seem to call this part of the class.

I'm beginning to think that the insert isn't getting run either.

Looks to me like your select is failing and the fetch_array is causing the fail. after all, it does call num_rows

Sxooter

cbrknight wrote:
never tried that... how do I do it?

Just throw a line like:

print $query;

between where you create the query and where you execute it.

Note that since I now think the select is what's failing, you might want to echo that out and run it in the mysql monitor. Note that you should connect as the same user / password as your app is connecting as to see if everything's working right.

cbrknight

It looks like fetch_array() calls num_rows(). The num_rows() function seems to be what calls the mysql_num_rows(). I am not sure why it is not working with this. I wonder if there is another way to take the information from one table and put it into another table without using

while($data = $database->fetch_array()){

...

}

cbrknight

Ok... did some research... Have been experimenting with the following code...

include ("includes/sql_db_wrapper.php");
include ("includes/functions.php");
$dbhost	= 'localhost';
$dbuser	= 'root';
$dbpass	= 'urnid10t';
$dbase	= 'ctfstores';
// Make Database connection
$database = new sql_wrapper($dbhost,$dbuser,$dbpass,$dbase); 

$data_insert = "INSERT IGNORE INTO zen_customers
 (`customers_firstname`, `customers_lastname`,`customers_email_address`,`customers_default_address_id`,`customers_telephone`,`customers_password`,`customers_username`)
SELECT `firstname`,`lastname`,`email`,`address1`,`phonenumber`,`password`,`username` FROM user_upload
";

$database->sql_query($data_insert);

$rows = $database->affected_rows;

echo $rows." Records Inserted.";

When I put the query into a command line it works. However when I run it from the script... It runs through all records and inserts them... then it starts over and inserts them a second time. So I am ending up with 2 records for every one I try to import. Any insites into why this is happening?