MYSQL :: Separating Parts Of Data Into Different Rows

Separating Mysql From Code
I have an exorbitant amount of data that I would like to put into a MySQL database and eventually put into a LAMP-based CMS (by a professional developer) and eventually uploaded onto a website for all people to access.

Is it possible to load information into MySQL before any coding is performed by a developer, before any parts of the CMS are scripted? Can MySQL operate on its own as a database independent from any coding or do I need a developer to set it up before data can be loaded onto MySQL?

If this is possible, is there any sort of language I would need to be fluent in or can it be done using a graphic interface?

Count() Ignoring Parts Of Where Clause
I have a select count() that seams be ignoring one clause. Data:

Table:
ID CA-Char CB-Int
1 dsfsd 6
2 dsfsd 0
3 dsfsd 1
4 sdrtt 1

SQL is:
"SELECT count(id) FROM Table WHERE CA-Char = 'dsfsd' AND CB-Int > 0"
Returns 3 - Should be 2

Does anyone know what could be going on? I have tried searching with
no useful results, most examples imply this should work.

Divide Items In Database Into 5 Parts
I'm looking for a way to divide my articles into 5 groups that are roughly the same size. I've been doing 2 groups using:

UPDATE jos_content
Set `fulltext`=Concat("{sidebar id=2}",`fulltext`)
WHERE mod(id,2) = 0
AND state="0";
UPDATE jos_content
Set `fulltext`=Concat("{sidebar id=1}",`fulltext`)
WHERE mod(id,2) = 1
AND state="0";

and each group gets a distinct sidebar inserted. I need to change this and divide the items into 5 groups. I'm not sure if there's a way to do this but thought maybe a query that said something like "if ID ends in 1 or 2, then insert sidebar1" and so forth using 2 ending numbers for each group. I can't figure out how to do this.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
Iâ€™m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each playerâ€™s stats (so theyâ€™ll be one row per player) and display as output, which isnâ€™t a problem. Then I want sort by a certain stat, but by now Iâ€™ve already looped through the table, so I canâ€™t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. Whatâ€™s the best way to approach this?

Selecting Data In Rows
I am trying to select some data in a column but can't seem to figure it out.

the table name is "Parameters"
I want to be able to just select the "catch_addr" from the value column so all i get is the email address for the id of 33

mysql> select * from Parameters where id = 33;
+----+---------------+--------------------------------------+
| id | parameter | value |
+----+---------------+--------------------------------------+
| 33 | bounce_mess | This address no longer accepts mail. |
| 33 | catch_addr | some@email.com |
| 33 | nonexist_mail | catch |
+----+---------------+--------------------------------------+
3 rows in set (0.00 sec)

Adding Up Data From Multiple Rows?
If I have numerical information stored in different rows what is the best method to find the sum of all the numerical information in the rows?

Import A Csv To Update Only Certain Rows Of Data?
I have an existing mysql table and a csv. I want to import the csv into the table, but it is at a different sort order. There are too many records to sort my csv by hand to match. There is a common 'cd_code' field that both have.

Is it possible to import a csv to the table so that each row of the csv is inserted in the correct row of the table, based on the common 'cd_code' field?

For example, if I sorted by cd_code asc, it would match the csv, but how to I import the csv into a particular sort?

