Could you point what is needed to make an array to be inserted as MySQL table's lines by using PHP?

replace

$sql = "INSERT INTO `server_campaigns`($columns) VALUES ($values)";

with something like this UNSAFE code ( no go if this is user input )

$sql="INSERT INTO  `server_campaigns`(".implode(',',$columns).') VALUES ('".implode("','",$values)."')";

or if you want something safe :

$sql="INSERT INTO  `server_campaigns`(".implode(',',$columns).') VALUES (";
foreach($values as $value)$sql.=var_export($value).',';
$sql[strlen($sql)]=')';

you can replace var_export with mysql_realescape_string if you prefer but var_export is safe in a web server context

Hi

I don't know if I correctly understood:

The code I used:

$columns = implode(", ",array_keys($yourArray));
$escaped_values = array_map('var_export', array_values($yourArray));
$values  = implode(", ", $escaped_values);

$sql="INSERT INTO  server_campaigns(".implode(',',$columns).") VALUES (";

foreach($values as $value)  
	$sql.=var_export($value).',';
	$sql[strlen($sql)]=')';

print_r($sql);
die;

Select allOpen in new window

The error I get:

<h4>A PHP Error was encountered</h4>

<p>Severity: Warning</p>
<p>Message:  Invalid argument supplied for foreach()</p>
<p>Filename: controllers/replication.php</p>
<p>Line Number: 212</p>

Select allOpen in new window

Could you give a look?

remove line 2 and 3. you are escaping the values separately in the foreach loop already.

or alternatively keep them and replace the foreach with a regular implode.

and if you're still in trouble post your code and the resulting query

What I did with success is:

$sql_server_campaigns  = "SELECT * FROM server_campaigns";
$result = mysqli_query($con_local, $sql_server_campaigns); 
$server_campaigns_Array = array(); 
$index = 0;
while($row = mysqli_fetch_assoc($result)){  //loop to store the data in an associative array.
	 $server_campaigns_Array[$index] = $row;
	 $index++;
}

mysqli_query($con_remota , 'TRUNCATE TABLE server_campaigns;');
                
$i = -1;
foreach($server_campaigns_Array as $key => $item)
{
	$i++;
	
	$sql="INSERT INTO  server_campaigns VALUES ('";
 
	$sql= $sql . implode("','",$server_campaigns_Array[$i]); 
	$sql = str_replace("''", 'null', $sql);
	$sql = $sql . "');";

	$result = mysqli_query($con_remota , $sql);
	
	if ( false===$result ) {
		  printf("error: %s\n", mysqli_error($con_remota));
		}

	 $sql="";
}

Select allOpen in new window

quite nifty but i see 2 issues there :

1) you are not using either var_export or mysql_realescapestring which is unsafe if the data is submitted by users : they can quite easily mess up your whole database. in this case, if the string contains a quote, the code will break entirely. you might want to replace the implode with a safer version using a foreach or at least add something like :

$server_campaigns_Array[$i] = array_map('addslashes', $server_campaigns_Array[$i]);

which will allow quoted strings to work properly

2) you are inserting many rows one by one which is much less efficient than building a single insert query with multiple rows : try something like this :

$sql="INSERT INTO  server_campaigns VALUES ";

foreach($server_campaigns_Array as $key => $item)
{
	$i++;
	
	$sql.="(";

        // existing code here without adding the ';' or running the query

       $sql.="),";

}

$sql[strlen($sql)]=';' // change the trailing ',' into a ';'

$result = mysqli_query($con_remota , $sql)
or printf("error: %s\n", mysqli_error($con_remota));

Select allOpen in new window

--

as a side note, $server_campaigns_Array[$i] is the same as $item. you should use $item and can skip the existence of $i altogether

Sorry the delay. I returned back few minutes later and I'm going to check it by now!

What I finally did following your steps:

$sql='';

