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

Hi everyone,

I performed the following query to implement triggers. But error code 1064 occured.



create table distance(
edge_id varchar(20) NOT NULL,
source_node varchar(20),
dest_node varchar(20),
distance float(9,2),
parent_edge_id varchar(20),
road_condition int,
congestion1 int,
congestion2 int,
congestion3 int,
primary key(edge_id)
);


create table weight(
edge_id varchar(20) NOT NULL REFERENCES distance(edge_id),
weight1 float(9,2),
weight2 float(9,2),
weight3 float(9,2),
primary key(edge_id)
);


My Aim is to automatically insert rows into the weight table, upon insertion of any row in distance table.

So, I wrote the followin syntax:

create TRIGGER calculate
AFTER INSERT ON distance
INSERT INTO weight values(NEW.edge_id,NEW.congestion1,NEW.congestion2,NEW.congestion3);


Is the syntax correct for creating triggers? If it's not so, then please someone tell how to create triggers in MySQL...

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

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

So I have a strange problem. I have a table with a column that is day of the week as an integer. (0-6) Is there any good way to map those back onto actual day names (Sunday, Monday etc) in Mysql. Also is there a way to do this with season names?

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

Date: 04/21/05     Keywords: mysql, sql

Ok, got an odd question. Is it possible, given a list of words to be matched against a FullText index...when you get the results...how can you know what word (or words) the result was actually matched against? I'm using a few tricks from the online MySQL manual to get a relevance score, but what I really want to know is which word(s) gave the strongest "reason" for returning any given result.

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

Date: 04/17/05     Keywords: no keywords

Does anyone know how to get foreign key information from a InnoDB table with the show command?

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

Date: 04/13/05     Keywords: mysql, sql

SELECT * from schedule WHERE (clientid = 95145) AND (date BETWEEN '2005-04-10' AND '2005-04-30') ORDER BY date

Any one know of a good site with a elaborate description of the WHERE clause of the SELECT statement? I am trying to select data that matches a client id and a date range. The MySQL manual doesn't seem to get into it in detail.

I got it to work. I had left out the quotes. Sheesh.

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

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

I've finally put together a code-based load balancing scheme for distributing server load to a pool of slaves. The issue I'm up against now is a way to poll the slaves to see which has the lowest load, and there doesn't seems to be a standard simple MySQL command to report that.

I'd like to keep the methods confined to SQL commands that can be executed via remote connections. The only option I can see at the moment is do "show processlist" and filter it to see the number of currently executing queries and get the average execution time, though that seems to be a bit "heavy".

I haven't seen anything in show status or the like that shows something as basic as server load.

I'm sure this has been covered before but I haven't been able to find any references. Any ideas?

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

Date: 03/30/05     Keywords: php, mysql, database, sql

hi all ~ i have a vB board out there in cyberland and it's got a "random quote" hack (the UQH by kurafire) ~

anyway, after three years of smooth sailing, it recently developed some kvetchy sickness and i can't figure out the problem.

this is the error message:

Database error in vBulletin 2.3.0:

Invalid SQL: SELECT *
FROM quotes
WHERE 1=1 AND mod!='N'
ORDER BY letter,name ASC
LIMIT 0,30
mysql error: 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 '!='N'
ORDER BY letter,name ASC


mysql error number: 1064

Date: Tuesday 29th of March 2005 07:20:54 PM
Script: http://www.lookingland.com/board/board/quotelist.php?s=

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

Date: 03/29/05     Keywords: no keywords

OK, let's say I've got three tables: A, B, and C.

Each of these tables has a field called x, which is typed as DATE.

Now let's say that I want to get the lowest (i.e. MIN) value of x in A, B, and C.

Is it possible to that in a single query?

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

Date: 03/29/05     Keywords: database, sql

Okay, I don't know how to form this SQL statement... I'm so not a database person, I always knew it.

I've got three tables: users, questions, and answers. Each user can answer a question only once, but each question has lots of different answers, each by different users.

What I want to do is let a user search for questions that he has not answered. I know how to do the search for questions he has answered, it's just one neat SQL statement that links all the tables together (user id matches userid in answer table, which also has a question id which matches the question id in the question table) but I don't know how to go the other way. I need to list all the questions for which no answer (with this user's user ID) exists.

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

Date: 03/28/05     Keywords: php, mysql, sql, web

i'm looking for a good webhost company to host my website. what are your recomendations?

my needs are pretty standard.

- php (5 would be nice)
- mysql
- ftp
- email
- subdomains
- +200mbs space
- +20gbs bandwith
- CPanel, URCHIN statistics are a plus
- if possible, ssh access. :)

so far, and based on my past experience, i'm set on using eyow.com. but i want to hear what you guys recommend.

