MYSQL :: Simple Message Board Query Question

Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions:

1-what can I do to avoid the system stop or time out message?

2- To use several tabels infomation should I use 'View' command or can I use other methods?

3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table?

(I use postgresql).

View Replies !

Can Error Message Show Entire Sql Query Instead Of Excerpt
I'm running MySQL 3.23.58 on Red Hat Linux 9.

In my web server error log I see things like:

[Thu Jan 18 00:33:56 2007] [error] [client 72.86.29.196] DBD::mysql::st
execute failed: You have an error in your SQL syntax near ' (NOW(),
"Setup [precheck] failed for 72.86.29.196 port 1239 [72.86.29.196]
(Con' at line 1 at /var/www/html/blah/mysql-help.pl line 34.

Is there a MySQL setting I can change somewhere so that it will log the
entire SQL query that generated the error, instead of just an excerpt?
It's a lot easier to figure out what went wrong if I can see the whole
query.

View Replies !

Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method.

I have a very simple question.

I have 2 tables: 'users' and 'posts' with the following structure:

users: id, username, email_address
posts: id, user_id, post_title, post_text

in a my own mind's mysql, I would like to:

SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id

I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.

View Replies !

Invision Board MySQL
After migrating my board and restoring the database, I am recieving errors like these:

mySQL query error: INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic_id,queued,post_htmlst ate,post_key,post_parent) VALUES(1,1,1,'62.254.0.32',1110622857,'','Test','Yixian',1013,0,0,'aef0953f14d95418c04f5f46a01d09c9' ,0)

mySQL error: Duplicate entry '1-1013' for key 3
mySQL error code:
Date: Saturday 12th of March 2005 02:20:57 AM

Also, the post count displayed on my new board appears to be about 5000 short of what it should. I think that while restoring it skipped these "depicate" key majiggers and hence the post count is now very much reduced, topic are truncated and I am unable to insert new data into ibf_posts.

View Replies !

Database Import For Invision Board
I've had to switch hosts and have been using Invision Power Board on the old host. When i tried to switch to the new host I discovered that they had an older version (1.2) and that everytime I tried to import the database they would return multiple errors, even if I dropped tables beforehand on the newly set up board.

Can I transfer the data to this new board?

Is it possible for me to just transfer the individual table information?

Can I even tansfer the information to a new php-based board?

View Replies !

Error 1044 After Moving Board
The PHPMyAdmin front page reports
Server: localhost via UNIX socket
User: nzmotorh@localhost

The MySQL page reports
Database: nzmotorh_nzmotorhomeforum
User:nzmotorh_markh

My config.php reads

<?php
// phpBB 3.0.x auto-generated configuration file
// Do not change anything in this file!
$dbms = 'mysqli';
$dbhost = 'localhost';
$dbport = '';
$dbname = 'nzmotorh_nzmotorhomeforum';
$dbuser = 'nzmotorh_markh';
$dbpasswd = '<password>; (I have changed this for privacy)

$table_prefix = 'phpbb_';
$acm_type = 'file';
$load_extensions = '';

@define('PHPBB_INSTALLED', true);
// @define('DEBUG', true);
// @define('DEBUG_EXTRA', true);
?>

View Replies !

Invision Power Board Installation
I'm trying to install IPB, and I'm getting a problem with my MySQL thing. I'm pretty new to this...I just installed it yesterday. My PHP works fine and stuff, but whenever I run that sm_install.php thing (the page that sets up your forum), it gives me these errors:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:ServerForumsm_install.php on line 415

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:ServerForumsm_install.php on line 417

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:ServerForumsm_install.php on line 419

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:ServerForumsm_install.php on line 420

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:ServerForumsm_install.php on line 454 .

View Replies !

A Simple Query?
In table "vb_user" I want to pull the data in field "username" and copy that username in another table called "vb_userfield" in a field called "field5". Both tables share the field "userid" which is how the relation is made. I know this is probably very simple but man... I just can't seem to pull it off.

I've got about 500 members and I really want to populate that field "field5" with thier username without having every one of them doing it manually.

View Replies !

Help With Simple Query
I know this is easy to accomplish, but my brain is fried and I can't figure it out.

I have a few markets (areaID) that vendors belong to. Within these markets, vendors are assigned categories (catID).

I am trying to do a SELECT on the vendors table to find out the total number of vendors to a particular category, within a particular market. So essentially, I want to know the number of vendors within each category within each market. Does that make sense?

View Replies !

Can Anyone Help Me With This Simple Query?
In my database. I want to pull out one record:

