Date: 12/12/05     Keywords: php, mysql, database, sql

I've just set up a testing server on a windows platform, it's not ideal but it's better than nothiing, for myself, when trying to create a table in a database, either manually or thru phpmyadmin, the MySQL server stops responding. I'm using the latest release of MySQL although that seems not to matter as it seemed to do it with an earlier version. Any ideas on this??

Source: http://www.livejournal.com/community/mysql/76242.html

Date: 12/10/05     Keywords: mysql, sql

Say I have a row with three scores:

taste=4
price=3
value=3

Can mysql figure the average of these three columns with one query?

Source: http://www.livejournal.com/community/mysql/75966.html

Date: 12/08/05     Keywords: mysql, rss, software, database, sql, linux

Any ideas?

CREATE TABLE `LAD_RSSDB_FEEDSTER_CORE` (
`RSS_ID` int NOT NULL auto_increment,
`SITE` varchar(64) DEFAULT 'rssdb' NOT NULL,
`ACCOUNT_ID` int DEFAULT -2 NOT NULL,
`URL` varchar(900) NOT NULL,
`CHANGED` tinyint DEFAULT 1 NOT NULL,
`STATUS` tinyint DEFAULT 0 NOT NULL,
`LAST_PINGED` datetime DEFAULT '1970-01-01 00:00:00' NOT NULL,
`LAST_DOWNLOADED` datetime DEFAULT '1970-01-01 00:00:00' NOT NULL,
`TIMESTAMP` timestamp NOT NULL,
`PING_INTERVAL` bigint DEFAULT 144000 NOT NULL,
`CRC32` int DEFAULT 0 NOT NULL,
`STRIKES` int DEFAULT 0 NOT NULL,
`FIRST_CREATED` datetime NOT NULL,
`CREATED_BY` varchar(50) DEFAULT 'unknown' NOT NULL,
`NM_PROCESSED` int DEFAULT 0 NOT NULL,
`NM_CUMUL` int DEFAULT 0 NOT NULL,
UNIQUE KEY `RSSDB_FEEDSTER_CORE_IND1` USING BTREE (`RSS_ID`),
KEY `RSSDB_FEEDSTER_CORE_IND2` USING BTREE (`URL`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

and I got the following error message:

#HY000Can't create table './rss/LAD_RSSDB_FEEDSTER_CORE.frm' (errno: 139)

Machine is l00nix and build is:
root@mosdef [6:43pm]:86:/data# mysqladmin version -p
Enter password:
mysqladmin Ver 8.41 Distrib 5.0.16, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.16-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 5 days 1 hour 57 min 29 sec

Threads: 7 Questions: 11430901 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 34 Queries per second avg: 26.036

The production version of this will be on FreeBSD - is this a locale issue? Do I need to recreate databases and/or recompile?

Source: http://www.livejournal.com/community/mysql/75539.html

Date: 12/06/05     Keywords: php, mysql, sql, linux

It's me again.

Got this message through phpmyadmin:

Warning: Your privilege table structure seems to be older than this MySQL version!
Please run the script mysql_fix_privilege_tables that should be included in your MySQL server distribution to solve this problem!

I'm on Linux (Ubuntu). where is this function/module and how do you call it? Through mysqladmin?

Source: http://www.livejournal.com/community/mysql/75435.html

Date: 12/06/05     Keywords: php, mysql, sql

I have a DB. Had RedHat 9.0. Went to Ubuntu (I love it). I've been setting everything back up. Before wiping the drive, I copied my entire DB folder (had mysql folder and my db folder). I thought that was sufficient to back it up. Upon installing Ubuntu, I apparently have a newer version of MySQL.

I usually use phpmyadmin to manage the db. It's going crazy with errors not being able to find tables (mainly .frm files).

I think I made a serious mistake. Was I supposed to use some kind of export command before making it portable? Is there any way of even getting my tables into a normal view? At this point, I don't even mind manually entering everything as long as I can get it viewed!

I guess what I'm asking... Am I screwed??????

Source: http://www.livejournal.com/community/mysql/75091.html

Date: 12/02/05     Keywords: mysql, sql

So MySQL 5.0 has stored procedures..  Could someone touch on why I would want to use these in an environment developed around 4.1? 

Source: http://www.livejournal.com/community/mysql/74834.html

Date: 12/01/05     Keywords: no keywords

Hi, everyone!

I have several tables, containing info about users. There're two keys 'id' and 'name'.
I need to output all information about some user on administrator's demand having these two keys in WHERE clause.
The problem is the user could have some fields in some tables empty, so when SELECT finds such fields it returns 'Empty set' to me and I wish it to display any existing information about distinct user nevertheless.
Does somebody know how this problem could be solved?

Source: http://www.livejournal.com/community/mysql/74579.html

Date: 11/30/05     Keywords: php, sql, web

I'm pretty new to db design. I had a class on MS Access/SQL back at PSU, but never really used it. So, I'm familiar, but I still don't know how to figure things out very well.


Anyway, here is my problem: I am attempting to make a db for the upcoming college bowl season. Every year, my family picks the bowl games and whoever wins gets a special prize. I want to make it into a db this year, though, so that you can view any pickset without having to see ALL of them (there are 28 bowls + at least 10 people, so you can see that is a ton).

I tried making a table with the bowls in it like this:

ID | Name | Winner
------------------------------
1 | Bowl 1 | Team 1
2 | Bowl 2 | Team 2

then tried making a table with the picks in it like this:

ID | Name | Pick
-------------------------
1 | Jim | Team 1
2 | Tony | Team 2

but as you can see that doesn't work out, because each person only gets one pick in the table.

So the next thing I tried was making a seperate table for each person's picks with just the bowl name and the pick in it. It seems like that will amount to a ton of tables, though, so I'd rather try something more compact.

The next thing I toyed around with was making a table like so:

Table: Pickset

ID | Name | Bowl 1 | Bowl 2 | Bowl 3
--------------------------------------------------------
1 | Jim | Pick 1 | Pick 2 | Pick 3
2 | Tony | Pick 1 | Pick 2 | Pick 3

But I can't figure out how to relate it to the other table. Maybe I don't need to? I will be using PHP to display it on the web, by the way.

Basically I want the page to say:

Display Picksets:
Jim [ ]
Tony [ ]
SUBMIT

So that whatever you check off will display in a nice table.

Can anyone help me out here..?

Source: http://www.livejournal.com/community/mysql/74483.html

Date: 11/29/05     Keywords: php, mysql, sql

Уважаемые mysql-гуру,

никак не могу найти решение для (как мне казалось) простой задачи.
Ситуация такая: на сайт загружается графический файл. Его название и номер объекта, к которому относится этот файл, заносятся в таблицу.
При этом мне те же значения ('$id','$filename') нужно занести в ещё в одну таблицу.
Можно ли сделать это за один прогон?
Мне не знаком вариант insert с двумя таблицами.
Может вы что-то подскажете?

спасибо! 

upd
sorry, a wrong community (or a wrong lanuage for the rihgt community)
the question was if it’s possible to insert values into two tables at once – I
upload a file and insert information about this file into two tables
(PHP&MySQL).
I’d like to know if there is a way to do it with one insert
only …

Source: http://www.livejournal.com/community/mysql/74109.html

Date: 11/19/05     Keywords: no keywords

i have this query:
SELECT grps.groupid, grps.title, grps.create_date, grps.leaderid AS userid, grps_post.userid AS lastposter, grps_post.dateline AS lastpostdateline, COUNT( grps_post.userid ) AS replies
FROM grps
LEFT JOIN grps_post ON ( grps_post.groupid = grps.groupid )
GROUP BY grps_post.dateline
ORDER BY grps_post.groupid

kinda, what i'm trying to make it do is SELECT the first row for the rest of the records, but the last row (i.e. the row with the highest dateline value) from the grps_post table along with the unique line from grps table, what am i doing wrong as i only seem to be able to retreive the first line?

i should know this, i'm pretty sure i do during the day time, but have no clue right now.

Source: http://www.livejournal.com/community/mysql/73661.html

Date: 11/18/05     Keywords: no keywords

SOLVED!

I have a table that has two fields. Each field contains a project number, which is the primary key of the project table. I'd like to be able to run a query where I join this table to the main table so that I can get the project titles (another field) for both of the two numbers. I tried this:

SELECT pn_project_rel_proj.relid, pn_project_rel_proj.proj1, pn_project_rel_proj.proj2, pn_project_rel_proj.relation, pn_project_projects.projid, pn_project_projects.title
FROM pn_project_rel_proj
LEFT JOIN pn_project_projects
ON proj1 = projid
LEFT JOIN pn_project_projects
ON proj2=projid
WHERE proj1 = '2' || proj2 = '2'

The error message tells me that it won't work because the "pn_project_projects" isn't unique.

How can I get this to work?

Source: http://www.livejournal.com/community/mysql/73399.html

Date: 11/16/05     Keywords: php, mysql, sql

Hi, I feel a bit silly asking this. I don't think I quite understand how to apply functions such as mysql_num_rows() or MySQL commands such as COUNT().

Obviously I'm having trouble with some code, but I want more general answers before working on the code.

Can anyone explain how to count the number of rows from a table which have a particular value in a particular column? What if one or both of those are variables?

Thanks in advance. :)

Cross-posted to php.

Source: http://www.livejournal.com/community/mysql/72773.html

Date: 11/05/05     Keywords: mysql, sql

I have a few tables, one is a collection of test_plans, the other a collection of test_procedures, and a third that joins them. Given the third contains foreign keys to link back to the primary key of a single test plan, and an collection of procedures, how do I select from mysql to determine which procedures have *not* been linked currently to a plan already in the join table?


TIA!

Updated - problem solved!

Source: http://www.livejournal.com/community/mysql/72572.html

Date: 11/03/05     Keywords: php

Thanks for all the suggestions in the previous post, but none of them seem to do what I want. After asking another friend, we eventually came up with this query (in PHP, thus the need for the backslashes before the square brackets) which does what I need:

$site = 'pp';
$slashedSearch = 'mini';
$query = "SELECT product_id, name, description, title, manufacturer,
                 warehouse_price, product_markup, supplier_markup, filename,
                 thumbnail_width, thumbnail_height, sell_price, minimum_price,
                 item_number, category_key
          FROM product
          INNER JOIN supplier
            ON supplier_key = supplier_id
          INNER JOIN picture
            ON picture_key = picture_id
          WHERE placement_{$site} = 1 AND
                product.active > 0 AND
                (name REGEXP '\[ ,.;:^\]$slashedSearch\[ ,.;:$\]' OR
                 description REGEXP '\[ ,.;:^\]$slashedSearch\[ ,.;:$\]' OR
                 title REGEXP '\[ ,.;:^\]$slashedSearch\[ ,.;:$\]' OR
                 manufacturer REGEXP '\[ ,.;:^\]$slashedSearch\[ ,.;:$\]' OR
                 product_id='$slashedSearch' OR
                 item_number='$slashedSearch')";

Source: http://www.livejournal.com/community/mysql/72426.html

Date: 11/02/05     Keywords: database

Big thanks to jsciv for the help in unfreezing my database after an unsuccessful FULLTEXT indexing!

Now, I have the following query:

SELECT product_id, name, description, title, manufacturer,
       warehouse_price, product_markup, supplier_markup, filename,
       thumbnail_width, thumbnail_height, sell_price, minimum_price,
       item_number, category_key
FROM product
INNER JOIN supplier
  ON supplier_key = supplier_id
INNER JOIN picture
  ON picture_key = picture_id
WHERE placement_fe = 1 AND
      product.active > 0 AND
      (name LIKE '%mini%' OR
       description LIKE '%mini%' OR
       title LIKE '%mini%' OR
       manufacturer LIKE '%mini%' OR
       product_id='mini' OR
       item_number='mini')

Source: http://www.livejournal.com/community/mysql/72112.html

Date: 10/27/05     Keywords: no keywords

Yesterday I tried to run the command:

ALTER TABLE product ADD FULLTEXT NameDescFTIndex(name,description)

but then my connection to the Internet ran out while I was running it (this table has over 20,000 rows and is 14.6 MB if that helps).

Now it seems that the table is completely locked down for updates and no matter what I try to update, nothing works. We also have a backlog of cronjobs (Perl scripts) that update this table that cannot finish their jobs due to this lock. How can I get rid of these unnecessary locks, so we can update the table again? I've looked through all the documentation extensively and have yet to find a solution... :(

Source: http://www.livejournal.com/community/mysql/71690.html

Date: 10/19/05     Keywords: php, database, web

howdy...

i have a fun one for you. i've written a user-driven database web-programthat uses PHP to create pages on the fly according to variables that draw information from the database. as it stands now, none of the "profile" pages actually exists, they are created via GET method - http://thepage.com/page.php?email=bob@thepage.com

now, what i'd like to do is offer my users the ability to use a static URL for example http://thepage.com/bob instead of http://thepage.com/page.php?email=bob@thepage.com. the first method i tried goes as follows.

i altered the htaccess to redirect all 404 requests to a file containing a script that pulled the last part of the URL "/bob" and used it as a variable to send as GET, thus creating the page. the reason i did this, theoretically it would handle ALL variations of http://thepage.com/bob that anyone could dream up and rely on the database to fill in the blanks. however, i was unable to make this work. i was able to complete the redirect portion by using a fixed redirect URL but i was unable to pull a GET variable from the URL and have it sent to the newly constructed page.

any and all suggestions are very welcome, especially if i am simply wrongheaded in my approach and there is a better, more efficient way to accomplish the same goal. thank you all for yout time and advice. take care.

Source: http://www.livejournal.com/community/mysql/71027.html

Date: 10/19/05     Keywords: mysql, sql

I have the RDBMS installed in the "C:\mysql" drive of my server, is there a way to "move" the data to say the "E:\mysql" without having to reinstall the whole RDBMS and reimport the data?

Source: http://www.livejournal.com/community/mysql/70860.html

Date: 10/18/05     Keywords: mysql, sql

Hello,
I've never used JOIN before and I am having some problems.

I have two tables invoice_main, clients

Invoice Main does not have the client's name in it, only their ID number. My project manager is asking to sort the names alphabetically. I thought I could grab the last name out of clients and append it on to invoice_main.

This is my query:
SELECT invoice_number, invoice_date, due_date, clientid, subtotal, paid_amounts, payment_status, ClientLast
FROM invoice_main
LEFT JOIN clients ON invoice_main.clientid=clients.ClientID
WHERE office_id='1'
ORDER BY ClientLast


This is the MySQL error
Column: 'clientid' in field list is ambiguous

Source: http://www.livejournal.com/community/mysql/70503.html

Date: 10/12/05     Keywords: php, mysql, sql, apache

I'd like to hear what (if any) you all use for benchmarking.

I'm currently doing some benchmarks for dynamic content on a php/mysql setup between Apache and LightTPD. I'm currently using "siege" but it's not working out as well as I hoped. Any application suggestions are welcome. :D


thanks!

Source: http://www.livejournal.com/community/mysql/70392.html