search

phpmyadmin / phpmyadmin

A web interface for MySQL and MariaDB
https://www.phpmyadmin.net/
GNU General Public License v2.0
7.06k stars 3.36k forks source link

Operate on temporary tables #7472

Closed pma-import closed 8 years ago

pma-import commented 20 years ago

This does not work as it should:

create temporary table t1 select * from t2; select * from t1;

pma-import commented 20 years ago

Logged In: YES user_id=418833

Confirmed. The query works fine in MySQL's command line.

pma-import commented 20 years ago
pma-import commented 20 years ago

Logged In: YES user_id=192186

Only thing needed to make this work is to disable table check in libraries/db_table_exists.lib.php. So we need to detect somehow we operate on temporary table.

pma-import commented 19 years ago

Logged In: YES user_id=1126753

Is there a way to do this manually until this is fixed?

pma-import commented 19 years ago
pma-import commented 19 years ago

Logged In: YES user_id=192186

Fixed in cvs.

pma-import commented 19 years ago
pma-import commented 19 years ago
pma-import commented 19 years ago

Logged In: YES user_id=1126753

I upgraded to 2.6.1 and still experienced the issue. The only file I didn't replace was config.inc.php. Is it required to replace this file as well?

pma-import commented 19 years ago

Logged In: YES user_id=418833

No, config.inc.php does not need to be replaced.

What problem are you exactly facing with 2.6.1?

pma-import commented 19 years ago
pma-import commented 19 years ago

Logged In: YES user_id=1126753

I created a MySQL addressbook which has 8 tables:

1Organizations - Organization names 1People - Contact names, & groups 2email - Stores multiple email addresses per contact 2im - multiple IM media type, & user names per contact 2locations - multiple addresses per contact 2phone - multiple phone numbers per contact (cell, home, etc) 2web - multiple URLs per contact 2workinfo - links People to Organizations

I wrote a query (or is it a script?) to create a flat-file Mozilla Thunderbird addressbook export for personal contacts, and another one for professional contacts. The query (or script?) does this, in order:

drops the temp table qryEmail if it exists creates 2 temp tables - one for each T-bird email-type table 1: contact ID and primary email table 2: contact ID and secondary email creates a temp table (qryEmail) that includes: contact ID, primary email, secondary email drops the first two email temp tables

drops the temp table qryPhone if it exists creates 5 temp tables - one for each T-bird phone-type table 1: contact ID & work phone table 2: contact ID & home phone table 3: contact ID & fax number table 4: contact ID & pager table 5: contact ID & cell creates a temp table (qryPhone) that includes: contact ID, work, home, fax, pager, cell drops the first 5 phone temp tables

drops the temp table qryAddress if it exists creates 2 temp tables - one for each T-bird address-type table 1: contact ID and home address table 2: contact ID and work address creates a temp table (qryAddress) that includes: contact ID, home address fields, work address fields drops the first 2 address temp tables

drops the temp table qryWeb if it exists creates 2 temp tables - one for each T-bird URL-type table 1: contact ID and home URL table 2: contact ID and work URL creates a temp table (qryWeb) that includes: contact ID, home URL, work URL drops the first 2 web (URL) tables

SELECTS from remaining temp tables, 1people, and 1organization to create the final Thunderbird flat file:

