combine data values from single table into newer values in the same table

[berserk]

Table: numdata

id  numb
1   123
2   456789
3   123
4   456789
5   123
6   456789
7   123
8   456789
9   123
10  456789
11  123
12  456789
13  123
14  456789
......

What i would like to do is combine the data in numb only, so the desired data output would be something like this.

    id  numb
    1   123456789
    2   123456789
    3   123456789
    4   123456789
    5   123456789
    6   123456789
    7   123456789
    8   123456789
    9   123456789
    10  123456789
    11  123456789
    12  123456789
    ....

I have about 1000 values to do like this and they are all different.
I have honestly no idea how to even go about this so any help would be greatly appreciated, thanks in advance.

[diafol]

If your id is a primary key, it may be easier to create a new table, delete the old and rename. BTW - is this a php or a DB question. If the latter, you've posted to the wrong forum. [It can be moved if you require]

[berserk]

well this is a little of both if that makes any sense, its a php question because i wanted to know if the solution was doable with php but also a mysql question because im doing this all to a database. If im posting in the wrong place then by all means please change the location :).
Lastly all i really need to do is just combine those values into single values and put them back into the same table, just rewriting the existing values that are there, if this is possible that is.

[diafol]

This is straightforward in php, but a little more involved with MySQL. Will this be a regular thing or a one-off?

[diafol]

Here's an untested php script:

<?php
$dsn = 'mysql:dbname=daniweb;host=localhost';
$user = 'root';
$password = '...';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$stmt = $dbh->query("SELECT numb FROM table");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$counter = 1;
$insertArray = array();
foreach($rows as $row)
{
    $numb = $row['numb'];
    if($counter % 2)
    { 
        $complete = $numb;

    }else{
        $complete .= $numb;
        $insertArray[] = $complete; 
    }
    $counter++; 
}

$insertString = "(" . implode("),(", $insertArray) . ")";

$sql = "INSERT INTO newtable (`numb`) VALUES $insertString";
$stmt = $dbh->query($sql);
$stmt->execute();
echo $stmt->rowCount() . " rows in the new table were created.";

I reckon something like that. Obviously create a new table with the same structure as the original, before attempting this and change the table names in the statements to whatever you use.

[berserk]

well i was actually thinking of that very idea, but i cant create a new table for this. I need to be able to take the values put them together and then rewrite the whole table over.

<?php

    require ('dbconnect.php'); // connect to database

    // initial database stuff

        $file = $_FILES["file"]["name"];
        $file_handle = fopen($file, "r");

        mysql_query("TRUNCATE TABLE numdata") or die("MySQL Error: " . mysql_error()); //Delete the existing rows
        while (($data = fgetcsv($file_handle, 0, ",")) !== FALSE)
        {
            foreach($data as $row)
            {
                $query="INSERT into numdata(numb) values($row)";
                mysql_query($query) or die(mysql_error());
                set_time_limit(0);
            }
        }

fclose($file_handle);


echo "Done!";         

    // Form to upload a new file
    echo "
         <form enctype='multipart/form-data' name='fileupload' action='upload.php' method='POST'>
            <label for= 'file'> Filename: </label>
            <input type='file' name='file' id= 'file'>
            <input type='submit' name='submit' value='submit'>
        </form>

    ";
?>

this is what my code looks like now, it just adds the data from each comma seperated value and throws it into the database, what i would like to do is maybe read the values from 123 and then just haveing the program read the next value 456789 into the same field with 123 so that they are together and not a seperate value in the database.

Also ty so much for the assistence, im going to keep the code you answered with because im sure i will be able to use that for reference later on, it feels wonderful to get this level of assistence :) thank you again.

[diafol]

ah ok. so the issue is with the format of the csv file rather than with the data already in the db. is that right?

[berserk]

yes that my main issue, i figured the problem wasnt very big but i have no idea how to go about fixing this issue. also sorry for the late responce, chrome messed up and wont let me log into this site anymore! its just not my week :/

[diafol]

he heh, I'm off to work now and don't know if i'll be able to help till tonight/early tomorrow.

[diafol]

Another untested script...

<?php
$dsn = 'mysql:dbname=daniweb;host=localhost';
$user = 'root';
$password = '...';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$counter = 1;
$insertArray = array();

$file = fopen('myfile.csv', 'r'); //change filename to suit you
while (($line = fgetcsv($file)) !== FALSE) {
    $numb = $line[1]; //change this to the array position for numb
    if($counter % 2)
    { 
        $complete = $numb;
    }else{
        $complete .= $numb;
        $insertArray[] = $complete; 
    }
    $counter++; 
}
fclose($file);

$insertString = "(" . implode("),(", $insertArray) . ")";

$sql = "INSERT INTO mytable (`numb`) VALUES $insertString"; //change mytable to your tablename
$stmt = $dbh->query($sql);
$stmt->execute();
echo $stmt->rowCount() . " rows in the new table were created.";

[berserk]

i see how u set up that file, unfortunatly i can skip these numbers i need it to be one whole number

