Date: 03/30/07     Keywords: mysql, sql

I have MySQL running on a server with a password-protected root user. Is there any way to call the 'mysqladmin shutdown' command without having to provide the password parameter in clear-text?

Thanks.

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

Date: 03/16/07     Keywords: php, mysql, sql

Howdy,

I'm in need of some help. I thought I just updated mysql on my Debian server, but apparently I'm having some sort of problem. Here's what I did:

edited etc/apt/sources.list to include the line deb http://http.us.debian.org/debian/stable main

apt-get update
apt-get upgrade mysql-dfsg-5.0

I previously had mysql 3.23.49, and want to upgrade to use wordpress. I then checked my phpmyadmin page and it still shows as using mysql 3.23.49, and wordpress still won't install, saying that my mysql release is too old.

Is there a step I'm missing?

Thanks. :)

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

Date: 03/16/07     Keywords: mysql, rss, browser, sql

[edit] - solved in comments

I've got a project I'm working on where results can be downloaded to a CSV file. I found the code here.

Here is my code:

					
$rsSearchResults = mysql_query($sql) or die(mysql_error());
$out = '';	
$fields = mysql_list_fields('dataprod_a5data_com','canman');	
$columns = mysql_num_fields($fields);		

// Put the name of all fields	
for ($i = 0; $i < $columns; $i++) {	
	$l=mysql_field_name($fields, $i);	
	$out .= '"'.$l.'",';	
}	
$out .="\n";		

// Add all values in the table
while ($l = mysql_fetch_array($rsSearchResults)) {	
	for ($i = 0; $i < $columns; $i++) {	
		$out .='"'.$l["$i"].'",';	
	}
	$out .="\n";
}

// Output to browser with appropriate mime type, you choose ;)	
//header("Content-type: text/x-csv");	
//header("Content-type: text/csv");	
header("Content-type: application/csv");	
header("Content-Disposition: attachment; filename=search_results.csv");	
echo $out;	
$currentdownloads++;
$q2 = mysql_query("UPDATE canman_users SET CurrentDownloads = '".$currentdownloads."' WHERE Username = '".$un."'");
exit;

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

Date: 03/16/07     Keywords: mysql, sql

Wiki tells me:

In mathematics, the cardinality of a set is a measure of the "number of elements of the set".

Keyname      Type      Cardinality    Field
infant       INDEX     1              infant

mysql> SELECT DISTINCT(infant) FROM table;
+----------+
|  infant  |
+----------+
| NULL     |
| A        |
| B        |
| C        |
| U        |
+----------+
5 rows in set (0.09 sec)

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

Date: 03/11/07     Keywords: no keywords

How I earn up to $4500, being able to program and desing a little, working from home

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

Date: 03/10/07     Keywords: cms, php, mysql, css, html, xml, asp, sql, java, jsp, postgresql, security, web, seo

Приветствую Вас!

Я не супер-гуру в программировании или дизайне. Я обычный человек знающий немножко PHP, немножко Java, немножко CSS, немножко дизайн. Пытаюсь по-чуть разобраться с разными CMS. К чему я это пишу?  Всё просто, этими знаниями я зарабатываю до $2500 в месяц работая только дома по несколько часов в день. Ну хорошо, я вру. Этого Вы ждали?:) Я работаю не по 2 часа, а по 3-5 часов в день. Но это всё равно не 8-12 часов, как я работал раньше на дядю. И получал я за это тогда около 500 долларов.

Как добиться большей зарплаты?
Наверняка большая половина здесь присутствующих работает на дядю за зарплату. И из этих людей найдется всего несколько человек которых размер этой зарплаты устраивает. Да и эти несколько человек не отказались бы получать больше. Впрочем, любой человек хочет комфортнее обустроить свой быт, лучше обеспечить семью, купить себе наконец мощный ноутбук, хорошо покушать. сделать евроремонт в квартире, или купить наконец эту самую квартиру, отдохнуть на хороших курортах и т.д. Любой человек стремиться заработать больше. Именно в этом я и хочу Вам помочь. Знаю Ваш вопрос: Зачем это лично мне? Опять же - всё просто.  Там где зарабатываю я работы хватит на всех и надолго. Мне нет смысла скрывать эту тему. Сам я всё равно не справлюсь со всеми заказами. Так как заказчиков и проектов для работы очень много. Грубо говоря - хватит на всех :)


