-
HWD to MySQL?
Date: 08/13/06 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. -
caching kicks ass
Date: 08/07/06 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. -
Organisations that change
Date: 08/07/06 Keywords: database
I'm developing a database of organisations with all the familiar field names that you would expect e.g., name of organisation, various address fields, office bearer fields etc.
However I also want to have a "Year" field, so I can lookup information about the organisation in 2002, 2003, 2004, 2005 etc as the organisations I'm keeping track of have a high turnover of office bearers and, of course, occassionally address (and a couple have even changed their name).
One method would be have a table for each year for each organisation, but that offends my sense of optimisation and normalisation.
Any suggestions? -
LOAD DATA INFILE and AUTO_INCREMENT
Date: 07/25/06 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
There are 140 lines in stats.txt, fields are terminated by a tab, lines are terminated by a new line. I get 140 new entries, but "id" column (auto_increment) is "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 | +------+----------------+------------+------------+
So, that is the problem. I just wonder this is a very common question, but I am new in databases...
Any of your help will be appreciated.
Best,
Leo -
php/mySQL question
Date: 07/20/06 Keywords: php, mysql, sql
Hey guys, mild programmer here. I've got a design question for you.
I've made a mySQL/PHP site for the purpose of displaying the comics I make :) It works pretty well, but now I'm looking to refine my interface with it. One of my problems is with the primary key; currently I have it defined to auto_increment. The annoying thing about this is if I delete a comic and add a new one, the primary key will number it after the old one, not after the last one on the table, so that there will be a gap in the numbering. My code currently relies on the primary keys to be continuous... I know in an ideal world I'd program to handle gaps but anyway, this is just a personal site XD And ideally I would like the primary keys to be continuous even if I did have it programed to handle gaps, just because I'm a bit OC like that :P
Anyway, I'm wondering how to make the tables easier to edit. Should/can I take out the auto_increment command and just assign a key 1+ the last key in the table via PHP, when I upload a new comic? Should I do something complicated like load the edited info to a temp new table, destroy and recreate the old table, and repopulate it with the new list each time I delete a row? Or is there a command that will reset the auto_increment feature to the last primary key (or to a number of my choosing)?
Thanks!
EDIT: on looking around online more, it seems the common advice is to drop then readd the ID column, which will automatically renumber the rows. Seems a little sketchy, but if it works I guess it'd do. Thoughts? -
Newbie trying to create a table
Date: 07/10/06 Keywords: mysql, software, database, sql, web
I'm trying to create a database to record all the ENV variables for website visits. I haven't used mysql before, though I have a good deal of experience with MS Access and DB2, so I feel confident when working with SQL.
I created a database called sitetrack, connected to it with:
use sitetrack;
and tried creating a table with the following statement:
CREATE TABLE sitetrack(
VISIT_DATE DATETIME,
QUERY_STRING VARCHAR,
SERVER_ADDR VARCHAR,
HTTP_ACCEPT_LANGUAGE VARCHAR,
SERVER_PROTOCOL VARCHAR,
TZ VARCHAR,
HTTP_CONNECTION VARCHAR,
HTTP_REFERER VARCHAR,
REMOTE_PORT VARCHAR,
HTTP_USER_AGENT VARCHAR,
HTTP_ACCEPT VARCHAR,
GATEWAY_INTERFACE VARCHAR,
HTTP_HOST VARCHAR,
SERVER_SOFTWARE VARCHAR,
SERVER_ADMIN VARCHAR,
REMOTE_ADDR VARCHAR,
SCRIPT_NAME VARCHAR,
SERVER_NAME VARCHAR,
HTTP_ACCEPT_ENCODING VARCHAR,
DOCUMENT_ROOT VARCHAR,
REQUEST_URI VARCHAR,
HTTP_ACCEPT_CHARSET VARCHAR,
REQUEST_METHOD VARCHAR,
SCRIPT_FILENAME VARCHAR,
HTTP_KEEP_ALIVE VARCHAR,
PATH VARCHAR,
SERVER_PORT VARCHAR);
When I do this I get the following error:
ERROR 1064 (42000): 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 '
SERVER_ADDR VARCHAR,
HTTP_ACCEPT_LANGUAGE VARCHAR,
SERVER_PROTOCOL VARCHAR,
TZ ' at line 3
It looks to me that there's something wrong with the "SERVER_ADDR VARCHAR," line, but I can't see what. I don't see SERVER_ADDR listed in the reserved words on any document I've come across. Could someone offer any advice or direct me to documentation that would help me figure this out? -
curdate() not workming?
Date: 07/06/06 Keywords: mysql, database, sql
Maybe it's the code or something else, but... should this work? I am trying to export some data from the database.
-----------------------------------
$now1 = curdate();
$now2 = $now1[0];
$future = $now2 + 2419200;
$query = "SELECT * FROM ibf_cal_events WHERE event_unix_from BETWEEN $now2 AND $future ORDER BY event_unix_from";
$result = mysql_query( $query );
$start = 1;
$end = mysql_num_rows( $result );
$items=0;
$title_array = array ();
$context_array = array ();
while( $start <= $end ) {
$row = mysql_fetch_row( $result );
$id = $row[0];
-----------------------------------
This gave me an error message:
Fatal error: Unknown function: curdate() in /importcalevent.php on line 18
Line 18 is $now1 = curdate();
I tried $now1 = getdate(); and I got this error message:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /importcalevent.php on line 31
Line 31 is $end = mysql_num_rows( $result );
So I am not sure whch is correct and which is incorrect.
I am using MySQL 4.1
Thanks all. -
MySQL is used in most of the freelance projects
Date: 07/05/06 Keywords: mysql, sql, postgresql
Statistical research based on analysis of more than 15000 freelance projects posted to getafreelancer.com (one of the biggest freelance sites along with scriptlance.com and others) shows that DBMS MySQL was used in 78.8% of completed projects, MS SQL Server – in 3.6%, PostgreSQL – in 0.62% http://freelancejob.com.ua/content/v
iew/391/4/#en -
SPIP
Date: 06/29/06 Keywords: no keywords
i don't know if this is the right place, but are there any SPIP users? I'm a complete newbie to everything and am looking for some solid tutorials/ help!
-
HELP: transferring database files into another computer
Date: 06/28/06 Keywords: mysql, browser, database, sql
My friends (team-members) and I are working on a Final Year Project, and we have a problem with mySQL!
My team member has a complete database of our project, but i'd like to copy her database into my laptop and work from there too.
I've copied the folder 'fyp' (C:\Program Files\MySQL\MySQL Server 5.0\data\fyp) into the same directory in my laptop.
The following happened:1. The database 'fyp' showed in mySQL query browser.
2. 'could not fetch columns' error appeared under this database in mySQL query browser.
3. Under "Catalogs" in mySQL administrator, the schemata 'fyp' is there, but it doesnt show its tables when i clicked on it.
DID I MISS OUT ANY FILES? DO I HAVE TO COPY ANY OTHER FILES OTHER THAN THE DATABASE 'FYP' FOLDER?
Kindly advise!
Thanks!
With love,
the mySQL noob. =/ -
mysql_connect woes
Date: 06/23/06 Keywords: php, mysql, asp, sql
I have a very simple script set up to verify weither or not I can connect to mySQL from a PHP script.
[bracket]?php
$link = mysql_connect('localhost', 'root', 'XXXXXXXX');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?[bracket]
I recieve the following error:
Could not connect: Can't connect to local MySQL server through socket '/var/mysql/tmp/mysql.sock' (13)
I tried replacing 'localhost' with 127.0.0.1 .
Now I have a completely different error message:
Could not connect: Lost connection to MySQL server during query
First of all I'd like to know why I can't just use 'localhost' and second, I'd like to get this damn thing to work! I'm really frustrated. I hope someone can help. I've searched all over the internet for a solution to my problem but am having no luck. mySQL is running fine, and the mysql.sock file is there where it should be. I can also log in from the command line. I am exasperated!
I am running Slackware 10, mySQL 5.0.21-log, and PHP 4.4.2.
Any ideas helping me out would be much appreciated. I need this to work! -
Master-Master replication configuration
Date: 06/23/06 Keywords: no keywords
I'm wondering exactly what I need to do to setup a Master-Master replication setup (the replication part anyway) as all I'm seeing is steps to set up just a plain Master-Slave setup. Do I just set the Masters to be slaves to each other?
-
SuperID
Date: 06/17/06 Keywords: database, sql
I am looking for work in the Atlanta area and I have been taking skills tests for the last two weeks. One of the companies that I went to had this strange(to me) concept of a super id. All their tables in all their databases had an auto incrementing id plus a superId that was unique through the database. They told me to only use the superID to deal with the data. They had some functions that would handle the superID and I just had to pass them SQL. The functions also kept track of date created and date modified for each record. Each table would look something like this:
superID | date_modified | date_created | table_id |~ fields for the table ~|
I get that its easier to use in some ways but they could not really explain the concept to me. Has anyone ever heard of this or done it? -
GoDaddy
Date: 06/17/06 Keywords: php
I have a domain hosted at godaddy and it won't install slq or regonize/read/load php pages properly. Can anybody please please help me?
-
Primary keys, automated entry?
Date: 06/15/06 Keywords: database
Thanks for the responses to my last post, I now have a better understanding regarding primary keys and natural/foreign keys.
Now, after reading online about the topic, I don't see any clear explanation on how the keys are produced. For instance, say I have a foreign key, auto-increment, if I want to enter a song in the database, and it already has the artist entered previously, will it automatically know to give it the right artists_id key if I tell it the name of the artist? Does that make sense? I would assume if the artist isn't already in the database, it will add it and increment the primary key (artist_id), and if it is, it'll just use the primary key that already exists for that artist. -
Updating several rows
Date: 05/31/06 Keywords: no keywords
Help with a query
array = 298,300,297,302,305,296,291,306,
UPDATE table SET billed="Yes", invoice='xxx" WHERE record [in array];
How do I update several rows at a time? (Record is the auto incrementing index number of the rows.) -
Newbie question
Date: 05/23/06 Keywords: php
Hi all.
I created a Job Bulletin for my company, and although it's working just fine, I have been unable to get the table to sort by state automatically.
Right now, I have this script in my php file:
$query_JobForms = "SELECT * FROM job_list ORDER BY job_list.job_state ASC LIMIT 0, 30";
However, every time a new job gets added, it just goes to the top and doesn't order by state. What am I doing wrong? -
skip networking in windows, worth it or no?
Date: 05/18/06 Keywords: mysql, browser, sql, web
Hey folks, another newbie-to-mysql-on-windows question here, and many thanks again to the help on my previous one...
In my previous mysql experience on unix-like platforms, it was generally considered a Good Thing to turn off networking if you didn't "need" it, just talking directly to the mysqld instead. In cases where a local web stack on the same box was all that ever needed to talk to mysql, this worked well, and unless I'm smoking crack, it even seemed to perform a little better than chatting on 3306 using TCP/IP and worrying about telling either the daemon or the firewall (or both) to restrict such conversations to localhost.
Somewhat naively, I tried to do this in Windows today and got some unexpected results.
I'm working with an old version of coldfusion and modern mysql and recently discovered that rather than the "use an old version of Connector J" method mentioned in the Macromedia knowledge base, I can just install the nice current version of the mysql ODBC Connector, then set up a system DSN right in Windows. I tell coldfusion to use this "ODBC socket" and poof, things seem pretty quick. So great, I think, now that I'm not using the old Connector J which seemed to require a TCP/IP method, now I should be able to skip networking in the mysql daemon and just tell the system DSN config where the "socket" is.
Not so simple! I turned off networking (using mysql Administrator, since I'm trying to "be GUI" about this) and as expected, it uncommented "skip networking" in the my.ini file. However, after this, Windows couldn't start the mysql service anymore. Furthermore, the Administrator and Query Browser didn't seem to be able to connect without a port number. As soon as I re-enabled networking by editing the my.ini file, everything was fine.
Did I miss something? Am I smoking crack? Should I just leave well enough alone and use networking with mysql configured to only allow connections from localhost and Windows told not to expose 3306 to the outside world?
All opinions welcome - just please don't suggest "use something other than crutsy old cold fusion" because unfortunately that's not really an option. TIA! -
looking for a good, free GUI mysql client for windows
Date: 05/16/06 Keywords: php, mysql, browser, database, sql, web
I'm currently working on re-engineering a web stack completely to use mysql as a back end instead of (shiver) a bunch of Access files. I've used mysql for years both personally and professionally, but being a UNIX dork I've almost exclusively done so from the command line. Whenever I've needed something a little more user-friendly than that, I've installed a web tool like phpmyadmin.
However in this case, I'm working in an all-Windows shop (mostly server 2003) and I know that I'm going to be passing this on to someone in a couple years. As I document various tasks, I'd like to use a desktop client with a GUI interface for doing things like "browsing" the database, modifying stuff now and then, etc.
I see that mysql.com has a nice "administrator" and "query browser" available and they're pretty nice - but neither is really what I'm looking for in terms of something that has a GUI interface and actually manipulates the data itself. Unfortunately I cannot count on a successor knowing much SQL or being comfortable on the command line.
I'm guessing there are many free tools out there but would love to start out on whatever people who already love to use such things find the most mature, featureful, easy to use, etc. So, any suggestions? TIA!
EDIT - solved, thanks! -
moving hosts
Date: 05/07/06 Keywords: php, mysql, database, sql, web
I'm currently moving hosts, from a host with mySQL 4.1.18 to 4.0.25. The site was made with a php templating program called website baker. For some reason when I backup the database in mySQL 4.1.18 (with compatibility for earlier versions) and I try to upload the database using cpanel in the new one, it just doesn't recognize anything and won't restore the database. Any ideas on how I could get around this?
x posted in mysql and php_mysql
| Previous page | || | Next page |