foreach($server_campaigns_Array as $key => $item)
{
	$item = array_map('addslashes', $item);
	$sql.="INSERT INTO server_campaigns VALUES ('";
	$sql.= implode("','",$item); 
	$sql.= "'); ";
}

$sql = str_replace("''", 'null', $sql);


print_r($sql);
die;

Select allOpen in new window

The sql code is perfectly formed to run in IDE, unfortunatelly it causes an obscure syntax error when running from inside PHP.

SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO server_campaigns VALUES ('3','CC01 - PF','0005','0','0','5','0',null' at line 1

Select allOpen in new window

But even optimizing the INSERT code like you suggested, running the sql in MySQL IDE  the time is too high for our purposes, at about 13sec, just for one table!  

So another strategy, DB replication will be needed....

Hi

I saw it before.....

I guess this   "(...),(...),(...)"    was due some mistake!

What I tryed was in the same string:

$sql = "INSERT INTO tab  VALUES (....);  INSERT INTO tab  VALUES (....); INSERT INTO tab  VALUES (....);"

You are meaning the correct way is:

$sql = "INSERT INTO tab  VALUES (....), (....),(....), (....),"

Isn't it?

I'm writing entire tables to replicate data from one MySQL server (Master) to other MySQL servers (slaves) since all the servers (master and slaves) must have the same configuration tables contents.

Isn't it?

yes that's exactly the point. not much of a difference  : you essentially need to stick the beginning of the query before the foreach. the only tricky part is handling commas between the parenthesis.

I'm writing entire tables to replicate data from one MySQL server (Master) to other MySQL servers (slaves) since all the servers (master and slaves) must have the same configuration tables contents.

many solutions available and the best one depends on volumetry, how fast you want it to converge, the number of servers, their locations ( same place or replicating over the internet ? ). replication is workable. mariadb cluster should be even better. your own way also is probably feasible if done with care : i've build a similar tool years ago and it still does the job ( 10 years and around 1000 billion lines replicated at least )...

Really

Performance is a need in this case.

If possible could you give a look at this other question in the same subject?

https://www.experts-exchange.com/questions/29004698/Could-you-point-a-way-to-completelly-copy-a-table-from-one-database-to-another-table-that-belongs-to-another-database-but-in-different-servers-by-using-PHP.html

In the 4th comment I expose what I'm intending to do.

The volumetry is low - 03 configuration tables. I'm going to confirm but the servers are closer same network, doesn't replicate in internet.

i'll have a look tomorrow. it's midnight here...

OK!

Thank you for the interest ;)

3 tables does not give any actual indication regarding volumetry. how big are those tables ? what would be the volume of change ?

additionally we'd need to determine what is slow : are you sure inserting into an empty table lasts 13 seconds ? maybe pulling the data accounts for part of that ? given the fact the insertion fits in a single query, i hardly believe the SQL server is the limitation : running mysql in qemu netsted in virtualbox on my laptop would probably produce better results... maybe the site and the server are in 2 separate places over a slow network...

replication would produce real time streaming of config files which might be better but apparently is not what you want. i'll assume you want to replication to be manually triggered by the user and have a reason to do so. i also assume federated storage is not an option.

- you probably do not want to truncate the tables BEFORE writing them : if you write to different tmp tables, you can rename all 3 tables in one single operation so switching the tables won't leave the app unusable in the meantime

- if your script runs on the sql server, you might rather use load data infile

- if you expect the tables to grow bigger, you need to chunk out the insertions because you'll exceed the max_packet_size at some point

- can't you work with incremental changes ? if you only add/modify lines this is trivial. if you need to handle deletions, it might be slightly more complex.

i'll tag along but i believe you had better ask a question describing what you actually need. i need a button that does that is possibly not what you need. what you need is probably something like "i need to stream config tables form one server to multiple others. the other servers are on the same location and can access the master over a regular sql connection... my script sits on a separate server ( farm ) in a different location.  i have 3 config files and i need the changes to be consistent ( maybe writing/replicating in a specific order removes that need ? ). the volume of changes would be x lines / about so many ko per day ...." ... this is the kind of questions we would ask ourselves before building such an app...

