Date: 05/10/05 (MySQL Communtiy)    Keywords: mysql, database, sql, web

I inherited a site with a mysql back-end. It was rather painful to get a understanding of what the heck the previous person was doing (it was not pretty). So adding to the db was ok, and making a slight modification here and there was ok. Now the pain came. The client wants to be able to search the database via part numbers (and then return the page it is on). I think to my self, hey no problem right?
bzzt wrong.
I have PartNumber, partnumber, quarter_partnumber and half_partnumber; also each product category is its own table in the database (for a total of ~45 tables). I have done some reading up on this and I believe a join could help.
now, should I bother with a huge join, try to search each table by its self (time wise very costly), or ?
let alone once I find the correct partnumber I have to then find a way to attach the webpages (a second db or a new field in each record).

my brain hurts with the stupid

Source: http://www.livejournal.com/community/mysql/56150.html

Date: 05/10/05 (MySQL Communtiy)    Keywords: mysql, sql

Does anyone have a mysql reporting tool that they like? A client of mine asked me about it and I’ve always used a homegrown one. We may continue to use my homegrown “reportmaker” but I thought it would be worth it to ask.

Source: http://www.livejournal.com/community/mysql/56347.html

Date: 05/11/05 (Web Development)    Keywords: php, mysql, asp, sql, security, linux, microsoft, apache

I have to do a report my class Technical Writing. I have chosen to do a report on the LAMP Architecture vs. Microsoft Line of products. LAMP being Linux, Apache Server, MySQL, and PHP. Microsoft being Microsoft Windows, IIS Server, MS SQL Server, and ASP.NEt. What do you guys think is best? Are there any security flaws in one or the other? Which is more efficient altogether?
If you could help me with this, I would be very appreciative.

Source: http://www.livejournal.com/community/webdev/197774.html

Date: 05/11/05 (MySQL Communtiy)    Keywords: mysql, sql

Hi everyone,

Am using MySQL 3.23.58... we have seen that it's possible to search any touple using the regular expression either using REGEXP or LIKE.

But i want to do something like searching by phonetics... like Spellchecking and closest word. Is there any method so that it can be done through SQL query only?

For Example:
Suppose if in a table 'items' the entry in 'item_name' field is 'COMPUTER' and if the searchable text is 'KOMPUTAR' then the syntax should find the table for the entry which pronounces like 'KOMPUTAR' and it should find 'COMPUTER'...

If there's not any SQL syntax then can it be done through REGEXP? if it's so then someone please give the REGEXP for this


Thank you.

Source: http://www.livejournal.com/community/mysql/57023.html

Date: 05/11/05 (MySQL Communtiy)    Keywords: php, mysql, sql, web

I've got a typical weblog system setup, which allows users to comment upon posts.

The comments are located in their own table which has a structure similar to this (actually it stores thinks like IP address, etc too):

CREATE TABLE comments (
  id int(11) NOT NULL default '0',
  article int(11) NOT NULL default '0',
  parent int(11) NOT NULL default '0',
  title varchar(65) default NULL,
  author varchar(25) NOT NULL default '',
  body text,
  PRIMARY KEY  (id,article)
);

This is pretty standard stuff - the comments are associated with a particler "article", and they may also be replies to other comments - in which case the "parent" will point to the parent comment id.

This structure allows there to be a nice nested tree of comments, in which some are "top level" because they have no parent set, and others are nested.

One problem I'm having is that with this structure is that the naive implementation for showing the comments is using recursion - and this is slowing down significantly as the size of the comments grows.

The pseudocode looks something like this:

#  $article is the article these comments are associated with
#  $parent is the parent of the children we want to see
#  $level  is increased when we go deeper into the tree,
#          used to display a nice indented tree
sub display_children($article, $parent, $level) 
{
   # retrieve all children of $parent
   $result = mysql_query('SELECT * FROM comments WHERE article=$article AND parent= $parent') ;

   # display each child
   while ($row = mysql_fetch_array($result)) 
   {
       # indent and display the title of this child
       echo str_repeat('  ',$level).$row['title']."\n";

       # call this function again to display this
       # child's children
       display_children($article,$row['id'], $level+1);
   }
}