Ну и что с этим делать?
Есть такой сайт - scriptlance. Точне сайтов такого свойства есть много. Но у этого есть несколько важных плюсов. Перечислю их:

1. этот сайт работает с гражданами бывшего СССР (с русскими, с украинцами, с бесорусами, с молдаванами, с таджиками и т.д.)
2. этот сайт не обманывает - т.е. когда пришло время снять оттуда заработанные деньги Вы их просто снимаете и точка. Без заморочек! На собственном опыте и опыте многих ребят из России, Украины и т.д.
   
3. этот сайт может вывести Вам деньги на e-gold а дальше в вэбмани, может вывести на Ваш банковский счет (этот вариант наиболее удобен)
4. этот сайт предоставляет Вам очень большое, огромное колличество проектов ежедневно. Т.е. ежедневно там появляется много новых проектов и Вам есть из чего выбирать.

1. Жмите на кнопку "Programmers" Programmers в заголовке главной странице, затем, в появившемся меню прямо под кнопкой, нажмите кнопку Sign In "Sign up"
2. После ввода е-майл адреса, вам приходит письмо со ссылкой, которую надо посетить для подтверждения регистрации.
3. После этого заполните анкету.
4. После этого они зачислят Вам на счет бонус - $1. Понимаю, что мало, но всё равно приятно. Иэ то только начало денег.
   

1. Уставнавливал вордпресс на хостинг заказчика (20 минут) - $50 (у него самого не было времени на это)
   
2. Делал несложный дизайн для 10-страничного сайта (3 часа) - $175
3. Перевел с русского на английский небольших три статьи (20 минут) - $40
4. Ставил модули для Joomla на сайт заказчика (30 минут) - $90
5. Писал небольшой скрипт на PHP для учета продаж с сайта (1 час 40 мин) - $250
6. Сделал логотип (15 мин) - $30
7. Делали средний сайт с напарником (один день) - $1750
8. Писал три несложных модуля под вордпресс (5 часов) - $720

1. Внимательно прочитайте описание проекта (задание), оцените сложность. Если вы не можете оценить проект достаточно точно, задайте уточняющие вопросы заказчику через Post Message Board (PMB)
2. Посмотрите портфолио заказчика - предыдущие проекты и отзывы о нём. Также, иногда очень полезно просмотреть профайлы и портфолио своих конкурентов.
3. Старайтесь писать по-английски как можно грамотнее.
4. Наличие фотографии в вашем профайле также существенно увеличивает ваши шансы заработать. Психологически, заказчику легче работать с реальным человеком.
5. Если у вас уже есть какие-нибудь завершённые проекты, рекомендую открыть хостинг, или даже бесплатный хостинг для демонстрации своего портфолио клиенту.
   

Как получить деньги?

1. отвечу на все вопросы
2. помогу зарегистрироваться, если есть проблемы
3. буду подкидывать Вам постоянно новых заказчиков, так как у меня их уже много и я сам не справлясь со всеми поступающими заказами
4. помогу определиться как лучше и куда обратить виртуальные деньги в наличность
5. да и всячески буду Вам помогать

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

Date: 03/09/07     Keywords: no keywords

I have a table containing test results. 

studentid INT
testscore FLOAT
dateadded DATETIME

There are multiple tests for each student. 

How can I SELECT the most recent test result for each student in the table?  I think I would use GROUP BY studentid, but am a little foggy from there..

thanks!

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

Date: 03/08/07     Keywords: mysql, sql

For those that follow Daylight Savings Time in the US and Canada, watch out this weekend, because we “spring forward”!

The biggest caveat I have is: Do not arrive 1 hour late to work on Sunday or Monday.

As for MySQL, to test if you are fine, run:

SELECT @@global.time_zone;

If you get back “SYSTEM”, then MySQL is looking to the OS for timezone data, which is the default.

The real sanity check:

SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00');

This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. If not, and you’ve played with timezone data, remember that timezone data is only loaded when MySQL starts, so if you haven’t restarted MySQL since you patched your OS, you need to do that.

This is mostly stolen from a MySQL list post I found here:

http://lists.mysql.com/mysql/205321

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

