Upload a text file into a mysql database
Solved
nguim
Posted messages
18
Status
Member
-
camile -
camile -
Hello everyone! I am a beginner in PHP and I have a problem in this language which is as follows: in my project, I am coding in PHP, I have a text file that I need to read each line containing 6 words each, and store these 6 words respectively in 6 columns of a table in my Database (MySQL). This is for all lines (more than 100,000 lines). Here is an excerpt from this file.
( IM ME ES PU AK MD)
624030743830000 A100001D7DEE6D 80E5FA73 37312379 0E47EA7A6F444640 33830000
624030743830001 A100001D7DEE6E 80AE089F 91944192 F9052B04E3969977 33830001
624030743830002 A100001D7DEE6F 809A1A9F 29861661 4617481A74D864B5 33830002
624030743830003 A100001D7DEE70 80E68950 20303707 1C45F0418B552C45 33830003
624030743830004 A100001D7DEE71 80C275C6 70591541 EA26D0523114FFF2 33830004
624030743830005 A100001D7DEE72 80932253 44181182 1384BC94E9D33B9C 33830005
624030743830006 A100001D7DEE73 8035029C 38663053 1225153351556433 33830006
624030743830007 A100001D7DEE74 80CB3B71 64591576 3C96BB2772B43D85 33830007
624030743830008 A100001D7DEE75 800B3616 06726100 E33622230DA94ABF 33830008
here is an excerpt of the content of my text file. Now I need to load it into a
table (named: coord) in my database (named: gestnu), table structure:
column 1 -> IM, 2 -> ME, 3 -> ES, 4 -> PU, 5 -> AK, 6 -> MD
Please help me, I am counting a lot on your assistance as my project is on hold
now because of this problem.
Thank you in advance.
( IM ME ES PU AK MD)
624030743830000 A100001D7DEE6D 80E5FA73 37312379 0E47EA7A6F444640 33830000
624030743830001 A100001D7DEE6E 80AE089F 91944192 F9052B04E3969977 33830001
624030743830002 A100001D7DEE6F 809A1A9F 29861661 4617481A74D864B5 33830002
624030743830003 A100001D7DEE70 80E68950 20303707 1C45F0418B552C45 33830003
624030743830004 A100001D7DEE71 80C275C6 70591541 EA26D0523114FFF2 33830004
624030743830005 A100001D7DEE72 80932253 44181182 1384BC94E9D33B9C 33830005
624030743830006 A100001D7DEE73 8035029C 38663053 1225153351556433 33830006
624030743830007 A100001D7DEE74 80CB3B71 64591576 3C96BB2772B43D85 33830007
624030743830008 A100001D7DEE75 800B3616 06726100 E33622230DA94ABF 33830008
here is an excerpt of the content of my text file. Now I need to load it into a
table (named: coord) in my database (named: gestnu), table structure:
column 1 -> IM, 2 -> ME, 3 -> ES, 4 -> PU, 5 -> AK, 6 -> MD
Please help me, I am counting a lot on your assistance as my project is on hold
now because of this problem.
Thank you in advance.
23 answers
- 1
- 2
Next
( IM ME ES PU AK MD)
624030743830000 A100001D7DEE6D 80E5FA73 37312379 0E47EA7A6F444640 33830000
624030743830001 A100001D7DEE6E 80AE089F 91944192 F9052B04E3969977 33830001
624030743830002 A100001D7DEE6F 809A1A9F 29861661 4617481A74D864B5 33830002
624030743830003 A100001D7DEE70 80E68950 20303707 1C45F0418B552C45 33830003
624030743830004 A100001D7DEE71 80C275C6 70591541 EA26D0523114FFF2 33830004
624030743830005 A100001D7DEE72 80932253 44181182 1384BC94E9D33B9C 33830005
624030743830006 A100001D7DEE73 8035029C 38663053 1225153351556433 33830006
624030743830007 A100001D7DEE74 80CB3B71 64591576 3C96BB2772B43D85 33830007
624030743830008 A100001D7DEE75 800B3616 06726100 E33622230DA94ABF 33830008
ou à ça :
624030743830000 A100001D7DEE6D 80E5FA73 37312379 0E47EA7A6F444640 33830000
624030743830001 A100001D7DEE6E 80AE089F 91944192 F9052B04E3969977 33830001
624030743830002 A100001D7DEE6F 809A1A9F 29861661 4617481A74D864B5 33830002
624030743830003 A100001D7DEE70 80E68950 20303707 1C45F0418B552C45 33830003
624030743830004 A100001D7DEE71 80C275C6 70591541 EA26D0523114FFF2 33830004
624030743830005 A100001D7DEE72 80932253 44181182 1384BC94E9D33B9C 33830005
624030743830006 A100001D7DEE73 8035029C 38663053 1225153351556433 33830006
624030743830007 A100001D7DEE74 80CB3B71 64591576 3C96BB2772B43D85 33830007
624030743830008 A100001D7DEE75 800B3616 06726100 E33622230DA94ABF 33830008
en gros là, il n'arrive pas à lire ta première ligne (surement parce que soit elle est vide, soit ne correspond pas à ce qu'il attend.
As-tu changer le nom du fichier par le tiens ? moi dans le code que je t'ai fournis il s'intitule text.txt mais le tiens a surement un autre nom...
je pourrai difficilement faire mieux à présent, j'ai repris tout ton code source de départ, ta base de données etc...je ne vois pas où sa pourrai planté là si ce n'est pas ces deux chose !
verifie bien que ton fichier commence directement par
624030743830000 A100001D7DEE6D 80E5FA73 37312379 0E47EA7A6F444640
et absolument rien d'autre (pas même un espace, un saut de ligne ou autre...)
et verifie surtout que tu as bien mis le nom de ton fichier (et éventuellement son arborescence...
--
un "lien" vaut mieux que deux tu l'auras...
Profile blocked
In the worst case, send me your files (php, txt and your structure) in a private message so I can check it because there’s no reason on my side everything works perfectly...
Hello,
at first glance it looks like CSV format, why don't you use the fgetscsv function from PHP?
or why not use file_get_contents("your_file");
you get everything as a string, and you truncate at each space.
The output will be an array (corresponding to the 6 from each line)
then you fiddle with a foreach to get the first 6, you insert into the database and start again for the next line...
But I think fgetscsv will do the job much better... I let you check out the function online, you'll love it...
good luck,
don’t hesitate to give us feedback...
--
a "link" is worth more than two you will have...
at first glance it looks like CSV format, why don't you use the fgetscsv function from PHP?
or why not use file_get_contents("your_file");
you get everything as a string, and you truncate at each space.
The output will be an array (corresponding to the 6 from each line)
then you fiddle with a foreach to get the first 6, you insert into the database and start again for the next line...
But I think fgetscsv will do the job much better... I let you check out the function online, you'll love it...
good luck,
don’t hesitate to give us feedback...
--
a "link" is worth more than two you will have...
Voici mon code:
et cela m'affiche l'erreur suivante:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '624030743830000 A100001D7DEE6D 80E5FA73 37312379 0E47EA7A6F444640 33830000' at line 1.
Je suis bloqué
Aidez-moi SVP !
<?php mysql_connect('[/contents/523-adresse-ip localhost]','[/contents/646-linux-gestion-des-utilisateurs root]',''); mysql_select_db('gestnum'); $file = 'fichab.txt'; $taille = 99999; $delimiteur = "\r"; if($fp = fopen($file,"r")) { mysql_query("DELETE FROM coordab"); while ($ligne = fgets($fp, $taille)) { $table = explode($delimiteur, $ligne); foreach($table as $elem) { $delimiteur_champ = "\t"; $champs = explode($delimiteur_champ, $elem); mysql_query("INSERT INTO coordab (ida, IMSI, MEID, ESN1, PUK1, AKY, MDN) VALUES ('".implode("','", $champs)."')") or die(mysql_error()); } } fclose ($fp); } ?> et cela m'affiche l'erreur suivante:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '624030743830000 A100001D7DEE6D 80E5FA73 37312379 0E47EA7A6F444640 33830000' at line 1.
Je suis bloqué
Aidez-moi SVP !
Why are you using implode?
I think that's what's messing up your code...
You state that you want to input fields A, B, C, D, etc., but you are only really passing parameter A; it expects the rest.
Try doing a var_dump($champ) to see how it behaves (to check what your array actually contains...
Then work on it, but I'm almost certain it's your implode that's causing the mess...^^
You're almost there, keep going...
--
A "link" is worth more than two you will have...
I think that's what's messing up your code...
You state that you want to input fields A, B, C, D, etc., but you are only really passing parameter A; it expects the rest.
Try doing a var_dump($champ) to see how it behaves (to check what your array actually contains...
Then work on it, but I'm almost certain it's your implode that's causing the mess...^^
You're almost there, keep going...
--
A "link" is worth more than two you will have...
Thank you for everything! Please don't get tired of helping me, here's what you asked me to do.
Here is the result:
array (size=6)
0 => string '624030743830000' (length=15)
1 => string 'A100001D7DEE6D' (length=14)
2 => string '80E5FA73' (length=8)
3 => string '37312379' (length=8)
4 => string '0E47EA7A6F444640' (length=16)
5 => string '33830000;' (length=9)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
Now I don't know what to do, remember what I told you from the beginning. I am a beginner in PHP and this is my very first project in PHP. Help me.
mysql_connect('localhost','root',''); mysql_select_db('gestnum'); $file = 'fichab.txt'; $taille = 99999; $delimiteur = "\r"; if($fp = fopen($file,"r")) { mysql_query("TRUNCATE TABLE coordab"); while ($ligne = fgets($fp, $taille)) { $table = explode($delimiteur, $ligne); $delimiteur_champ = "\x20"; foreach($table as $elem) { $champs = explode($delimiteur_champ, $elem); mysql_query("INSERT INTO coordab (ida,IMSI, MEID, ESN1, PUK1, AKY, MDN) VALUES ".var_dump($champs)) or die(mysql_error()); } } fclose ($fp); } ?> Here is the result:
array (size=6)
0 => string '624030743830000' (length=15)
1 => string 'A100001D7DEE6D' (length=14)
2 => string '80E5FA73' (length=8)
3 => string '37312379' (length=8)
4 => string '0E47EA7A6F444640' (length=16)
5 => string '33830000;' (length=9)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
Now I don't know what to do, remember what I told you from the beginning. I am a beginner in PHP and this is my very first project in PHP. Help me.
Ooh la la,
the error is very easy to understand, you are doing a var_dump in an SQL query. Brrrr, to be banned ^^
You need to put your var_dump outside, like this:
Moreover, you have 6 data in your array (6 columns) and you are trying to insert 7... of course, it's stuck,
in my opinion, it is your first column (ida) which I imagine is AUTO_INCREMENT, so don't bother specifying it... unless it's necessary, but in that case, you need to indicate what you want to fill it with...
Hoping to have helped you further...
--
a "link" is worth more than two you’ll get...
the error is very easy to understand, you are doing a var_dump in an SQL query. Brrrr, to be banned ^^
You need to put your var_dump outside, like this:
var_dump($champs);
mysql_query("INSERT INTO coordab (IMSI, MEID, ESN1, PUK1, AKY, MDN)
VALUES ($champ[0],$champ[1],$champ[2],$champ[3],$champ[4],$champ[5]") or die(mysql_error());
Moreover, you have 6 data in your array (6 columns) and you are trying to insert 7... of course, it's stuck,
in my opinion, it is your first column (ida) which I imagine is AUTO_INCREMENT, so don't bother specifying it... unless it's necessary, but in that case, you need to indicate what you want to fill it with...
Hoping to have helped you further...
--
a "link" is worth more than two you’ll get...
Here’s what it displays to me now:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FA73,37312379,0E47EA7A6F444640,33830000)' at line 2
Please help me again, I am overwhelmed.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FA73,37312379,0E47EA7A6F444640,33830000)' at line 2
Please help me again, I am overwhelmed.
After testing on my end, it should work on yours, try this:
If it still doesn’t work, it must be related to the structure of your table in the database, you’ll need to send us a bit of its structure so we can see what it looks like (fields varchar(255), ida auto_increment etc...)
Keep us posted...
--
a "link" is worth more than two you shall have...
<?php mysql_connect('localhost','root',''); mysql_select_db('gestnum'); //empty the coordab table before mysql_query("TRUNCATE TABLE coordab"); //Open the file in question (read-only) $handle = fopen("text.txt", "r"); //loop through the entire file (pretending it's a csv for easier processing) while ($data = fgetcsv($handle, 1000, ",")) { //$data corresponds to a complete line, we just need to explode it to retrieve everything $explode = explode(' ',$data[0]); //Now that we have everything, we insert it into the database mysql_query("INSERT INTO coordab (IMSI, MEID, ESN1, PUK1, AKY, MDN) VALUES (".$explode[0].",".$explode[1].",".$explode[2].",".$explode[3].",".$explode[4].",".$explode[5].");") or die(mysql_error()); } //Close the file fclose($handle); ?> If it still doesn’t work, it must be related to the structure of your table in the database, you’ll need to send us a bit of its structure so we can see what it looks like (fields varchar(255), ida auto_increment etc...)
Keep us posted...
--
a "link" is worth more than two you shall have...
Thank you for your availability, here is the structure of my coordab table:
# Name Type Intercollation Attributes Null Default Extra Action
1 ida int(60) No None AUTO_INCREMENT Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
2 IMSI varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
3 MEID varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
4 ESN1 varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
5 PUK1 varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
6 AKY varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
7 MDN varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
Whole text Whole text
more
I just tested the code you sent me but it still shows this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FA73,37312379,0E47EA7A6F444640,33830000)' at line 2
Please don't get tired, I don't know.
# Name Type Intercollation Attributes Null Default Extra Action
1 ida int(60) No None AUTO_INCREMENT Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
2 IMSI varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
3 MEID varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
4 ESN1 varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
5 PUK1 varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
6 AKY varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
more
7 MDN varchar(255) latin1_swedish_ci No None Modify Modify Delete Delete
Primary Primary
Unique Unique
Index Index
Spatial Spatial
Whole text Whole text
more
I just tested the code you sent me but it still shows this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FA73,37312379,0E47EA7A6F444640,33830000)' at line 2
Please don't get tired, I don't know.
Wow, the structure is a bit rough,
I would have needed the structure extracted directly from phpMyAdmin.
To do this, go to your phpMyAdmin,
- select your database,
- then in the "export" tab,
- select the option "Custom - display all possible options"
- once there, select all the tables,
- in the "output" section, select "display results",
and at the bottom of the page, finally click on "execute".
There, the code will be displayed, copy and paste it here...
In my opinion, the problem comes from the structure really... on my side I tested the code I sent you, no problem...
Don't get discouraged
We're almost there...
--
A "link" is worth more than two you will have...
I would have needed the structure extracted directly from phpMyAdmin.
To do this, go to your phpMyAdmin,
- select your database,
- then in the "export" tab,
- select the option "Custom - display all possible options"
- once there, select all the tables,
- in the "output" section, select "display results",
and at the bottom of the page, finally click on "execute".
There, the code will be displayed, copy and paste it here...
In my opinion, the problem comes from the structure really... on my side I tested the code I sent you, no problem...
Don't get discouraged
We're almost there...
--
A "link" is worth more than two you will have...
phpMyAdmin SQL Dump
-- version 4.0.4
-- https://www.phpmyadmin.net/
--
-- Client: localhost
-- Generated on: Thu, 13 February 2014 at 10:29
-- Server version: 5.6.12-log
-- PHP version: 5.4.12
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: 'gestnum'
--
CREATE DATABASE IF NOT EXISTS 'gestnum' DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE 'gestnum';
-- --------------------------------------------------------
--
-- Table structure for 'coordab'
--
CREATE TABLE IF NOT EXISTS 'coordab' (
'ida' int(60) NOT NULL AUTO_INCREMENT,
'IMSI' varchar(255) NOT NULL,
'MEID' varchar(255) NOT NULL,
'ESN1' varchar(255) NOT NULL,
'PUK1' varchar(255) NOT NULL,
'AKY' varchar(255) NOT NULL,
'MDN' varchar(255) NOT NULL,
PRIMARY KEY ('ida')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for 'user'
--
CREATE TABLE IF NOT EXISTS 'user' (
'idu' int(50) NOT NULL AUTO_INCREMENT,
'nom' varchar(50) NOT NULL,
'prenom' varchar(50) NOT NULL,
'idutilisateur' varchar(50) NOT NULL,
'password' varchar(50) NOT NULL,
PRIMARY KEY ('idu')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;
-- version 4.0.4
-- https://www.phpmyadmin.net/
--
-- Client: localhost
-- Generated on: Thu, 13 February 2014 at 10:29
-- Server version: 5.6.12-log
-- PHP version: 5.4.12
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: 'gestnum'
--
CREATE DATABASE IF NOT EXISTS 'gestnum' DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE 'gestnum';
-- --------------------------------------------------------
--
-- Table structure for 'coordab'
--
CREATE TABLE IF NOT EXISTS 'coordab' (
'ida' int(60) NOT NULL AUTO_INCREMENT,
'IMSI' varchar(255) NOT NULL,
'MEID' varchar(255) NOT NULL,
'ESN1' varchar(255) NOT NULL,
'PUK1' varchar(255) NOT NULL,
'AKY' varchar(255) NOT NULL,
'MDN' varchar(255) NOT NULL,
PRIMARY KEY ('ida')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for 'user'
--
CREATE TABLE IF NOT EXISTS 'user' (
'idu' int(50) NOT NULL AUTO_INCREMENT,
'nom' varchar(50) NOT NULL,
'prenom' varchar(50) NOT NULL,
'idutilisateur' varchar(50) NOT NULL,
'password' varchar(50) NOT NULL,
PRIMARY KEY ('idu')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;
Re hello,
the effort was totally stupid and came from me, just a story of quotation marks around the data in the request...
replace the request with this one (just a tweak with the quotation marks, and there you go, on my side impeccable...):
However, be careful because if your file is super heavy it might take a little while ^^
keep me posted (theoretically your next post should be "yesss it works" lol)
--
a "link" is worth more than two you’ll have...
the effort was totally stupid and came from me, just a story of quotation marks around the data in the request...
replace the request with this one (just a tweak with the quotation marks, and there you go, on my side impeccable...):
//Now that we have everything, we insert it into the database mysql_query('INSERT INTO coordab (IMSI, MEID, ESN1, PUK1, AKY, MDN) VALUES ("'.$explode[0].'","'.$explode[1].'","'.$explode[2].'","'.$explode[3].'","'.$explode[4].'","'.$explode[5].'");') or die(mysql_error()); </php> However, be careful because if your file is super heavy it might take a little while ^^
keep me posted (theoretically your next post should be "yesss it works" lol)
--
a "link" is worth more than two you’ll have...
Here’s the code you sent me and that I put in:
<?php
mysql_connect('localhost','root','');
mysql_select_db('gestnum');
//we empty the coordab table first
mysql_query("TRUNCATE TABLE coordab");
//We open the file in question (in read-only mode)
$handle = fopen("fichab.txt", "r");
//we go through the entire file (pretending it is a csv for easier processing)
while ($data = fgetcsv($handle, 1000, ",")) {
//$data corresponds to a complete line, we just have to explode it to retrieve everything
$explode = explode(' ',$data[0]);
//Now that we have everything, we insert it into the database
mysql_query('INSERT INTO coordab (IMSI, MEID, ESN1, PUK1, AKY, MDN)
VALUES ("'.$explode[0].'","'.$explode[1].'","'.$explode[2].'","'.$explode[3].'","'.$explode[4].'","'.$explode[5].'");') or die(mysql_error());
}
//We close the file
fclose($handle);
?>
And here’s what it displays to me
Notice: Undefined offset: 2 in C:\wamp\www\etudephp\chargerbd.php on line 20
Call Stack
# Time Memory Function Location
1 0.0007 251280 {main}( ) ..\chargerbd.php:0
( ! ) Notice: Undefined offset: 3 in C:\wamp\www\etudephp\chargerbd.php on line 20
Call Stack
# Time Memory Function Location
1 0.0007 251280 {main}( ) ..\chargerbd.php:0
( ! ) Notice: Undefined offset: 4 in C:\wamp\www\etudephp\chargerbd.php on line 20
Call Stack
# Time Memory Function Location
1 0.0007 251280 {main}( ) ..\chargerbd.php:0
( ! ) Notice: Undefined offset: 5 in C:\wamp\www\etudephp\chargerbd.php on line 20
Call Stack
# Time Memory Function Location
1 0.0007 251280 {main}( ) ..\chargerbd.php:0
Please help me again, I know I have already occupied you a lot.
<?php
mysql_connect('localhost','root','');
mysql_select_db('gestnum');
//we empty the coordab table first
mysql_query("TRUNCATE TABLE coordab");
//We open the file in question (in read-only mode)
$handle = fopen("fichab.txt", "r");
//we go through the entire file (pretending it is a csv for easier processing)
while ($data = fgetcsv($handle, 1000, ",")) {
//$data corresponds to a complete line, we just have to explode it to retrieve everything
$explode = explode(' ',$data[0]);
//Now that we have everything, we insert it into the database
mysql_query('INSERT INTO coordab (IMSI, MEID, ESN1, PUK1, AKY, MDN)
VALUES ("'.$explode[0].'","'.$explode[1].'","'.$explode[2].'","'.$explode[3].'","'.$explode[4].'","'.$explode[5].'");') or die(mysql_error());
}
//We close the file
fclose($handle);
?>
And here’s what it displays to me
Notice: Undefined offset: 2 in C:\wamp\www\etudephp\chargerbd.php on line 20
Call Stack
# Time Memory Function Location
1 0.0007 251280 {main}( ) ..\chargerbd.php:0
( ! ) Notice: Undefined offset: 3 in C:\wamp\www\etudephp\chargerbd.php on line 20
Call Stack
# Time Memory Function Location
1 0.0007 251280 {main}( ) ..\chargerbd.php:0
( ! ) Notice: Undefined offset: 4 in C:\wamp\www\etudephp\chargerbd.php on line 20
Call Stack
# Time Memory Function Location
1 0.0007 251280 {main}( ) ..\chargerbd.php:0
( ! ) Notice: Undefined offset: 5 in C:\wamp\www\etudephp\chargerbd.php on line 20
Call Stack
# Time Memory Function Location
1 0.0007 251280 {main}( ) ..\chargerbd.php:0
Please help me again, I know I have already occupied you a lot.
It's a silly thing that crashes your code...
Actually, in your fichtab.txt file, there is a space and a newline at the very end... that's what's causing the crash, and it makes sense.
We ask the code to read each line and to explode the result of each line...
When it reaches the last one, since you have a newline, it doesn't try to understand and just reads it... it detects two spaces in this line and then nothing else... as a result, it thinks you have an incomplete line and therefore doesn't insert anything into the database and gives you an error.
Basically, just remove the newlines and spaces, etc., that are right after the last line of your file... and... enjoy ^^
Good luck!
--
a "link" is worth more than two you'll have...
Actually, in your fichtab.txt file, there is a space and a newline at the very end... that's what's causing the crash, and it makes sense.
We ask the code to read each line and to explode the result of each line...
When it reaches the last one, since you have a newline, it doesn't try to understand and just reads it... it detects two spaces in this line and then nothing else... as a result, it thinks you have an incomplete line and therefore doesn't insert anything into the database and gives you an error.
Basically, just remove the newlines and spaces, etc., that are right after the last line of your file... and... enjoy ^^
Good luck!
--
a "link" is worth more than two you'll have...
Thank you to the whole team, because my problem is almost resolved. But one aspect remains
still: indeed my text file has 99,999 lines, but only 547 lines
are stored in the database when I run the script. Then I get the following error
message:
I am using WampServer 2.4. What should I do? Please help me.
still: indeed my text file has 99,999 lines, but only 547 lines
are stored in the database when I run the script. Then I get the following error
message:
( ! ) Fatal error: Maximum execution time of 30 seconds exceeded in C:\wamp\www\pagecon\toutespages\locations\chargerbd.php on line 19 Call Stack # Time Memory Function Location 1 0.0020 253936 {main}( ) ..\chargerbd.php:0 I am using WampServer 2.4. What should I do? Please help me.
Thank you to the whole team, as my problem is almost resolved. But one aspect remains
still: indeed, my text file has 99,999 lines, but only 547 lines
are stored in the database when I run the script. Then I get the following error
:
I am using WampServer 2.4. What should I do? Please help me.
still: indeed, my text file has 99,999 lines, but only 547 lines
are stored in the database when I run the script. Then I get the following error
:
! ) Fatal error: Maximum execution time of 30 seconds exceeded in C:\wamp\www\pagecon\toutespages\locations\chargerbd.php on line 19 Call Stack # Time Memory Function Location 1 0.0020 253936 {main}( ) ..\chargerbd.php:0 I am using WampServer 2.4. What should I do? Please help me.
In that case, at the very top of your file, add this (the first line of your script right after your <?php):
(originally your script can only run for a given amount of time... (30 seconds on your machine)... with this line, you instruct it not to have a time limit for execution...
it's particularly useful when you have very large scripts...^^
Good luck to you,
let us know if it's resolved!
--
a "link" is worth more than two you'll have...
ini_set('max_execution_time', 0); (originally your script can only run for a given amount of time... (30 seconds on your machine)... with this line, you instruct it not to have a time limit for execution...
it's particularly useful when you have very large scripts...^^
Good luck to you,
let us know if it's resolved!
--
a "link" is worth more than two you'll have...
Thank you very much for solving my problem. I set set_time_limit(0); at the top
and in php.ini, I set max_execution_time to 100000; and it works wonderfully. Thanks again.
But I still have a small issue, how can I insert a progress bar that shows the loading progress of the data into the database, as it takes quite a long time to load (tens of minutes). Thank you in advance!
and in php.ini, I set max_execution_time to 100000; and it works wonderfully. Thanks again.
But I still have a small issue, how can I insert a progress bar that shows the loading progress of the data into the database, as it takes quite a long time to load (tens of minutes). Thank you in advance!
You're welcome, I was happy to help you... and maybe one day it will be your turn heehee ^^
Otherwise wow, to create a progress bar we took a very wrong route here...
We should have done it in AJAX for example, and even that's very complicated to do...
At worst, just display a small spinning gear, and below a message saying "please wait... the process may take several minutes, do not reload the page even if it seems frozen"
Anyway, in your current case, I think that's the best solution... doing it in AJAX would be, in my opinion, too complex for you as a beginner...^^
Glad I could solve your problem.
Since the post is resolved, can you mark it as resolved?
Thank you..
http://sarl-adams.fr
--
a "link" is worth more than two you will have...
Otherwise wow, to create a progress bar we took a very wrong route here...
We should have done it in AJAX for example, and even that's very complicated to do...
At worst, just display a small spinning gear, and below a message saying "please wait... the process may take several minutes, do not reload the page even if it seems frozen"
Anyway, in your current case, I think that's the best solution... doing it in AJAX would be, in my opinion, too complex for you as a beginner...^^
Glad I could solve your problem.
Since the post is resolved, can you mark it as resolved?
Thank you..
http://sarl-adams.fr
--
a "link" is worth more than two you will have...
Hello,
I'm sorry, I'm bringing this topic back up because I have the same problem as Nguim. The only thing that changes is that I have to enter the character strings from 45 files!
To set the context: I'm working on a project that analyzes printer consumption. A Python script has been written to retrieve printer counters every day. In each CSV file (corresponding to a printer), there are dozens of character strings, looking like this:
30/01/14;28433
31/01/14;28631
01/02/14;28641
02/02/14;28641
03/02/14;28657
04/02/14;28674
05/02/14;28700
07/02/14;28743
I managed to create a script that opens, reads all the files, and splits the character strings; that's fine on that side. But after that, I can't manage to enter them into my database.
It consists of six tables, but I'm interested in the "counters" table. It is structured as follows:
id_printer (foreign key from the printer table), date_reading, counter.
Knowing that id_printer and date_reading form the primary key of the counters table.
My script is as follows:
And the result is this (this is just a part of a file):
Array
(
[0] => 30/01/14
[1] => 50651
30/01/14
[2] => 50651
31/01/14
[3] => 50658
01/02/14
[4] => 50685
02/02/14
[5] => 50685
)
Is it normal that the dates don't have a value in front? Considering that the first date does?
From there, I don't know how to insert everything into the database; I read what was done in this topic and tried it on a single file, but it returns this:
PHP Notice: Undefined offset: 1 in /var/www/script_chaines_test.php on line 19
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2
I think it must be a silly mistake, but I can't understand... And once I manage to do it for one file, how to do it for all the files? Also, once in the database, there should be the printer to which the counters correspond...
I hope I was clear enough in my explanations, and I thank you in advance for your help, as I'm stuck on this and can't continue my project until it's done... :(
Ju'
P.S: I will, on my side, try to continue analyzing the script given here ^^
P.S.2: So I tried to do it for a single file with the code below, which was given in the topic:
And it returns this error:
PHP Notice: Undefined offset: 1 in /var/www/script_chaines_test.php on line 19
Cannot add or update a child row: a foreign key constraint fails ('imprimantes_test'.'compteur', CONSTRAINT 'FK_imprimante' FOREIGN KEY ('id_imprimante') REFERENCES 'imprimantes' ('id_imprimante'))
So as I thought, there will really be a problem with the foreign key of the printer table... :(
I'm sorry, I'm bringing this topic back up because I have the same problem as Nguim. The only thing that changes is that I have to enter the character strings from 45 files!
To set the context: I'm working on a project that analyzes printer consumption. A Python script has been written to retrieve printer counters every day. In each CSV file (corresponding to a printer), there are dozens of character strings, looking like this:
30/01/14;28433
31/01/14;28631
01/02/14;28641
02/02/14;28641
03/02/14;28657
04/02/14;28674
05/02/14;28700
07/02/14;28743
I managed to create a script that opens, reads all the files, and splits the character strings; that's fine on that side. But after that, I can't manage to enter them into my database.
It consists of six tables, but I'm interested in the "counters" table. It is structured as follows:
id_printer (foreign key from the printer table), date_reading, counter.
Knowing that id_printer and date_reading form the primary key of the counters table.
My script is as follows:
<?php function parcourir_repertoire($repertoire) { $le_repertoire = opendir($repertoire) or die("Error, the directory $repertoire does not exist"); while($fichier = @readdir($le_repertoire)) { if ($fichier == "." || $fichier == "..") continue; if (is_dir($repertoire. '/'.$fichier)) { print '<ul>'.$repertoire.'/'.$fichier; parcourir_repertoire($repertoire.'/'.$fichier); print '</ul>'; } else { print "$fichier \n"; $myfile = file_get_contents($repertoire. '/' .$fichier, "r"); // Splits the character strings of all files $chaine = $myfile; print_r(explode(';', $chaine)); } } } parcourir_repertoire('/script_python/historique'); ?> And the result is this (this is just a part of a file):
Array
(
[0] => 30/01/14
[1] => 50651
30/01/14
[2] => 50651
31/01/14
[3] => 50658
01/02/14
[4] => 50685
02/02/14
[5] => 50685
)
Is it normal that the dates don't have a value in front? Considering that the first date does?
From there, I don't know how to insert everything into the database; I read what was done in this topic and tried it on a single file, but it returns this:
PHP Notice: Undefined offset: 1 in /var/www/script_chaines_test.php on line 19
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2
I think it must be a silly mistake, but I can't understand... And once I manage to do it for one file, how to do it for all the files? Also, once in the database, there should be the printer to which the counters correspond...
I hope I was clear enough in my explanations, and I thank you in advance for your help, as I'm stuck on this and can't continue my project until it's done... :(
Ju'
P.S: I will, on my side, try to continue analyzing the script given here ^^
P.S.2: So I tried to do it for a single file with the code below, which was given in the topic:
<?php mysql_connect('localhost', 'root', 'a'); mysql_select_db('imprimantes_test'); mysql_query("TRUNCATE TABLE compteur"); // We loop through the file (read-only) $handle = fopen("172.17.68.94.csv", "r"); // We loop through the whole file while ($data = fgetcsv($handle, 1000, ";")) { //$data corresponds to a complete line; we just need to explode it to retrieve everything $explode = explode(' ', $data[0]); // We insert it into the database mysql_query("INSERT INTO compteur (id_imprimante, date_releve, compteur) VALUES ('', '.$explode[0].', '.$explode[1].');") or die(mysql_error()); } // We close the file fclose($handle); ?> And it returns this error:
PHP Notice: Undefined offset: 1 in /var/www/script_chaines_test.php on line 19
Cannot add or update a child row: a foreign key constraint fails ('imprimantes_test'.'compteur', CONSTRAINT 'FK_imprimante' FOREIGN KEY ('id_imprimante') REFERENCES 'imprimantes' ('id_imprimante'))
So as I thought, there will really be a problem with the foreign key of the printer table... :(
Hello,
by reading the subject very (and I mean very very) quickly, the mistake is already clear.
Indeed, you have an error in your insertion into mySQL, which is logical because you are basically telling it "insert NOTHING into id_imprimante", whereas this is a mandatory field.
Try this instead for starters:
This syntax allows you to specify only the fields you want to fill, mySQL will take care of creating the id in question on its own...
If you have no choice but to provide the ID, then you'll need to fetch it from the relevant table, store it in your loop, and reinsert it into your insertion (I know, it sounds like Chinese when I put it that way, sorry.)
I hope I have answered your questions...
feel free to get back to us, and/or mark this message as resolved if that is the case...^^
don’t get discouraged, for a beginner the start is very well thought out ^^
--
Matthebest62 (www.sarl-adams.fr)
by reading the subject very (and I mean very very) quickly, the mistake is already clear.
Indeed, you have an error in your insertion into mySQL, which is logical because you are basically telling it "insert NOTHING into id_imprimante", whereas this is a mandatory field.
Try this instead for starters:
mysql_query('INSERT INTO compteur SET date_releve="'.$explode[0].'", compteur="'.$explode[1].'"') or die(mysql_error()); This syntax allows you to specify only the fields you want to fill, mySQL will take care of creating the id in question on its own...
If you have no choice but to provide the ID, then you'll need to fetch it from the relevant table, store it in your loop, and reinsert it into your insertion (I know, it sounds like Chinese when I put it that way, sorry.)
I hope I have answered your questions...
feel free to get back to us, and/or mark this message as resolved if that is the case...^^
don’t get discouraged, for a beginner the start is very well thought out ^^
--
Matthebest62 (www.sarl-adams.fr)
- 1
- 2
Next