How To Import Data Into Existing Rows Of A Table?
I have moved 665 records off of Filemaker into mysql-4.0.18. I had a
great deal of trouble exporting a text field, possibly because it had
control characters that interferred with my field delimiter (also it
was from a Mac, and I'm new at this.)

So I have a table called invoices and another table called tasks, that
has only an invoice number and the work billed on that invoice number.
To see the work billed along with the other invoice data I have to do
a join, and this works but is clumsy for me. Since tasks.work is a
text field of 'infinite' variety I don't see the sense of keeping it
in a separate table like I would with clients' info; it belongs *in*
the invoice table, not just with it.

Is it possible to import this text data into a field in the invoice
table? I've messed around with this but it always appends the data as
new records. Perhaps I was doing an insert back then. Is the answer
something like 'load data infile 'workdone.csv' into invoices
(workdone);'

The invoices table was filled in a certain order (by invoice number)
and the workdone.csv was exported in the same order, so each invoice
row should get its correct text.

Is this a good thing, or am I thinking too 'flat-file-ish'?

Inserting 100 Rows Of Data Into An Existing Table
I want to import 100 rows of data from an Excel spreadsheet into an existing MySQL table. Both the spreadsheet table, and the MySQL table have the same exact format & headers.

In MySQl Query Browser, I can locate only the Edit function, which seems to only allow me to type in the data line by line. Can I import the data in MySQL Query Browser, or do I need another product? And if I can import, how do I do this?

Loading Data Results In NULL Rows??
I am using LOAD DATA very simply but it is resulting in all the rows being NULL and I can't understand why.

Here is what I'm doing:
LOAD DATA INFILE '/var/www/Data/testfile.csv'
INTO TABLE trial.testdb
FIELDS TERMINATED BY 'Â¿'
LINES TERMINATED BY '
'
IGNORE 2 LINES;

The command runs successful but when I check the data, the first column which is a date field, comes up at 0000-00-00 and all other columns are loaded as NULL.

Any clues on why this is happening? The # of columns in the file match up with what's in my table.

Check For One Piece Of Data In Multiple Rows.
I have a table: (arbitrary names bellow)

main_id
info
info
info
id1
id2
id3
id4
id5
id6
id7
id8
id9
info

----
id1-4 will always have info 5-9 may be null. I need to do a select that will find if certain data is in id1-9 and return all rows where this matches.

SELECT *
FROM `recipes`
WHERE id1 =1032 OR id2 =1032 OR id3 =1032 OR id4 =1032 OR id5 =1032 OR id5 =1032 OR id6 =1032 OR id8 =1032 OR id9 =1032
ORDER BY class LIMIT 0 , 30

is a bit cumbersome. is there a better way to do this query?

Also... I am not concerned about speed when row is added to a table, but want speed when this query is made. is there a good index structure to make? And if data is already in, is there a way to make it re-index the table?

SQL*plus Inserting New Row With Values = To Existing Rows Data
I am trying to insert a new employee into an empolyee table and get some of the values (namely phone no.) to be the same as what another employee currently has. This new employee is replacing the old employee, but i have to keep the old employee alive in the db as other data is reliant on its existance.

I was wondering how do i insert the new employee asking the, VALUE ('etc', 'etc',) line, to assign a value to certain fields equal to the value contained in other employee rows.

Error: 1329 No Data - Zero Rows Fetched, Selected, Or Processed
I have some processing that requires two cursors, one within the other. I am using:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row_fetched=1;

to detect when I have fetched all the records within a cursor. However within the cursors there are other UPDATE and SELECT statements.

My question is:

Is Error 1329 only thrown by the FETCH statement?
Or can SELECT and UPDATE statements also throw 1329?

I wrote some test code that I believe shows 1329 is NOT thrown by SELECT or UPDATE. But maybe someone could add some additional info.

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A..

Example:

B.part = "abcdefg" matches A.part ="cde"

I cannot put literal in query.. it must be from column data..

It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.

Converting From Many Columns/few Rows To Few Columns/many Rows
So I have a table that looks like:

Code:

char_id1234(...up to 30)
582NULL416
25739NULL12
391NULLNULLNULL

Each char_id is the primary key in another table, each numerical column value is the primary key (skill_id) in a third table, and each data value is the primay key (rank_id) in a fourth table.

I am attempting to join all four tables into a single query, but it seems impossible without converting the table to something like this:

Code:

char_idskil_idrank_id
5812
5834
58416
2517
25239
25412

Does Mysql Store Max Size For Data Type Each Time, Or Only What Is Needed For Specific Data?
If I use MEDIUMTEXT, which (if I understand correctly) has a max size of 65k, does mysql use the entire size each time data is written to that column? I have a text field on my site that users fill in, but more often that not, they don't. However, when they do, it can be 1000 bytes or more. Am I wasting space using MEDIUMTEXT or does mysql only set aside 1000 bytes for that column if the data is only 1000 bytes?

How To Export Data From Phpmyadmin Into LOAD DATA INFILE Syntax
I have a table with a lot of data (several millions of records).
I would like to export the data using phpmyadmin into a SQL file. I know that phpmyadmin has an export feature.

I need the exported data to be in the LOAD DATA INFILE format after the export. Is there a way to achieve this?
Regular export uses the INSERT INTO ... syntax for the data.

LOAD DATA INFILE To Import Data Exported From A MS Access Xp
I am trying to use LOAD DATA INFILE to import data exported from a MS Access Xp table. But all i get in the date field is 00:00:00, as Access exports DATE and TIME and MySQL seems to use just the TIME portion of this.

Is there any way to ahve MySQL import just the DATE portion or is there any way to have Access export only the DATE portion?

What MYSQL Data Type Can Hold Multiline Text Data?
say a file:

name#1 (start)
line2
line3
...
line14 (end)
name#2
....
name299
....
<eof>

It's really too messy to split into fields, file process and put it in tabular format. Every 14 lines there's a new "record" - I use the term record very loosely because by record I mean one data item eg. The new "records are separated by name# - so when I see a new name# I know it's a new "record". So in mysql I might have one primary key and just one other filed per a record that would be multiline text.

What I an specifically asking is:

What MYSQL data type can hold multiline text data?

Split Innodb Data File Into Smaller Data Files
we configured innodb configuration with default my.ini settings. now data file is increased more than 40 GB.After that we come to know that we need keep the settings like below

#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

so that it will keep on creating 2GB data files.
Now how we can change the settings and splitting the data file of 40 gb please.

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

Add Additional Data To Existing Data In A Blob Field
I have a Blob field called "category" that we use to store different categories of job types that our users set up so they receive emails based off of the ones that they have picked. What I need to do is to add 4 new values to the beginning of each users "category" field.

I am not really sure how to do this except that I might need to use UPDATE to get it done. This is where it will get tricky, I need to check for the 4 values to see if they already have them and just add the oines that they don't have.

Here are the 4 values that I need to check for and add. Allied Health Care, Nursing, Rehab/Therapy, and Other/Pharmacy/Physicians. Here is what a current list of values would look like in the users table and the category column: Code:

LOAD DATA Fails When Non-ascii Chars In Data
I am trying to load a cities database that is in CSV format. Some of the fields contain extended characters like the umlaut or accented chars. The load fails with: Code:

Data Truncation: Data Too Long For Column 'DESCRIPCION' At Row 1
I'have this problem when i try to insert a simple text in a column VARCHAR (40).
If I exceed the 14 chars size, this exception is fired. I'm working with the mysql 5.0.41 and fails in the query browser and in my java app with the conector 5.0.6.

I'd tried to change to UTF my table, change to a VARCHAR(100),change to CHAR (100) and nothing it's function.

Fill Up NULL Data With Data Whose ID Is Equal
I have this table

id name
-----------
1 Daniel
1 NULL
2 Marc
1 NULL
2 NULL

and I need this one

id name
-----------
1 Daniel
1 Daniel
2 Marc
1 Daniel
2 Marc

Restore Data By Copy The Data File
i have restored the data by copying the data file into c:mysqldataabc It works.

But, I have the problem as I want to copy another updated data files (*.frm, *.MYD, *.MYI) into the folder.

All data is lost! but, the size of the data file is not zero.

If I do the same action to replace the old data files, it works again.

Now, how would I can restore the updated data?

Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with.

My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear.

What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear.

My Table...

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
4..............QD...................4......................2007 (current month and year)
5..............QE...................5......................2007
6..............QF...................6......................2007

I can simply get the question for the current month...

SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007;
but if I try to get all others using

SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007
I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
*********************(remove runMonth4 and runYear 2007)
5..............QE...................5......................2007
6..............QF...................6......................2007

What am I missing?

How Many Rows?
I've a query as below, where i need to get the number of rows it produces that is the count of number of rows. Plz help .

Select * from audio left join video on (audio_sid=video_sid and audio_prgm_num=video_prgm_num) left join
pcr_info p on (audio_sid=pcr_sid and audio_prgm_num=pcr_prgm_num) right join stream s on (stream_id=audio_sid) where audio_type =
'MPEGLayer1' and isnull(video_type) group by stream_name order by stream_name

Sum Rows But The Max
I'm trying to create a query that calculates the sum of a bunch of rows then return the max (or the highest) value of those sums. So for example I have a table called imgarchives with a field called LinkBacks. I want to do SELECT SUM(LinkBacks) FROM imgarchives ORDER BY LinkBacks DESC LIMIT 0,1

The trouble with that is that I'm not looking for a sum of ALL the columns, I want to create a sum for all the archives based on BlogID. I was thinking GROUP BY might be necessary but I'm not sure how to use that with SUM(). My thoughts are:

SELECT SUM(LinkBacks) FROM imgarchives GROUP BY BlogID ORDER BY LinkBacks DESC LIMIT 0,1

That doesn't seem to work. -_- ack.

Add Of Rows
i have a table with data (time[int] und output[int]). I need a column in that the difference between the previous measurement and the current measurement is inside.

Rows Between 10 And 20
I want to select all rows between 10 and 20 from a view how can I do this?

Top N Rows
is there a way in mysql to find out the top n rows?

for example, employee names of top 3 salary holders...

First 'N' Rows Of Each
For example: Table (type, serial#), with data

('X', 1000)
('X', 1001)
('X', 1003)
('X', 1010)
('Y', 1002)
('Y', 1005)
('Y', 1011)
('Z', 1015)

How would I retrieve at most 2 rows for each type?

('X', 1000)
('X', 1001)
('Y', 1002)
('Y', 1011)
('Z', 1015)

Getting All Rows
I've two tables audio and video and need to get all the rows from two tables by joining. How do i do that.I need all the columns from both the tables. audio_sid and video_sid from both tables are same along with audio_prog_num and video_prgm_num from audio and video tables respectively.

Rows
does there exist a mysql query option that would only find rows, whert the first letter of a given varchar-column is caps?

or in other words to give a php-pendant, a mysql-query that would do about that righti within the query:
PHP Code:
if (preg_match ("/[A-Z]/", substr ($query,0,1))) { echo $given_back_row; }

Certain Rows
I am trying to extract certain rows from a database table that contains the following fields:
bookingid, bookingdate, bookingestateid, bookincustomerid, bookingconfirmed.
My SQL is:

SELECT * FROM booking WHERE bookingDate > '2005-10-16' AND bookingConfirmed !=0

I want to count the number of rows from the bookingdate (which is dynamic) until the next row where bookingconfirmed is anything other than 0. I can't work out how to do it.

50 Rows
I have a view that references another view that references some tables and views.
When I perform SELECT * FROM myView I get all rows as expected. When I perform SELECT myColumn FROM myView I only get 50 rows.
Is there any reason for this behaviour

How Many Rows
I've a query as below, where i need to get the number of rows it produces that is the count of number of rows. Plz help .

Code:

Select * from audio left join video on (audio_sid=video_sid and audio_prgm_num=video_prgm_num) left join
pcr_info p on (audio_sid=pcr_sid and audio_prgm_num=pcr_prgm_num) right join stream s on (stream_id=audio_sid) where audio_type =
'MPEGLayer1' and isnull(video_type) group by stream_name order by stream_name

Get Best Fit Of Rows
I was wondering if its possible in Mysql to pull out the rows in a table that values add up to a certain number - but making sure the best fit is achieved. For example, the table has 2 columns:
record and cost (cost being a decimal field -ignore the £ sign)
rec1 £0.50
rec2 £1.50
rec3 £0.25
rec4 £1.00

Lets say I have a cost of £5.00 I want to get the records that fits into £5.00 (having the least amount of records). So the best fit would be:
rec2, rec2, rec2, rec1

Is there a way of Mysql returning these 4 rows? Or at least just the 2 rows rec2, rec1?

100 Rows In A Db Better Than 1
you have a db for auto repair shops. One table is called services. It has two columns, one for ID and one for the service title. Now let us say you have 100 services.
Now let us say you have a table called shops. That table has typical info about a shop (address, name, tel, etc) and also an id field.
As I have been taught if I want to specify which services a shop offers I SHOULD NOT add a column to the shop table called "services" and enter a concatenated list such as - 1, 23, 34, 45, 55 (where each number is a service) but rather I should have a third table with two columns, one for service ID and one for Shop id.
Now if a single shop offers 100 services that would be 100 rows. If 10 shops have 100 services that is 1,000 rows.
Why is this better than having one more column in the shop table.