Where the date I wish to publish a story where the published_web_date(date) is less or equal to now
and
The published_web_time(time) is less or equal to now

However, using the below query data is nomatter what the published_web_date is if the published_web_time has not elapsed then nothing is shown.

select * from cms_stories
WHERE section = 'news'
AND published_web_date <= NOW()
AND published_web_time <= NOW()
ORDER BY story_id
DESC LIMIT 1";

View Replies !

Simple SQL Query
I'm making a football prediction website and I want to be able to display the weekly fixtures in a GridView so the admin can edit, insert, etc.
This is a simple version of the two tables that I have: Fixtures and Clubs...

CREATE TABLE Fixtures
(
MatchID Int PRIMARY KEY,
HomeClubID Int,
AwayClubID Int
)

CREATE TABLE Clubs
(
ClubID Int PRIMARY KEY,
Name Nvarchar(50)
)
where HomeClubID and AwayClubID are actually foreign keys to ClubID.

My Problem is that I'd like to create a query that will show each fixture with both the names of the home and away clubs on the same row, eg:

| MatchID | Name | Name |
|--------------------------------|
| 1 | Arsenal | Man Utd |
|--------------------------------|
| 2 | Liverpool | Chelsea |

View Replies !

Simple SQL Query Syntax
I am looking for some help on a simple (yet beyond my reach) SQL query that I can run via phpMyAdmin whenever I need to.
In a nutshell, I have a Database called A with table B, in which fields C and D sit. For each record, field C has an integer and field D has a human-readable date/time entry in the format: 2005-08-23 17:35:33.
It is important to note that the above date part is YYYY-MM-DD as the convention might be different in your locality.
Anyway, I am looking for an SQL query that will go through the table and whenever the date/time shown in field D is no more than 5 days ago, to add X (some whole number chosen and manually specified in the query each time it's run) to the value in field C

View Replies !

Mysql Simple Query
I am doing a project in mysql and php..
While inserting the data, i made a mistake of inserting date values into the month field and viceversa. can i swap it with a query. I cant take a backup of this table.. because its very big and the webspace for mysql database for me is running very low.

ONE MORE THING :- there are many tables almost 565... can this all be swapped in a single query or procedure.

View Replies !

Simple Mysql Query
I know this will be simple but I just cant get it to work and I am being tested here by my line manager.
Our database is named: lsql_Category and we have a table column named: Keywords
We have thousands of rows
The column Keywords has data in most of the rows and some without any data at all. Normally assorted and various text words.
I want to clear all data from this coloumn but DO NOT want to delete the coloumn cos we want to input data at later time.
The result should be that the column Keywords remains but will not have any data.
Can someone help? I think it is a select command and a where command but that is all I know.

View Replies !

Simple Null Query
How do you say I want to select all data from this column but only show the data where the data is not equal to null.
I have;

SELECT prevleaving1
FROM `jobemployment`
WHERE prevleaving1 IS NOT null

View Replies !

Simple Not Distinct Query?
I have a very large product table and believe that some of the fields contain duplicate values when they shouldn't. So I'm trying to create a simple query to identify those that do.. ie something like

Select modelnumber from tbl_product where modelnumber is not distinct

OR

Select NOT Distinct modelnumber from tbl_product.

These obviously don't work (Otherwise I wouldn't be asking for help!), and was wondering if there is a not distinct function that im missing or misusing?

View Replies !

Simple Query Trouble
I have written a simple select query...but it is not doing what I want it to:

$query = "SELECT * FROM helpdeskticket WHERE (`customerid` = '$customerid') AND (`helpdeskticket`.`status` = 'Assigned') OR (`helpdeskticket`.`status` = 'Unassigned') OR (`helpdeskticket`.`status` = 'Can Be Closed') ORDER BY `helpdeskticket`.`ticketid` DESC

Because its wrong i'll explain what I wish it to do. Select all fields from helpdesk ticket where customerid is equal to $customerid, and the status is either assigned, unassigned or can be closed. Then order by ticketid descending.

At the moment it seems to be messing around with the AND and the OR, and is selecting everything from the database table, ignoring the customer id.

View Replies !

Simple Count Query
I am trying to use the below query to count the number of images held for each book. Unfortunately it is only returning the books with images. I would like all books returned even if they have no corresponding image.