# Display comments for article 40:
display_children( 40, 0, 0 );

(This is a pretty faithful representation in PHP, but the actual code is Perl, using DBI).

So now onto the question:

How can I speed this up, and avoid the recursion?

I've looked at CPAN for DBI::Tree, and other obvious candidates but I see nothing suitable.

I think I probably need to change the structure into something more efficient to display, but I admit my SQL-fu is weak.

Any suggestions appreciated.

Source: http://www.livejournal.com/community/mysql/56692.html

Date: 05/13/05 (Java Web)    Keywords: mysql, database, sql

Note: My WordPress database name is wordpress. I will use it in the example below. It will work for any MySQL database. (The procedure below will work with any version of WordPress too) Backup: mysqldump --opt wordpress | gzip -9 > wordpress.sql.gz Restore: gunzip wordpress.sql.gz mysql -u rootusername -p **** wordpress < wordpress.sql For detailed usage of mysqldump and mysql commands refer to [...]

Source: http://blog.taragana.com/index.php/archive/note-to-self-backup-restore-procedure-for-mysql-database-for-wordpress-etc/

Date: 05/14/05 (Code WTF)    Keywords: mysql, sql

[ru] писал выборку из БД по полу .. на автомате написал....
mysql_sex(); // и тут призадумался над аргументами....
[uk] писав виборку з БД по статті... на автоматі написав....
mysql_sex(); // і тут призадумався над аргументами....
[en] have wrote a select query, selecting a sex of user... but i have write not mysql_query, i have type mysql_sex(); // how do you think what arguments should be?

Source: http://www.livejournal.com/community/code_wtf/2990.html

Date: 05/16/05 (WebDesign)    Keywords: mysql, css, sql, web

So I know there has been MUCH talk about how CSS is the new standard to replace tables and such.. well I was struggling with learning exactly how it all played out.. until.. well I opened my eyes.. for all those Dreamweaver folks out there.. there are some prebuilt CSS page layouts that if you simply look them over, show you exactly how it's done and in good fashion as well.. so I'm now on my way to learning how to use CSS as the brains behind my web layouts from here on.. so there's my tip of the day.. now.. on to the topic at hand..

.. can anyone recommend a good DB Converter.. one such as Access to MySQL?..

Thanks in advance.

Source: http://www.livejournal.com/community/webdesign/879929.html

Date: 05/18/05 (WebDesign)    Keywords: php, mysql, database, asp, sql

How difficult is it to learn PHP for use with a MySQL database? And how difficult is it to create the MySQL database? Like, would it take me months? I have no previous PHP experience...I've only ever done ASP.NET with VB.NET.

I'm going to be starting a new project at my summer internship, and I'm wondering if it's worth it to focus on this particular part of it (or if I need to just setup a prototype of how the data display will look, and tell them they need to find someone with PHP/MySQL experience to create the actual database and code).

Source: http://www.livejournal.com/community/webdesign/882497.html

Date: 05/18/05 (Web Development)    Keywords: php, mysql, browser, html, sql, java, tracker

I'm trying to create a javascript click tracker, but I'm having a problem. The setup is very simple so I'll just give the code.

index.html
---------------





A Link



tracker.php simply inserts the url of the link into mysql.

The problem is very strange. Everything works fine if the files served from my local machine. If I upload it to the server things get weird. It doesn't work with firefox from my office (3 different machines and 3 different versions), but it works with firefox from every other place (home, friends, etc). It works fine with IE and Konqueror. Note: I did change localhost in the js function to the proper value for the server. It works if I go directly to the URL that's in the javascript so it seems that that line isn't exucted by my local firefox browsers. Does anyone have an ideas on this? Is there a free click tracker script that any of you know of? (free as in I can see the code)

Source: http://www.livejournal.com/community/webdev/200619.html