Date: 03/05/07     Keywords: mysql, sql

Is there a built-in MySQL function for calculating Percentile?

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

Date: 02/21/07     Keywords: mysql, sql

Does anyone have recommendations for a MySQL 4x - 5x book targeting intermediate to advanced users, perhaps some sort of book that talks about schema optimizations? The company I work for has been progressively reworking, over the last three years, the solution to handle multiple feeds of MLS/real estate information and I honestly got to say I am stumped as well. The problem is figuring out how to normalize several independantly designed schema feeds into one consolidated and hopefully unified source instead of multiple sub-tables that feed into one gigantic view.

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

Date: 02/19/07     Keywords: php, rss, web

Hello! I would like to provide new project for webmasters and php-developers scriptmafia.org
You can read rss-translation in LiveJournal scriptmafia

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

Date: 02/08/07     Keywords: database

Let's say I have a message IDs like this sequence extracted from a database.

1
2
3
4
5
6
7
8
9
10
27
28
29
30
39
45
58
59
60
63

If I want to select the first 10 data, it's easy with LIMIT 0,10.

But, how do I select the second 10 data when it goes random? And third and fourth and the rest?

Is there a simpler way than putting out the last ID in a cookie?

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

Date: 02/08/07     Keywords: no keywords

I need to update several rows. Each row has a date column with data like "2004-03-02". I want to change the year to 2007 for all the rows in my data base without changing the month and the day.

Basically change a date like "2004-03-02" to "2007-03-02"

Is there a way to do this do this all in one update query?

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

Date: 01/22/07     Keywords: mysql, sql

i have two tables, contacts and tasks. users create a contact and then create tasks for the contact. one contact can have many tasks. i want to retrieve the contacts and, if there are any active tasks, i want to retrieve only one. if there are no active tasks, i still want to get the contact. the idea is that users can see a list of contacts, and a little icon appears next to any that have pending task/s. i've been doing this (simplified a bit):

SELECT c.`id`,c.`name`, t.`id`, t.`task_type` FROM `contacts` AS `c` LEFT JOIN `tasks` AS `t` ON c.`id` = t.`contact_id` AND t.`done` = '0' WHERE 1 GROUP BY c.`id`

which was working, but lately is running very slow (mysql hangs for 2+ minutes at "copying to tmp table"). is there a more efficient way to do this? everything else i can come up with only returns contacts with active tasks. i had to toss that GROUP BY in there because otherwise i'd get a contact returned as many times as it had tasks, which is obviously not right and a bit hacky. so, i'm sure i'm doing something wrong here.

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

Date: 01/11/07     Keywords: mysql, software, database, sql, web

I am running MySQL 4.1.22 on Windows 2000, and I'm having problems with European characters.

When data is input through the software that uses it, European characters are converted into strange symbols that may or may not display properly in a web context. For instance É looks like É in the database, ü comes out as ü.

When I input the data through SQLyog, everything from the first European character to the end of the string is cut off.

I am using utf8_general_ci, which is how the software set up the database. What am I doing wrong, or what else should I be looking for?

Thanks for any help you can offer.

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

Date: 12/19/06     Keywords: php, mysql, software, html, sql, java, spam, microsoft

Episode 3 is here!

If folks have ideas or suggestions for content, please let me know.



You can Direct download all the oursql podcasts at:
http://technocation.org/podcasts/oursql/

Direct play this edition at:

http://tinyurl.com/yy7pgz



News

Download MySQL Turbo Manager Free Edition at: http://www.mentattech.com/themes/mentat/download.html



Wireless providers prefer MySQL over Oracle, Microsoft SQL Server over both:

http://www.crn.com/sections/breakingnews/breakingnews.jhtml?articleId=196700062


Learning Resource

Artful Software's list of common queries. They also have an interesting e-book available, called "Get it Done with MySQL 5.0".

http://www.artfulsoftware.com/queries.php


DATEDIFF(), DAYOFWEEK() functions

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Promo

This week’s promotion comes from Geek Fu at http://www.geekfuactiongrip.com



Feature

Concurrency and Isolation Levels
http://sheeri.net/index.php?p=123



Acknowledgements

http://www.technocation.org

http://music.podshow.com

http://www.russellwolff.com

http://www.smallfishadventures.com/Home.html "The Thank you song" -- Smallfish


