Looking for a PHP or MySQL article?

If you are looking for an article that is not on this site, or looking for a solution regarding PHP or MySQL problem, please comment this post and I'll do my best to give you solutions to your problems as soon as possible.

I am writing these PHP articles and tutorials to help you become a better web developer. All my articles are short and with code examples.

So if you have a "How to...?" question and you need my help (for free :) ) please post it in comments area.

Also if you need a professional web developer, I am available for freelance, providing quality code, security, database optimization, fast websites

Synchronize MySQL structure

Here is the scenario:

You have a localhost database, that is your development database.
And you have a production database, somewhere on a server.

After you've done some updates on your localhost development mysql database, you want to synchronize with the production database.

You can do that very easy using Navicat for MySQL. Tools -> Data Synchonization. You cand download it, it is fully functional for 30 days, after that you'll have to buy it.

So no more searching for altered tables, new tables, deleted fields, just synchronize your database easy.

First letter uppercase in MySQL

So you have all types of string in a mysql database.

Eg:

MySQL
MYSQL
mysql
MySqL

And you want to make the first letter of each record uppercase and the rest lowercase.
Here is the update syntax for this job:

UPDATE mytable SET myfield=CONCAT(UPPER(SUBSTRING(myfield, 1, 1)), LOWER(SUBSTRING(myfield FROM 2)))

MySQL implode like function

If you need an implode like function in MySQL, there is a solution: GROUP_CONCAT

GROUP_CONCAT concatenates a string by comma (,).

So if you had a table tags (id, tag)

1 test
2 test2
3 test3

SELECT GROUP_CONCAT(tag) FROM tags

The result would be: test,test2,test3

Pretty cool, huh?

MySQL order by rand() optimization

Optimize Order by RAND() in MySQL

Where when are dealing with large databases, and you want to query you database using ORDER BY RAND(), you will your query is very slow. This article shows you how to speed up this kind of queries.

http://akinas.com/pages/en/blog/mysql_random_row/

Your optimized ORDER BY RAND() queries will be hundreds time faster now.

MySQL Data Types

I found a great link, where MYSQL Data Types are detailed. If you need to know just how much space each data type takes, you should read this great article about mysql data types. It will help you to create the best structure (optimized structure) for your database. The database structure and optimization is important when you deal with large amount of data(millions of records for example).


Here is the link http://www.peachpit.com/articles/article.aspx?p=30885&seqNum=7

Replace string in MySQL

Replacing a string in MySQL

So you have hundreds of records in a table, and you want to replace a string in a single query.
You could use the REPLACE MySQL function. Here is your replace query:

UPDATE yourtable SET yourfield = REPLACE(yourfield,"some_string","some_new_string")
WHERE yourconditions.

This query replaces "some_string" with "some_new_string" in "yourfield" field. You can also add some conditions in WHERE statement.

How to replace a string in mySQL

Store images on disk or database?

Should you store uploaded images on disk or database?

I store my uploaded images on disk, and not in database. Why? One thing is that most hosting providers limit the mysql database. So if you have a large number of images, the database won't be enough for you. In database I keep the path and the filename, and the file is kept on the disk.
Also, having the images stored on the disk, you can access them very easy with ftp clients for example. Showing the image in the browser, does not affect database server's performance, having the image stored on the disk.

PHP MySQL Calendar Class

PHP MySQL Calendar Class

PEAR has this great calendar class . There is no need for you to waste your time by building a calendar class, this is a great one. Documentation for this class can be found here.

Some working examples of this calendar class written in php and mysql can be found here. As you can see it is really easy to work with this class, and it is well documented. You can easily extend it and use it for your own needs.

PEAR MDB2 get array rows

This tip is for those who use PEAR MDB2 as your database abstraction layer. There are two nice methods, that are not in documentation, queryRow($query) and queryAll($query).

queryRow($query) - returns an associative array for one record
queryAll($query) - return an associative array for all found records

So for those who like to use arrays (like myself) you don't have to use fetchRow() anymore.

Documentation for this class can be found here.

MySQL get date from string

Let's say you have a string, like '18-Sep-2007' and you want to format it in date format %Y-%m-%d. You can use STR_TO_DATE function. First parameter is your string, and the second parameter is the format of your string, in this case %d-%b-%Y. You can see the format values here:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

So STR_TO_DATE('18-Sep-2007','%d-%b-%Y') = 2007-09-18

You can use this result to compare dates, to order dates, or other date operations.

PHP MySQL Blog

Hello,

Here I will post tips and tricks about PHP and MySQL. Everything I will find useful you will find here, so you won't have to search lots of pages on the internet. Also if you have any question please post a message, and I will try to help you.