i'm also wondering why 13s is too slow for you... ? if it's about locking the tables see above you can circumvent that trivially with renames.

Hi @skullnobrains


The 03 tables are only used to configurations so the bigger one have at about 150 lines and  109 columns.

By using the strategy you pointed:

$sql = "INSERT INTO tab  VALUES (....), (....),(....), (....);" the time decreased to less than a second for the biggest table, that must feet my needs so I'm going to use it !!!


The tables are used when PHP site starts running and then, when necessary,  it could be truncated and repopulated on the fly. A flag points to the site that the configuration tables had been changed and must to be rereaded to reconfigure the app.

The use of it is in a call-center charge redistribution.
201110-a-french-wine-chateau-de-pez.jpg

Just to complete information. All servers are local network.

The complete operation is about 5 to 7 seconds.

DateInterval Object
(
    [y] => 0
    [m] => 0
    [d] => 0
    [h] => 0
    [i] => 0
    [s] => 6
    [weekday] => 0
    [weekday_behavior] => 0
    [first_last_day_of] => 0
    [invert] => 0
    [days] => 0
    [special_type] => 0
    [special_amount] => 0
    [have_weekday_relative] => 0
    [have_special_relative] => 0
)

Select allOpen in new window

Problem is solved with your guidance.

Thank you very much for this outstanding assistance!

ok, i think i get it.

if all your machines are in the same location, i'd very strongly suggest you use a mariadb cluster which is easy to manage and will replicate your config tables on the fly.

if you keep doing this in php ( which seems reasonable for small config files ), you probably should alter your algorithm a little by inserting into temporary tables and renaming the tables :
- drop table tbl_A_new if exists
- create table tbl_A_new like tbl_A
- insert into tbl_A_new ...
- rename tbl_A to tbl_A_old , tbl_A_new to tbl_A
- drop table tbl_A_old
that way there is zero time when your config tables are empty. you can rename all 3 tables at once so the config tables stay in consistent state as well.

btw, i'll probably be around that castle some time soon ;) ... they're in the good parts of st estephe and their wine ain't bad... grab a few bottles if you can 2009 should be a little better than 2008 and 2005 much better... and the 3 of them should keep a little more

Thank you for the bonus on how to manage the tables!

What a coincidence you will be around this Castle (represented in the wine's label)!
I guess Bordeaux wine (one of the best)

i'm not too far anyway : i live in paris and i have an old client i need to meet in clermont ferrand... even though i'm not really into bordeau wine, i like that part and the wine they make there which is probably the only part of the bordeau that is actually historically fit for vineyards ( the rest were mostly swamps that where dried out and planted with grapes )... so when i get a chance... see you around. happy coding

Thank you!

Last year, some friends of mine visited Paris and liked it too much.

Just out of curiosity here in São Paulo we have the French Night, in a partially closed street party a way to remember Allan Kardec.

feel free to send a private message if you happen to be near paris... i'll be happy to grab a drink and possibly show you around if i'm not too busy at that time... btw, i'm pretty sure not a french out of 1000 knows about Kardec's existence ;) and i'm quite astonished he would be associated with a french night abroad... i'm always astonished the french are so famous for so many things they are not ! ... see you

Thank you for the offer! I'm very sensibilized with!

Yes. In France the Spiritist Movement practically disappeared!
In Europe it never came. In fact, it only thrived in Brazil.
Kardec was wrong when he predicted that in 02 generations Spiritism would win the World and
this would renew society ... But it's not his fault. I believe that mankind has not yet
be prepared for Spiritism. Its principles remain revolutionary even
to the present day.
And in Brazil it reached the dimension that has thanks mainly to the phenomenon Francisco
Cândido Xavier ... (the greatest Brazilian of all time according to extensive research
held in a large Brazilian television network) that was also a wide disseminator of Kardec.