MYSQL :: Alternative To Group_concat For Early Versions Of Mysql

GROUP_CONCAT Alternative For MySQL < 4.1 ?
GROUP_CONCAT is a great function...But have MySQL version < 4.1 and my hosting provider can't upgrade MySQL. Is there a way to get this result with a different SELECT query?

I have a list of recipes in one table, with a list of ingredients in another. Since there are a different amount of ingredients per recipe, the 2nd table has multiple ingredients per recipe.....

Alternative To GROUP_CONCAT
I have a db of events with categories and locations. A form w/ checkboxes inserts into 2 tables the following:

Table 1: event
(id, title, date, description, contact)

Table 2: event_search (only foreign keys)
(event_id,location_id,type_id)

2 other tables are listing the locations and the types

Table 3: location (International, national, all 50 us states)
(id, name)

Table 4: event_type (promotional, free, concert...)
(id, name)

Some events have multiple types and locations, so this is the only way I've figured out to have a normalized db.

How can I list the events, displaying their locations and type, without using GROUP_CONCAT (only works w/ mySQL 4.1 :( )

4.0.x Equivalent/alternative For Group_Concat?
The following query works great on my test machine with mySQL 4.1.x. However, because cPanel is taking their sweet time working in mySQL 4.1, I can't use this on my website. Is there 'any' way to accomplish this same type of return in mySQL 4.0.22? Code:

Benchmarks Of Runtime Versions Of Mysql Or Other Sql Versions Sqlite Hsqldb
mysql vs sqlite vs hsql

First of all i'm on a win32 platform using java. I also have mysql
installed. My question isabout benchmarks and multiple inserts &
selects
SQLITE: http://www.sqlite.org/
HSQL: http://hsqldb.sourceforge.net
I current have a mysql database of approx. 80mb.
Each day I Insert approx .5mb of new records into this table,
and I might also run mutiple Update, Delete, and Select queries as
well.
I'm trying to get an idea of how fast a sql database engine will run
given that is is inside of the JVM. I know it's going to be slower
than a db engine written in C and that's a trade off for being
portable to different operating systems.
What I want to know is how much of a trade off though - I don't want
to have to wait 10 mins or deal with screen freezing... Right
now on a windows platform using mysql things move really fast.
Can anyone give me an idea of the time delay needed to do these
Inserts in HSQL (ie: how much does the jvm slow things down)..
Thanks in advance

PHP Alternative For MySQL
I am familiar with Microsoft Access, but I have never used MySQL. Does MySQL
have the ability to set up Queries and Reports like Access, or do you need
separate software in order to do this? It appears PHP is always mentioned in
conjuction with MySQL. I really don't want to learn another scripting
language and I am not familiar with Unix. Any suggestions for alternatives
to PHP that would be simpler than using an acutual programming or scripting
language?

MySQL Versions
I am using MySQL 5 on my computer but the web-hotel I am contemplating to use only use MySQL 4.0xx. Can I use the database without modifying it? Or do I need to "convert" (downgrade) it for MySQL 4.0xx? If so, how do I do that?

Why Does GROUP_CONCAT() Function Fail In MySQL 4.1.12 In This Query?
Here are my results using MySQL 4.1.12 and I honestly don't think this is right, but can't figure out what to do:

Running Two MySQL Versions
Actually what I want to do is remove 4.1 because I downloaded 5.0 and installed w/o removing the previous version. I now have port 3307 for 5.0. Is this a problem and how can I change it back to 3306 so e.g. when I use Apache/PHP evrything works?

Two Versions Of Mysql On Same Unix Box
I have mysql 3.23.58 version running on my unix box. I installed a new application on it which need mysql version > 4.0 so I thought of having two mysql versions running on the same unix box. I have first installation in /usr/local/mysql and I untared the new version in /var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23.

I am following instructions given in http://dev.mysql.com/doc/refman/4.1/en/multiple-servers.html I also created a empty file and named it as mysql.sock.new and stored it in /var/local. When I try to start this new version from /var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23 using ./bin/mysqld_safe --datadir=/var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23/data & it says "A mysqld process already exists". Please tell me where I am going wrong and how to configure it properly?

Row Select In Old Mysql Versions
We are using MySQL version 4.0.20-standard.

Our application requires a query to do a row select. From what I have found this version does not support rownum. We are unable to update the version of MySQL. Can anyone give us some support.

Running Two MySQL Versions
It seems that my computer is running two different mysql versions. When I'm trying to login to the database it refuse my password and giving me access to the test configuration. This is what i get in the event viewer.

Do you already have another mysqld server running on port: 3306 ?
Event id 100.

The problem occur when I changed my password for the database. I have turned off Mysql in local services but i think that that's the original version. When I start the xampp i think it starts the winmysqladmin version (not sure). How do I reconfigure the hole thing and how can I see what's running on my machine.

I actually have another question to. I'm trying to run and open the shell mysqladmin which doesn't work. It's just a flash, which one of them am I supposed to run?? You have several options like mysqladmin, mysql, mysqld, mysqld-nt....etc. I opened the winmyslqadmin which i think wasn't that good.

Downgrade MySQL Versions
I need to move a MySQL database created with MySQL 4.1.22 on PHP 5.2.3 to a different server with MySQL 3.23.58 on PHP 4.3.8.
Is it possible to convert the database to do this?
Alternatively, is there a way to export from the newer version in a format that the older version will accept?

Best Excel To MySQL Conversion (or Alternative)
would like to use excel or if there is a recommendation for alternative program to enter data in when on the road, upon return would like an automated tool to pass the data to MySQL, keeping all FORMATTING info, BOLDS , ITALICS, etc.

Can Run Two Versions Of MySQL In Windows 2000?
I have been running 4.0.13 for several weeks now and though I'd like to =
test 4.1.0. 4.0.13 is under c:MySQL. I downloaded 4.1.0 under c:4.1 =
AlphaMySQL. When I try to launch mysql it brings me to the old =
version, how do I access the new one without killing the old one? (I =
have also tried putting 4.1.0 on a totally different machine and linking =
to it also.)

Previous Versions Of MySQL With XAMMP
I've downloaded XAMPP for Linux 1.5.1 for use on Fedora Core 4. I notice it only has the latest version of MySQL on it. I'm working on two web sites (for different clients) and they are hosted on different shared Linux hosting servers (with PHP), each with different versions of MySQL available, i.e:

MySQL 4.0.25-standard
MySQL 3.23.52

Is it possible for me to use older versions of MySQL with XAMPP? Could I just turn off MySQL in XAMMP and just run and older version of MySQL independently of XAMMP? If I can then how would I install multiple versions of MySQL in Fedora Core 4?

Symlinking Between Different Serveurs And MySQL Versions
I have a hosted server where we have both web and intranet share a same database. For interne reasons we need to migrate the intranet onto one of our servers and keep the web on the hosted server.

We will thus have the database splited in two. Our intern server willl have MySQL5 and our hosted one will remain with MySQL3.23.39 at list for a while longer.

I would like your advises on the faisability of using symlinking to access the tables on the hosted server from our intern server, knowing that MySQL version are different. The tables on the hosted side are MyISAM.

The main idea is to avoiding using replication to access comon tables on remote servers.

Utility To Convert Between MySQL Versions?
My webhost downgraded from mysql 5 to 4 and I'm seeing errors in the forum I was running. All I can see that would have changed and given me this database error would have been this downgrade. Is there a conversion utility availaible that can change back and forth between MySQL versions? I've looked around for about an hour thru Google and on this forum, but I haven't found anything yet.

I was getting password errors, but we seem to have resolved that issue, and now I'm left with this database connection error.

Thanks for any assistance!

P.S. The errors I get are:

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/clanloc/public_html/forum-old/db/mysql4.php on line 330

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in /home/clanloc/public_html/forum-old/db/mysql4.php on line 331
phpBB : Critical Error

Could not connect to the database

Selecting Correct Versions Of MySQL
I have searched this forum and google to try and get a comprehensive answer to this question but i haven't found anything yet.
I am looking to select the correct versions of MySQL, Apache and Php so that they all work together. I notice that there are alot of issues with various different versions sets being chosen and there seem to be instances where it can be very problematic if the wrong one is chosen.
I am looking to use InnoDB and would like to use the latest versions of everything as possible but it doesn't really outline anywhere what works with what best. There are lots of cases that outline what can be problematic but i'm trying to avoid this if possible and find what isn't problematic.

Trouble With NATURAL JOIN Between MySQL Versions
I have this query which works perfectly well in MySQL 5, but doesn't work in 4.1.
I know there were some changes made to join syntax between the versions but for the life of me I can't figure out exactly what I need to do to fix it:

SELECT Status, UserName, FontName, FontPath
FROM AccountInfo
NATURAL JOIN AccountPrefs, Templates.Fonts
WHERE AccountPrefs.HeaderFont = Fonts.FontID
AND UserID = '$userID'
works fine in v5, but in v4.1 I get the old "Column 'UserID' in where clause is ambiguous".
I'm trying to match the UserID in AccountInfo and AccountPrefs, I just left the font thing in there as well because I'm not sure if it's contributing to the problem.

How To Detect If Different Mysql Versions Are Installed/runing
from my InnoSetup setup :) I need to detect if any version of mysql is runing on my windows. Others sugested to check fo a mutex or a Class Name(dont know what are these) or Windows Names or Look for a mysql*.exe, so is there something common for mysql starting with v3 .. v5? I mean what is the thing that i have to search for, to know at least if mysql is runing.

