Svetoslav Marinov’s Blog

I start setting up my new machine - Sony VAIO and this time it didn't go well the first time.
Keep reading...

I downloaded the latest php (PHP 5.2.9-1 (cli) (built: Mar  5 2009 20:02:28),
mysql (mysql-5.1.32-winx64.msi) and phpmyadmin (phpMyAdmin-3.1.3-english.tar.bz2).

Round #1
When I first logged in I saw a warning at the bottom of the first screen of phpmyadmin

Your PHP MySQL library version 5.0.50a differs from your MySQL server version 5.1.32.
This may cause unpredictable behavior.

Because I was already using the latest php I decided to downgrade MySQL to 5.0.51a

Round #2
http://downloads.mysql.com/archives.php?p=mysql-5.0&v=5.0.51a

Round #2.5
For some strange coinsidence (or maybe because it's Friday 13th :D) the configuration
wizard wouldn't start.

The error was caused by a bad configuration setting in MySQLInstanceConfig.exe.
By the way I have disabled UAC because of the constant prompting is quite
annoying but I still like Windows (This was for my friends who like Mac).

MySQLInstanceConfig.exe Error in manifest or policy file on line 6.
The value "asAdministrator" of attribute "level" in requestedPrivileges" is invalid.

Here is how to fix it by editing the file resource using Resource Hacker program (free).
http://bugs.mysql.com/bug.php?id=34340

I am not done yet

Round #3
Now the latest phpMyAdmin produced an error:

SHOW PLUGINS

MySQL said: Documentation
#1064 - 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 'PLUGINS' at line 1
Open new phpMyAdmin windowOpen new phpMyAdmin window

It seems this is a caching problem (see http://www.wampserver.com/phorum/read.php?2,46184,46227 )
Restart your browser and try again.

Happy Web Development

Related:

• http://downloads.mysql.com/archives.php?p=mysql-5.0
• http://downloads.mysql.com/archives.php?p=mysql-5.0&v=5.0.51a
• http://friendsofed.infopop.net/4/OpenTopic?a=tpc&s=989094322&f=5283032876&m=9081093451http://noteslog.com/post/your-php-mysql-library-version/
• http://bugs.mysql.com/bug.php?id=34340
• http://www.angusj.com/resourcehacker/
• http://www.wampserver.com/phorum/read.php?2,46184,46227

I was working with the Zend Framework and flowplayer and I needed to extract the ID from the url.
Here is the short JavaScript snippet.

var extr_id_regex = new RegExp('(\\d+)$', "gi");
var url = '/a/b/c/123';
var id = url.match(extr_id_regex);

if (id) {
alert(id);
}

Related

• http://www.w3schools.com/jS/js_obj_regexp.asp
• http://www.w3schools.com/jsref/jsref_obj_regexp.asp

Many times we need just the total number of rows that are produced by a single SELECT statement.
If you have even just a little experience with MySQL or other database(s) you'll think of COUNT(*) or similar function.

The query below uses COUNT(*) function but does not return the total number of rows.
It actually returns how many orders each customer has made.
When a query contains GROUP BY clause the situation changes.

[code]
mysql> SELECT c.*, COUNT(c.id) as cnt FROM `test_orders` AS o INNER JOIN test_customers AS c ON o.customer_id = c.id GROUP BY c.id;
+----+-----------+------------------------+-----+
| id | cust_name | cust_email | cnt |
+----+-----------+------------------------+-----+
| 1 | Slavi | slavi at slavi dot biz | 2 |
| 2 | Test | test at test dot com | 1 |
+----+-----------+------------------------+-----+
2 rows in set (0.00 sec)
[/code]

What if we want to create a pagination ?
Then we'll need to calculate the total number of rows for this query.

Solutions:
#1 Putting the main query into a subquery

[code]
SELECT count(*) as total_rows FROM (SELECT c.*, COUNT(c.id) as cnt FROM `test_orders` AS o INNER JOIN test_customers AS c ON o.customer_id = c.id GROUP BY c.id) as tab
[/code]

#2 Using MySQL's SQL_CALC_FOUND_ROWS and performing a second query
[code]
SELECT SQL_CALC_FOUND_ROWS c.*, COUNT(c.id) as cnt FROM `test_orders` AS o INNER JOIN test_customers AS c ON o.customer_id = c.id GROUP BY c.id LIMIT 10;
[/code]

[code]
mysql> SELECT SQL_CALC_FOUND_ROWS c.*, COUNT(c.id) as cnt FROM `test_orders` AS o INNER JOIN test_customers AS c ON o.customer_id = c.id GROUP BY c.id;
+----+-----------+------------------------+-----+
| id | cust_name | cust_email | cnt |
+----+-----------+------------------------+-----+
| 1 | Slavi | slavi at slavi dot biz | 2 |
| 2 | Test | test at test dot com | 1 |
+----+-----------+------------------------+-----+

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
[/code]

Note:
For "mysql>" is means that the commands are executing in a MySQL console.

Database Scheme

[code]
--
-- Table structure for table `test_customers`
--

CREATE TABLE `test_customers` (
`id` int(11) NOT NULL auto_increment,
`cust_name` varchar(255) NOT NULL,
`cust_email` varchar(255) NOT NULL,
KEY `id` (`id`,`cust_name`,`cust_email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `test_customers`
--

INSERT INTO `test_customers` (`id`, `cust_name`, `cust_email`) VALUES
(1, 'Slavi', 'slavi at slavi dot biz'),
(2, 'Test', 'test at test dot com');

-- --------------------------------------------------------

--
-- Table structure for table `test_orders`
--

CREATE TABLE `test_orders` (
`order_id` int(11) NOT NULL auto_increment,
`customer_id` int(11) NOT NULL,
`order_date` datetime NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `test_orders`
--

INSERT INTO `test_orders` (`order_id`, `customer_id`, `order_date`) VALUES
(1, 1, '2009-01-06 20:00:00'),
(2, 1, '2009-01-06 20:30:00'),
(3, 2, '2009-01-06 19:00:00');
[/code]

Related

• http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html
• http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
• http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

If you always wanted to see Hello World in a MySQL stored procedure now is the time.

[code]
DROP PROCEDURE IF EXISTS sp_hello_world;
CREATE PROCEDURE sp_hello_world() SELECT 'Hello World';
[/code]

For some strange reason Wordpress removes my formatting after the image above.

Why software try to be smart ?