Array in mysql

marcelotguedes

how to get the result of the array and write to mysql?

<?php

$tabela = "test";

$db = @mysql_connect ("localhost", "root", "root");
mysql_select_db("dbtest",$db);
$arr = file( 'prod.txt' );
foreach( $arr as $v )
{


$v = preg_replace( '!\s+!', ' ', $v );


if( substr( $v, 0, 1 ) == '*' )
{
   $lote = $y = substr( $v,1, 4 ); // o lote
    $rs[$y] = array(); 
}else{

$ean = $rs[$y][]['ean'] = substr( $v, 0, 13 );
$qtd = $rs[$y][]['qtd'] = trim( substr( $v, 14 ) );    }
}


 $result=mysql_query(" INSERT INTO test(tab1,tab2,tab3) VALUES ( '$lote','$ean','$qtd' ) " );
  echo "<pre>";
print_r( $rs );
  echo "<pre>";

?>

traq

Each item in the array would need to be an individual record. Arrays don't exist in relational databases.

<?php 

$tabela = "test"; 

$db = @mysql_connect ("localhost", "root", "root"); 
mysql_select_db("dbtest",$db); 
$arr = file( 'prod.txt' ); 
foreach( $arr as $v ){ 
    $v = preg_replace( '!\s+!', ' ', $v ); 

// make sure all of your vars are defined,
// and none get re-used from previous iterations
list( $lote,$ean,$qtd ) = null;

// you need to sanitize your input data (see below)).
// otherwise, you're at-risk risk for SQL injection attacks and/or simple errors.

if( substr( $v, 0, 1 ) == '*' ) { 
    $lote = $y = mysql_real_escape_string( substr( $v,1, 4 ) ); 
    $rs[$y] = array();  
}else{ 
    $ean = $rs[$y][]['ean'] = mysql_real_escape_string( substr( $v, 0, 13 ) ); 
    $qtd = $rs[$y][]['qtd'] = mysql_real_escape_string( trim( substr( $v, 14 ) ) );    
}

// write a new set a values for your query
$values[] = "('$lote','$ean','$qtd')";
} 

// use implode to combine the sets of values for your query
 $result=mysql_query(" INSERT INTO test(tab1,tab2,tab3) VALUES ".implode( ",",$values ) );

# Also, if at all possible, you should avoid using the mysql_* functions. #
Existing code should be updated to avoid performance and security problems.

php.net (official PHP website) wrote:
Warning

This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future.
Instead, either the mysqli or PDO_MySQL extension should be used.
See also the MySQL API Overview for further help while choosing a MySQL API.

marcelotguedes

[2001] => Array
(
[0] => Array
(
[ean] => 7894900010015
)

        [1] => Array
            (
                [qtd] => 150
            )

        [2] => Array
            (
                [ean] => 7894900010020
            )

        [3] => Array
            (
                [qtd] => 300
            )

        [4] => Array
            (
                [ean] => 7899400500465
            )

        [5] => Array
            (
                [qtd] => 050
            )

        [6] => Array
            (
                [ean] => 7894643812312
            )

        [7] => Array
            (
                [qtd] => 100
            )
    )
[2002] => Array
    (
        [0] => Array
            (
                [ean] => 7894900016156
            )
        [1] => Array
            (
                [qtd] => 035
            )
        [2] => Array
            (
                [ean] => 7894881561861
            )
        [3] => Array
            (
                [qtd] => 011
            )
        [4] => Array
            (
                [ean] => 7894860450002
            )
        [5] => Array
            (
                [qtd] => 001
            )
    )
[2003] => Array
    (
        [0] => Array
            (
                [ean] => 7894868153183
            )
        [1] => Array
            (
                [qtd] => 335
            )
        [2] => Array
            (
                [ean] => 7898456484411
            )
        [3] => Array
            (
                [qtd] => 200
            )
    )
[2004] => Array
    (
        [0] => Array
            (
                [ean] => 7894868153001
            )
        [1] => Array
            (
                [qtd] => 355
            )
        [2] => Array
            (
                [ean] => 7898456484002
            )
        [3] => Array
            (
                [qtd] => 211
            )
    )

this is the result of the array, and would like to enter this information in mysql, where 2001, 2002, 2003 and 2004 are recorded in column and batch ean ean the qty column and column number, because with this code only writes 1 line array

traq