For mysql v3 there are the posible runing files:
mysqld.exe
mysqld-max.exe
mysqld-max-nt.exe
mysqld-nt.exe
mysqld-opt.exe
or on Win98 only winmysqladmin.exe?
For mysqlv4 .. im not sure yet
For mysql 5 seems to be more simple, only
mysqld-nt.exe

Do I have to search for all these files(and others that I dont know yet :) ) to be sure that its not runing?

Alternative Tool For MySQL Command Line Client
Can you name a good Alternative tool for MySQL Command Line Client.

Migrating A MySQL Db From Plesk Over To CPanel - Possibly Differant Versions Aswell ?
Hey all. As title, i had hosting on a plesk server, but was migrating everything over to cPanel due to a better package.

Ive run into some problems though, mainly due to differant versions i think. Anyone know of a way around this as it would be alot of work redoing the whole sites again

X IN GROUP_CONCAT ?
I have a query that, a bit simplified, looks like this:

SELECT
positions
FROM
(SELECT
tbl.id,
(GROUP_CONCAT(tbl.foo) AS positions
FROM
tablefoo AS tbl
LEFT JOIN tablebar AS bar ON bar.lostteethbaseid = tbl.id
GROUP BY tbl.id
) AS test
WHERE
NOT (15 IN (positions))
AND NOT (16 IN (positions))
See what I want to achive is to retrive all positions from a table using GROUP_CONCAT and then check if certain values are present in the list of positions. Positions could look something like 15,27,30. However

15 IN (positions)
does NOT work like

15 IN (15,27,30)
but rather like

15 IN (ཋ,27,30')
I got the query to work using LIKE:

positons LIKE '%15%'

But I would really like to use IN instead since that would be much quicker on an indexed field. Worth noting is that casting the field "positions" from BLOB to CHAR does not help.

GROUP_CONCAT LIKE 2 NOT LIKE 20 Etc?
I have used group_concat to consolidate all the 'genreids' as follows in a field...

2, 3, 4

How would i perform a search of this field, so that if i wanted to find genreid 2? But not 20, 21, etc?

At current ive been using:

....AND genreids LIKE '%2%'

However the above obviously matches too much :/

Group_concat
I am attempting to pull data from multiple tables into a resultset that
I can use as an Excel spreadsheet for reporting. Code:

GROUP_CONCAT Bug
I am using the GROUP_CONCAT() as follows:

SELECT (SELECT CONCAT(ProjectName) FROM Project WHERE
FIND_IN_SET(ProjectID, RelatedProjectID))AS RelatedProject

We have a project tracker system and we can have one project be
related to several different projects. the issue is when there is
only 1 related project associated with another project, this query
does not work. Is there some sort of bug with GROUP_CONCAT?

Group_concat
mysql 4.1
I'm trying to use the group_concat function together with IN and I get a message "Invalid use of group function"
How else can I accomplish this?
"AND user.class_id IN (0, group_concat(classes.class_id))"

Group_concat
is there some limitations on data size when doing group_concat

Something Like GROUP_CONCAT()
basically, i want my query to do something like this would do.Code:

SELECT listings.*, GROUP_CONCAT(photos.pic SEPARATOR ','), listings.office_id FROM listings, offices JOIN photos ON photos.list_id = listings.ln WHERE listings.office_id = offices.office_id AND ln = '$ln' GROUP BY photos.pic

but that doesn't work work in 4.0.x, does anyone know how i can get the same affect in mysql 4.0.x?

Issue With GROUP_CONCAT
A table looks like:

time millisec
10:02:04 234
10:02:04 567
10:04:12 003

Both time and millisec are in order by simply select. In fact, I want to use the counter to do row operations, like different between two adjacent rows. Maybe one has better idea than adding a counter.

Complement Of Group_concat
is there a complement function of the "group_concat" function?

for example:
id,items
1,'2,45,5'
2,'5,6'

=> convert into that format
id,items
1,'2'
1,'45'
1,'5'
2,'5'
2,'6'

i need to use mysql 4.1.22 (no stored procedures...)

GROUP_CONCAT Emulation
Does anyone know how to emulate the GROUP_CONCAT function for versions before it was added?

GROUP_CONCAT Crossjoin Problems
I've been searching the forums and found this thread which seems very similar to my problem, but I'm having trouble adapting it due to the complexity of the example.

Here's my demo table and problem query:

CREATE TABLE items (id INT, name CHAR(5));
INSERT INTO items VALUES (1,'test');

CREATE TABLE gallery (img_id INT, item_id INT );
INSERT INTO gallery VALUES (1,1);
INSERT INTO gallery VALUES (2,1);
INSERT INTO gallery VALUES (3,1);

CREATE TABLE gallery2 (img_id INT, item_id INT );
INSERT INTO gallery2 VALUES (10,1);
INSERT INTO gallery2 VALUES (20,1);
INSERT INTO gallery2 VALUES (30,1);

SELECT items.*,
GROUP_CONCAT(gallery2.img_id) AS gallery2,
GROUP_CONCAT(gallery.img_id) AS gallery
FROM items
LEFT JOIN gallery2 ON gallery2.item_id=items.id
LEFT JOIN gallery ON gallery.item_id=items.id
WHERE items.id = 1
GROUP BY items.id
However this returns :

id name gallery2 gallery
1 test 10,10,10,20,20,20,30,30,30 1,2,3,1,2,3,1,2,3
Could anyone help me with how to prevent the crossjoining?

LIMIT 5 Only Returns 2 When Using GROUP_CONCAT
I'm trying to limit my results in the following query. When I make this query without the GROUP_CONCAT lines, I get the number of results I specified in LIMIT, when I put those lines back in, I get less than the number of results I specify with LIMIT....

GROUP_CONCAT In Subquery - Error
HTML
SELECT GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F._id = C._id
AND C.fieldC IN (1, 2, 3)
I want to incorporate it into another query:

HTML
SELECT A.fieldA, SUM(A.fieldB) AS total,
GROUP_CONCAT(A.fieldC) AS listing,
(SELECT GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F.i_id = C.i_id
AND C.fieldC IN (listing)
) AS items
FROM TABLEA A
GROUP BY A.fieldA
ORDER BY total
But hat query throws the error:

Quote:

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 '(SELECT GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F.i_id at line 2

Group_concat Change Separator
how can I change separator in result of my group_concat?

Count The Result Of Group_concat?
I learned group_concat from this forum, very useful command, but I am wondering if I can * COUNT the result of group_concat *

for example, the following example, I know this is a wrong clause, but I hope I can COUNT and then use the result to do some HAVING works. So is it possible to count the result of group_concat?

SELECT class, sex, COUNT(group_concat(name)) as num from school group by class, sex HAVING num > 20

Group_concat And String Functions
I am running: MySQL Distrib 5.0.15, for pc-linux-gnu on i686

I think I have run into a limitation of the GROUP_CONCAT function but I may really be doing things incorrectly. I hope you can point me in the right direction.

We have a table of installed software that includes fields for: Product, Version, OperatingSystem and MachineName. Any software product can live on several different machines and might have different versions installed on each.

I was hoping to produce a list of products, versions and an aggregate of the os/machine names that have this product installed. I tried: Code:

Alternative For LIKE ?
I am trying to optimize a database query that uses the LIKE clause in the SELECT statement, and I discovered recently the the LIKE clause doesn't allow MySQL to use indexes, and this means poorer performance.

Do you have any other alternative form for a query like the following:

select id from mytable where column1 like "%keyword%"

maybe by using REGEXP?

Alternative
I am dynamically building an XHTML entry form into a properly normalized database....therefore i need to use all my "reference" tables to populate a bunch of select elements with the proper OPTION values.

i have never used GROUP BY and not sure what it does, so maybe i can do just one statement (containing joins) and parse the resulting single array?

or is it better to make a select statement and a populating loop for every option list...it kind of seems wasteful to me, so i was hoping for a better way.

NOT IN Alternative
I know there's a lot of old posts on this topic but none seem
to be relevant to my problem (selecting from a single table rather than
several different tables)...
I have a table with a user identifier (uID) and a movie identifier
(movID) and need to select the movie identifiers that appear in the
table for uID-1 but not for uID-2, using an alternative to NOT IN as it
isn't supported by the version of MySQL that I'm using.

GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version.

The query is this:

Select Group_concat Doesn't Return Numbers?!?
Say that I have a table with a with of integers and a row with text. For starters, I just want to have the integers returned in one string:

select group_concat(MyIntegers) from MyTable;
This gives me a lot of questionmarks: ?????????

However, if I do this, I get the real result:
select lower(group_concat(MyIntegers)) from MyTable;
This gives me: 11,15,20,etc.

If I select the string row, no extra work is needed.
select group_concat(MyStrings) from MyTable;
I get: "blabla,hey,there" etc.

Why can't I get the numbers "as is" in the first query? I'm using the latest 5.0.19 Win32 MySQL server, running the latest ODBC driver etc. on a Windows server 2003.

Versions
I have MySQL 5.0.24 installed on a XP Pro SP2 PC. The latest version is 5.0.41. I looked at the change logs and they say they are bug fixes only. Have there been any security issues with 5.0.24 that I should be aware of? Do I need to move to 5.0.41 to be safe?

Run 2 Versions
Is it possible to run both
MySQL 3.23 and
MySQL 5,
at the same time on the same machine with php4.3.0 and apache 1.3.27?
What should I consider ?

Two Versions
I wanna run MySQL 3.23.54 and MySQL 5.0 in the same Redhat Linux 9 box at the same time. Is it possible?

Mysqldump Alternative?
We are using cpanel backup solutions which use mysqldump to make backups of databases. One database is about 1.4 GB. It seems this takes forever and it also prevents applications using this database or other database on the same server from operating. The server hardware is good enough also. The server is a dedicated server and only has 2 sites with 5-6 databases on it.

Is there an alternative to using mysqldump or anything that can be done to prevent this process from preventing mysql applications from running when mysqldump takes place? It seems it takes several hours to run.

Alternative To NOT IN Clause ?
I am developing a community website which has so many articles that can be seen by people. Here one can track articles by its freshness and whether it has been seen by 'friends' and so on.. If the article is new, and has been commented by friends but not by the logged in user, I bring that article to the user's focus....

The tables concerned are articles, users, comments and friends.

The query is like ....