SELECT tblBooks.Title, tblBooks.Description, tblBooks.Author, tblBooks.Annotation, tblBooks.Collection, tblBooks.Price, tblBooks.SortID, tblBooks.Heading, Count( tblImage.ImageID ) AS CountImage
FROM tblBooks
INNER JOIN tblImage ON tblImage.BookID = tblBooks.BookID
GROUP BY tblBooks.BookID

View Replies !

Simple Between Query Problem
I have multiple fields on my form where a user submits a minimum and maximum value. Such as minimum price and maximum price, so I use a Between in my queries like this.

SELECT COUNT(*) AS 'numrec' FROM tblmls WHERE mlsstatus IN('Active', 'New', 'Back on Market', 'Price Change') AND mlsclass = 'Residential' AND mlslistprice BETWEEN ?' AND ?'
My question is, is there a way to do wild cards using a between statement? Or is there a better way to do this?

Currently if they don't put anything in I don't run that part of the querie, but if they put in a minimum but no maximum I just autosubmit a large number. If they put in a maximum but no minimum I just submit a 0.

View Replies !

Simple Query That I Can't Figure Out
First off, here's my table:
CREATE TABLE order_info (
ID int(10) NOT NULL auto_increment,
ordernum int(10) NOT NULL default Ɔ',
item_code text NOT NULL,
item_qty int(5) NOT NULL default Ɔ',
item_price text NOT NULL,
have int(5) default NULL,
standby int(5) default NULL,
backorder int(5) default NULL,
refund int(5) default NULL,
`status` int(2) NOT NULL default Ƈ',
PRIMARY KEY (ID),
KEY ordernum (ordernum),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=477150 ;
I would like to make a simple query that returns ordernum's that match this critera:
All of the items have a 'status' of 3
Have at least 1 'refund'
and I would like the ordernum's returned to be distinct.

Here's the query I had before the requirement of the 'refund' column:

SELECT
DISTINCT order_info.ordernum
FROM
order_info
WHERE
order_info.ordernum NOT IN (
SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.status != 3
)
AND
order_info.status = 3
I tried adding:

SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.ordernum NOT IN (
SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.status != 3
)
AND
order_info.status = 3
AND
SUM(order_info.refund) > 0
GROUP BY
order_info.ordernum
But it returns incorrect information (orders with 0 'refunds').

What is the correct way to make this query?

View Replies !

Bug In A Simple MySQL Query
Having a problem with a very simple query I am trying to execute. Hopefully someone can shed some light on the issue.

SQL Query

SELECT tblUserInfo.PersonalID, tblUserInfo.mainPicLink, tblUserInfo.Location FROM tblUserInfo, tblCoreUser
WHERE tblUserInfo.Orientation = 'Straight'
AND tblUserInfo.Location = 'Hampshire'
AND tblUserInfo.PersonalID = tblCoreUser.PersonalID
AND tblCoreUser.usrSex = 'M'
LIMIT 10;
Error:

Unknown column 'tblCoreUser.PersonalID' in 'where clause'

View Replies !

A Simple Query Question ...
The following returns one row -

PHP

SELECT ab.id, ab.menu_name, ab.keywords, bb.content, NULL , NULL , NULL , NULL , NULL , NULL
FROM menu_management AS ab
JOIN dynamenu_content AS bb ON ab.id = bb.menu_id
WHERE 1 =1
AND bb.publish =1
AND ab.version = 'fr'
AND ab.menu_name LIKE '%Nanotechnologies%'
OR ab.keywords LIKE '%Nanotechnologies%'
OR bb.content LIKE '%Nanotechnologies%'
LIMIT 0 , 30

But the following returns zero rows

PHP

SELECT ab.id, ab.menu_name, ab.keywords, bb.content, NULL , NULL , NULL , NULL , NULL , NULL
FROM menu_management AS ab
JOIN dynamenu_content AS bb ON ab.id = bb.menu_id
WHERE 1 =1
AND bb.publish =1
AND ab.version = 'fr'
AND (
ab.menu_name LIKE '%Nanotechnologies%'
OR ab.keywords LIKE '%Nanotechnologies%'
OR bb.content LIKE '%Nanotechnologies%'
)
LIMIT 0 , 30

In fact I am trying to make a query (which will later be with other queries thro' UNION ALL) where upto ab.version = 'fr' will remain constant, but the following part will change.

Means, among ab.menu_name, ab.keywords and bb.content, there may be all the three, or two or one condition only, depending on user's input. Also, the condition between them can be OR or AND, depending on whether user tends to search for all or any matches.

View Replies !

Simple Query Problem
I'm a newbie having trouble properly executing a simple query.

I'm trying to make a pathway to prevent tennis players from signing up more than once on a specific date.

Here is my

PHP

$play_date=($_POST['play_date']);
$user_id=$_SESSION['user_id'];

// Make the query to check if the player already signed up on that date.
$query = "SELECT user_id, play_date
FROM table
WHERE user_id=$user_id AND play_date=$play_date
";
$result = mysql_query ($query);
if (mysql_num_rows($result)>0) {

echo '<p><font color="red" size="+2">Sorry, you already signed up to play on this date!</font></p>'

} else {

Although the above code runs, it doesn't prevent one from signing up twice on the same date.

View Replies !

Simple JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of student numbers for each school from two tables School,Student.....

View Replies !

Using A Simple 'function' In SELECT Query
Is there any way of creating a SELECT query which has as it's result a column which is comprised of a function whos inputs are the values in other results columns.
For a simple example - a four column output, x, y, x+y, x/y
SELECT A.x, A.y, A.x+A.y, A.x/A.y FROM A
This is works fine, as is, and produces the correct results. However, as the function gets more and more complicated and the A.x and A.y turn into sub-queires (and generally the expressions all become larger and then more unwieldy) not only does the function get very painful to write and debug but the query times rise rapidly also.
Can alisas' be used here (or any other methods people know of) to refer to a columns' results to ease writing the functions and keep the load on the DB to a minimum?

View Replies !

Simple Query Question: LIMIT
I'm looking to list all the rows in a database, but skipping the first 5. I know that I can use "LIMIT 0,5" to get the first 5 results, but what query do I use to list everything from 6th result onwards?

View Replies !

Simple Select Query Take 5 Secons
This select i've got take 5 seconds and i really don't know why!!!!

The number of rows in any table is NOT bigger than 50 records!

SELECT DISTINCT(j.id) as idunic,j.* FROM jobs j
INNER JOIN jobs_categs jc ON j.id = jc.id_job
INNER JOIN jobs_type jt ON j.id = jt.id_job
INNER JOIN jobs_cities jci ON jci.id_job = j.id

WHERE j.status = 'OK'
AND j.deleted = 'NO'
AND j.suspend = 'NOK'
AND j.date_end > NOW()
AND jci.id_city = ƌ'

ORDER BY j.date_begin DESC LIMIT 0, 25

View Replies !

PLEASE HELP*SIMPLE - Mysql INSERT QUERY
And YES its connected to mysql DB i need help (a fix) ASAP thanks

<?

$sql = "UPDATE `games` SET `sponsor` ='$WebsiteURL' WHERE `gameid` =$GameID LIMIT 1";

$result = mysql_query($sql);

echo "$result";

?>

View Replies !

Simple Query? Why Is My Hair Falling Out?
Im trying to pull all records from table a and table b for 14 hrs now. It seems so simple?
both tables are indexed with the fields 'classid' and 'states'
actual results should be 36 records as you can see that is not the case.
Here is what ive tried so far:

SELECT * FROM classifieds_g WHERE states='AZ'

records 4

SELECT * FROM classifieds WHERE states='AZ'

records 32

Samples and results of what I've tried (should return 36 records)

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT classifieds.states, classifieds_g.states FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' GROUP BY classifieds.states

records 1

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ'

records 4000

SELECT DISTINCT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g, classifieds WHERE classifieds_g.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g INNER JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.states

records 1

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.classid

records 32 (all the same)

SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )GROUP BY states

records 1

SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )

records 4

View Replies !

Simple Mysql Query Not Working
I have a simple table set up listing stockists and there details, included in this table I have a name column and a website column.

I wish to select the name and the website of the stockists only if a website has been entered into the website column.

Here is the query I have tried to use
Quote:

SELECT name, website FROM stockists WHERE website IS NOT NULL ORDER BY name ASC

This query is returning stockists with no website as well as the ones with websites and I donâ€™t know why!

View Replies !

Having Problems Woth This Simple Sql Query!
hi, i have two tables essentially they are the same but one gets all normal file details placed in to it and the other has all the files that have been attached to comments put in to it...it just made programming far easier this way....

Now this seems like such a simple query but i just cant get it to work...

I want to select file_name,file_type, and upload_id from both tables so i can create an archive of all files posted to the site...how do i do this....

its something like

SELECT file_name,file_type,upload_id from uploads, comment uploads ORDER BY date_submitted DESC LIMIT 30

but thats obviously not it....i also tried a join query:

SELECT uploads.*, upload_comments.* from uploads INNER JOIN comment uploads ORDER BY date_submitted DESC LIMIT 30

View Replies !

Issue With Simple SELECT Query
i have three rows in my articles table. for some reason the query below does not show the first entry in that table, only the last two. is there an obvious reason why this would be the case? im displaying just the $title and the $id, simple enough! i can post database structure if required. stumped!

$queryGetArticles = "SELECT * FROM articles";
$resultGetArticles = mysql_query($queryGetArticles) or die ("couldnt get the data");
$row = mysql_fetch_array($resultGetArticles,MYSQL_ASSOC);

echo "<div id='stories'><ol>";
while ($row = mysql_fetch_array($resultGetArticles)) {
extract($row);
echo "<div class='story'><li><h3>$title (id: $articleID)</h3></li></div>";
}
echo "</ol></div>";

View Replies !

Need Help Formulating (somewhat) Simple MySQL Query
I'm more experienced in PHP, but not very experienced in MySQL. I really know nothing of joins, left joins, right joins, unions, and so on. Naturally, some of my code is becoming extremely cluttered because I don't know anything about these things. Sooo... I really need to get off my rear and learn how to manage data better.

I have two tables: users and news.

NEWS:

news_id
news_title
news_authorid
news_timestamp
USERS:

user_id
user_username
user_displayname
user_password
You'll notice that in each and every one of my news posts, it stores the author id of the user that posted the news. It'd be nice to just enter in the name, rather than an id, right? But the problem is, usernames are becoming somewhat dynamic, so it's useful to use the user's id as reference so we can select whatever we want about a user.

My question is, how can I "join" these two tables to get the user's display_name when selecting and displaying data from my news table? I could go like this:

$myfancydbclass->query("select * from news order by when_it_was_created");

while ($this)
{
$myfancydbclass->query("select * from users where user_id = $row->news_authorid");
}
But is there a way I can "join" the two tables so I dont have to run two queries just to get the news and user's display name (use fields user_id and news_authorid to join the two)? I hope I've made my question clear. If you don't understand what I'm asking, ask me a question back. As you can tell, I know nothing about this. Basically, what would the query be to join these two tables?

View Replies !

Query Cache: Simple Question
I just have a simple question about MySQL QQ.

What I want to know is: Is the query cache only setup to apply to queries that stay the same?

PHP

"SELECT one,two,three FROM table LIMIT 1"

(Standard query, no WHERE clause, works the same for all visitors)

But a Query like this then doesn't stay the same

PHP

"SELECT one,two,three FROM table WHERE id='".$id."' LIMIT 1"

Since the ID value depends on a php variable, thus is different each time, then the query is in fact not the same.

Is the use of the query cache still beneficial in a case like this?

View Replies !

Simple Query To Search By Tags
I have a straightforward tagging setup:

places (id, title)
taggings(place_id, tag_id)
tags(id, title)

I'm trying to write a simple query that returns places with certain tags. I can write the query that returns places for one tag (select * from places, taggings, tags where places.id = taggings.place_id and taggings.tag_id = tags.id and tags.title = "sushi").

I need to write a query that returns all places based on multiple tags. For example, if a user searches for "cheap" and "sushi" it should return places that are tagged as both "cheap" and "sushi".

View Replies !

Change To Simple Query Causing It To Run Show, Help?
I m looking for some help if possible. I ve got a very simple query which just outputs all the records from the table (tbl_addresses). It has 8587 records.

SELECT a.ID, a.address, a.datecreated, a.datemodified
FROM tbl_addresses as a
WHERE a.statusid = 1
LIMIT #arguments.pagesize# OFFSET #arguments.startrow#

I ve been asked to update the query to get the name field from another table called tbl_organisations. This table has 8883 records. There is a link table to relate the two (lnk_addresses). This table has 8243 records.

I ve updated my query to the following:

SELECT o.name, a.ID, a.address, a.datecreated, a.datemodified
FROM tbl_addresses as a
LEFT JOIN lnk_addresses as la ON a.id = la.addressId
LEFT JOIN tbl_organisations as o ON la.orgId = o.id
WHERE a.statusid = 1
LIMIT #arguments.pagesize# OFFSET #arguments.startrow#

The query now runs at snails pace and takes minutes to display anything. Even If I remove the 2nd join (LEFT JOIN tbl_organisations as o ON la.orgId = o.id) its slow so I guess its related to the tbl_addresses and lnk_addresses.

View Replies !

Join 2 Simple Queries Into A Single Query?
I would like to perform both these tasks in a single query, is there some kind of AND ? to chain multiple test criteria into a single query?......

View Replies !

Prefix Blank Lines To A Simple Select Query
I would like to display a certain number of blank lines before the output of a select query, ie:

SELECT Var1, Var2 FROM SOME_SOMETABLE UNION ...?

View Replies !

Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following:

-----------------------------------
SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC

while(FetchRowAssoc())
{
SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id

echo cat_it, cat_name, itemcount, blah... etc.
}
------------------------------------

I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table.

Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category?

"SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.

View Replies !

Seemingly Simple FULLTEXT Query Over Multiple Tables
Can't seem to get my head around this query. Here's what I have:

I have 2 tables:

TABLE a, TABLE b:

Table a
Columns: UID, FName, LName, Bio

Table b
Columns: UID, Specialty

I want to search these FULLTEXT Indexed table columns (bio, Specialty) from the different tables for a string, and output the resulting UID and Username, one row only per UID (UID matches between tables)...

To put it another way, if the search string is found in either of the 2 columns in the 2 tables, I would like to output the UID and FName from TABLE A.

SELECT DISTINCT E.UID, E.FName, E.LName
FROM contact AS E, brg_uid_specialties AS C
WHERE E.UID=C.UID
AND MATCH (E.Bio) AGAINST ('bubble noodle Oranges')
OR MATCH (Specialties) AGAINST ('bubble noodle Oranges')"
The resulting output from my sample tables is an enormous dataset (when it should only get 1 hit), and I can't figure out why... .

View Replies !

MySQL Query Cache: Should I Cache Small, Simple Queries Or Only Complex Ones?
Query cache works great for long, complex queries, but should I also be caching the simplest of select queries.

For example let's say I had a table with 100 records and I needed to select something using a unique key:

SELECT name FROM products WHERE id = 3
Is caching the above pointless, especially in terms of wasted cache memory, considering how basic it is?

View Replies !

Simple "NOT IN" Query
I have two tables:

tab_a id(int)
tab_b id(int)

and I try the following select which returns an error:

SELECT id from tab_a
where id NOT IN
(select id from tab_b);

#1064 - 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 'select id from tab_b) LIMIT 0, 30' at line 3

View Replies !

SQM Message
What database is all about.

View Replies !

Collate Message
I am getting the following error message when I am trying to load a script ‘ERROR 1064 (00000) at line 20: 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 'collate latin1_bin default NULL ) ENGINE=MyISAM DEFAULT CHARSET'

I am using 4.0.18 do I need to modify the script?

View Replies !

Error Message During Set Up
I've gotten the fo;;owing message while trying to set up MYSQL on a
Powerbook on MacOSX:

/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock'
(2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
I installed Startup, Debug, Max, and Standard in that order.

View Replies !

Message From Server
I'm developing a web application using the struts framework and mysql 4.0.24_Debian-10 on tomcat 5.5.
Trying to connect to mysql server I got the following error on application(tomcat) startup:
>>>>>
Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Host 'localhost.localdomain' is not allowed to connect to this MySQL server")
<<<<<
WHY?
I've been searching on previous threads but couldn't find anything matching this situation.

View Replies !

Repair Message
Running MySQL 4.0. I had an error in my database that wouldn't let me run selects (it said error 127 from table handler). I ran repair on the table and it fixed it dropping one row. I was just wondering what the meaning of the repair message is so that I can try to avoid the error in the future.

Found link that points at 11548 (outside data file) at 10152.

View Replies !

Select Last Message
I'm working on an article-system, (width PHP, Mysql), where I want to get the latest commments to an article.
I have to tables, artikler and kommentarer

$comment = mysql_query("SELECT `artikler`.`id`, LEFT(`kommentarer`.`kommentar`, 40) as `text` FROM `artikler`, `kommentarer` WHERE `artikler`.`id` = `kommentarer`.`id_ref` GROUP BY `kommentarer`.`id_ref` ORDER BY `kommentarer`.`dato` DESC LIMIT 0, 5") or die (mysql_error());

"dato" is a datetime field

But this query shows the first comment to an article.How do I get the latest?

View Replies !

ODBC Message
I have a mysql 3.23.49. It's working on Linux Red Hat, all our application run in Windows XP connecting through ODBC driver.

We've been receiving some warning messages recently, but that application run more than 3 years and we'd never received that message:

Warning: Lost connection to MySQL server during query

The computer has 4 cpu (1.5Mhz), 2G RAM and free space in Hard Disk, it's only database server.

Where can I start analize from?
The top command, most of time show mysql working with 100% cpu.

View Replies !