MYSQL :: Transform Or Pivot(?) Or Crosstab(?) Table In MySQL. One Data Table.

Pivot Table Query Assistance!
Here's the table I'm working with:

Table Name: pprops

pp_id pl_id re_id
----- ----- -----
1     45    19
2     45    22
3     34    19
4     35    29

I'm using this as a pivot table, and would like to be able to pull out pl_id based on multiple constraints of re_id. For instance, using the sample data above, I would like have a SELECT query with the constraints of: re_id = 19 AND re_id = 22
With the result being: pl_id = 45

I thought I could do this using:
SELECT DISTINCT pl_id FROM pprops WHERE re_id = 19 AND re_id = 22;

Unfortunately, because re_id can only have 1 value in it I always get 0 results.

Will I have to use some form of subquery or self-join to get this to work?

17 Separate Statistic Columns Or A Separate Table And A Pivot Table?
Okay, I have a table that lists items. These items can possibly have different values for 17 statistic fields, many of which will be blank for each item.

do I setup all 17 of these as fields in the item table, or should I do as follows:

Items:
id
name
description

stats:
id
statname

item2stats:
item_id
stat_id
stat_value

What are the pros and cons of each? What will be easier to search through? I would want to retrieve these items along with all their stats, and all the stat fields could be possible search fields in an advanced search.

Renaming Table In Database, Restoring Data To Table
A consultant recently hired at my company overlayed/wiped- out a table in a database I created for him. This database was a copy of a live database so I have the original table data he deleted.

How do I rename the product table he created to os-product, it must live in the same database?

How do I create a the product table and import the original data from the live database?

Mysql Crosstab
I have table:
Product : A: B : C: D: E: F: G: H (row 1)
X : 1: 2: 3: 4: 5: 6: 7: 8 (row 2)
Y : 10: 11: 12: 13: 14: 15: 17: 23 (row3)
Region1 is parent of A&B
Region2 is parent of C&D
Region3 is parent of E&F
Region4 is parent of G&H
East is parent of Region1 & Region2
West is parent of Region3 & Region4

Table expected:
X : East : Region1 : A : 1
X : East : Region1 : B : 2
X : East : Region2 : C : 3
X : East : Region2 : D : 4
X : West : Region3 : E : 5
X : West : Region3 : F : 6
X : West : Region4 : G : 7
X : West : Region4 : H : 8
Y : East : Region1 : A : 10
Y : East : Region1 : B : 11
Y : East : Region2 : C : 12
Y : East : Region2 : D : 13
Y : West : Region3 : E : 14
Y : West : Region3 : F : 15
Y : West : Region4 : G : 23
Y : West : Region4 : H : 17

My table is also shown on my attachment.

Help CHECKING FORM DATA AGAINST MYSQL TABLE DATA
PHP

// See if relevant fields have been set.

$submitted = IsSet($_REQUEST['Submit'])   ? TRUE : FALSE;
$user      = IsSet($_REQUEST['username']) ?
                   cleanValue($_REQUEST['username']) : ''
$pswd      = IsSet($_REQUEST['password']) ?
                   cleanValue($_REQUEST['password']) : ''

