Date: 07/21/06 (PHP Community)    Keywords: mysql, sql

i have this function the reads a csv file (it's actually a pipe-separated file...), exported from access and dumped into mysql. after some formatting and setting things up, i start a while... loop, reading each line of the file and updating or inserting into the table as necessary. my problem is that after the while loop, the script seems to just die. everything is updated/inserted correctly, but it just stops after the loop. the "report" code never happens. the stuff that executes outside of the function after i call it never happens. can anyone see anything wrong with this? i can post the entire function if you'd like.


while (!feof($csv_file)) { $line = fgets($csv_file); $line = str_replace('"','',$line); $line = explode('|',$line); //check for existing row $sql = "SELECT `id` FROM `cards` WHERE `record_number` = {$line[0]}"; $res = mysql_query($sql); //row exists, update (might be faster to check for changes first...) if(mysql_num_rows($res)) { $updated_rows++; foreach($line as $key => $val) { $update_data[$csv_fields[$key]] = addslashes($val); } foreach($update_data as $key => $val) { $sql = "UPDATE `cards` SET `$key` = '$val' WHERE `record_number` = {$line[0]}"; mysql_query($sql) or die("tragic error: failed to update record ".$line[0]." <br/> $sql"); } } //row doesn't exist, add else { $inserted_rows++; foreach($line as $key => $val) { $insert_data[$csv_fields[$key]] = addslashes($val); } $sql = "INSERT INTO `cards` ("; foreach($insert_data as $key => $val) { $sql .= "`$key`,"; } $sql = rtrim($sql,','); $sql .= ") VALUES ("; foreach($insert_data as $key => $val) { $sql .= "'$val',"; } $sql = rtrim($sql,','); $sql .= ")"; mysql_query($sql) or die("tragic error: failed to insert record ".$line[0]." <br/> $sql"); } } //report! echo "<strong>RESULTS</strong><br/><br/>"; if (count($inserted_columns) > 0) { echo "added the following fields:<br/>"; foreach($inserted_columns as $col) { echo "    ".$col."<br/>"; } } echo "<br/>updated $updated_rows existing rows"; echo "<br/>inserted $inserted_rows new rows"; //clean up fclose($csv_file); echo "closed file";

Source: http://community.livejournal.com/php/473329.html

Date: 07/25/06 (MySQL Communtiy)    Keywords: mysql, database, sql

hi all

I'm trying to import data into a table with a column that has AUTO_INCREMENT:

MYSQL> desc stats;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned |      | PRI | NULL    | auto_increment |
| date  | bigint(14) unsigned | YES  |     | NULL    |                |
| ent1  | varchar(10)         |      |     |         |                |
| ent2  | varchar(10)         |      |     |         |                |
+-------+---------------------+------+-----+---------+----------------+

LOAD DATA INFILE "stats.txt" INTO TABLE stats (date, ent1, ent2);
Query OK, 140 rows affected (0.01 sec)
Records: 140  Deleted: 0  Skipped: 0  Warnings: 0

+------+----------------+------------+------------+
| id   | date           | prid       | did        |
+------+----------------+------------+------------+
          
| 3870 | 20060725133314 | FFFFFFFFFF | f111111111 |
| 3871 | 20060725133613 | FCCCCCCCCC | f222222222 |
| 3872 | 20060725133722 | FDDDDDDDDD | d333333333 |
|  0   | 20060704011139 | FVVVVVVVVV | e111111111 |
|  0   | 20060704104952 | LSSSSSSSSS | f222222222 |
          
|  0   | 20060720161936 | FDDDDDDDDD | d111111111 |
+------+----------------+------------+------------+

Source: http://community.livejournal.com/mysql/100368.html

Date: 07/29/06 (PHP Community)    Keywords: mysql, sql, security, web

Any problems with using error_log with parameter 1 (email) that I should be aware of? I just discovered it while researching something else and would like to abuse the hell out of it for production website. It would be nice to suppress all error messages and instead have them piped to a dedicated email address.

Other question... security. Any recommended reads, blogs, notes, what not on the subject? And I mean anything and everything.


Also, turns out I mistakingly posted this to another community by accident, so to save time... this is what I mean by error_log()

$link = mysql_connect("www.myServer.com","myAccount","password");
if($link == false)
{
$errMsg = "SITE: www.myServer.com\n";
$errMsg .= __FILE__ . "@" . __LINE__ ."\n";
$errMsg .= "Detail: Unable to connect to mysql server\n";
$errMsg .= "mysql_error: " . mysql_error() . "\n";
error_log($errMsg, 1, "errMsg@myOtherServer.com");
//then die or skip rest of script
}


And while I am on the subject, is there anything equivalent to c/c++ #define.  I really miss that and it would come in handy with something like the above code.

Source: http://community.livejournal.com/php/477454.html

Date: 08/05/06 (WebDesign)    Keywords: cms, php, mysql, database, sql, web, hosting, shopping

Sooo, I am 98% sure I'm taking on a client where I need to implement a CMS for him to make changes to the site if necessary. When I first wrote the quote a few months ago, I suggested Drupal as the CMS because it was free, looked rather customizable and somewhat easy to use. I have limited experience writing PHP and dealing with MySQL databases but understand the mechanics of it.

This particular client is offering subscriptions to a database of business industry contacts. There will be about 500 contacts on the pay list (including name, office, affiliation, etc) and he will add to the list about every three months or so. What he will change on a regular monthly basis will be the articles he writes. The subscription concept then requires a shopping cart/check out system as well as a password/username management system for each user.

Would Drupal be a good way to go for both my client and for me? Also, I'm switching web hosts and am considering becoming a reseller. Is there a web host that has it installed already? Door Host ( http://www.doorhost.net/ ) used to have it, but I don't think they do anymore. I'm considering going with Total Choice Hosting buying either their basic reseller plan or the regular Deluxe Plan.

Nonetheless, who has used this or other comparable CMSs and which do you prefer and why?

Drupal: http://www.drupal.org/

Sites built with Drupal:
http://www.terminus1525.ca
http://urlgreyhot.com/personal/
http://www.spreadfirefox.com/
http://www.theonion.com/
http://www.echoditto.com/

Oh, I just found this Drupal web hosting company called Site Ground that has all of the major CMSs installed already and gives you 24,000 MB of space. Anyone heard of them? I also found these guys: Canaca.com.

Source: http://community.livejournal.com/webdesign/1146967.html

Date: 08/07/06 (MySQL Communtiy)    Keywords: php, mysql, html, sql, web

in my last post -
http://community.livejournal.com/mysql/100762.html

I asked about how to speed up a query using many NOT LIKE %45% statements. I am starting to understand what I need to do, thanks to some advice. Since I am a newbie sql wannabee, it will take me a while. I am still not there yet, I think the best plan is to make another column tinyint, and mark the records I do not want to show. I am not quite sure of they syntax and how to do "if this, insert this data into the records column tinyint" so that I use that as a marker of a record NOT to show. I would figure I do that every once a month or so to mark all the records I don't want to show during a query. Any other ideas? Maybe it's easier to just filter them in the php web code?

One thing I did find out was that mysql had query_cache_size = 0. I set it to 80 megabytes and now any duplicate query is instant!! Nice. . .I was wondering why it couldn't cache duplicate searchs and some other things like that.

Source: http://community.livejournal.com/mysql/101221.html

Date: 08/11/06 (PHP Community)    Keywords: mysql, sql, java, web

What I got now:

SignUP

Phase 1
Basic webform with Javascript helper script that isn't required.

Phase 2
Collect user information for sale from previous form, validate/normalize the information (anti-everything but alphanumeric except ' and -), insert information into DB with pending flag.

Embed record ID into hidden form field
Present user with sales choice (invoice, electr invoice, credit).

Phase 3
Grab the embedded form field, validate ID by "x = (int) y;", and compare REMOTE_ADDR to the DB Record, and update record via a super-limited DB user account ( it only has privileges set by MySQL to update "accountInfo.payMethod" and select "accountInfo.ID" and "accountInfo.IP"

Display confirmation page of their order then either jump to the CCard service and out of my responsiblity.


I can't see how someone could break this system as it stands, but I am tempted to do the entire thing with Session handling, passing the SID to the second form and retrieving it on the third to get $_SESSION['myID'] just to make it even harder to break.

Source: http://community.livejournal.com/php/481637.html

Date: 08/13/06 (MySQL Communtiy)    Keywords: mysql, html, sql, web

I have these files generated by a perl script, called something.hwd.

Format of each file is the title separated by a pipe symbol and then the html of the article. Example:

This is the title | Some html about the article goes here

Does anyone know how to mass import about 500 of these files into MySQL as records in a single table? I was going to write a script that would grab the date and time stamp of each file and then make the file format like this

2006-06-06 | Title | The HTML

I almost had it working but some records were skipped, I was using mysql's import command.

Anyway, if you can't figure it out, no problem. The client is willing to start with a fresh website.

Source: http://community.livejournal.com/mysql/101402.html

Date: 08/14/06 (WebDesign)    Keywords: mysql, database, sql, web, google

ok i've picked a new web host. i googled this but couldn't make it give me an answer. i would like to transfer emails that are stored on my current host's server to the new host and make them viewable thru the new host's webmail. is this kind of thing possible? would it depend on the web host and the email program they use?

also i would like to transfer my message board as well. would it be as simple as downloading the mysql database and uploading it to the new server?

thanks again for all your help!

Source: http://community.livejournal.com/webdesign/1150308.html

Date: 08/17/06 (PHP Community)    Keywords: php, mysql, sql

   I've been thinking about an anti-flood mechanism for php that doesn't involve some sort of captcha method.  I looked into using shared memory but its back to the same problem of having a race condition between scripts.  As a temporary work around I guess I could just use a MySQL table, but I was hoping for something with a smaller memory and speed foot print.  Anyone got any idea's?

Example of what I mean:
  
   If 10000 hits are made in less then 30 minutes to a php script, the script has a switch at the beginning that immediately dies(). 

I thought of a cron script that runs every 30 minutes and counts the number of new lines from an offset in the record then adds "deny from $userIP" under the Order deny,allow directive of .htaccess, but was wondering if there is anything else before I start making a bunch of regEx's to parse the access log.

Source: http://community.livejournal.com/php/485252.html

Date: 08/17/06 (PHP Community)    Keywords: php, mysql, sql, google

Hello, I am new you can call me Kristi.

I have been searching google for a php calendar for a few days now, and haven't found one that I like. I specifically looking for one that just lists the dates of important events not an actual calendar. I've looked on codegrrl.com but I couldn't find the calendar my friend Crystal used. If you have any suggestions please post them!

Or if you just created a php calendar and want someone to try it out then that's fine too.

Oh by the way, I'm pretty new at PHP so I want a simple calendar that is easy to install also my server doesn't support MYSQL which can be a problem!

Thanks so much!

Source: http://community.livejournal.com/php/484796.html

Date: 08/27/06 (Java Web)    Keywords: mysql, sql

Suddenly my Hard-disk & MySQL Server had gone read-only, thereby making this blog (because it is cached) and other sites inaccessible, including ssh access. Do you have any idea why this could have happened? A hard reboot rectified the error for now. But I am concerned about what caused this in the first place. Any ideas? Any [...]

Source: http://blog.taragana.com/index.php/archive/help-hard-disk-mysql-server-suddenly-read-only/

Date: 08/29/06 (Web Development)    Keywords: php, mysql, software, database, sql, linux, hosting

I see that this forum recommends Dreamhost. How long have you been using it? Are they located in the U.S.? I don't know if the latter is necessary, but I guess if a host goes down and loses everyone's files, there could be a group law suit with some recovery. I don't know what to worry about the most, but I did go with a service once that suddenly disappeared. Luckily I had a techie who found the files still available somewhere and transferred them to another server for me.

I'm not a "techie". I'm also not a "designer" so if you check out my sites, please be kind on the critique of their appearance (search engine optimization and user-friendly access is my biggest concern).

I'm presently using a really cheap hosting service for one of my sites, 3ix. My son has used it for 2 yrs. with no problem. Has anyone here used them?

I have a site I need to move over to a server with Linux, MySQL, lots of database space available, lots of free software available, including PHP. 3ix offers a lot of software. Any suggestions would be appreciated.

Source: http://community.livejournal.com/webdev/350691.html

Date: 08/30/06 (MySQL Communtiy)    Keywords: mysql, sql

When I try to remotely connect to mysql, I'm getting the collowing error:

ERROR 2003: Can't connect to MySQL server on 'subdomain.domain.edu' (111)

As far as I can tell, "skip-networking" has been commented out in my.cnf -- a pound sign in the front means commented out, as far as I know. There is a /tmp/mysql.sock file, which should mean there's an available socket. I've compared my server against a test server I use, and the my.cnf files look pretty much identical.

The only different that I know of between my server at the test server is that the real server is behind a firewall, with packets being routed via snat. That might be my problem, because I can't seem to telnet to port 3306 from anywhere either.

Any help would be greatly appreciated. Thanks!

Source: http://community.livejournal.com/mysql/102128.html

Date: 08/31/06 (MySQL Communtiy)    Keywords: mysql, sql, web

      Hello, I just found the MySQL community and figured this could be an educational group to watch.  I am predominately a web developer 1st, and a DB scheme designer by necessity.  Otherwise I learned SQL on Access in 1999 then it took me 3-4 years to unlearn the MS way and now prefer MySQL.  So yeah, that's me.

Now my fun filled question:   Is there some sort of way to join 'SHOW TABLES' and 'SHOW FIELDS FROM' in a SQL query?  Maybe a multi-line query or such that is dynamic.  I've been rummaging through the MySQL reference manual and found nothing so far... though I did try the crazy idea of DESCRIBE (show tables); which was a long shot that did nothing.

Update:
Got this from beskov's comment.

SELECT table_name, column_name
FROM Information_Schema.columns;

And added WHERE to focus just on the DB I am interested in.

SELECT table_name, column_name
FROM Information_Schema.columns
WHERE table_schema = 'myDbName';

Source: http://community.livejournal.com/mysql/102165.html

Date: 09/02/06 (MySQL Communtiy)    Keywords: mysql, database, sql, hosting

Anyone know of a freeware tool to Synchronize a MySQL testing server on my machine, a MySQL remote server on my firm's server, and a MySQL client's server. Trick is that the database name's for each server is different according to each server's hosting scheme. Local has no prefix but the firm and client server's have prefix's. I have to do it in stages test->firm, test->client... that's fine because its better then nothing.

otherwise I am just going to write a script to try and transfer data between the servers with the testing server being the master of the others.

Source: http://community.livejournal.com/mysql/102449.html

Date: 09/19/06 (Web Development)    Keywords: php, mysql, css, html, sql, java, web

Greetings to all,


Hello! I'm new to the WebDev community and was looking for a place to share my thoughts about web development. I'm into HTML and currently learning PHP and MySql. I can edit just about anything though :) (JavaScript, CSS whatever). I would like to make a web based game in PHP perhaps using a flash GUI. But not sure yet. Anyway thats about it just thought I'd introduce myself :)


Black Knight

Source: http://community.livejournal.com/webdev/353713.html

Date: 09/21/06 (MySQL Communtiy)    Keywords: php, mysql, database, sql

My laptop dual boots Windows XP and Ubuntu with a shared FAT32 drive they can both access. I'm working on a PHP project and ideally I would like both XP and Ubuntu to be able to access the same databases. Does anybody know if this is possible? ie, install MySQL on both OSes but somehow have them look on the shared drived for the actual database files? Any pointers / guides you guys could give me would be greatly appreciated!

Source: http://community.livejournal.com/mysql/103467.html

Date: 09/22/06 (Java Web)    Keywords: php, mysql, sql, linux, apache

I have started developing an interesting PHP application for LAMP (Linux-Apache-MySQL-PHP) stack. I have to say it has been an wonderful experience so far. It is rather simple (really simple) and easy to setup little frameworks (read utility functions in a file) in php for your tasks. PHP has a delicious array of functionalities to use. [...]

Source: http://blog.taragana.com/index.php/archive/thoughts-on-professional-php-development/

Date: 09/25/06 (PHP Development)    Keywords: php, mysql, xml, sql, web

В настоящий момент мы ищем ведущего web программиста c серьезным опытом и отличным знанием PHP 5, MySQL, XML/XSLT, ООП для участия в разработке и развитии web проекта, основаного на концепции web 2.0 - www.comby.com.

Что мы предлагаем?
Работа с технологиями нового поколения, по-настоящему дружный коллектив, а также достойный профессионала уровень оплаты труда позволят Вам в полной мере реализовать свой профессиональный потенциал*.
*Заработная плата от 2000$
**На постоянную работу, полный рабочий день.

Контактная информация:
E-mail: comby-personal@comby.com
Тел: (495) 245-63-70
Контактное лицо: Антон
________________________________________
Web Life - Интернет компания c многолетним опытом создания и развития Интернет проектов в области телекоммуникаций.
Основное направление деятельности в России– развитие и продвижение проектов www.comby.com  и www.comfi.com .

Source: http://community.livejournal.com/php_dev/72522.html

Date: 09/25/06 (MySQL Communtiy)    Keywords: mysql, database, sql

Hello,

I'm making a wallpaper database for my site, and I have two tables: wallpapers and wpartist. The wallpapers table holds all of the wallpaper info (obviously), while the wpartist table has the info on the artists who made the wallpapers. The two tables are JOINed by the artistid field.

Right now I'm able to show all wallpaper results, but I would like to also be able to show only a certain category, like a specific character. Is it possible to do that now that WHERE is being used by the joined tables? Here is the code I'm using now:

$result = mysql_query
("SELECT * FROM wallpapers, wpartist
WHERE wallpapers.artistid = wpartist.artistid
order by date desc");


I hope you guys can help! =)

Source: http://community.livejournal.com/mysql/104162.html