Feedback

If you have any feedback about this podcast, or want to suggest topics to cover in future podcasts, please email

podcast@technocation.org This email address is being protected from spam bots, you need Javascript enabled to view it

You can also:

Call the comment line at +1 617-674-2369

Or use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://tinyurl.com/sc6qw

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

Date: 12/15/06     Keywords: software

My lead and I've spent the last month bouncing idea's against each other on how to store the entire structure of the United States (states->counties->cities & zips) as well as Canada, Mexico, England, Germany, and Japan.

So far the idea is 2 tables: locations & location_rules.
the location tables would be like so
id, name, abbr, parent, rule_id

so for the USA, a quick example would be.
1, United States of America, U.S.A., 0, 1
2, Colorado, CO, 1, 2
3, Denver County, Denver, 2, 3
4, Denver, DEN, 3, 4


and location_rules is still up in the air, but something like this
id, head, type
1, 0, Country
2, 0, State
3, 0, County
4, 0, City

Right of the bat the problem is dealing with US ZIP codes which often are 1 to 1 with cities, but some cities have multiple ZIP codes and a few counties share zip codes.

The second problem is the current schema used for the USA sucks, but it's been an effective guide to figure out what were dealing with. USA alone has 3360~ counties, 76,000~ cities, and I don't want to think about ZIP codes but it's more or greater then cities in count. So that's a table with 140K~ entries. Doing dirty math, I figured Canada is about 2/3 the size of the USA for something like 238K records total. My experience so far is that life gets interesting when you get near the quarter of a million records edge and this is definately going to go well past that point.

So yeah, that's what I got... anyone know a better way besides making sets of tables for each country, thereby increasing the complexity of the software that uses it.

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

Date: 12/14/06     Keywords: mysql, sql

I have a cluttered query to get the next week-day (minus weekends) which I'd like to place in a function to be used over and over. 

Is there a way to write query snippets as a custom function for MySQL?    (something like SELECT NEWFUNCTION();)

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

Date: 12/14/06     Keywords: php, mysql, browser, html, sql, web

I realized I haven't told Livejournal about the MySQL Podcast that's been produced. The third episode has been published -- see http://www.technocation.org to subscribe or search for "mysql" or "oursql" on your favorite site to find podcasts, including itunes. Episode 0 and Episode 1 are still available.

Episode 2 is here! It's a bit late due to a computer crash and the fact that I sprained my ankle, but I think it's worth waiting for. Particularly since this week's feature is everyone's favorite -- performance tips!


It's best to subscribe to the podcast by clicking the "podcast" graphic at http://www.technocation.org , or searching for "mysql" in your favorite podcast directory (including iTunes). But if you want to play episode 2 in your browser, visit http://technocation.org/index.php?option=com_content&task=view&id=22&Itemid=29

Please consider promoting the show -- we have a 40 second promo ready for download at:

http://www.technocation.org/podcasts/oursql/OurSQLpromo1.mp3

Show Notes:
News

http://www.mysqlconf.com/

Free Survey, win a free pass

http://www.zoomerang.com/recipient/survey-intro.zgi?p=WEB225WZRG2XU7

You can now find this podcast, OurSQL, on many podcast sites including:


http://www.itunes.com

http://www.podcastalley.com

http://www.podfeed.net

http://www.podcastblaster.com


just search for "mysql" on any of those sites and you'll find the podcast. You can also find it at:


http://feeds.feedburner.com/oursql


Learning Resource

Promo

http://www.tech-in-sight.com/

Feature

Peter Zaitsev's blog post about count for InnoDB tables:
http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

Acknowledgements/Sponsors

http://www.technocation.org

http://music.podshow.com

http://www.russellwolff.com

http://www.smallfishadventures.com/Home.html "The Thank you song" -- Smallfish




You can Direct download oursql podcasts at:
http://technocation.org/podcasts/oursql/

If you have any feedback about this podcast, or want to suggest topics to cover in future podcasts, please email

podcast@technocation.org

You can also:

Call the comment line at +1 617-674-2369

Or use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://tinyurl.com/sc6qw

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

Date: 12/13/06     Keywords: php

Is there a way either through the cell settings or INSERT query to dynamically create a DATETIME value (rather than generating it with PHP)?

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