Re: [PHP-DB] Optimising LIMITs
Hi Richard! Step 1 --- Is it right? 1. `threadid` value is unique. 2. `threadid` value is auto_increment. 3. if `threadid`(2) > `threadid`(1) => `created`(2) > `created`(1). You can do the following: 1. Create the index on two fields: (boardid, status) 2. ALTER TABLE `board` ORDER BY `threadid` DESC; Here is new query to fetch a block of records: SELECT * FROM `board` WHERE `boardid` = 1 AND `status` = "L" LIMIT 100,50 Step 2 The average size of row in your table is 128,000,000 / 190,000 ~= 675 bytes. Split your table in two tables, like: CREATE TABLE `boardinfo` ( `threadid` INT(4) UNSIGNED AUTO_INCREMENT, PRIMARY KEY( `threadid` ), `boardid` INT(4) UNSIGNED, `status` ENUM( 'L', '' ), `created` TIMESTAMP ... (all fixed fields ) ) ALTER TABLE `boardinfo` CREATE INDEX `main` (`boardid,`status`) ALTER TABLE `boardinfo` ORDER BY `threadid` DESC CREATE TABLE `boarddata` ( `threadid_ref` INT(4) UNSIGNED, PRIMARY KEY ( `threadid_ref` ), `threaddata` LONGTEXT ) Here is your query after: SELECT * FROM `boardinfo` LEFT JOIN `boarddata` ON `threadid` = `threadid_ref` WHERE `boardid` = 1 AND `status` = "L" LIMIT 100,50 Sorry for my englist. Best regards, Pavel > Hi all, > > I have what is probably a quite standard question and would love to > know how you would all approach this scenario: > > I have a table in a database that has approx. 190,000 records in it. > The table is currently 128MB in size and I'm happy that it is well > constructed with no data duplication and sensible indexes. > > I'm using MySQL 3.28.58. and my question is about querying this volume > of data efficiently. > > The table holds forum threads (several years worth) so a common query > running on the table is to bring back the top 50 or 100 threads from a > board within the forum. > > To do this I'm using a LIMIT on my query and for the paging through > the data (i.e. the first 100 threads, the next 100, etc) I use the > LIMIT n,x syntax. Threads are sorted by date (most recent to the top). > > This is fine and it works well but I'm concerned it's not the most > efficient way to do this because the use of LIMIT is causing the > whole table to be scanned each time. > > Here is a typical (simplified) query: > > SELECT * > FROM thread > WHERE > thread.status='L' AND > thread.boardid=1 > ORDER BY created DESC > LIMIT 100,50 > > This takes over 1.02 seconds to process. > > Running an EXPLAIN on my query shows that it's using one key > (boardid), but in the Extra field it shows it is having to use a > filesort on the data. 5701 rows were used in order to bring back the > final 50 - that's every single thread for this board. > > What I'm trying to figure out is a more efficient way of selecting a > block of 50 or 100 records from any point in my table without MySQL > needing to sort/check them all first. > > One thought I did have was that the Primary Key on my table is called > threadid - and I thought that instead of bring back the data in my > original query, I could collect nothing but the thread IDs and then > use a separate query that does something like: "SELECT * FROM thread > WHERE threadid IN (...)" (where ... = all of the IDs previously > selected). Would the fact that threadid is my primary key make the > original LIMIT/sort faster? > > Any thoughts appreciated. > > -- > Best regards, > Richard Davey > http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySql PHP API
I'm confused. I replied to your inquiry to tell you what information you need to provide if you are to receive help.. Why are you asking me questions about *your* problem? And keep the discussion on the list. Doug Mike Ni wrote: Do you know for certain whether it is a matter of installation or a matter of db_programming? Mike --- Doug Thompson <[EMAIL PROTECTED]> wrote: On Mon, 1 Mar 2004 17:16:01 -0800 (PST), Mike Ni wrote: Hey everyone, Does anyone outhere is running Apache/Linux/PHP/Mysql? I am having hard time with "MYSQL PHP API". Apache/PHP simply would not recognize any cmysql function call such as "mysql_connect". I have recompile the php with mysql extension and th problem continue tobe there. I run the "phpifo" and it work. In smuuary: * Beside Mysql, PHP seems to be working including phpino * According to phpinfo( ), it suuport mysql. Yet, wouldn't recognize the functin call Plese let me know if you have any thought. Thanks! Mike: You don't say if you are trying to get a new installation running for the first time or are these scripts that were working at one time and something has changed that has caused them to stop working? Is the installation on your local machine or on a remote server? More importantly, you need to provide a cut-and-paste copy of the portion of the code that isn't working and all error messages being returned by the system. Finally, please don't cross port to several lists at once. Regards, Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySql PHP API
On Mon, 1 Mar 2004 17:16:01 -0800 (PST), Mike Ni wrote: >Hey everyone, > > >Does anyone outhere is running Apache/Linux/PHP/Mysql? > >I am having hard time with "MYSQL PHP API". Apache/PHP >simply would not recognize any cmysql function call >such as "mysql_connect". > >I have recompile the php with mysql extension and th >problem continue tobe there. > >I run the "phpifo" and it work. >In smuuary: > >* Beside Mysql, PHP seems to be working including >phpino >* According to phpinfo( ), it suuport mysql. Yet, >wouldn't recognize the functin call > > >Plese let me know if you have any thought. > >Thanks! Mike: You don't say if you are trying to get a new installation running for the first time or are these scripts that were working at one time and something has changed that has caused them to stop working? Is the installation on your local machine or on a remote server? More importantly, you need to provide a cut-and-paste copy of the portion of the code that isn't working and all error messages being returned by the system. Finally, please don't cross port to several lists at once. Regards, Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySql PHP API
Hey everyone, Does anyone outhere is running Apache/Linux/PHP/Mysql? I am having hard time with "MYSQL PHP API". Apache/PHP simply would not recognize any cmysql function call such as "mysql_connect". I have recompile the php with mysql extension and th problem continue tobe there. I run the "phpifo" and it work. In smuuary: * Beside Mysql, PHP seems to be working including phpino * According to phpinfo( ), it suuport mysql. Yet, wouldn't recognize the functin call Plese let me know if you have any thought. Thanks! __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] "undefined function" from "mysql_connect" again
Hey everyone, I am having a tough time with "MySQL" API/Linux and I heard othes had the same issue a few days ago. I keep getting error message "undefined fnction" while calling "mysql_connect". Did anyone find out what was causing this? Thanks! MIke __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sybase support in PHP5
Is Sybase support in PHP5 working? I get: /bin/sh /usr/portage/tmp/php-5.0.0b3/libtool --silent --preserve-dup-deps --mode=compile gcc -Iext/sybase/ -I/usr/portage/tmp/php-5.0.0b3/ext/sybase/ -DPHP_ATOM_INC -I/usr/portage/tmp/php-5.0.0b3/include -I/usr/portage/tmp/php-5.0.0b3/main -I/usr/portage/tmp/php-5.0.0b3 -I/usr/portage/tmp/php-5.0.0b3/Zend -I/usr/include/libxml2 -I/usr/include/mysql -I/opt/sybase-11.9.2/include -I/usr/portage/tmp/php-5.0.0b3/TSRM -g -O2 -prefer-pic -c /usr/portage/tmp/php-5.0.0b3/ext/sybase/php_sybase_db.c -o ext/sybase/php_sybase_db.lo /usr/portage/tmp/php-5.0.0b3/ext/sybase/php_sybase_db.c: In function `zif_sybase_fetch_object': /usr/portage/tmp/php-5.0.0b3/ext/sybase/php_sybase_db.c:1110: invalid lvalue in assignment /usr/portage/tmp/php-5.0.0b3/ext/sybase/php_sybase_db.c:: invalid lvalue in assignment make: *** [ext/sybase/php_sybase_db.lo] Error 1
[PHP-DB] Remove log files in Postgres
Hi all, does any one know a configuration parameter or command line that automatically remove in a period of time the log files in postresql database? Thanks a lot. Norma Ramirez
Re: Re[2]: [PHP-DB] Optimising LIMITs
> JM> Primary keys aren't any faster than normal keys. They're simply for > JM> identification. (correct me if I'm wrong, that is my understanding) > JM> If you don't already have an index on created, I'd do that. > > I do - here's a question though, what is the best way to have an > index? To combine multiple fields into one index - or to have one > field per index? I ask because MySQL will appear to select the best > possible index for the query and sometimes it's wrong - I read > somewhere you can control which index it uses, but if you had a > "combined" index would this make things any more/less effective? Well, it can't use a multi-column index if you are only matching on one column. If you have 3 indexes, (status, boardid), (status), and (boardid), it won't slow you down any on your queries. If one index is needed it is put into memory and stored there until another index bumps it, otherwise it resides on disk. I don't see it has too much of a performance dock, it may slow down inserts a little... I am not sure about this, but I think if you are going to have the 2 column indexes, they have to match up in both tables you're joining. You'll have to experiment with it a bit. you can say "select * from thread (index statusBoardCombinedIndex) where" to get the index you want. > > JM> If you want to optimize things further, I would run the query at midnight > JM> each morning, and instead of paging through the entire table, just page > JM> through that temporary table. > > Figured someone might suggest that - I will have to look into it. > Thanks. > > -- > Best regards, > Richard Davey > http://www.phpcommunity.org/wiki/296.html > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] Optimising LIMITs
Hello Jeffrey, Monday, March 1, 2004, 5:27:41 PM, you wrote: JM> Primary keys aren't any faster than normal keys. They're simply for JM> identification. (correct me if I'm wrong, that is my understanding) JM> If you don't already have an index on created, I'd do that. I do - here's a question though, what is the best way to have an index? To combine multiple fields into one index - or to have one field per index? I ask because MySQL will appear to select the best possible index for the query and sometimes it's wrong - I read somewhere you can control which index it uses, but if you had a "combined" index would this make things any more/less effective? JM> If you want to optimize things further, I would run the query at midnight JM> each morning, and instead of paging through the entire table, just page JM> through that temporary table. Figured someone might suggest that - I will have to look into it. Thanks. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] "global" variables
Hello Torsten, Monday, March 1, 2004, 6:30:11 PM, you wrote: TL> You mean this will unset $v_dec["page_no"]? Well $page_no at that point in your script equals nothing, so yes - it will always make $v_dec equal nothing. TL> This explains why at reload of index.php happens what happend. TL> But how to solve it? I _have_ to store some variables anywhere. Yes agreed - but you cannot store them in a variable because they will be lost each time the page reloads. Why not store them in a cookie? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] "global" variables
Hello Devey, > $v_dec["page_no"] = $page_no; > Which means that $v_dec will always equal nothing, because at the > point at which the file is included, $page_no hasn't been set to > anything at all. You mean this will unset $v_dec["page_no"]? This explains why at reload of index.php happens what happend. But how to solve it? I _have_ to store some variables anywhere. The session management at this point is rudimentary. I wrote it that way, that it works for now, i.e. building the query pages. This is of course not the final session management. At login I just write the session_id() into the table without checking if the user already owns a session. I left it to the end of work. So maybe it is not a problem of login_status()? function check_user($check_name, $check_passw) { $connect = @OCILogon("", "", ""); if(!$connect) { $err_oci = OCIError(); echo "(2) No connection - OCIError(): ".$err_oci["message"]; echo ""; } else { $sql_check = "SELECT count(*) AS count FROM user_web WHERE user_name = '".$check_name."' AND user_pass = '".$check_passw."'"; $stmt = OCIParse($connect, $sql_check); OCIExecute($stmt); while(OCIFetch($stmt)) { $res_check = (int) OCIResult($stmt, "COUNT"); } if(!$res_check == 1) { OCIFreeStatement($stmt); OCILogOff($connect); return(false); } else { $sql_check = "SELECT user_pid FROM user_web WHERE user_name = '".$check_name."' AND user_pass = '".$check_passw."'"; $stmt = OCIParse($connect, $sql_check); OCIExecute($stmt); while(OCIFetch($stmt)) { $user_pid = (int) OCIResult($stmt, "USER_PID"); } OCIFreeStatement($stmt); OCILogOff($connect); return($user_pid); } } } function login($user_pid, $session_id) { $connect = @OCILogon("", "", ""); if(!$connect) { $err_oci = OCIError(); echo "(2) No connection - OCIError(): ".$err_oci["message"]; echo ""; } else { $sql_login = "UPDATE user_web SET user_session = '".$session_id."' WHERE user_pid = '".$user_pid."'"; $stmt = OCIParse($connect, $sql_login); OCIExecute($stmt); } OCIFreeStatement($stmt); OCILogOff($connect); } function login_status($session_id) { $connect = @OCILogon("", "", ""); if(!$connect) { $err_oci = OCIError(); echo "(2) No connection - OCIError(): ".$err_oci["message"]; echo ""; } else { $sql_session_id = "SELECT count(user_session) FROM user_web WHERE user_session = '".$session_id."'"; $stmt = OCIParse($connect, $sql_session_id); OCIExecute($stmt); while(OCIFetch($stmt)) { $res_status = OCIResult($stmt, "COUNT(USER_SESSION)"); } if(!$res_status == 1) { return(false); } else { return($res_status); } } OCIFreeStatement($stmt); OCILogOff($connect); } Now I changed $page_no to $v_dec["page_no"] with the same result. If through submitting a form index.php is reloaded, but the value in $v_dec["page_no"] get lost. #manage.php if(!isset($v_dec["page_no"])) { $v_dec["page_no"] = "login"; } if(!(login_status($session_id)) == 1) { $v_dec["page_no"] = "login"; } /* User logged in? */ if(!($v_dec["page_no"] == "login")) { include('head.php'); } else { if(isset($_POST['login'])) { if(!($_POST['user'] == "") && !($_POST['passw'] =="")) { $user_pid = check_user($_POST['user'], $_POST['passw']); if(($user_pid != false) && (login_status($user_pid) == false)) { login($user_pid, $session_id); $res_login = 1; $v_dec["page_no"] = "start_auswahl"; } else { $res_login = 0; $res_login_text = "Login incorrect."; } } else { $res_login = 0; $res_login_text = "Insert Login AND Passwort."; } } else { $res_login = 0; } if($res_login == 0) { include('b_login.php'); } } /* if page = wells, springs, precipitation, surface used überprüfen, ob Formulare abgeschickt */ if($v_dec["page_no"] == "start_select") { if(isset($_POST['b_start_select'])) { if(!($_POST['sources'] == "")) { switch($_POST['sources']) { case
[PHP-DB] Optimising LIMITs
Hi all, I have what is probably a quite standard question and would love to know how you would all approach this scenario: I have a table in a database that has approx. 190,000 records in it. The table is currently 128MB in size and I'm happy that it is well constructed with no data duplication and sensible indexes. I'm using MySQL 3.28.58. and my question is about querying this volume of data efficiently. The table holds forum threads (several years worth) so a common query running on the table is to bring back the top 50 or 100 threads from a board within the forum. To do this I'm using a LIMIT on my query and for the paging through the data (i.e. the first 100 threads, the next 100, etc) I use the LIMIT n,x syntax. Threads are sorted by date (most recent to the top). This is fine and it works well but I'm concerned it's not the most efficient way to do this because the use of LIMIT is causing the whole table to be scanned each time. Here is a typical (simplified) query: SELECT * FROM thread WHERE thread.status='L' AND thread.boardid=1 ORDER BY created DESC LIMIT 100,50 This takes over 1.02 seconds to process. Running an EXPLAIN on my query shows that it's using one key (boardid), but in the Extra field it shows it is having to use a filesort on the data. 5701 rows were used in order to bring back the final 50 - that's every single thread for this board. What I'm trying to figure out is a more efficient way of selecting a block of 50 or 100 records from any point in my table without MySQL needing to sort/check them all first. One thought I did have was that the Primary Key on my table is called threadid - and I thought that instead of bring back the data in my original query, I could collect nothing but the thread IDs and then use a separate query that does something like: "SELECT * FROM thread WHERE threadid IN (...)" (where ... = all of the IDs previously selected). Would the fact that threadid is my primary key make the original LIMIT/sort faster? Any thoughts appreciated. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Changed field long to something else it worked thanks guys
"Piet From South Africa" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi > > I want to know if there is a limit to the amount of columns that can be > created in a table, i have 30 columns, and i cannot insert data, i have > checked everything over and over again, even written a shorter insert > function that worked on the database. > > Can anyone help me here! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] "global" variables
Richard Davey wrote: > You don't check $v_dec in your manage.php script though, you check to > see if $page_no is set which it never will be, because no-where do you > give it a value. Thanks Davey, but is $page_no not set every time (see below) or did you mean that I must rather use $v_dec["page_no"] in the code? if(!isset($page_no)) { $page_no = "login"; } $page_no is set to "login" if(!(login_status($session_id)) == 1) { $page_no = "login"; } $page_no is set to "login" if(($user_pid != false) && (login_status($user_pid) == false)) { login($user_pid, $session_id); $res_login = 1; $page_no = "start_select"; If login ok then $page_no is set to "start_select". if(!($page_no == "login")) switch { case...: $page_no="new value"} Torsten -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] my code
For one thing, your column name Long is invalid. It's a reserved keyword in MySQL. Either enclose it in backticks or change the name of the column. If you want to see what the db is reporting, do this: $result = mysql_query($query) or die(mysql_error()) And see what the database tells you. > -Original Message- > From: Piet from South Africa [mailto:[EMAIL PROTECTED] > Sent: Monday, March 01, 2004 11:48 AM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] my code > > > This is my code > > My function call: > > insert_property($name, $upfile1, $upfile2, $upfile3, > $upfile4, $bed, $bath, > > $living, $dining, $reception, $garages, $flats, $servant, > $pool, $jacuzzi, > $lapa, > > $entertain, $security, $securest, $short, $long, $contact, > $number, $mail, > $fax, > > $period, $prov, $city, $area); > > My function: > > function insert_property($name, $upfile1, $upfile2, $upfile3, > $upfile4, > $bed, $bath, > > $living, $dining, $reception, $garages, $flats, $servant, > $pool, $jacuzzi, > $lapa, > > $entertain, $security, $securest, $short, $long, $contact, > $number, $mail, > $fax, > > $period, $prov, $city, $area) > > { > > global $link; > > > $query = "insert into property (name, photo1, photo2, photo3, > logo, bed, > bath, > > living, dining, reception, garages, flats, servant, pool, > jacuzzi, lapa, > > entertain, security, securest, short, long, contact, number, > mail, fax, > > period, prov, city, area) > > values ('" . $name . "', '" . $upfile1 . "', '" . $upfile2 . "', '" . > $upfile3 . "' > > ,'" . $upfile4 . "', '" . $bed . "', '" . $bath . "', '" . > $living . "' > > ,'" . $dining . "', '" . $reception . "', '" . $garages . "', > '" . $flats . > "' > > , '" . $servant . "', '" . $pool . "', '" . $jacuzzi . "', '" > . $lapa . "' > > , '" . $entertain . "', '" . $security . "', '" . $securest . "', '" . > $short . "' > > , '" . $long . "', '" . $contact . "', '" . $number . "', '" > . $mail . "' > > , '" . $fax . "', '" . $period . "', '" . $prov . "', '" . $city . "' > > , '" . $area . "')"; > > > $result = mysql_query($query); > > if (!$result) > > { > > echo "no result"; > > } > > else > > { > > echo ""; > > > } > > } > > > "Piet From South Africa" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > Hi > > > > I want to know if there is a limit to the amount of columns > that can be > > created in a table, i have 30 columns, and i cannot insert > data, i have > > checked everything over and over again, even written a > shorter insert > > function that worked on the database. > > > > Can anyone help me here! > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] My database
id int(11) No name varchar(200) No photo1 varchar(200) No photo2 varchar(200) No photo3 varchar(200) No logo varchar(200) No bed varchar(200) No bath varchar(200) No living varchar(200) No dining varchar(200) No reception varchar(200) No garages varchar(200) No flats varchar(200) No servant varchar(200) No pool varchar(200) No jacuzzi varchar(200) No lapa varchar(200) No entertain varchar(200) No security varchar(200) No securest varchar(200) No short varchar(200) No long varchar(200) No contact varchar(200) No number varchar(200) No mail varchar(200) No fax varchar(200) No period varchar(200) No prov varchar(200) No city varchar(200) No area varchar(200) No "Piet From South Africa" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi > > I want to know if there is a limit to the amount of columns that can be > created in a table, i have 30 columns, and i cannot insert data, i have > checked everything over and over again, even written a shorter insert > function that worked on the database. > > Can anyone help me here! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] my code
This is my code My function call: insert_property($name, $upfile1, $upfile2, $upfile3, $upfile4, $bed, $bath, $living, $dining, $reception, $garages, $flats, $servant, $pool, $jacuzzi, $lapa, $entertain, $security, $securest, $short, $long, $contact, $number, $mail, $fax, $period, $prov, $city, $area); My function: function insert_property($name, $upfile1, $upfile2, $upfile3, $upfile4, $bed, $bath, $living, $dining, $reception, $garages, $flats, $servant, $pool, $jacuzzi, $lapa, $entertain, $security, $securest, $short, $long, $contact, $number, $mail, $fax, $period, $prov, $city, $area) { global $link; $query = "insert into property (name, photo1, photo2, photo3, logo, bed, bath, living, dining, reception, garages, flats, servant, pool, jacuzzi, lapa, entertain, security, securest, short, long, contact, number, mail, fax, period, prov, city, area) values ('" . $name . "', '" . $upfile1 . "', '" . $upfile2 . "', '" . $upfile3 . "' ,'" . $upfile4 . "', '" . $bed . "', '" . $bath . "', '" . $living . "' ,'" . $dining . "', '" . $reception . "', '" . $garages . "', '" . $flats . "' , '" . $servant . "', '" . $pool . "', '" . $jacuzzi . "', '" . $lapa . "' , '" . $entertain . "', '" . $security . "', '" . $securest . "', '" . $short . "' , '" . $long . "', '" . $contact . "', '" . $number . "', '" . $mail . "' , '" . $fax . "', '" . $period . "', '" . $prov . "', '" . $city . "' , '" . $area . "')"; $result = mysql_query($query); if (!$result) { echo "no result"; } else { echo ""; } } "Piet From South Africa" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi > > I want to know if there is a limit to the amount of columns that can be > created in a table, i have 30 columns, and i cannot insert data, i have > checked everything over and over again, even written a shorter insert > function that worked on the database. > > Can anyone help me here! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Is there a limit to columns in a mysql table
1. What type of error is being returned by the database when your INSERT fails? 2. Does the INSERT work from the command line interface for your database? 3. What does the table structure look like (DESCRIBE tablename)? > -Original Message- > From: Piet from South Africa [mailto:[EMAIL PROTECTED] > Sent: Monday, March 01, 2004 11:29 AM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Is there a limit to columns in a mysql table > > > Hi > > I want to know if there is a limit to the amount of columns > that can be > created in a table, i have 30 columns, and i cannot insert > data, i have > checked everything over and over again, even written a shorter insert > function that worked on the database. > > Can anyone help me here! > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] "global" variables
Hello Torsten, Monday, March 1, 2004, 4:48:39 PM, you wrote: TL> I build a small web interface to a database. This should be on the PHP General list, not DB - but even so, I'll answer your question: TL> if(!isset($page_no)) TL> { TL> $page_no = "login"; TL> } TL> I declared the $page_no in my TL> #variables.php as TL> $v_dec["page_no"] = $page_no. You don't check $v_dec in your manage.php script though, you check to see if $page_no is set which it never will be, because no-where do you give it a value. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Is there a limit to columns in a mysql table
Hi I want to know if there is a limit to the amount of columns that can be created in a table, i have 30 columns, and i cannot insert data, i have checked everything over and over again, even written a shorter insert function that worked on the database. Can anyone help me here! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] "global" variables
Hi, I build a small web interface to a database. I include into the index.php the and a : #index.php In I include the and the and then decide on the status of a variable $page_no which further has to be included to perfom the query jobs: #manage.php if(!isset($page_no)) { $page_no = "login"; } if(!(login_status($session_id)) == 1) { $page_no = "login"; } // user logged in? if(!($page_no == "login")) { include('head.php'); } else { if(isset($_POST['login'])) { if(!($_POST['user'] == "") && !($_POST['passw'] =="")) { $user_pid = check_user($_POST['user'], $_POST['passw']); if(($user_pid != false) && (login_status($user_pid) == false)) { login($user_pid, $session_id); $res_login = 1; $page_no = "start_select"; } else . . . if($res_login == 0) { include('b_login.php'); } } if($page_no == "start_select") { if(isset($_POST['b_start_select'])) { if(!($_POST['sources'] == "")) { switch($_POST['sources']) { case "wells": $page_no = "wells"; break; case "springs": $page_no = "springs"; break; case "precipitation": $page_no = "precipitation"; break; case "surface": $page_no = "surface"; break; case "used": $page_no = "used"; break; default: $page_no = "start_select"; } } } } if(!($page_no == "login")) { switch($page_no) { case "start_select": include('b_start_select.php'); break; case "wells": include('b_wells.php'); break; case "springs": include('b_springs.php'); break; case "precipitation": include('b_precipitation.php'); break; case "surface": include('b_surface.php'); break; case "used": include('b_used.php'); break; default: echo "If you see this side, please mail to.; } include('fuss.php'); } ?> I declared the $page_no in my #variables.php as $v_dec["page_no"] = $page_no. Now I have the problem that after login and switching to b_start_select.php still $page_no="start_select", because I can print it to this page: echo $page_no gives: start_select but echo $v_dec["page_no"] prints nothing Second problem: When I pass my selection through the form on the b_start_select.php (form action = "index.php") $page_no again becomes unset. That means I fall back to the login page. Any suggestions? How in general do you this. Thank you, Torsten -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating HTML objects from Oracle DB data @ runtime
Your question is rather vague. What kind of "help" are you seeking? If of the kind "contracting out", we'll be glad to answer your RFQ. Cheers Ignatius __ AUDERGHEM ANALYTICA Web applications specialists www.auderghem-analytica.com Brussels, Belgium __ _ - Original Message - From: "mike calcagno" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 01, 2004 13:54 Subject: [PHP-DB] Creating HTML objects from Oracle DB data @ runtime > I have a project where I am writing a web app version of out medical > laboratory software. I will be connecting to an oracle db and ordering tests > on patients. I will need to create multiple checkboxes at runtime depending > on the data in the db. I will then need to submit all of the checked > checkboxes and save them to the database. > > any help is appreciated. > > Thanks, > Mike C. > [EMAIL PROTECTED] > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Creating HTML objects from Oracle DB data @ runtime
I have a project where I am writing a web app version of out medical laboratory software. I will be connecting to an oracle db and ordering tests on patients. I will need to create multiple checkboxes at runtime depending on the data in the db. I will then need to submit all of the checked checkboxes and save them to the database. any help is appreciated. Thanks, Mike C. [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Weird problem posting to database
Setup: Apache, PHP, Firebird backend Problem: PHP will not allow text boxes to POST more than about 1k. Page just hangs. Already checked: php.ini, max_post_size is 8M. Nothing referring to POST limitations in httpd.conf Any thoughts? Thanks Evan Morris [EMAIL PROTECTED] +27 11 792 2777 (t) +27 11 792 2711 (f) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query... count?
Perfect... I finally used.. SELECT DISTINCT(bu), COUNT(bu) FROM $table_name WHERE date BETWEEN '$date1' AND '$date2' group by bu (I added the Distinct(bu) bit, to label the results correctly... god I love this list) Have a great day! Jason Wong <[EMAIL PROTECTED]> 01/03/2004 11:03 To [EMAIL PROTECTED] cc Subject Re: [PHP-DB] MySQL query... count? On Monday 01 March 2004 18:53, [EMAIL PROTECTED] wrote: > What I need to do for a new report, is say how many downloads were > recorded for each Business unit? > > I can select the Business units using Distinct, but hhow can I then count > them? > I've tried: > SELECT DISTINCT(bu)buname, COUNT(buname)bucount FROM $table_name > > But it doesn't work.. am I being dumb on this cold Monday morning? Don't use DISTINCT, use GROUP BY. -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* One thought driven home is better than three left on base. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query... count?
On Monday 01 March 2004 18:53, [EMAIL PROTECTED] wrote: > What I need to do for a new report, is say how many downloads were > recorded for each Business unit? > > I can select the Business units using Distinct, but hhow can I then count > them? > I've tried: > SELECT DISTINCT(bu)buname, COUNT(buname)bucount FROM $table_name > > But it doesn't work.. am I being dumb on this cold Monday morning? Don't use DISTINCT, use GROUP BY. -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* One thought driven home is better than three left on base. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query... count?
This problem pertains to a system that records all downloads from our site: I have a field in my DB, that states which business unit owns the download that was recorded... What I need to do for a new report, is say how many downloads were recorded for each Business unit? I can select the Business units using Distinct, but hhow can I then count them? I've tried: SELECT DISTINCT(bu)buname, COUNT(buname)bucount FROM $table_name But it doesn't work.. am I being dumb on this cold Monday morning? Tris... * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: problem to connect mssql DB under iis6
tests under 4.3.4 and 4.3.3 "Yannig Beyet" <[EMAIL PROTECTED]> a écrit dans le message de news:[EMAIL PROTECTED] > Hi, > > I'am currently migrating my servers from nt4/IIS4 to Windows server > 2003/IIS6, my db is under NT4/SQL7 Secondary domain controller, > > from iis4 no problem to access (guest user for the web site), > from iis6, the only way to connect DB is to run the web site under domain > administrator > > the problem exist for all my sites under PHP, no problem for sites in asp... > i use phpbb forum, for example... > > any idea ? > > -- > Yannig -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
Thankyou very much :) --- Ignatius Reilly <[EMAIL PROTECTED]> wrote: > Two ways: > - full path (like "c:/temp/data.txt") > - simple file name: MySQL will assume the file is > located in the current > database data directory (like > d:/mysql/data/my_database) > > _ > - Original Message - > From: "JeRRy" <[EMAIL PROTECTED]> > To: "Ignatius Reilly" <[EMAIL PROTECTED]> > Sent: Monday, March 01, 2004 11:15 > Subject: Re: [PHP-DB] plain-text file (.txt) to > mysql database > > > > Hi, > > > > Right now I understnad. Thanks for the example. > :) > > > > As for data.txt ... Where should I pop this on my > > server? In the root of my account? (e.g. > ~/data.txt > > ?) > > > > J > > > > --- Ignatius Reilly <[EMAIL PROTECTED]> > wrote: > > > In your case something like this will do: > > > > > > LOAD DATA INFILE "data.txt" > > > INTO TABLE my_table > > > FIELDS TERMINATED BY ' ' > > > LINES TERMINATED BY '\r\n'# or '\n' - > > > depending on your system > > > IGNORE 1 LINES# if > > > applicable > > > ( First, Last, Title ) > > > # actual order of your > > > columns in your txt file, mapping columns names > > > > > > HTH > > > Ignatius > > > _ > > > - Original Message - > > > From: "JeRRy" <[EMAIL PROTECTED]> > > > To: "Ignatius Reilly" <[EMAIL PROTECTED]>; > > > <[EMAIL PROTECTED]> > > > Sent: Monday, March 01, 2004 10:44 > > > Subject: Re: [PHP-DB] plain-text file (.txt) to > > > mysql database > > > > > > > > > > Hi, > > > > > > > > So would I need to edit anything in my .txt > file? > > > > > > > > Could you show me an example on what you mean > by: > > > > > > > > you can specify which > > > > columns of your text file > > > > goes to what DB column. > > > > > > > > > > > > > > > > Because my text file has first , last , title > > > > seperated by a space and a new entry is > seperated > > > by a > > > > enter. (new line) > > > > > > > > I took a look at www.mysql.com and had a read > on > > > 'LOAD > > > > DATA' but am not 100% sure on how it works > with my > > > > sort of .txt file setup. > > > > > > > > If you could show me an example that worked > with > > > my > > > > .txt file that would be great, or would I need > to > > > edit > > > > my .txt file somewhat? > > > > > > > > Sorry about this, never used a .txt file for > use > > > of > > > > mysql before but since I need to do alot of > > > entries at > > > > once it would be the easiest way to do it. > > > > > > > > Thanks! > > > > > > > > > > > > > > > > > > > > --- Ignatius Reilly <[EMAIL PROTECTED]> > > > wrote: > > > > > G'day, mate, > > > > > > > > > > With MySQL LOAD statement, you can specify > which > > > > > columns of your text file > > > > > goes to what DB column. > > > > > > > > > > HTH > > > > > Ignatius > > > > > _ > > > > > - Original Message - > > > > > From: "JeRRy" <[EMAIL PROTECTED]> > > > > > To: <[EMAIL PROTECTED]> > > > > > Sent: Monday, March 01, 2004 10:08 > > > > > Subject: [PHP-DB] plain-text file (.txt) to > > > mysql > > > > > database > > > > > > > > > > > > > > > > Hi, > > > > > > > > > > > > Quick question, does anyone know of a > > > program/site > > > > > > that could do the following: > > > > > > > > > > > > I have a .txt file written in notepad. > > > > > > > > > > > > Bob Someone Mr > > > > > > > > > > > > Now I have a db with the fields: > > > > > > > > > > > > First Last Title > > > > > > > > > > > > Now the .txt file looks something like > this: > > > > > > > > > > > > Bob Someone Mr > > > > > > Susan Taylor Miss > > > > > > Andrea Nothing Ms > > > > > > ... > > > > > > > > > > > > Now I want to use a Program/site to put > the > > > names > > > > > in > > > > > > the right field. So each field is > seperated > > > by a > > > > > > space as shown above. I have about 1000 > > > entries > > > > > to > > > > > > put in and don't think doing each query > per > > > line > > > > > is > > > > > > going to be a 5 min job, any suggestions? > > > > > > > > > > > > Thanks! > > > > > > > > > > > > Find local movie times and trailers on > Yahoo! > > > > > Movies. > > > > > > http://au.movies.yahoo.com > > > > > > > > > > > > -- > > > > > > PHP Database Mailing List > > > (http://www.php.net/) > > > > > > To unsubscribe, visit: > > > > > http://www.php.net/unsub.php > > > > > > > > > > > > > > > > > > > > > > > > > Find local movie times and trailers on Yahoo! > > > Movies. > > > > http://au.movies.yahoo.com > > > > > > > > -- > > > > PHP Database Mailing List > (http://www.php.net/) > > > > To unsubscribe, visit: > > > http://www.php.net/unsub.php > > > > > > > > > > > > > > > Find local movie times and trailers on Yahoo! > Movies. > > http://au.movies.yahoo.com > > > Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
Two ways: - full path (like "c:/temp/data.txt") - simple file name: MySQL will assume the file is located in the current database data directory (like d:/mysql/data/my_database) _ - Original Message - From: "JeRRy" <[EMAIL PROTECTED]> To: "Ignatius Reilly" <[EMAIL PROTECTED]> Sent: Monday, March 01, 2004 11:15 Subject: Re: [PHP-DB] plain-text file (.txt) to mysql database > Hi, > > Right now I understnad. Thanks for the example. :) > > As for data.txt ... Where should I pop this on my > server? In the root of my account? (e.g. ~/data.txt > ?) > > J > > --- Ignatius Reilly <[EMAIL PROTECTED]> wrote: > > In your case something like this will do: > > > > LOAD DATA INFILE "data.txt" > > INTO TABLE my_table > > FIELDS TERMINATED BY ' ' > > LINES TERMINATED BY '\r\n'# or '\n' - > > depending on your system > > IGNORE 1 LINES# if > > applicable > > ( First, Last, Title ) > > # actual order of your > > columns in your txt file, mapping columns names > > > > HTH > > Ignatius > > _ > > - Original Message - > > From: "JeRRy" <[EMAIL PROTECTED]> > > To: "Ignatius Reilly" <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > Sent: Monday, March 01, 2004 10:44 > > Subject: Re: [PHP-DB] plain-text file (.txt) to > > mysql database > > > > > > > Hi, > > > > > > So would I need to edit anything in my .txt file? > > > > > > Could you show me an example on what you mean by: > > > > > > you can specify which > > > columns of your text file > > > goes to what DB column. > > > > > > > > > > > > Because my text file has first , last , title > > > seperated by a space and a new entry is seperated > > by a > > > enter. (new line) > > > > > > I took a look at www.mysql.com and had a read on > > 'LOAD > > > DATA' but am not 100% sure on how it works with my > > > sort of .txt file setup. > > > > > > If you could show me an example that worked with > > my > > > .txt file that would be great, or would I need to > > edit > > > my .txt file somewhat? > > > > > > Sorry about this, never used a .txt file for use > > of > > > mysql before but since I need to do alot of > > entries at > > > once it would be the easiest way to do it. > > > > > > Thanks! > > > > > > > > > > > > > > > --- Ignatius Reilly <[EMAIL PROTECTED]> > > wrote: > > > > G'day, mate, > > > > > > > > With MySQL LOAD statement, you can specify which > > > > columns of your text file > > > > goes to what DB column. > > > > > > > > HTH > > > > Ignatius > > > > _ > > > > - Original Message - > > > > From: "JeRRy" <[EMAIL PROTECTED]> > > > > To: <[EMAIL PROTECTED]> > > > > Sent: Monday, March 01, 2004 10:08 > > > > Subject: [PHP-DB] plain-text file (.txt) to > > mysql > > > > database > > > > > > > > > > > > > Hi, > > > > > > > > > > Quick question, does anyone know of a > > program/site > > > > > that could do the following: > > > > > > > > > > I have a .txt file written in notepad. > > > > > > > > > > Bob Someone Mr > > > > > > > > > > Now I have a db with the fields: > > > > > > > > > > First Last Title > > > > > > > > > > Now the .txt file looks something like this: > > > > > > > > > > Bob Someone Mr > > > > > Susan Taylor Miss > > > > > Andrea Nothing Ms > > > > > ... > > > > > > > > > > Now I want to use a Program/site to put the > > names > > > > in > > > > > the right field. So each field is seperated > > by a > > > > > space as shown above. I have about 1000 > > entries > > > > to > > > > > put in and don't think doing each query per > > line > > > > is > > > > > going to be a 5 min job, any suggestions? > > > > > > > > > > Thanks! > > > > > > > > > > Find local movie times and trailers on Yahoo! > > > > Movies. > > > > > http://au.movies.yahoo.com > > > > > > > > > > -- > > > > > PHP Database Mailing List > > (http://www.php.net/) > > > > > To unsubscribe, visit: > > > > http://www.php.net/unsub.php > > > > > > > > > > > > > > > > > > > > Find local movie times and trailers on Yahoo! > > Movies. > > > http://au.movies.yahoo.com > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, visit: > > http://www.php.net/unsub.php > > > > > > > > > > Find local movie times and trailers on Yahoo! Movies. > http://au.movies.yahoo.com > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
In your case something like this will do: LOAD DATA INFILE "data.txt" INTO TABLE my_table FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n'# or '\n' - depending on your system IGNORE 1 LINES# if applicable ( First, Last, Title ) # actual order of your columns in your txt file, mapping columns names HTH Ignatius _ - Original Message - From: "JeRRy" <[EMAIL PROTECTED]> To: "Ignatius Reilly" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, March 01, 2004 10:44 Subject: Re: [PHP-DB] plain-text file (.txt) to mysql database > Hi, > > So would I need to edit anything in my .txt file? > > Could you show me an example on what you mean by: > > you can specify which > columns of your text file > goes to what DB column. > > > > Because my text file has first , last , title > seperated by a space and a new entry is seperated by a > enter. (new line) > > I took a look at www.mysql.com and had a read on 'LOAD > DATA' but am not 100% sure on how it works with my > sort of .txt file setup. > > If you could show me an example that worked with my > .txt file that would be great, or would I need to edit > my .txt file somewhat? > > Sorry about this, never used a .txt file for use of > mysql before but since I need to do alot of entries at > once it would be the easiest way to do it. > > Thanks! > > > > > --- Ignatius Reilly <[EMAIL PROTECTED]> wrote: > > G'day, mate, > > > > With MySQL LOAD statement, you can specify which > > columns of your text file > > goes to what DB column. > > > > HTH > > Ignatius > > _ > > - Original Message - > > From: "JeRRy" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Monday, March 01, 2004 10:08 > > Subject: [PHP-DB] plain-text file (.txt) to mysql > > database > > > > > > > Hi, > > > > > > Quick question, does anyone know of a program/site > > > that could do the following: > > > > > > I have a .txt file written in notepad. > > > > > > Bob Someone Mr > > > > > > Now I have a db with the fields: > > > > > > First Last Title > > > > > > Now the .txt file looks something like this: > > > > > > Bob Someone Mr > > > Susan Taylor Miss > > > Andrea Nothing Ms > > > ... > > > > > > Now I want to use a Program/site to put the names > > in > > > the right field. So each field is seperated by a > > > space as shown above. I have about 1000 entries > > to > > > put in and don't think doing each query per line > > is > > > going to be a 5 min job, any suggestions? > > > > > > Thanks! > > > > > > Find local movie times and trailers on Yahoo! > > Movies. > > > http://au.movies.yahoo.com > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, visit: > > http://www.php.net/unsub.php > > > > > > > > > > Find local movie times and trailers on Yahoo! Movies. > http://au.movies.yahoo.com > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
Hi Ricardo, Yep that is why I asked for website/Program already established. Don't have the time to do what is mentioned as this is very urgent and needs to be done quicker rather than later. Short notice posting I know but anything is worth a try. Thanks for your suggestion. J --- Ricardo Lopes <[EMAIL PROTECTED]> wrote: > I know this might sound a little bad, but i would > use Perl or an editor that > supports regulars expresions to convert the data > into insert statements and > after that i would use a database administration > tool (ex. myCC) to insert > the data, you could put some statements before doing > the inserts to disable > foreign key checks and others things to speed up the > process, but in your > case i think this would have no effect in the speed. > > > - Original Message - > From: "JeRRy" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, March 01, 2004 9:08 AM > Subject: [PHP-DB] plain-text file (.txt) to mysql > database > > > > Hi, > > > > Quick question, does anyone know of a program/site > > that could do the following: > > > > I have a .txt file written in notepad. > > > > Bob Someone Mr > > > > Now I have a db with the fields: > > > > First Last Title > > > > Now the .txt file looks something like this: > > > > Bob Someone Mr > > Susan Taylor Miss > > Andrea Nothing Ms > > ... > > > > Now I want to use a Program/site to put the names > in > > the right field. So each field is seperated by a > > space as shown above. I have about 1000 entries > to > > put in and don't think doing each query per line > is > > going to be a 5 min job, any suggestions? > > > > Thanks! > > > > Find local movie times and trailers on Yahoo! > Movies. > > http://au.movies.yahoo.com > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: > http://www.php.net/unsub.php > > > > > Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
Hi, So would I need to edit anything in my .txt file? Could you show me an example on what you mean by: you can specify which columns of your text file goes to what DB column. Because my text file has first , last , title seperated by a space and a new entry is seperated by a enter. (new line) I took a look at www.mysql.com and had a read on 'LOAD DATA' but am not 100% sure on how it works with my sort of .txt file setup. If you could show me an example that worked with my .txt file that would be great, or would I need to edit my .txt file somewhat? Sorry about this, never used a .txt file for use of mysql before but since I need to do alot of entries at once it would be the easiest way to do it. Thanks! --- Ignatius Reilly <[EMAIL PROTECTED]> wrote: > G'day, mate, > > With MySQL LOAD statement, you can specify which > columns of your text file > goes to what DB column. > > HTH > Ignatius > _ > - Original Message - > From: "JeRRy" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, March 01, 2004 10:08 > Subject: [PHP-DB] plain-text file (.txt) to mysql > database > > > > Hi, > > > > Quick question, does anyone know of a program/site > > that could do the following: > > > > I have a .txt file written in notepad. > > > > Bob Someone Mr > > > > Now I have a db with the fields: > > > > First Last Title > > > > Now the .txt file looks something like this: > > > > Bob Someone Mr > > Susan Taylor Miss > > Andrea Nothing Ms > > ... > > > > Now I want to use a Program/site to put the names > in > > the right field. So each field is seperated by a > > space as shown above. I have about 1000 entries > to > > put in and don't think doing each query per line > is > > going to be a 5 min job, any suggestions? > > > > Thanks! > > > > Find local movie times and trailers on Yahoo! > Movies. > > http://au.movies.yahoo.com > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: > http://www.php.net/unsub.php > > > > > Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
G'day, mate, With MySQL LOAD statement, you can specify which columns of your text file goes to what DB column. HTH Ignatius _ - Original Message - From: "JeRRy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 01, 2004 10:08 Subject: [PHP-DB] plain-text file (.txt) to mysql database > Hi, > > Quick question, does anyone know of a program/site > that could do the following: > > I have a .txt file written in notepad. > > Bob Someone Mr > > Now I have a db with the fields: > > First Last Title > > Now the .txt file looks something like this: > > Bob Someone Mr > Susan Taylor Miss > Andrea Nothing Ms > ... > > Now I want to use a Program/site to put the names in > the right field. So each field is seperated by a > space as shown above. I have about 1000 entries to > put in and don't think doing each query per line is > going to be a 5 min job, any suggestions? > > Thanks! > > Find local movie times and trailers on Yahoo! Movies. > http://au.movies.yahoo.com > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
I know this might sound a little bad, but i would use Perl or an editor that supports regulars expresions to convert the data into insert statements and after that i would use a database administration tool (ex. myCC) to insert the data, you could put some statements before doing the inserts to disable foreign key checks and others things to speed up the process, but in your case i think this would have no effect in the speed. - Original Message - From: "JeRRy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 01, 2004 9:08 AM Subject: [PHP-DB] plain-text file (.txt) to mysql database > Hi, > > Quick question, does anyone know of a program/site > that could do the following: > > I have a .txt file written in notepad. > > Bob Someone Mr > > Now I have a db with the fields: > > First Last Title > > Now the .txt file looks something like this: > > Bob Someone Mr > Susan Taylor Miss > Andrea Nothing Ms > ... > > Now I want to use a Program/site to put the names in > the right field. So each field is seperated by a > space as shown above. I have about 1000 entries to > put in and don't think doing each query per line is > going to be a 5 min job, any suggestions? > > Thanks! > > Find local movie times and trailers on Yahoo! Movies. > http://au.movies.yahoo.com > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] plain-text file (.txt) to mysql database
Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] plain-text file (.txt) to mysql database
Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] problem to connect mssql DB under iis6
Hi, I'am currently migrating my servers from nt4/IIS4 to Windows server 2003/IIS6, my db is under NT4/SQL7 Secondary domain controller, from iis4 no problem to access (guest user for the web site), from iis6, the only way to connect DB is to run the web site under domain administrator the problem exist for all my sites under PHP, no problem for sites in asp... i use phpbb forum, for example... any idea ? -- Yannig -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php