[SOLVED] array shift

[VicToMeyeZR]

hi yall.  I have a question.  I am performing a database merge of a user table.  I have a function here, that pulls from the one table puts the values into an insert statement for the next table.

 

function mysql_multirow_copy($z,$toTable) {
    $fields = "";
    for ($i=0;$i<(mysql_num_fields($z));$i++) {
        if ($i>0) {
            $fields .= ", ";
        }
        $fields .= mysql_field_name($z,$i);
    }
    $q = "INSERT INTO $toTable ($fields) VALUES ";
    $c = 0;
    mysql_data_seek($z,0); //critical reset in case $z has been parsed beforehand. !
    while ($a = mysql_fetch_assoc($z)) {
        foreach ($a as $as) {
            $a[key($a)] = addslashes($as);
    if(!$as) { $a[key($a)] = ''; }
    $a['user_id'] = null;
    //print ($a[key($a)]);
            next ($a);
        }
        if ($c>0) {
            $q .= ", ";
        }
        $q .= "('".implode(array_values($a),"','")."')";
        $c++;
    }
    $q .= ";";
    print $q;
    mysql_query($q);
    return ($q);
} 

 

here is the problem.  the insert statement values count do not match the table.  I am forcing the user_id field to '' so the db can reassign as I merge more together, but for some reason the array of values has an extra count in the beginning.  I have 110 keys, and I keep getting 111 values.

 

Any ideas?

[btherl]

You can use var_dump() or print_r() to see the contents of the array.  Then you can see what your extra array element is.

 

I'm a bit suspicious about $a[key($a)] .. If that does what I think it does, you can do it more simply like this:

 

foreach ($a as $k => $as) { ... 

 

And $k will be they key of the current item.

[VicToMeyeZR]

You can use var_dump() or print_r() to see the contents of the array.  Then you can see what your extra array element is.

 

I'm a bit suspicious about $a[key($a)] .. If that does what I think it does, you can do it more simply like this:

 

foreach ($a as $k => $as) { ... 

 

And $k will be they key of the current item.

 

I will give your foreach a try.  The extra field was it generated 2 of the same field user_id..

 

[VicToMeyeZR]

ok, all those problems fixed, not my last one.

 

The first key field in the table is user_id, and its value is auto increment.  Now while its running this loop, I want it to make the value of the user_id field as blank or ''.

 

here is the code now:

function mysql_multirow_copy($z,$toTable) {
    $fields = "`";
    for ($i=0;$i<(mysql_num_fields($z));$i++) {
        if ($i>0) {
            $fields .= "`, `";
        }
        $fields .= mysql_field_name($z,$i);
    }
$fields .= "`";
    $q = "INSERT INTO `$toTable` ($fields) VALUES ";
    $c = 0;
    mysql_data_seek($z,0); //critical reset in case $z has been parsed beforehand. !
    while ($a = mysql_fetch_assoc($z)) {
        /*foreach ($a as $as) {
            $a[key($a)] = addslashes($as);
		//if(!$as) { $a[key($a)] = ''; }
		if ($a['user_id']) { $a['user_id'] = ''; }
            next ($a);
        }*/
	foreach ($a as $k => $as) {
            $k = addslashes($as);
        }
        if ($c > 0) {
            $q .= ", ";
        }
        $q .= "('".implode(array_values($a),"','")."')";
	$c++;
    }
    $q .= ";";
print $q;
    mysql_query($q);
    return ($q);
} 

 

thoughts?

[btherl]

The equivalent of

 

$a[key($a)] = addslashes($as);

 

is

 

$a[$k] = addslashes($as);

 

I think what you need is this:

 

    while ($a = mysql_fetch_assoc($z)) {
        if (array_key_exists('user_id', $a)) {
            $a['user_id'] = '';
        }
        foreach ($a as $k => $as) {
            $a[$k] = addslashes($as);
        }
        if ($c > 0) {
            $q .= ", ";
        }
        $q .= "('".implode(array_values($a),"','")."')";
	$c++;
    }

 

I've blanked the user_id value before doing the addslashes foreach.  You could put that inside the loop like this if you wanted:

 

if ($k == 'user_id') $a[$k] = '';

 

And I didn't edit any of your other code inside that loop - only the code that blanks user_id and calls addslashes().  BTW there is no need to use array_values() before calling implode().  You can pass $a directly.

[roopurt18]

It's been a while since I've used MySQL, but I seem to remember you can run queries across databases.

 

insert into `database1`.`users` ( `username`, `password` ) 
select `username`, `password` from `database2`.`users` where 1=1

 

You could try that out with two test tables.  Then you don't have to write all this silly code, just execute one sql statement.