example:
123
456789
becomes
123456789

is there any way i could skip the comma and use some other delimiter?

[berserk]

OKAY! so i am so very close to the desired output thanks to your scripts, i took some of what you did and amde it work with mine but im getting some extra values. here is my script

<?php

    require ('dbconnect.php'); // connect to database

    // initial database stuff

        $file = $_FILES["file"]["name"];
        $file_handle = fopen($file, "r");

        $count= 1;
        mysql_query("TRUNCATE TABLE numdata") or die("MySQL Error: " . mysql_error()); //Delete the existing rows


        while (($data = fgetcsv($file_handle, 0, ",")) !== FALSE)
        {
            $row = $line[1]; //starting position
            foreach($data as $row)
            {
                if($count % 2)
{
                    $complete =  $row;
                }else{
                    $complete .= $row;
                    $insertArray[] = $complete;
                }
                $count++; 
                $query="INSERT INTO numdata(numb) values($complete)";
                mysql_query($query) or die(mysql_error());
                set_time_limit(0);
            }
        }

fclose($file_handle);


echo "Done!";         

    // Form to upload a new file
    echo "
         <form enctype='multipart/form-data' name='fileupload' action='newup.php' method='POST'>
            <label for= 'file'> Filename: </label>
            <input type='file' name='file' id= 'file'>
            <input type='submit' name='submit' value='submit'>
        </form>

    ";
?>

the ouput before was:
123
456789
123
456789
...

NOW the output is:
123
123456789
123
123456789
123
123456789
123
123456789

is there any way i can skip the first entry? when i tried to elimnate it it gave me this error.

Column count doesn't match value count at row 1

ALMOST THERE lol

[diafol]

You have a double loop. while and foreach - I don't think you need that.

[berserk]

i tried taking one loop out but im still getting this:

Column count doesn't match value count at row 1

[diafol]

How many fields do you have per line? If only one, then as the line array zero-based, you many need to use this...

$row = $line[0]; //starting position

You don't show your csv data format, so we have no idea. Can you show a sample of the file contents?

[berserk]

sure thing

606,0000000
606,0160158
606,0173557
606,0262530
606,0561177
606,0654393
606,0843095
606,1305858
606,2000192
606,2000735
606,2000753
606,2000902
606,2001473
606,2001688
606,2002053
606,2002109
606,2005516
606,2011277
606,2015371
606,2015668
606,2017779
606,2020017
606,2020068
606,2020090
606,2020094
606,2020103
606,2020149
606,2020188
606,2020206
606,2020271
606,2020292
606,2020300
606,2020307
606,2020316
606,2020361
606,2020399
606,2020460
606,2020481
606,2020493
606,2020507
606,2020514
606,2020559
606,2020588
606,2020640
606,2020654
606,2020700
606,2020704
606,2020728
606,2020774
606,2020790
606,2020794
606,2020831
606,2020870
606,2020964
606,2020994
606,2021007
606,2021010
606,2021016
606,2021018
606,2021019
606,2021023
606,2021024
606,2021030
606,2021038
606,2021041
606,2021042
606,2021047
606,2021048
606,2021051
606,2021056
606,2021058
606,2021060
606,2021066
606,2021067
606,2021068
606,2021071
606,2021074
606,2021076
606,2021078
606,2021081
606,2021082
606,2021083
606,2021084
606,2021085
606,2021086
606,2021087
606,2021089
606,2021092
606,2021093
606,2021094
606,2021096
606,2021097
606,2021103
606,2021106
606,2021107
606,2021108
606,2021109
606,2021111
606,2021112
606,2021117
606,2021119
606,2021122
606,2021123
606,2021124
606,2021126
606,2021129
606,2021134
606,2021135
606,2021136
606,2021137
606,2021139
606,2021141
606,2021143
606,2021144
606,2021145
606,2021147
606,2021156
606,2021157
606,2021159
606,2021160
606,2021161
606,2021163
606,2021166
606,2021170
606,2021171
606,2021172
606,2021173
606,2021177
606,2021178
606,2021179
606,2021185
606,2021186
606,2021188
606,2021190
606,2021192
606,2021195
606,2021198
606,2021201
606,2021203
606,2021205
606,2021206
606,2021209
606,2021210
606,2021211
606,2021213
606,2021215
606,2021216
606,2021217
606,2021219
606,2021221
606,2021224
606,2021226
606,2021227
606,2021228
606,2021235
606,2021240

this is how the file looks, it reads into the database as this:

606
6069345942
606
6063455678
606
6067651234
...

[berserk]

lol OKAY i got it, i figured out why it was putting the extra 606 in there, i was having it add the query to the table twice, once for the 606 and then again for the whole number. i moved the mysql_query($query) or die(mysql_error()); outside the second loop and that did the trick.
THANK YOU SO MUCH for your assistence, i could not have done this with out you my friend. You have trully made this website that much more essential for me when it comes to asking for meaningful help to my programming problems!

thank you again! :D

[diafol]

no problem :)