Mysql Pivot table

I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution in MySQL (5.X). I have a table (called history) with 3 columns: hostid, itemname, itemvalue. If I do a select (select * from history), it will return +--------+----------+-----------+ | hostid | itemname | itemvalue | +--------+----------+-----------+ | 1 | A | 10 | +--------+----------+-----------+ | 1 | B | 3 | +--------+----------+-----------+ | 2 | A | 9 | ...

How to test which port MySQL is running on and whether it can be connected to?

I have installed MySQL and even logged in there as a user. But when I try to connect like this: http://localhost:3306 mysql://localhost:3306 Neither works. Not sure if both are supposed to work, but at least one of them should :) How can I make sure that the port is indeed 3306? Is there a linux command to see it  somehow? Also, is there a more correct way to try it via a url?  Answers To find a listener on a port, do this: netstat -tln You should see a line that looks like this if mysql is indeed listening...

MySQL - Using COUNT(*) in the WHERE clause

I am trying to accomplish the following in MySQL (see pseudo code) SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) > 10 ORDER BY lastupdated DESC Is there a way to do this without using a (SELECT...) in the WHERE clause because that would  seem like a waste of resources.  Answers try this; select gid from `gd` group by gid having count(*) > 10 order by lastupdated desc try SELECT DISTINCT gid FROM `gd` group by gid having count(*) > 10 ORDER BY max(lastupdated) DESC Just academic...

MySQL check if a table exists without throwing an exception

What is the best way to check if a table exists in MySQL (preferably via PDO in PHP)  without throwing an exception. I do not feel like parsing the results of "SHOW TABLES  LIKE" et cetera. There must be some sort of boolean query?  Answers I don't know the PDO syntax for it, but this seems pretty straight-forward: $result = mysql_query("SHOW TABLES LIKE 'myTable'"); $tableExists = mysql_num_rows($result) > 0; Using mysqli i've created following function. Asuming you have an mysqli instance called  $con. function...

MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050

Using the command: CREATE TABLE IF NOT EXISTS `test`.`t1` ( `col` VARCHAR(16) NOT NULL ) ENGINE=MEMORY; Running this twice in the MySQL Query Browser results in: Table 't1' already exists Error 1050 I would have thought that creating the table "IF NOT EXISTS" would not throw errors.   Answers Works fine for me in 5.0.27 I just get a warning (not an error) that the table exists; You can use the following query to create a table to a particular database in MySql. create database if not exists `test`; USE...