[2001] => Array(
    [0] => Array(
        [ean] => 7894900010015
    )
    [1] => Array(
        [qtd] => 150
    )
    [2] => Array(
        [ean] => 7894900010020
    )
    [3] => Array(
        [qtd] => 300
    )
    [4] => Array(
        [ean] => 7899400500465
    )
    [5] => Array(
        [qtd] => 050
    )
    [6] => Array(
        [ean] => 7894643812312
    )
    [7] => Array(
        [qtd] => 100
    )
)

Correct?

I don't really see how this relates to the code you originally posted.

For example, you're using "$arr as $v", where $v would end up being the second-level array. But you're treating is as strings.

Then, assuming you did get the strings you wanted, you're trying to take substrings that are bigger than the original strings are.

Also, now that I'm looking more closely, I see you doing things like [font=monospace]$ean = $rs[$y][]['ean'][/font], which would result in a fatal error (because you can't use [] for reading).

So, to see if I understand correctly: taking the above portion of the array as an example, do you want your DB records to look something like this?

+------+------+---------------+-----+
| year | lote |      ean      | qtd |
+------+------+---------------+-----+
| 2001 |      | 7894900010015 | 150 |
| 2001 |      | 7894900010020 | 300 |
| 2001 |      | 7899400500465 | 050 |
| 2001 |      | 7894643812312 | 100 |
+------+------+---------------+-----+

ALSO (important!), is the array always in this exact format (i.e., an "ean" followed by a "qtd")? do the eans and qtds belong together? what's the "lote" in your original code, and where does it come from?

Forgive all the questions, but this seems like a fragile data structure. It would be difficult to correctly parse it without knowing all the rules.

marcelotguedes

this extrura the Traq posted is almost certain:

+------+------+---------------+-----+
| year | lote | ean | qtd |
+------+------+---------------+-----+
| 2001 |2001| 7894900010015 | 150 |
| 2001 |2001| 7894900010020 | 300 |
| 2001 |2001| 7899400500465 | 050 |
| 2001 |2001| 7894643812312 | 100 |
+------+------+---------------+-----+

but the "year" column does not exist but the column "area" records 2001, 2002 ... will be recorded in the column "lote"

file.txt

my initial post the code this matter. txt file, all code displays the array as posted before and works now want this array to be written to mysql

2001
7894900010015 150
7894900010020 300
7899400500465 050
7894643812312 100
2002
7894900016156 035
7894881561861 011
7894860450002 001
2003
7894868153183 335
7898456484411 200
2004
7894868153001 355
7898456484002 211

Thank you and any questions just post

traq

Is this the actual contents of your file?

*2001
7894900010015 150
7894900010020 300
7899400500465 050
7894643812312 100
*2002
7894900016156 035
7894881561861 011
7894860450002 001
*2003
7894868153183 335
7898456484411 200
*2004
7894868153001 355
7898456484002 211

IF SO, let's do this:

<?php
$file = file( "path/to/your/file",FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES );

…which results in an array like so:

array(15) {
  [0]=>
  string(5) "*2001"
  [1]=>
  string(17) "7894900010015 150"
  [2]=>
  string(17) "7894900010020 300"
  [3]=>
  string(17) "7899400500465 050"
  [4]=>
  string(17) "7894643812312 100"
  [5]=>
  string(5) "*2002"
  [6]=>
  string(17) "7894900016156 035"
  [7]=>
  string(17) "7894881561861 011"
  [8]=>
  string(17) "7894860450002 001"
  [9]=>
  string(5) "*2003"
  [10]=>
  string(17) "7894868153183 335"
  [11]=>
  string(17) "7898456484411 200"
  [12]=>
  string(5) "*2004"
  [13]=>
  string(17) "7894868153001 355"
  [14]=>
  string(17) "7898456484002 211"
}

Then, we can parse it one line at a time:

<?php

foreach( $file as $line ){

// "*" indicates a new year
if( strpos( $line,"*" ) === 0 ){
    $lote = substr( $line,1,4 );
}
// otherwise, assume it's an "ean qtd" string.
// note; this assumes that the first line in the file is a "year" line.
// if it's not, you'll end up with records that have empty years
//  (as well as an "undefined variable" error).
else{
    // this will fail if you ever have a line that is improperly formatted
    //  (e.g., no space / extra spaces).
    list( $ean,$qtd ) = explode( " ",$line );

    // put values into a set for SQL query
    // this also assumes that all data in your file is SAFE.
    // if not, you'll need to escape these values before now.
    $values[] = "('$lote','$ean','$qtd')";
}
}
// combine all value-sets for SQL query
$SQL = "INSERT INTO your_table(lote,ean,qtd) VALUES\n  ".implode( ",\n  ",$values );