Date: 05/18/05 (PHP Community)    Keywords: mysql, database, sql, web, google

This is driving me nuts.

I've got about a dozen different forms on my website that all have a date field of some sort, whether it be news date, dob, etc.  All of them are used to insert data into a mysql table.  I've got the corresponding fields in the table set as "DATE" fields.  But I'm having issues getting the date to convert properly.

Since the format of a mysql date field is YYYY-MM-DD, I have to do some converting in cases where I need to retrieve the date from the table.  For example, if I'm showing somebody's date of birth, I pull the info from the table and then use date() to convert the date to MM-DD-YYYY.  As in $dob = date("m-d-Y", strtotime($dob)); where $dob is the date pulled from the table.

The problem is changing that date and updating the table. When stuffing the date back into the table, I've gotta convert it back to YYYY-MM-DD, so then I use $dob = date("Y-m-d", strtotime($_POST["dob"]));

Works fine unless the date is entered on the form with / instead of - as a separator.  So what this leads me to discover is that on forms where the user enters the date, they could enter it any number of ways.  Even if I specify that they use dashes instead of slashes, there's no way to guarantee that they will.  That means that my date in the table will only be correct some of the time.

So what can I do to the date fields on all of my forms to ensure that no matter how the user enters the date, it will be converted properly to YYYY-MM-DD and stuffed into the table?

The best answer I could find on google was using dropdowns for month, day and year.  That would be fine on forms where the date is being entered for the first time.  But it would be a huge pain to implement on "edit" forms where the fields are populated from the database when the form loads.

I'm about to start pulling my hair out here, so hopefully somebody has a good idea.  I'm open to nearly any suggestion.

Source: http://www.livejournal.com/community/php/299442.html

Date: 05/19/05 (MySQL Communtiy)    Keywords: php, mysql, sql

I have a PHP script that was failing until I fixed the permissions. It was falling down on DELETE. The script's account in the mysql.user table had Delete_priv set to N. When it was changed to Y, the script ran.

However, Select_priv, Insert_priv, and Update_priv are also set to N, and the script has not had any problems running SELECT, INSERT and UPDATE queries. Why would this be happening?

Source: http://www.livejournal.com/community/mysql/57244.html

Date: 05/19/05 (PHP Community)    Keywords: cms, php, templates, mysql, sql, linux, apache

I don't know why I never thought to do it before, but I put some benchmarking code into my CMS project today (PluggedOut CMS) - and was somewhat astounded at how quickly it's throwing pages back at me.

For an "un-cached" page, it's throwing things back in around 0.004 seconds, which I thought was damn fast... but get this - if I cache the page (so it doesn't have to bother building it from templates etc...) - it took just under 0.0004 seconds on average.

I don't know of a better advert for Linux, Apache, MySQL, and PHP quite frankly.

For those interested, the testing is on a Athlon 64 3200 (2.8Ghz), with 1Gb RAM, and a 160Gb HDD. It has Mandrake X64 on it.

Source: http://www.livejournal.com/community/php/299782.html

Date: 05/19/05 (Web Development)    Keywords: mysql, sql, web

Hello everyone,

We've got a MySQL DB on a solaris box that is going to be retired shortly and we need to move the data source for a web application from MySQL to MS SQL. I dont't work with MySQL as much, so I would like to know if anyone can tell me how to get a SQL create script out of the MySQL db so that I can recreate it in MS SQL.

peace.

Source: http://www.livejournal.com/community/webdev/201858.html

Date: 05/19/05 (SQL Server)    Keywords: mysql, sql, web

Hello everyone,

We've got a MySQL DB on a solaris box that is going to be retired shortly and we need to move the data source for a web application from MySQL to MS SQL. I dont't work with MySQL as much, so I would like to know if anyone can tell me how to get a SQL create script out of the MySQL db so that I can recreate it in MS SQL.

peace.

Source: http://www.livejournal.com/community/sqlserver/26975.html

Date: 05/23/05 (MySQL Communtiy)    Keywords: php, mysql, database, sql

