[PHP-DB] splitting CSV rows into multiple SQL inserts?
Hi all,
I am attempting to take a CSV file that has order header and line item
data on each line and split it into mulitple insert queries into a mysql
db. Each line in the CSV file may only be one order, though it is common
for there to be more than one item ordered, so I need to be able to loop
to insert the line item values into the proper line item table. I also
need to build a counter for a value that is not provided in the CVS for
each line item.
Here is a sample of a few lines of the CSV:
1110,6/20/2005,Jan Doe,1234 Spring St.,Anytown,PA,17033,0618456990,22.50,1,The
Sample Book
1114,6/22/2005,Jon Smith,888 Main St.,Big
City,CA,92648,009444,19.95,1,Coloring Book
1114,6/22/2005,Jon Smith,888 Main St.,Big
City,CA,92648,9834119933,4.40,1,Picture Book
1114,6/22/2005,Jon Smith,888 Main St.,Big
City,CA,92648,948922,59.99,4,Coffee Book
In the above file, the last 4 fields [item_num, cost, quantity, title]
belong in a line_items table. The first number, the order_number, also
goes into the line_items table, as well as the order_header table. The
contact info for each customer also goes into the order_header table. I do
not want duplicate entries in the order_header table, so I can't just to a
simple loop through each line in the text file and do an insert. I need to
be able to group an order by the order_number and insert the correct
number of rows in both tables. I also need to create a counter per order
showing which line item number each item is. Eg: the Coloring Book would
be assigned a 1, the Picture book a 2, and the Coffee Book a 3 for order
#1114. The Sample Book in order 1110 would be given a 1, since it is the
first [and only] item in that order.
I have been successful in assigning each value to a varable and looping
through the file via:
while ($line = fgets($fp,1024))
{
$i++;
if ($i 1) { // using 1 because CSV includes a header row
list($order_number, ...) = csv_explode($line);
[i am using an Excel generated CSV with double quotes around each value
and so i have a csv_explode function setup to properly extract each value;
also the real CSV has about 2 dozen fields - i just cut it down to its
basics for the example here]
Doing 2 inserts here and closing the loop is obviously not the answer,
since I get duplicate header rows, and I haven't built a counter for the
line_item's table's line counter field. The primary key in the line item
table is a combo of the order_number and the line_counter. Each fresh
order_number needs to reset the line_counter to 1, until all line items
for that order are inserted.
I am having difficulty figuring out how to loop through the CSV to do the
inserts. Any tips? Is this something that should be posted on PHP-General
instead?
TIA,
/vjl/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Shrinking gifs
Hello list
I'd like to make a thumbnaile list using some gifs.
I use the following code but I got error (Call to undefined function:
imagecreatefromgif() ).
Does anybody knows what's wrong?
function viewsm($ph) {
$ph='photo/'.$ph;
echo $ph;
$imgReal = imagecreatefromgif($ph);
$x = ImagesX($imgReal);
$y = ImagesY($imgReal);
$newX=$x*0.50;
$newY=$y*0.50;
$img = ImageCreate($newX,$newY);
ImageCopyResized($img, $imgReal, 0, 0, 0, 0, $newX, $newY, $x,
$y);
return $img;
}
RH-9 Linux
Apache httpd-2.0.40-21.11
PHP Version 4.3.11
gd-1.8.4-11
Thanks
Nikos
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Shrinking gifs
I'm afraid I dont know such command -Original Message- From: Darryl Steyn [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 21, 2005 2:50 PM To: nikos Subject: Re: [PHP-DB] Shrinking gifs afaik, you need to use imagemagik to resize a gif
Re: [PHP-DB] Shrinking gifs. .
nikos wrote: Hello list I'd like to make a thumbnaile list using some gifs. I use the following code but I got error (Call to undefined function: imagecreatefromgif() ). Does anybody knows what's wrong? [snip] RH-9 Linux Apache httpd-2.0.40-21.11 PHP Version 4.3.11 gd-1.8.4-11 gif support was removed from gd in 1.6*. It was re-added in 2.0.28. This is all due to the Unisys patent on the LZW compression that gifs use. short answer - you just don't have support for making gifs, it was a licensing issue at the time the software you're using was released/packaged. Upgrade gd. * funny, since gd stood for 'gif draw' originally. cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
Hi Everyone, When trying to connect to MySQL via php, the following error message is displayed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) This is the first time I have seen this error message after a fresh installation. Here's what's been done so far. installed rpm's from mysql.com for RHEL 4AS compiled php 5.0.4 from source ./configure --with-apxs2=/usr/local/apache2/bin/apxs --enable-force-cgi-redirect \ --enable-discard-path --enable-fastcgi --disable-path-info-check \ --enable-sigchild --enable-magic-quotes --with-openssl --with-zlib \ --enable-calendar --with-jpeg-dir --with-png-dir --with-zlib-dir \ --with-ttf --with-freetype-dir --with-t1lib --enable-gd-native-ttf \ --with-mysql=/usr --with-readline --with-snmp --with-gd compile apache 2.0.54 (standard compile) copied /usr/share/mysql/my.medium - /etc/my.conf Verified that socket = /var/lib/mysql/mysql.sock PHPinfo reveils the following: Client API version - 4.1.12 MYSQL_MODULE_TYPE - external MYSQL_SOCKET- /var/lib/mysql/mysql.sock MYSQL_INCLUDE - -l/usr/include/mysql MYSQL_LIBS - -L/usr/lib -lmysqlclient apache error log: [client 10.70.0.26] PHP Warning: mysql_connect() [a href='function.mysql-connect'function.mysql-connect/a]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) in /usr/local/apache2/htdocs/test.php on line 5 [EMAIL PROTECTED] mysql]# ls -lta mysql.sock srwxrwxrwx 1 mysql mysql 0 Jun 21 14:37 mysql.sock [EMAIL PROTECTED] mysql]# pwd /var/lib/mysql [EMAIL PROTECTED] mysql]# [EMAIL PROTECTED] mysql]# netstat -ln | grep mysql unix 2 [ ACC ] STREAM LISTENING 8541 /var/lib/mysql/mysql.sock [EMAIL PROTECTED] mysql]# So.. as you can see.. I am stumped.. Any thought or suggestions on where to go from here.. Thanks.. -- Dominique - Dominique Démoré Technical Services Coordinator Rainbow District School Board 69 Young Street Sudbury, Ontario P3E 3G5 Tel: (705) 674-3171 x. 258 Fax: (705) 671-2442 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] select * from table where column 'CONTAINS' more than one value (how?)
Hello all, I have no problem creating a table, using a query from my dbase table news: SELECT * FROM table where column = VALUE. However, now that most of our articles have more than one column type (i.e. instead of just technology, the column can now contain technology, politics, local. My question is now that I have more than one value, what format can I use for a contain command. EXAMPLE: articles needed local political articles: SELECT * FROM table where column contains politics, local. Does anyone know how to format the above command into a proper SQL query language? Thank you in advance.
RE: [PHP-DB] select * from table where column 'CONTAINS' more than one value (ho
use full text searches http://dev.mysql.com/doc/mysql/en/fulltext-search.html bastien From: [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] select * from table where column 'CONTAINS' more than one value (how?) Date: Tue, 21 Jun 2005 20:49:12 EDT Hello all, I have no problem creating a table, using a query from my dbase table news: SELECT * FROM table where column = VALUE. However, now that most of our articles have more than one column type (i.e. instead of just technology, the column can now contain technology, politics, local. My question is now that I have more than one value, what format can I use for a contain command. EXAMPLE: articles needed local political articles: SELECT * FROM table where column contains politics, local. Does anyone know how to format the above command into a proper SQL query language? Thank you in advance. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select * from table where column 'CONTAINS' more than one value (how?)
You can use LIKE and wildcards, which is faster than fulltext searches, which will provide you a lot of information you don't need. Do this: SELECT * FROM table where column LIKE %politics% OR column LIKE %local% to find if the field contains politics or local.. -Micah On Tuesday 21 June 2005 05:49 pm, [EMAIL PROTECTED] wrote: Hello all, I have no problem creating a table, using a query from my dbase table news: SELECT * FROM table where column = VALUE. However, now that most of our articles have more than one column type (i.e. instead of just technology, the column can now contain technology, politics, local. My question is now that I have more than one value, what format can I use for a contain command. EXAMPLE: articles needed local political articles: SELECT * FROM table where column contains politics, local. Does anyone know how to format the above command into a proper SQL query language? Thank you in advance. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
