JOIN Or OBJECT ? Which Is Good Approach

A Better Approach
Another future problem might be multiple attachments per post. (I'd end up with even more queries?)

This is what I'm using now:

1. Query the topics for the page.

SELECT * FROM ib_topics WHERE board_id = 123 ORDER BY topic_bumptime DESC, topic_id DESC LIMIT 0, 10

2. For each topic, obtain the first and last 3 posts. Join the attachment table, if the post has an attachment. (i.o.w. this query is used multiple times, each time with a different topic_id)

( SELECT *, ib_posts.post_id AS post_id FROM ib_posts
LEFT JOIN ib_attachments ON post_attachment = "1" AND ib_posts.post_id = ib_attachments.post_id
WHERE topic_id = 1234 ORDER BY ib_posts.post_id ASC LIMIT 1)
UNION DISTINCT
( SELECT *, ib_posts.post_id AS post_id FROM ib_posts
LEFT JOIN ib_attachments ON post_attachment = "1" AND ib_posts.post_id = ib_attachments.post_id
WHERE topic_id = 1234 ORDER BY ib_posts.post_id DESC LIMIT 3)

Is there a better approach to this?

---

Approach In MySQL
What is the better approach in MySQL from a performance point of view?
I have approx 1000 stock market fond titles that I want to insert in
MySQL on daily basis and keep them in there for some years, I'll then
use the data for presentations, analysis,… the data I save is the
name, the date, the lowest, highest and last value and the volume.
The analysis will be run on all of them on daily/weekly basis.

implementation 1:
-----------------------
I create one (1) huge table with the fields date | name | lowest |
highest | last | volume

implementation 2:
-----------------------
I create one (1) huge table per year with the fields date | name |
lowest | highest | last | volume

implementation 3:
-----------------------
I create one table per name with the fields date lowest | highest |
last | volume

implementation 4:
-----------------------
I create tables with the fields date | name1 | name2 | … | name 249
i.e.:
table 1 for lowest values for titles 1 to 249
table 2 for lowest values for titles 250 to 499
table 3 for lowest values for titles 500 to 749
table 4 for lowest values for titles 750 to 1000
table 5 for highest values for titles 1 to 249
etc.

---

One Table Approach For All Publications
we are working on a web portal rewrite using a MVC platform (cakePHP)
so we are now having a discussion if having a one db table for all our publications (publications, news, articles, classified ads, events, magazines, etc) since all this publication holds the same data fields in 95% of the cases. For that 5% we will make an extra table publications_extras with some extra fields and this "prepend" table will be called only for that 5% of the cases.

We will cache as much queries *** possible so the db load will be very load for one day (lets say 500 select queries a day for the publications table and maybe 100-200 insert and update queries).

So Is this one-table-approach considered a bad practice or a good practice. Please tell me why and be as specific *** possible.

Oh, and one of the main reason to use this approach is also to have the M and C part of the MVC logic as unified *** possible. So in nearly all the cases for all type of the publications there will be only two for all of them (one model and one controller)

---

Best Approach To User Authentication?
So I'm setting up a php/mysql driven website on our company's intranet. I need to setup username's and passwords for different individuals because they all need access to different parts of the site.

Now I'm reading up on how to do user authentication. One way I am reading about, involves creating a username/password table in the database, and when the user attempts to login in some generic php/html form, the php logs into the database using a generic login/password, then checks to see if the user's login/password match up with what is in the table.

That's an interesting way to do it. But the thing that is odd to me is that you actually log into the database using a generic login/password (hidden in the php script) and you would simply have to control what the user has access to in your frontend code, based off their login.

MySQL has it's own user authentication system in place, where you create logins and passwords for the database and control what databases and tables they have access to and control what they can do with them. So why wouldn't you do it this way?

Sorry if this sounds confusing, but hopefully users experienced in this understand where I am coming from.

---

Approach A Heirarchy Tree
I need to organize a bunch of data into a heirarchy pattern. I could easily build this in XML but am hoping to keep all data for this project in a database. But, I'm not quite sure how to approach the heirarchy pattern within MySQL. Since this pattern is so common, I figure there must be a standard method of doing this in MySQL that Im not familiar with.My initial attempt was to add a number of fields to a table that work as keys. The names of the keys indicate which additional tables to draw information that would be sub to that 'node'. This approach seems very inflexible and awkward so I'm looking for better ideas.

---

Question About Search Module Approach
I am doing a search module and right now, I am using a FULLTEXT to do it. So, if I search for 'car', it'll only return me words starting with car...

I read that FULLTEXT is the good choice when it is about searching, but, if I look at sitepoint search or flickr search, it seem that they do not use FULLTEXT but more query like this

SEARCH table WHERE field LIKE '%search%' with maybe some regex!

---

Object Required: ''
I really don't know where this error is about

Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/includes/subnav/products_subnav.asp, line 3

My first few line of code are:

<%
    sqlHots = "SELECT ShortName, ProductID, ManuID, ProdTypeID FROM tblProducts WHERE New = '1'"
    Set rsHot = objConn.Execute(sqlHots)
%>
<div id="navlist">
    <table cellpadding="0" cellspacing="0" bgColor=#dbdbdb>
        <tr><td height=10></td></tr>
        <tr valign="top">
            <td vAlign=top align=left width=170>
                <font face="Arial, Helvetica, sans-serif"><b>Hardware</b></font>
                <ul>

And the line 3 is pointing to this line:
Set rsHot = objConn.Execute(sqlHots)

I looked all over and couldn't think of why.

---

PHP Sql Object/builder
Does anyone know of an implementation of a PHP class for sql queries. At this stage, I only need select -- but one that can handle all data manipulation would be quite useful.

---

Many-Many Vs Object Relational
In the process of trying to figure this thing out, I've been
doing the old "stand around in the store and read as much as possible
before you look like a derelict" thing. This time, with the O'Reilly
"Managing and Using SQL" (I think that's it).

Anyway, I was looking at a chapter discussing database design (without
any direct reference to impementations in MySQL), and they mention
Obj/Relationaldesign as a way to have a many-to-many relationship. I'm
wondering if they're trying to imply that there are different ways to
implement a many to many. In my bkgnd, I would assume that in any
many-to-many you have to have the joining table, and there's not much
else to do but go to it and select all the rows that have the key
you're interested in.

---

Object Creation And Maintenance
how to create a schema in mysql? and also, how to maintain objects like tables, tablespaces, databases, etc. i really need them since i am doing a documentation regarding mysql.

---

Better Retrieve User Object
This question must be asked a lot, but I still couldn't find anything good. I have a lot of php pages that need to use the user object. What I am wondering is what would be a better way to handle this:

1. Make a global user object once logged in, and re-retrieve it only when it is updated, or
2. Create a new local user object for every page if logged in, and go from there.

Obviously the first one will make less queries to the database, but I am still wondering how others handle this. Maybe there's another option.

---

Object Oriented Databases
is it possible to create an object oriented database using mysql? if it isnt can i use another database with a particular scripting language that supports OO(eg php)?
u can probably tell that i dont really know how to go about storing objects in databases.

---

Edit This Object Code
Code:

$s_groups = $db->selectObjects("db_direct","1 ORDER BY sid");


I have to add "WHERE sid < 10" in this code. I tried
$s_groups = $db->selectObjects("db_direct","1 WHERE sid <10 ORDER BY sid"); which didn't help

---

Cannot Open Shared Object File!
i just upgraded from MySQL 4.1 to MySQL 5.1. But when i try to run a program, i'm getting this error:

error while loading shared libraries: libmysqlclient.so.14: cannot open shared object file: No such file or directory

---

Kind Of Object Is Returned By Mysql_fetch_object()
What is the object returned by a call to mysql_fetch_object()? Is it an
array, a class, what? I know I can access fields from the result object
using the "->" operator, but how can I programmatically get a list of the
field names?

---

Object Types/nested Relations
create type some_type as object (
name varchar(20),
other number
);

create type some_tab_type as table of some_type;

create table atable (
a varchar(10) not null,
b char(1) not null,
has some_tab_type,
primary key (a))
nested table has store as some_tab;

what is the best way to replicate this in a mysql settup?

Basically its the multivalued nested table type result that I'm after.

---

OLE Object From MS Access To A MySQL Table
I don't know which forum (MySQL or MS Access) that can help me, so I decided to put my thread on both. Sorry for the inconvenience.

I want to copy a table from MS Access to a MySQL table.

1.
The problem is, one of the fields in the Access table has data type of OLE Object. What would be the equivalent of OLE Object data type in MySQL?

2.
Quite often that I lost connection to mySQL Server when I'm trying to acess tables in my MS Access that are connected (through) ODBC) to MySQL (Error #2013). Is there a way to fix this?

---

Step By Step Approach To Manage Dual-master Set Up
I am thinking of having a dual master setup where two database servers
A and B are slaves to each other.

Could someone please point me to some documentation that details what
exactly I should do if A or B goes down?

---

Folder-and-file-like Object Relationship Implementation
I'd like to develop something which has a concept like the
folders-and-files in your PC.

A folder can have subfolders and/or files. The subfolders themselves
have the same characteristics.

This characteristic can also be found in Google Newsgroup, aka Usenet.
A group can have many postings and many other groups.

I want to reapply this concept for listing contents for categories,
not unlike the categories one can find in Yahoo. For example:

The root has many categories: Electronics, Art, Entertainment, etc.
Under Electronics there are subcategories such as cameras, washing
machine and refrigerators. However, here's the twist:

When I select Electronics, I want all items under all subcategories to
be listed as well. That means I need to traverse to every descendant
categories to view all items under them.

If I start out with two tables, CategoryTable and ItemTable, how
should I design their relationship and how should the SQL query be
constructed ?

---

Error: 'Parameter Object Is Improperly Defined'
I have a mySQL database with a Delphi front end application.  I use an ADO connection to access the database.

The database holds about 15,000 lines.  Eveything works OK, except for one record.

When I try to insert the record, I get the following error message:

EOleException - "Parameter object is improperly defined. Inconsistent or incomplete information was provided."

Now, I tried re-inserting the record into the database (through my application) and found that the error came down to one line:

Payment can be deposited into the following account:

---

Storing A Large Data Struct/object In MYSQL
I have the following structure.

typedef __nogc struct RemoteDisplayInfoType{
int HistData [512][512];
int channelNdx;
double xVar[2];
double yVar[2];
double paAngle[2];

}RemoteDisplayInfoType;

I want to store this in MySQl database and retreive it back. I am using ADO.Net with managed c++.

---

Fatal Error: Call To A Member Function On A Non-object
I am wondering why I am getting the following error:
Fatal error: Call to a member function on a non-object in /home/blu6592/public_html/new/register2.php on line 46.
Here is the code that is on line 46:

PHP Code:

 $name_check = $db_object->query("SELECT username FROM comments WHERE username = '".$_POST['username']."'");

---

Microsoft VBScript Runtime (0x800A01A8) Object Required: 'oConn'
I'm kind of stuck on a few changes I'm making to my coding. I'm putting connection strings into a sub in a file called functions.asp. however, when I try to send a query, the object does not seem to exist. strange thing is it works when I comment out Call ConnectDB("Members") in test.asp and uncomment

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Users;OPTION=3"


please take a look at my code...

---

Creating Datetime Data Object From Date And Time Objects
I have date and time objects that correspond to eachother. I want to add a certain amount of minutes and hours to them and get the resulting date and time.

I was planning on creating a datetime datatype from the two and then using the date_add() function and then convert back; however, I do not know how to create a datetime object from two seperate date and time types and then back to seperate types, or whether it is even possible.

---

DBD::mysql Object Version 2.9003 Does Not Match Bootstrap Parameter
Hi everyone,

Machine info: Fedora Core 3, MySQL-server-4.1.9, and
perl-DBD-MySQL-2.9003-5

When I try to use mysqlhotcopy command to backup database, following
error occurs :(&

install_driver(mysql) failed: DBD::mysql object version 2.9003 does
not match bootstrap parameter 2.0419 at
/usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 253.
Compilation failed in require at (eval 4) line 3.

I installed perl-DBD-MySQL thru up2date. Please help me..

---

How To Pass A Java.util.Date Object To A DATETIME Column Definition?
I am writing to a MySQL table via JDBC. I have some column definitions with type DATETIME. Here is my table definition:

mysql> describe sessions;

+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| uid | int(11) | | PRI | 0 | |
| site_id | varchar(32) | | | | |
| session_id | int(11) | | | 0 | |
| context | varchar(32) | YES | | NULL | |
| create_time | datetime | YES | | NULL | |
| expiration_time | datetime | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

My statement text is: Code:

---

Is 100 MB MySQL Good?
I am recently looking for web hosting and I find a web hosting offers the space and bandwidth just what I want. However, the MySQL database size is limited to 100 MB. I would like to know is it a good size or no? I am new to MySQL so I do not know much about it at all. I will be mainly running a blog and a small forum. So I would like to make sure that I won't hit the limit or something. Can anyone tell me if 100 MB MySQL is a good size, thanks a lot. And Merry Christmas

---

Select Where At Least One Row Is Good.
+-----------+----------+--------+
| name | group_id | active |
+-----------+----------+--------+
| Andrea | 1 | 0 |
| Barry | 1 | 0 |
| Chantal | 1 | 0 |
| Dean | 2 | 0 |
| Erin | 2 | 1 |
| Felix | 2 | 0 |
| Gabrielle | 3 | 0 |
| Humberto | 3 | 0 |
+-----------+----------+--------+
I'd like a query something like: SELECT * FROM people WHERE group_id = 2 AND <at least one of them is active>

Which would result in:

+-------+----------+--------+
| name | group_id | active |
+-------+----------+--------+
| Dean | 2 | 0 |
| Erin | 2 | 1 |
| Felix | 2 | 0 |
+-------+----------+--------+
If I selected on group_id = 1, I would want to get no rows out.

---

RES: What Is A Good Benchmark?
I have Risc/AlphaServer ES40 with processor EV6 500mhz running RedHat
7.2/Mysql. Could you please send me Bench test ?
I like to try

-----Mensagem original-----
De: Bryan Koenig [mailto:bryan@fullcirclesystems.com]
Enviada em: quarta-feira, 23 de julho de 2003 11:46
Para: mysql@lists.mysql.com
Assunto: RE: What is a good benchmark?

On my p4 2gig

mysql> SELECT BENCHMARK(1000000,ENCODE("hello","goodbye"));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE("hello","goodbye")) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (0.86 sec)

-----Original Message-----
From: Jake Johnson [mailto:jake@plutoid.com]
Sent: Wednesday, July 23, 2003 8:34 AM
To: Mysql
Subject: What is a good benchmark?

I ran this benchmark on my pIII 500 and was wondering what everyone else
was getting?

mysql> SELECT BENCHMARK(1000000,ENCODE("hello","goodbye"));

+----------------------------------------------+
| BENCHMARK(1000000,ENCODE("hello","goodbye")) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (2.59 sec)
_________________________________________________ ____________________
Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on
Rims, Car Audio, and Performance Parts.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=...rclesystems.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=julio@amr.com.br
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw

---

Any Good Book For SQL?
Do you know any good book which I can found on-line? (and free of-course)

---

Any Good Book For SQL
Do you know any good book which I can found on-line?

---

A Good Benchmark
I ran this benchmark on my pIII 500 and was wondering what everyone else
was getting?

mysql> SELECT BENCHMARK(1000000,ENCODE("hello","goodbye"));

+----------------------------------------------+
| BENCHMARK(1000000,ENCODE("hello","goodbye")) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (2.59 sec)

---

Good With MySQL
I can't get this to work. I'm trying to join three tables, the last join is one to many and I want one row, by id desc to be joined. Here's what I've got so far and it's not working.

SELECT c.*,m.*,(SELECT * FROM pictures LIMIT 1 ORDER BY id ASC) as p
FROM comments c
LEFT JOIN profile m ON c.comment_id = m.id
WHERE c.p_id = m.id AND c.p_id = p.p_id
ORDER BY c.id DESC;

The error is when I try to order the pictures select statement.

---

Good Resources
I'm a newbie to the world of MySQL.I'm especially interested in MySQL administration
and security.

---

Good Tutorial For CASE?
Can't find a good tutorial about the CASE function.

---

Are Default Values Necessary/good?
Any thoughts on whether or not every column should have a default value? phpmyadmin seems to add them to everything, but I'm not sure if they are really necessary in a well structured database. All of the defaults end up being empty strings or 0 so php wont treat that any differently from a NULL value when the data is retrieved, will it?

2 examples:
with defaults:


CREATE TABLE address (
ID int(11) NOT NULL auto_increment,
Street_1 varchar(64) default '',
Street_2 varchar(64) default '',
Street_3 varchar(64) default '',
City varchar(64) default '',
State smallint(6) default &#390;',
Country smallint(6) default &#390;',
Zip varchar(8) default '',
PRIMARY KEY (ID)
) ENGINE=InnoDB;
without:

CREATE TABLE address (
ID int(11) auto_increment,
Street_1 varchar(64),
Street_2 varchar(64),
Street_3 varchar(64),
City varchar(64),
State smallint(6),
Country smallint(6),
Zip varchar(8),
PRIMARY KEY (ID)
) ENGINE=InnoDB;

---

Any Good Reporting Module ?
I am looking for a reporting tool for mysql. throw some suggestions.

1. report sould be editable (hopefully simple ascii text file)

2. need to concat some fields

e.g......

---

Good SQL Development Book.
I have around 5+ years SQL Development experience.

---

Best Pracitices For Good Performance
I am wanting to make a forum and guestbook host, and was hoping someone could advise me on how to set up the database so that it runs best. I would like for people to be able to create their own forums and guestbooks. I have thought of (1) having a forum database and a guestbook database and then having each forum or guestbook be its own table in one of those databases, or (2) having a single database with a forum table and a guestbook table and a column to specify the id or name for which forum or guestbook the posts go in.

I'm really new to this, but it seems to me the advantage to #2 would be that it would make searching across multiple forums possible, while it wouldn't really be possible at all with option #1. However, I would think that option #2 would also be a good bit slower than option #1 since when someone is posting to any of the forums, the whole table won't be able to be accessed until the post query is done. But with option #1, only the queries for a specific forum would have to wait for a post to complete. So I would think that #1 would probably be the much better choice between the two in terms of performance, although it would make searching across forums impossible.

I have also thought of a 3rd option, and I'm not sure how well it would work, so I would like any thoughts about it. Perhaps I could do something similar to option #2 and have a single database but instead of having just 2 tables in it (one for forums and one for guestbooks), I could have 4 tables (two for forums and two for guestbooks). Lets call one forum table forum_main and another forum_secondary. When a post is made, the query would tell forum_main to INSERT DELAYED the post and tell forum_secondary to INSERT the post at high priority. When someone asks for the posts in a forum, results from both forum_main and forum_secondary would be shown and if any posts in forum_main were in forum_secondary, they'd be deleted from forum_secondary. That way, forum_main could be the main large table and it would make sure that forum_secondary is small and wouldn't have to do much searching to find messages. If I only used one forum and INSERT DELAYED, I get the impression from reading about it that the delay could be a while if there are a lot of queries being made to the forum.

I want the posts to be made available immediately so people aren't wondering where their posts are, and this seems like a possible solution. I'm not sure if it would really help out all that much though. I just though of a 4th option that seems best to me and that is to combine options #3 and #1, so I could have forum_main, but instead of forum_secondary, have seperate tables for each forum (named something like forum_$id). The forum_$id tables would INSERT at high priority, while the forum_main table would INSERT DELAYED. Then the separate INSERTs at the forum_$id tables wouldn't hold up requests on ALL the forums, just that particular forum for a little while, but it would still make it into the forum_main eventually and be available for searching across forums.

---

Good DB Modeling Tool
I'm wondering which tool you guys recommend for creating a database model. This will be used for MySQL and possibly SQL Server 2000/2005. Also, I'd like to have the capability to reverse engineer my diagram to create the appropriate SQL code. Price isn't much of an issue if such software must be purchased.

---

Good Database Books
So I'm looking online at amazon.com, I do a search for the word "database" and get 6191 books back ... so rather than do that, I ask you community: What books did you read to learn about databases? For the budding DBA that knows enough to hurt himself, what books do you recommend?

---

GOOD TABLE STRUCTURE?
I am redesigning some of the larger tables in my "mysql" database, I want to create a new table with somewhere between 25-30 columns and 1000's of rows. This will be getting queryed alot and query caching is not an option how will this effect the response time of my application and is it better to have more rows or more columns in a table structure as I could redesign either way??

---

MySQL A Good Choice?
I'm a starter on the MySQL area, I have MS SQL Server experience.

I want to create a web application for showing logged information. It concerns logged vehicle information. The system should be available for several customers looking at their own vehicles.

Is MySQL suitable to perform this task?

I know I give not much information but it's a first attempt to gather some information concerning MySQL.

---

Null Values. Good / Bad?
Say I want to store a user profile in a "users" table but many of the fields are optional upon signup (e.g. "your website", "DOB", etc). Is there any problem in allowing null values for optional fields? I was always told that NULL values will make your queries incredibly slow. But it would be a bit ridiculous to create more tables just to avoid NULLs.

---

Good Program For Mysql
Do you now a good program for mysql admin like phpMyAdmin, than better!!

---

Good Reference Material
Ive been wading through the 1700+ page manual for mysql. Can somebody recommend any "condensed" references where I can get the basics without trying to wade through everything just to figure out how to find out the basics like what users exist and what permissions they have, are my data files full and need extending.

---

Good MySQL Tutorials
I have MySQL 4.1 and PHP 5.0 installed on Apache 2.0 server, and I want to learn how to use it. Sadly, every tutorial didn't always work with my software. Could someone point me to a good tutorial?

---

Good Article On Indexing
I thought I'd got to grips with this stuff.... but recently we have taken on a contractor at work and in some tables that I have set a uniqe key accross two columns such as "user_id, team_id" he has also added primary keys on those columns too?
I didn't realise you could have a unique key AND primary key on the same fields.... so it sounds like I need to go right back to basics and learn how this stuff actually works

---

Good Noob Tutorial
I have just installed it on a Mac. I know it's somewhere (nowhere that I can see) on the system, and that's it. I don't have any (and I mean any) experience with using Terminal. I've spent half a day scouring forums, MySQL documentation, various articles, you name it, but have got to the stage (it's now after 1am) of abject despair. Everywhere it says MySQL is easy to learn, and I'm sure I can if I can just find out how to get started. All the tutorials I've looked at seem to assume you've already got it installed and configured.
I haven't been able to find a single article/user guide/tutorial that speaks to my level of knowledge (or rather, lack of it). All seem to assume you know what you're doing in Terminal and that you know what the terminology means. The MySQL documentation is a nightmare in that respect. I know I have to do something to configure the setup, and have found some articles describing what I need to enter to do that, but my problem is getting off first base. I've got the MacBook-OSX:~ username$ prompt in Terminal and no idea what to do next.

---