Which results in this SQL string:

INSERT INTO your_table(lote,ean,qtd) VALUES
  ('2001','7894900010015','150'),
  ('2001','7894900010020','300'),
  ('2001','7899400500465','050'),
  ('2001','7894643812312','100'),
  ('2002','7894900016156','035'),
  ('2002','7894881561861','011'),
  ('2002','7894860450002','001'),
  ('2003','7894868153183','335'),
  ('2003','7898456484411','200'),
  ('2004','7894868153001','355'),
  ('2004','7898456484002','211')

marcelotguedes

<?php

$tabela = "teste";

$db = @mysql_connect ("localhost", "root", "root");
mysql_select_db("visitas",$db);
$file = file( "prod.txt",FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES );
foreach( $file as $line ){ 

// "*" indicates a new year 
if( strpos( $line,"*" ) === 0 ){ 
    $lote = substr( $line,1,4 ); 
} 
// otherwise, assume it's an "ean qtd" string. 
// note; this assumes that the first line in the file is a "year" line. 
// if it's not, you'll end up with records that have empty years 
//  (as well as an "undefined variable" error). 
else{ 
    // this will fail if you ever have a line that is improperly formatted 
    //  (e.g., no space / extra spaces). 
    list( $ean,$quantidade ) = explode( " ",$line ); 

    // put values into a set for SQL query 
    // this also assumes that all data in your file is SAFE. 
    // if not, you'll need to escape these values before now. 
    $values[] = "('$lote','$ean','$quantidade')"; 
} 
} 
// combine all value-sets for SQL query 
$SQL = "INSERT INTO teste(lote,ean,quantidade) VALUES\n  ".implode( ",\n  ",$values );


?>

can tell me if there is any mistake in the code because it is not recording in mysql

thank you

traq

You're not running the query.

marcelotguedes

<?php
$link = @mysql_connect('localhost', 'root', 'root');
if (!$link) {
die('Não foi possível conectar: ' . mysql_error());
}
mysql_select_db('visitas');
$file = file( "prod.txt",FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES );
foreach( $file as $line ){

// "*" indicates a new year 
if( strpos( $line,"*" ) === 0 ){ 
    $lote = substr( $line,1,4 ); 
} 
// otherwise, assume it's an "ean qtd" string. 
// note; this assumes that the first line in the file is a "year" line. 
// if it's not, you'll end up with records that have empty years 
//  (as well as an "undefined variable" error). 
else{ 
    // this will fail if you ever have a line that is improperly formatted 
    //  (e.g., no space / extra spaces). 
    list( $ean,$quantidade ) = explode( " ",$line ); 

    // put values into a set for SQL query 
    // this also assumes that all data in your file is SAFE. 
    // if not, you'll need to escape these values before now. 
    $values[] = "('$lote','$ean','$quantidade')"; 
}

}
// combine all value-sets for SQL query
// Connection data (server_address, database, name, poassword)

// Define an insert query
echo 'Conexão bem sucedida';

@("INSERT INTO teste (id,lote,ean,quantidade) values \n ".implode( ",\n ",$values ));
printf("Last inserted record has id %d\n", mysql_insert_id());

$res = mysql_query("select * from teste");

while($escrever = mysql_fetch_array($res)){

echo "<table><tr><td>Lote</td><td>Ean</td><td>Quantidade</td></tr>";
/Escreve cada linha da tabela/
echo "<tr><td>" . $escrever['lote'] . "</td><td>" . $escrever['ean'] . "</td><td>" . $escrever['quantidade'] . "</td></tr>";

}/Fim do while/

echo "</table>"; /fecha a tabela apos termino de impressão das linhas/

mysql_close($link);
?>

traq

@mysql_query("INSERT INTO teste (id,lote,ean,quantidade) values \n ".implode( ",\n ",$values ));

Advice: never ignore errors. The @ operator is for suppressing errors that you specifically intend to handle yourself. In almost all cases, it simply leads to sloppy code and confusing failures. Especially during development, you should always want to see every error, so you can fix it.

In this case, you'll be getting an error from MySQL along the lines of "column count doesn't match value count" because you're specifying four column names but sets of only three values.