Hello! This is probably a painfully stupid question, but I'm asking anyway. I'm trying to select all records in the database where the start date (start, which is of type date, obviously) is between the user-supplied dates.

Entering select email from nexus where start between '2000-01-01' and '2005-05-01'; from the mysql prompt returns the correct information, but when I put it in a php script, it returns nothing. I've got other queries in this same script ("select email from nexus where ".$field." like '%".$term."%'") which are working as expected, so I'm pretty sure it's just that particular query.

It's probably something really stupid that I just don't see because I'm looking too hard, but I'm all out of ideas. If any of you kind folks have any insight, I'd really appreciate it. Thanks!

Source: http://www.livejournal.com/community/mysql/57350.html

Date: 05/23/05 (WebDesign)    Keywords: php, mysql, sql, google

Well, it turns out this whole PHP and MySQL project was a heck of a lot easier than I thought.

I have hit one hitch, though. I've looked all over Google for an easy script for using paging with my SQL queries. When all is said and done, this table I'm going to have will have somewhere around 5,000 entries, so I really need to enable paging.

Problem is, all the scripts I've found won't work.

I'm using a somewhat complex SQL statement (it's not just a SELECT * FROM TABLE kind of thing), and I've got all my query display and search functions working correctly. I just need this paging thing to work. I understand the general idea of how the whole function is supposed to work, but I keep getting errors.

If anyone can give me any help at all, I'd really appreciate it. I've totally resorted to cut-and-pasting code and just modifying the SQL statement to work with my existing tables, but it still won't work. I can post code portions if necessary..

Thanks in advance.

Source: http://www.livejournal.com/community/webdesign/887367.html

Date: 05/26/05 (WebDesign)    Keywords: php, mysql, sql

I've been asked to make a site capable of handling sales of digital material, downloadable immediately on payment. It has to be able to handle packages sold as one product that are more than one file. (I've also seen some advertising that they can integrate login systems with, say, phpBB, which would be a nice (though not necessary) addition.)

There are dozens (if not scores) of code bundles out there to do this - but most of them cost, and I have no idea which ones are most effective. Can anyone recommend one they've worked with that is particularly good (and preferably not too expensive)? (PHP/MySQL preferred.)

Thanks!

Source: http://www.livejournal.com/community/webdesign/889152.html

Date: 05/27/05 (Web Development)    Keywords: php, mysql, database, sql, web

I'm working away at this PHP + MySQL stuff...

Now I have two tables - links and linkcats. Links is a list of links that will be displayed on the Employee Resources page. Linkcats is a list of the categories under which these links will fall - Intranet Websites, Internet Websites, and Employee Resources. I would like to be able to dynamically display the link categories with the appropriate links from the links table under each category. I got it to work (kind of), but it loops through the SQL statement to display the actual links however many entries there are in the categories table.

You can see what I'm talking about here:
http://www.shifuimam.com/php_tutorials/database_trial/www/mclinks.php

This is my code, along with the descriptions of the two tables:
http://mypage.iu.edu/~cstrodtb/livejournal/mclinks.txt

Any help is appreciated.

x-posted to webdesign.

Source: http://www.livejournal.com/community/webdev/204666.html

Date: 05/27/05 (WebDesign)    Keywords: php, mysql, database, sql, web

I'm working away at this PHP + MySQL stuff...

Now I have two tables - links and linkcats. Links is a list of links that will be displayed on the Employee Resources page. Linkcats is a list of the categories under which these links will fall - Intranet Websites, Internet Websites, and Employee Resources. I would like to be able to dynamically display the link categories with the appropriate links from the links table under each category. I got it to work (kind of), but it loops through the SQL statement to display the actual links however many entries there are in the categories table.

You can see what I'm talking about here:
http://www.shifuimam.com/php_tutorials/database_trial/www/mclinks.php

This is my code, along with the descriptions of the two tables:
http://mypage.iu.edu/~cstrodtb/livejournal/mclinks.txt

Any help is appreciated.

Source: http://www.livejournal.com/community/webdesign/890460.html