if($submitted) {                                        // Form submitted yet?

    if (empty($user) || empty($pswd)) {                 // Are Form fields empty?

        // Display error message when fields are not filled in.

$message = 'Make sure you enter both a username and password.'

    }

    else {

/* THIS IS MY HUMBLE EFFORT BELOW  AND IT DONT WORK HELP */

//CONVERT PASSWORD FROM FORM INTO MD5

$pswd = md5($pswd);

//CONNECT TO DATABASE

$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd")
    or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
    or die("Couldn't select database.");

    //SELCT TABLE AND DATA

$sql = @mysql_query("SELECT * FROM users WHERE username='$user' AND password='$pswd' AND activated=Ƈ' ");
if (!$sql) {
  exit('<br /><br /><h3>Error retrieving Art members from database!<br />'.
      'Error: ' . mysql_error() . '</h3><br /><hr /><br />');
}

//MAKE VARIABLES FOR CHECKING FORM DATA AGAINST  IS THIS RIGHT!!

while ($login_check = mysql_fetch_array($sql)) {
  $user = htmlspecialchars($login_check['username']);
  $pswd = $login_check['password'];

  //NOT SURE FROM HERE

  // ORIGINAL SCRIPT BELOW

// Authenticate username and password (i.e. match against a database).
// Start a session only after username and password have been verified.

THIS IS THE EXAMPLE LOGIN CHECK THAT CAME WITH THE CLASS

if ((!strcmp($user, 'empl')) &&         // Does username match 'empl'?
  (!strcmp($pswd,'demo'))) {        // Does password match 'demo'?

            $sess_param['new_sid'] = TRUE;      // Create new session ID to be safe.
            $sess_param['security_level'] = 100;    // Security level for Employees area
            $sess_param['gc_maxlifetime'] = 300;   // Inactivity timeout of 300 seconds (5 minutes)
            $sess = new DB_eSession(&$sess_param);   // session_start() done.
            $sess->setSessVar('authenticated', TRUE);  // Set that authentication occurred.

// Save the username for displaying and encrypt
            $sess->setSessVar('username', $user, ENCRYPT_VALUE, EXTRA_FIELD);

            $URI = $sess->setSessURI('employees.php');  // Optional - pass session ID in URI. Not as safe.

            header("Location: $URI");                   // Go to members area.
            exit;
        }

REGARDS Bill007

Inserting Data From One Table To Another Table In Same Database
I have got about 80 tables (same structure/fields) from my client. I want data of all 80 tables in one single table. There are around 90 lakhs records in 80 tables. How do I get to do this ?

How To Load A BLOB Data Into A DB Table And How To Get It From The DB Table?
Explains how to load a BLOB data into a DB table and how to get it from the DB table? image will be uploaded using cfform.

Transform Excel To Mysql
I have a table in excel....wich i want to transform to mysql table...there are some programs that just do that, but i`ve also had a google and read that just saving it as a .csv and then import it to mysql would do the trick...
is that true?
and my second question, after that....how do i upload the whole mysql table to my website

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

Transform Date To Mysql Timestamp Via CSV Import
I have a CSV file (from excel) with contains personal data, like names, adress, and birth date. The birth date is in this format yyyy-mm-dd. I would like to import this CSV file into my MYsql database with the "import CSV file" function that is build in PHPmyadmin.

The problem is that I need UNIX timestamps in my database, and not the date in yyy-mm-dd format. Code:

Pulling Data From One Table Into Another Table Within The Same Db
I'm trying to pull data from one table and inserting it into another table all within the same db. I am going to delete the first table so not to worry about data dublication.

I've tried SELECT INTO... and
INSERT INTO... VALUES(SELECT...) but neither of those seem to work.

Extracting Data From Large SINGLE-table Database To MULT-table Relational Database
I have a very large single-table database of articles that I want to convert to a multi-table, relational database.

The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles.

I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field.

I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.

Editing Row Data In A Table Using Mysql
I'm trying to edit the data in a table using mysql.

As far as I know I can view the data in the particular row of the table using the comand: "select row.name from table.name;

And mysql has the "edit" command.

Is it possible that I can use the "edit" command to edit the data in the table?
What command should I use? If not what is the simpliest, command line method can I use to edit the data in the table?

"mysql --version" returns:
mysql Ver 12.22 Distrib 4.0.21, for pc-linux (i686)

Mysql Table Data To Excel Using Php
how do i put mysql table data to ms excel work sheets.

Upload .mdb Access Data Into MySQL Table
I'm new to mySQL, but used to SQL Server.....which I'd be using DTS for this whole process. I'm glad to have to learn to do it with mySQL though.

I have an Access .mdb file, that I need to upload from my local machine to our hosted mySQL site via BlueHost.

I'll be using phpMyAdmin. It looks like the only way is to import into an existing table is from a text file.

Are there any other ways to take the .mdb file and copy it up, and into the mySQL table?

I got a converter tool, Access2MySQLPro 5, which has 10 trial conversions. Didn't know if anyone else has better ideas?

Basically, I want to take this .mdb file data and put it on the mySQL table.

Sort MySQL Data By Field In Second Table
I've got a table, 'Events', with a series of fields, one of which is 'CompanyID'. This is an integer that refers to another table, 'Company', which is a list of IDs and companies.

Now, is it possible to select from the first table of 'Events' and sort the data by the company name in 'Company' referred to by the 'CompanyID'?

Import Data From .txt File Into MySQL Table
i have text files which are generated daily and stored in a folder on the C: drive. I was wondering what would the mySQL command be to import that data into a mySQL table. The text file does not have a header with the field names so would i have to make that table first with the desired columns?? Also, does the file have to be in the databse directory or can it be anywhere on the machine??

MySQL Table Upload Large Data
Via MyAdmin I have inserted data in a MySQL table. But the number of inserting at ones is limited. How do I need to insert data from a ms access large table in one step in a new or existing MySQL table ?

Can't Edit Table Data With MySQL Administrator
I'm just getting started with MYSQL. I can create a simple database and add some records to it. I can view the tables with MySQL Administrator. However, "Edit Table Data" is greyed out. What is the magic incantation to enable this feature?

Loading Lots Of Data Into Mysql Table
So I need to load lots of data into my database.

So I discover LOAD DATA INFILE.

Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.

Only one problem. It truncates fields to 256 characters, even on a
text field.

Send Data From Excel Via ODBC To MySQL Table
I have an excel file that updates continuously with new data. I would like to send this data to a MySql table on a periodic basis, say every 5 mins or so, via ODBC. Does anyone know if this is possible?

Recovery Data From Delete Query And Multi Table Deletion In MySQL
When I test the Delete multi table function in MySQL,
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table_name[.*] [,
table_name[.*] ...]
FROM table-references
[WHERE where_definition]

I accidentally delete all data in one table. All data in that table
are gone when I try to select them out in Control Center. But when I
go into the /mysql/data/mydatabase/, I see a MYD, MYI, frm for that
table. And it seems that data is still inside the MYD, althought it's
not formatted correctly. So my first qusetion is whether we can
recovery data from an accidentally delete action, using MYD or some
other log files.

My second question is about the multi table deletion in MYSQL
For example : "DELETE t1,t2 FROM t1,t2 WHERE t1.id=5 AND t2.id=5"
It only runs successfully when all table (i.e. t1 AND t2) have row(s)
whose id is equal to 5. In other words, it would NOT delete rows with
id = 5 in t1 when t2 has NO record with id = 5, both tables must at
least have one record with id =5. So is it the case for multi table
deletion in MySQL or I have done somethings wrong?

Data From Table To Table
I getting this error code 1064 when inserting some data into a table.
The funny thing is that the data that I am inserting, comes directly out of another table.I get the error message when I use quotes in my message.

example:
test 'bug' test

The total error message is:
1064: You have an error in your SQL syntax near 'bug' test'' at line 1

does anyone have any idea how to solve this?

Help With Moving Data From One Table Field To Another Table Field
I am a relative newbie at this so would appreciate help - already searched and found and tried several suggestions for similar issues - but nothing quite worked!

I need to move a membership roster to another table in the same db - currently the data is at jos_users1 and it needs to be moved to jos_users. The jos_users1 table has only one field, named email.

The jos_user table has several fields including the email field, and already has data in it - so I don't want to overwrite the table - just upload the additional email addresses into the table.

Using Table A Column Row Data For Table B Column Headings
Is it possible to add column data in table A and have (dynamically
linked) table B column headers?

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan

Crosstab Stored Procedure
I have a stored procedure, which works, as follows:

BEGIN
Select timesheet_eventid, timesheet_teetime, timesheet_tee,
sum(IF(timesheet_player = "P1", timesheet_membid, '')) AS "Player1",
sum(IF(timesheet_player = "P2", timesheet_membid, '')) AS "Player2",
sum(IF(timesheet_player = "P3", timesheet_membid, '')) AS "Player3",
sum(IF(timesheet_player = "P4", timesheet_membid, '')) AS "Player4"
FROM view_crosstab
WHERE timesheet_eventid=prmEventID
Group by timesheet_teetime;
END

However Instead of putting timesheet_membid in the body of the result, I want to put "player". I have tried the following, however it only puts in P1 (not P2, P3, or P4).

BEGIN
Select timesheet_eventid, timesheet_teetime, timesheet_tee,
(IF(timesheet_player = "P1", player, '')) AS "Player1",
(IF(timesheet_player = "P2", player, '')) AS "Player2",
(IF(timesheet_player = "P3", player, '')) AS "Player3",
(IF(timesheet_player = "P4", player, '')) AS "Player4"
FROM view_crosstab
WHERE timesheet_eventid=prmEventID
Group by timesheet_teetime;
END

How do I get this cross to work properly?

Extract, Transform
We have an old legacy database at my company called CIMPRO, running on
a SCO UNIX box. We're trying to find a way to connect to the CIMPRO
database, grab the data, and send it to a Mysql database.
I've been able to successfully connect to the database using PHP and
ODBC to view the data in the tables.
i'd like to find a way to run a Linux tool or script to connect to the
database, do an initial copy of the table structure from the legacy
database to Mysql (on the linux box) and then copy selected data.
If that is doable I'd also like to be able to refresh the table data
periodically (e.g., cron job) either by deleting and reimporting or by
checking to see what has changed.

Mysql Move Table Content To Another Table
How do I move the contents from one table to another table in the same
database. ie the contents of "cart table" to "orders table" thus removing the
contents of the "cart table" and placing it in the "orders table".
I have looked everywhere for a simple method other than the long winded Select, Insert and the Deleting.

Adding Visual Table To MySQL Table
I just successfully created my table that is up and running. I also created an HTML form that allows me to submit new information. I have seen people that created tables and when they view the table they add style (color, fonts, etc..) to it as well as actually put the data in a visual HTML table. I was wondering how I would do this.

Transform '07,01,06' Into A Date Format With Just A Query
I've been handed just about the worst HTML/PHP/MySQL site ever scrapped together. While we're in the process of rebuilding the site from scratch for our client, they desperately need some data pulled from their DB.

Originally the data was dumped to files but due to attempted injection attacks, the table has grown so large it times out. So I'm trying to pull the data directly but I have a huge problem. I need to pull all data with a date >= July 1, 2006. The problem is the dipsh*t who built this has the date field with a datatype of varchar and the date in the format of གྷ,01,06'. Obviously I can't do a date comparision check with that so how can I transform this data into a correct date format using only a query? I do NOT want to go in and start coding in this site. It's a live site and I'm afaid anything I do will break it.

It's MySQL 3.23.57 and all I have access to is PHPMyAdmin. PLEASE help me!!! Someone work a little query magic for me.

Transform SubSelect In OUTER JOIN
maybe I'm simply to dump but I could not transform this SQL-Statment
which uses a Sub-select and create on that uses an OUTER JOIN ....

Select And Union Result Into Crosstab-type In One Query
Maybe this is impossible?

I have four tables of identical structure but not related.

I am running a Select on four tables with three Union All. I would like the result from the Select and Unions (which work fine) to be routed into a Crosstab-type query to gvie the result from each table in columns. But here is the difficult bit, I would like to do this in one query.

As I am new to MySQL, I am confused by subqueries and temporary tables and how to reference them, though I realise the latter may be the way to go.

CODE ...

Table Data
It may be better to explain the problem, I have about 500 tables in html all with different table headers and table detail contents. Which refer to products e.g.
table"1"
Model weight color etc.
WNC12 16 kg black
table "2"
Model depth finish etc.
WBC14 24" Steel

I want to call up these details via a product ID in php/mysql having different data is this possible without having to have 500 tables?
Is there a simpler way? please tell me if I am wasting my time.

Table Data.
I have normalised my data and am left with the following tables:

Code:
USER user_id,fname,lname,tel
BOOK book_id,user_id,book_name,book_desc,b_date
CHAPTER chapter_id,book_id,chapter_name

In my web-app, a user can create books with 1 or more chapters. My question is this:
When a user creates a book, do I have to perform 2 SQL queries.......

INSERT INTO BOOK.....
INSERT INTO CHAPTER.....

......or is there a way to do it in one query

Copying Data Into Another Table?
Can someone please tell me how I can copy one item in one row into another table? I am wanting to do the above when a comment is being added to the second table. Thanks in advance.

My tables are like this:
Article (Articleid, selectDay, selectMonth, selectYear, article_item)
Users (Userid, username, password, email, ip)
Commentpost (Articleid, userid, comments, date_entered)

Shifting Data In A Table
Is there a way to shift data in a table.

for instance:

q_1 = Ƌ'
q_2 = Ƌ'
q_3 = Ɖ'
q_4 = Ɖ'
q_5 = ''
q_6 = ''

shifts so that:

q_1 = ''
q_2 = ''
q_3 = Ƌ'
q_4 = Ƌ'
q_5 = Ɖ'
q_6 = Ɖ'

I hope that makes sense. Basically, I have some data that needs to be inserted in the middle of a table and shift the existing data to the right.

Moving Data From One Table Into Another
I have a users table that has 20 columns (table A). I want to be able to take half of those columns and move them into another table (table B), and use B as a lookup.

Table A (currently)
user_id
fname
lname
ip_address
date_added
I want to make it like this:

Table A
user_id (PK)
fname
lname

Table B
tbl_A_user_id (FK)
ip_address
date_added
The problem is that I have some clients using versions on their site, and need to be able to move the data from A into B, then drop the columns in A without losing data.

Extracing Data From A Table
I have a table that stores information on registered members. For each member I store: ID and the datetime that he registered

like so:

Need To Import Data Into Table
Newbie here needing to import data into a dbase file table named "template". The file containing this data is "template.dump".

Input Data Into Table
how do I write the SQL query that I would use to input data into table

Input Data Into Table
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$Email = $_POST['Email'];
$City = $_POST['City'];
$State = $_POST['State'];
$Country = $_POST['Country'];
$DriverType = $_POST['DriverType'];
$TruckYear = $_POST['TruckYear'];
$Truck = $_POST['Truck'];
$DOB = $_POST['DOB'];
$Height = $_POST['Height'];
$Weight = $_POST['Weight'];
$Picture = $_POST['Picture'];.........................................

Exporting Data From Table
I am trying to find a way that i can 'export' the information that i have captured in a table, as a csv file - or even txt file. i need this information so that i can send it to a client everytime the information in the table is update. Is this possible and would someone be able and willing to assist me with this?

Export Only Table Data
Is there any way to export only table data into a text file not like dumping other informantion of the table like mysqldump does.

Inserting Data Into Table
My task is to create a table which works as a "linker" of two other tables. In this linker table as foreign keys I am using keys from this two tables.

I am inserting data into first table t1 in one loop in Perl script, then in a second loop I am inserting data into table t2 and immediately after into t3 which is a linker table between t1 and t2. I have no problems with id (primary key) from t2 , but while extracting keys from t1 there is message:

Subquery returns more than 1 row
after subquery:

insert into t3 (t1id,t2id) values ((select id from t1 where name like 'x'),(select max(t2id) from t2)); simply because value '' are present in table t1 two times so it has two keys. How this problems can be solved?

I tried to use:
insert into t3 (t1id,t2id) values ((select id from t1 where name like 'x'=ANY(select id from t1 where name like 'x')),(select max(t2id) from t2));

however then there was a message:ERROR 1048 (23000): Column 'id' cannot be null because it is t1 primary key? Are there any ways of dealing with this problem?