SELECT 1people.FirstName AS First Name , 1people.LastName AS Last Name , CONCAT( 1people.LastName, ', ', 1people.FirstName ) AS Display Name , CONCAT( LEFT( 1people.LastName, 3 ) , LEFT( 1people.FirstName, 3 ) ) AS Nickname , qryEmail.PrimaryEmail AS Primary Email , IF ( qryEmail.SecondaryEmail IS NULL , '', qryEmail.SecondaryEmail ) AS Secondary Email , IF ( qryPhone.WorkPhone IS NULL , '', qryPhone.WorkPhone ) AS Work Phone , IF ( qryPhone.HomePhone IS NULL , '', qryPhone.HomePhone ) AS Home Phone , IF ( qryPhone.FaxNumber IS NULL , '', qryPhone.FaxNumber ) AS Fax Number , IF ( qryPhone.PagerNumber IS NULL , '', qryPhone.PagerNumber ) AS Pager Number , IF ( qryPhone.MobileNumber IS NULL , '', qryPhone.MobileNumber ) AS Mobile Number , IF ( qryAddress.HomeAddress IS NULL , '', qryAddress.HomeAddress ) AS Home Address , IF ( 1 =1, '', '' ) AS Home Address 2 , IF ( qryAddress.HomeCity IS NULL , '', qryAddress.HomeCity ) AS Home City , IF ( qryAddress.HomeState IS NULL , '', qryAddress.HomeState ) AS Home State , IF ( qryAddress.HomeZip IS NULL , '', qryAddress.HomeZip ) AS Home ZipCode , IF ( qryAddress.HomeCountry IS NULL , '', qryAddress.HomeCountry ) AS Home Country , IF ( qryAddress.WorkAddress IS NULL , '', qryAddress.WorkAddress ) AS Work Address , IF ( 1 =1, '', '' ) AS Work Address 2 , IF ( qryAddress.WorkCity IS NULL , '', qryAddress.WorkCity ) AS Work City , IF ( qryAddress.WorkState IS NULL , '', qryAddress.WorkState ) AS Work State , IF ( qryAddress.WorkZip IS NULL , '', qryAddress.WorkZip ) AS Work ZipCode , IF ( qryAddress.WorkCountry IS NULL , '', qryAddress.WorkCountry ) AS Work Country , IF ( 2workinfo.PriLvl =1, 2workinfo.JobTitle, '' ) AS Job Title , IF ( 2workinfo.PriLvl =1, 2workinfo.Department, '' ) AS Department, IF ( 2workinfo.PriLvl =1, 2workinfo.OrgID, '' ) AS Organization, IF ( qryWeb.WorkWeb IS NULL , '', qryWeb.WorkWeb ) AS Web Page 1 , IF ( qryWeb.PersonalWeb IS NULL , '', qryWeb.PersonalWeb ) AS Web Page 2 , 1people.BirthYear AS Birth Year , 1people.BirthMonth AS Birth Month , 1people.BirthDay AS Birth Day , IF ( 1 =1, '', '' ) AS Custom 1 , IF ( 1 =1, '', '' ) AS Custom 2 , IF ( 1 =1, '', '' ) AS Custom 3 , IF ( 1 =1, '', '' ) AS Custom 4 , 1people.Notes FROM 1people LEFT JOIN qryEmail ON 1people.PersonID = qryEmail.PersonID LEFT JOIN qryPhone ON 1people.PersonID = qryPhone.PersonID LEFT JOIN qryAddress ON 1people.PersonID = qryAddress.PersonID LEFT JOIN 2workinfo ON 1people.PersonID = 2workinfo.PersonID LEFT JOIN qryWeb ON 1people.PersonID = qryWeb.PersonID WHERE qryEmail.PrimaryEmail != '' AND ( 1people.Category = 'Family' OR 1people.Category = 'Friends' ) GROUP BY 1people.LastName, 1people.FirstName;# Rows: 97

The query works with MySQL Query Browser. However, when I run the query with phpMyAdmin, I get "# Rows: 97", but none of them show up on the screen.

If there is a better way to accomplish my objective (a flat-file Mozilla Thunderbird addressbook export from my contacts database), I'm open to suggestions!

pma-import commented 19 years ago

Logged In: YES user_id=192186

Can you post here dump of that table and exact sql query you run?

Simplified code:

CREATE TEMPORARY TABLE t1 SELECT * FROM t2; SELECT * FROM t1;

Works okay here.

pma-import commented 19 years ago
pma-import commented 19 years ago

Logged In: YES user_id=1126753

I'm ready to attach my table structure dump and my query, but don't see a way to attach a file. How do I attach files?

pma-import commented 19 years ago
pma-import commented 19 years ago

Logged In: YES user_id=192186

You should see box for uploading at bottom of this page....

pma-import commented 19 years ago
pma-import commented 19 years ago

Logged In: YES user_id=1126753

At the bottom of this page, I see an "Attached Files" section, but no link to attach a file. I have tried to find the link in Firefox and IE. Perhaps it is due to these fields:

resolution_id Fixed status_id Closed

This is all I see:

Attached Files: Name Description Download No Files Currently Attached

pma-import commented 19 years ago
pma-import commented 19 years ago

Logged In: YES user_id=192186

It's probably because you're neither reporter nor team member ... please open new bug report and attach it there. We'll solve it there.

pma-import commented 19 years ago
pma-import commented 19 years ago

Logged In: YES user_id=1126753

Done. ID is 1110727.

pma-import commented 10 years ago