SQL Insert

[losintikfos]

Hi Experts,

 

I am trying to insert an array to my MySQL table using this code shown below;

 

$data = array(0 => array('Name' => 'Test1', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 1 => array('Name' => 'Test2', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 2 => array('Name' => 'Test3', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 3 => array('Name' => 'Test4', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 4 => array('Name' => 'Test5', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), )

foreach($data as $row){

//Connecting to Database
        $db = new DbConnect();
        $con = mysql_connect($db->hostName,$db->username, $db->password);
        if (!$con)
        {die('Could not connect: ' . $db->showerror());}



        //SELECT Database
        mysql_select_db("test", $con);



            $sql = " INSERT INTO sites(name, street, city, postcode, telephone, email, description, image, image_type, price_month, active, broker_id, operator_id, secondary_contact, secondary_contact_email, postal_contact, postal_email, postal_telephone) VALUES ('{$row['Name']}', '{$row['Street']}', '{$row['City']}', '{$row['PostCode']}', NULL, NULL, '{$row['Description']}', NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL);";


            echo $sql;

            $result = mysql_query($sql);



            if(!$result){
                echo "SQL executing error" .mysql_error();
            }


            else{
                return 'success';
            }

}

 

When i execute the above code i get success to confirm but the data is not inserted onto the table. Can someone help me to fix this!

 

Pls help. ???

[RichardRotterdam]

Why is the query in a loop? It's better to just create one query which makes it faster too.

[losintikfos]

I loop through the array to fetch the values within so i can pass it to the query for insert. If i remove the foreach statement i donot get any value from the array! mmm

[losintikfos]

I have changed the code to look something as shown below and still doesn't work.

 

$data = array(0 => array('Name' => 'Test1', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 1 => array('Name' => 'Test2', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 2 => array('Name' => 'Test3', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 3 => array('Name' => 'Test4', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 4 => array('Name' => 'Test5', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), )

 

foreach($data as $row){//Nothing here}

 

//Connecting to Database

        $db = new DbConnect();

        $con = mysql_connect($db->hostName,$db->username, $db->password);

        if (!$con)

        {die('Could not connect: ' . $db->showerror());}

 

 

 

        //SELECT Database

        mysql_select_db("test", $con);

 

 

 

            $sql = " INSERT INTO sites(name, street, city, postcode, telephone, email, description, image, image_type, price_month, active, broker_id, operator_id, secondary_contact, secondary_contact_email, postal_contact, postal_email, postal_telephone) VALUES ('{$row['Name']}', '{$row['Street']}', '{$row['City']}', '{$row['PostCode']}', NULL, NULL, '{$row['Description']}', NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL);";

 

 

            echo $sql;

 

            $result = mysql_query($sql);

 

 

 

            if(!$result){

                echo "SQL executing error" .mysql_error();

            }

 

 

            else{

                echo 'success';

            }

[RichardRotterdam]

I noticed something. You use a db class and mysql functions. I suggest you stick with one of them.

 

also I thought something like the following would be better. You just have to create your single insert query yourself.

<?php
$data = array(0 => array('Name' => 'Test1', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 1 => array('Name' => 'Test2', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 2 => array('Name' => 'Test3', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 3 => array('Name' => 'Test4', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 4 => array('Name' => 'Test5', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), )

mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME) or die(mysql_error());

//SELECT Database
mysql_select_db("test");

foreach($data as $row){

/**
 *Create your single query here 
 */
$sql = "";


}
//just one insert needed now
mysql_query($sql)or die(mysql_error();
?>

[losintikfos]

Thanks for the advice!

 

I do appreciate it. Also i have changed the code to suite the one above. I still end up with success message but the values within the array is not inserted. hmmmm

[RichardRotterdam]

What does your code look like so far?

[losintikfos]

The code looks like this currently;

 

<?php
$data = array(0 => array('Name' => 'Test1', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 1 => array('Name' => 'Test2', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 2 => array('Name' => 'Test3', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 3 => array('Name' => 'Test4', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), 4 => array('Name' => 'Test5', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description', ), );



//Connecting to Database
$con = mysql_connect('HOST','USER', 'PASS');
if (!$con)
{die('Could not connect: ' . mysql_error());}



//SELECT Database
mysql_select_db("test", $con);
foreach($data as $row){
    $sql = "INSERT INTO site(name, street, city, postcode, telephone, email, description, image, image_type, price_month, active, broker_id, operator_id, secondary_contact, secondary_contact_email, postal_contact, postal_email, postal_telephone) VALUES ('{$row['Name']}', '{$row['Street']}', '{$row['City']}', '{$row['PostCode']}', NULL, NULL, '{$row['Description']}', NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
}



mysql_query($sql) or die("SQL executing error" .mysql_error());
echo "Success";



?>

[RichardRotterdam]

if  you echo out your query you will notice it's not correct do this after your loop

echo $sql;

 

your query should look like

insert into your_table_name(field1,field2,field3 values 
(record1val1,record1val2,record1val3),
(record2val1,record2val2,record2val3),
(record3val1,record3val2,record3val3);

[losintikfos]

You right!

My query doesn't seem to hold the look like a build array of values for insert as you've depicted above.

 

Do you know how to get the value list for the value for insert?

 

Pls help.

[losintikfos]

I have changed the query to something like this;

 

    //SELECT Database
    mysql_select_db("test", $con);
    $sql = " INSERT INTO site(name, street, city, postcode, telephone, email, description, image, image_type, price_month, active, broker_id, operator_id, secondary_contact, secondary_contact_email, postal_contact, postal_email, postal_telephone) VALUES";

    $ind = count($data);
    foreach($data as $row){

            // Build the value array
            $sql .= "({$row['Name']}, '{$row['Street']}', '{$row['City']}')";

            $ind --;

    }

    echo $sql;

 

The 'echo $sql' did spit out the following;

INSERT INTO site(name, street, city, postcode, telephone, email, description, image, image_type, price_month, active, broker_id, operator_id, secondary_contact, secondary_contact_email, postal_contact, postal_email, postal_telephone) VALUES('Test1', 'Test Street', 'Test City')('Test2', 'Test Street', 'Test City')('Test3', 'Test Street', 'Test City')('Test4', 'Test Street', 'Test City')('Test5', 'Test Street', 'Test City')('Test6', 'Test Street', 'Test City')

 

 

But i am get this SQL trace;

SQL executing errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Test2', 'Test Street', 'Test City')('Test3', 'Test Street', 'Test City')('Test' at line 1

 

 

Can anyone spot what i am doing wrong?

[revraz]

Need commas in there

 

INSERT INTO site(name, street, city, postcode, telephone, email, description, image, image_type, price_month, active, broker_id, operator_id, secondary_contact, secondary_contact_email, postal_contact, postal_email, postal_telephone) VALUES('Test1', 'Test Street', 'Test City'),('Test2', 'Test Street', 'Test City'),('Test3', 'Test Street', 'Test City'),('Test4', 'Test Street', 'Test City'),('Test5', 'Test Street', 'Test City'),('Test6', 'Test Street', 'Test City')

 

[taquitosensei]

There's no commas in between your data sets

[manny]

There try this

<?php
$data = array(
	0 => array('Name' => 'Test1', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description'), 
	1 => array('Name' => 'Test2', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description'), 
	2 => array('Name' => 'Test3', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description'), 
	3 => array('Name' => 'Test4', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description'), 
	4 => array('Name' => 'Test5', 'Street' => 'Test Street', 'City' => 'Test City', 'PostCode' => 'Test Pcode', 'Description' => 'test Description')
);

//Connecting to Database
$con = mysql_connect('HOST','USER', 'PASS');
if (!$con)
{die('Could not connect: ' . mysql_error());}

//SELECT Database
mysql_select_db("test", $con);
foreach($data as $id=>$row)
{
    $sql = "INSERT INTO site (
	    	`name`, 
	    	`street`, 
	    	`city`, 
	    	`postcode`, 
	    	`telephone`, 
	    	`email`, 
	    	`description`,
	    	`image`, 
	    	`image_type`, 
	    	`price_month`, 
	    	`active`, 
	    	`broker_id`, 
	    	`operator_id`, 
	    	`secondary_contact`, 
	    	`secondary_contact_email`, 
	    	`postal_contact`, 
	    	`postal_email`, 
	    	`postal_telephone`
	    	) 
    VALUES (
    	'{$row['Name']}', 
    	'{$row['Street']}', 
    	'{$row['City']}', 
    	'{$row['PostCode']}', 
    	'NULL', 
    	'NULL', 
    	'{$row['Description']}', 
    	'NULL', 
    	'NULL', 
    	'NULL', 
    	'0', 
    	'NULL', 
    	'NULL', 
    	'NULL', 
    	'NULL', 
    	'NULL', 
    	'NULL', 
    	'NULL'
    	)";
}

mysql_query($sql) or die("SQL executing error" .mysql_error());
echo "Success";

?>

a few Syntax errors and also tell me if this solved your problem

 

[losintikfos]

That didn't neither!

 

Pretty strange. I get success but nothing in the table.

[RichardRotterdam]

you were actually on the right track with the following code

<?php
//SELECT Database
mysql_select_db("test", $con);
$sql = " INSERT INTO site(name, street, city, postcode, telephone, email, description, image, image_type, price_month, active, broker_id, operator_id, secondary_contact, secondary_contact_email, postal_contact, postal_email, postal_telephone) VALUES";

$ind = count($data);
foreach($data as $row){

            // Build the value array
            $sql .= "({$row['Name']}, '{$row['Street']}', '{$row['City']}')";

            $ind --;

}

echo $sql;

 

Look whats going wrong in your outputed query I highlighted in red

 

INSERT INTO site(name, street, city, postcode, telephone, email, description, image, image_type, price_month, active, broker_id, operator_id, secondary_contact, secondary_contact_email, postal_contact, postal_email, postal_telephone) VALUES('Test1', 'Test Street', 'Test City')('Test2', 'Test Street', 'Test City')

 

see the red marked part? There should be a comma between those.

 

also you are using the $ind var the wrong way