(sorry if there is a list somewhere of recommended webhosts. i've searched the lj communities and nothing has come up.)


crossposted to webdev , php , mysql

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

Date: 03/25/05     Keywords: php, mysql, browser, database, sql

I'm trying to create a PHP browser-based chat room application. It will include Savant, JPSpan, and a custom MySQL wrapper class. The server is running PHP 4.3.10 and MySQL 4.0.18-standard. Now that the background information is out of the way, to the real issue at hand...

I obviously want the application to be able to support as many users as the server will allow. That said, the application must be as efficient as possible in terms of the database design and implementation. The database will be relational in design. There will be tables for rooms, users, and posts made by users to rooms. The application will also include a feature to allow users to search posts. My plan at the moment is to use a HEAP table as a buffer for current posts, given that they provide a speed advantage because they exist in memory and use hash indexing. On some sort of timed interval, posts would be moved from the HEAP table to a separate permanent table for archived posts.

However, this approach would require that posts be archived regularly over the course of the day. The more frequently posts are archived, the smaller the number of posts that could potentially be lost due to a server crash, power less, etc. The HEAP table really only needs to hold posts long enough for them to propagate to users. I'm not certain what frequency would be best.

Also, because HEAP tables don't support the TEXT field type, the HEAP table would initially need a VARCHAR(255) field for the post body. The application would have to check the length of posts when users submit them and then ALTER the table to add additional VARCHAR(255) fields, split the post body up to store it across those fields, and then concatenate the value of those fields to display the post. Since displaying each post would require getting all fields in the table anyway, it should be fairly easy for the application to figure out with each display how many fields it needs to concatenate to display the post body.

Any input? Any alternative approaches? I probably neglected to mention some tenant of my application requirements, so if you have questions, please ask, and I'll most certainly answer them. I think I've covered most of the bases, though. HEAP tables are a new concept to me, so I'm mostly lingering in the world of hypotheses before I actually try to make this work.

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

Date: 03/20/05     Keywords: php, mysql, sql, web

Hi all, lurker here and whatnot.

I just created a LJ-community for the discussion of the phpBB messageboards: phpbb

(if comunity promos such as this aren't allowed here, feel free to delete)
cross-posted: webmasterguild, php, mysql

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

Date: 03/18/05     Keywords: php, mysql, xml, sql

i finally installed php5 as a cgi on my server because the server company has not upgraded to php5 yet. PHP5 runs only in 1 specific directory.(.htaccess file) Php appears to work fine as php5 but now i can't get it to read the mysql db.

the mysql library is compiled and i don't get an error when the connection string is called. the results from the query just doesn't appear. I'm definite it's not the php code because I haven't altered the code.

Anyone have any idea as to why? -Thank you all in advance!


here is the php code: maybe something changed w/ php5?
$sql="SELECT categoryid, category FROM t_category ORDER BY category ASC";
$result=$this->f_selectquery($sql);
foreach ($result as $key => $value) {
echo "$value[1] \n";
}

here is the configure command.
'./configure' '--prefix=/home/acctname/php' '--enable-force-cgi-redirect' '--with-xml' '--with-libxml-dir=/home/acctname/php/lib' '--enable-soap' '--with-xsl=/home/acctname/php/lib' '--with-mysql=/home/acctname/php/lib' '--with-curl=/home/acctname/php/lib' '--with-mhash-dir=/home/acctname/php/lib' '--with-mcrypt-dir=/home/acctname/php/lib' '--with-zlib-dir=/home/acctname/php/lib' '--with-jpeg-dir=/usr/' '--with-png-dir=/usr/' '--with-gd' '--enable-gd-native-ttf' '--enable-ftp' '--enable-sockets' '--enable-wddx' '--with-iconv' '--enable-soap'


here are the mysql properties under php
MySQL Support enabled
Active Persistent Links 0
Active Links 0
Client API version 4.1.8
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/run/mysqld/mysqld.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib -lmysqlclient

Directive Local Value Master Value
mysql.allow_persistent On On
mysql.connect_timeout 60 60
mysql.default_host no value no value
mysql.default_password no value no value
mysql.default_port no value no value
mysql.default_socket no value no value
mysql.default_user no value no value
mysql.max_links Unlimited Unlimited
mysql.max_persistent Unlimited Unlimited
mysql.trace_mode Off Off

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

Date: 03/17/05     Keywords: php, mysql, database, sql

Alright, so I'm a bit new to MySQL. I haven't had to work with databases much, and when I have done them, it was with Access (Ick!). Anyway, i've got a php page that I'm trying to connect to a MySQL database. This isn't how the data's going to be displayed, obviously, but I'm unsure where to go from here.


// Connecting, selecting database
$connect = mysql_connect('localhost', 'mannsye', 'nickmann1169');
if (!$connect)
die('Could not connect: ' . mysql_error());
else
echo "Connected successfully

";

$category = 1;

mysql_select_db("mannsye_conigeninformation") or die("Could not select database");

// Performing SQL query
$query = "SELECT * FROM residental_links, residental_types ";
$query += "WHERE ((residental_links.section)=(residental_types.type)) ";

$query += "ORDER BY residental_types.index, residental_links.name;";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($connect);
?>


I've got my query seperated from the code, but as I know very little about MySQL, I don't know if the syntax is correct or not.


SELECT * FROM residental_links, residental_types
WHERE ((residental_links.section)=(residental_types.type))
ORDER BY residental_types.index, residental_links.name;


Whenever I try and access the database and the tables, I can connect, but I can't display any data.



Connected successfully

Query failed: 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 '0' at line 1



Please, any help would be appreciated.

Many thanks!
--Lisa

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

Date: 03/15/05     Keywords: php, mysql, database, sql

I'm working on a database table and am having trouble getting the dates added properly. I want two date fields, one to store the date the record was created, the other to store the date it was last modified. However, I can't figure out how to get the fields to hold anything but a bunch of zeros. I've tried TIMESTAMP and DATETIME and using the PHP time function and reading through the mysql.com documentation and am still in the dark. Any help would be appreciated.

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

Date: 03/15/05     Keywords: sql

Hi everyone, I just joined, I think I have a simple question but my brain has frozen and is having difficulty remembering my SQL lessons from university a few years back...

I have 2 tables - Student and Unit, Student lists all name and contact details, Unit lists all subjects each student is studying. Each ONE student has MANY units and each ONE unit has MANY students. How can I perform a query that lists ALL units being studied by EACH student?

as in:

Student 12345 Units ABC123, DEF321, HIJ567

Student 45678 Units TUV654, DEF321, MNO321

I've been trying variations of GROUP BY and HAVING, to no avail...

Thanks in advance! Carla

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

Date: 03/13/05     Keywords: mysql, sql

the timediff functions and others witihn mysql... is that only available for max db? and is maxdb the paid version?

I'm just making sure my simple sql syntax is not wrong.

thanks in advance.

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

Date: 03/08/05     Keywords: web

Is there a website that provides a general overview of the different kinds of JOINS and why one is used over another? Or can anyone provide a brief overview here?

thanks!

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

Date: 02/19/05     Keywords: no keywords

Ok, so I've been thinking about this for half a day, doing research, askings questions, and not only have I injured myself, I think I've taken several other people down with me.

I'm working with the following statement:

SELECT mstrTbl.master_id, mstrTbl.desc, secTbl.nbr, quaTbl.date
FROM mstrTbl, secTbl, terTbl, quaTbl
WHERE mstrTbl.another_id=1
  AND mstrTbl.master_id=secTbl.master_id
  AND secTbl.third_id=terTbl.third_id
  AND terTbl.fourth_id=qua.fourth_id
ORDER BY mstrTbl.master_id, secTbl.nbr, quaTbl.date

Now I'm actually pulling more fields out of mstrTbl, secTbl, and quaTbl than I'm showing, but this is the jest of it.

Now, there are two other tables, one of which has a description I need out of it, and honestly, that's all I need. The problem is there there aren't any unique keys in either of these tables (don't ask me, I just work here). I also have no control over the design of these tables. I want to pull out this single description field out of the table. Here's how that query looks:

SELECT max(nuTbl.change_ts), nuTbl.one_of_id, phiTbl.wanted_desc
FROM nuTbl, phiTbl
WHERE nuTbl.one_of_id=(mstrTbl.look_ID)
  AND phiTbl.vendor_nbr=osa.vendor_nbr
  AND phiTbl.change_ts=nuTbl.change_ts
GROUP BY nuTbl.vendor_nbr, phiTbl.wanted_desc

Notice that in the first part of the where clause I'm putting the mstrTbl.look_ID from the first query. I can hard code this and run the query myself, and it works (it only returns one row with the information). How can I join these two queries?

Oh, and yes, I have to do a check on the nuTbl to get the latest date from it, as sometimes the dates in phiTbl are newer, but not applicable to the exact thing I'm working on. Why? I can't tell you that. Just trust me when I say that in the business side of it, I can't.

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

Date: 02/11/05     Keywords: programming, sql

I basically taught myself programming so majority of times my code isn't as efficient as it could be.

my question is w/ sql statements... if i only need say 1 field from a table should i use

"select X FROM table"

or is

"select * from table" as efficient

i know common sense is pointing me to the first example but it never hurts to ask. also what happens if i need like 4 or 5 fields out of 10... would that be the same situation as if it were just 1 like the example above?

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