Stats Query - If You Are Pro, Help Me :D

Stats Not Logging
My stats arnt being logged. Page loads with no errors..

$update = "UPDATE `log` SET hits='hits + 1' WHERE ip='$ip' AND date='$date'";
if (mysql_query($update) or die(mysql_error()) ) {
} elseif($logged[username]) {
mysql_query("INSERT into log (ip, hits, date, user) VALUES ('$ip', '1', '$date', '$logged[username]'" or die(mysql_error()) );
}
else{
mysql_query("INSERT into log (ip, hits, date, user) VALUES ('$ip', '1', '$date', '$user'" or die(mysql_error()) );
}?

---

Index Use Stats
Is there some way to obtain some sort of statistics of how often, or how much is any given index used for queries? What I want to know is if an index is not used so it can be erased or changed, because it is being of no help, and just wastes space and slows down insertions.

---

Running Update Stats
I am trying to run an update stats command but keep getting a syntax error:
Looking at the doco it looks like I can use:

update stats <table_name>;

but when I use:

mysql> update stats x_balance;
ERROR 1064: You have an error in your SQL syntax near 'x_balance' at line 1

What am I doing wrong.

---

Running Stats On Data
I have a table (artist_nodes) that refers to a number of keywords (around 100) (nodes table) associated with a number of artists (artists table) i.e.

artist_nodes table

col 1 - id_artist_node (primary key)

col 2 nodeID_artist_nodes

col 3 artistID_artist_nodes

the content of each column is a number code that relates to the primary keys of content in the artists and nodes tables.

Is there a method for counting the number of distinct values in the nodeID_artist_nodes column, summing these values, and calculating how often values occur with each other?

So for example, if 1 = installation, and 45=interior and 46= exterior

i am looking for the number of instances of 1, 45, and 46, and also how often each occurs with the others when associated with the same artistID (as a percentage)

so the output i am looking for in php would be something like

installation (1) = 35 instances (3% of sample)

interior (45) = 23 instances (1% of sample)

exterior (46) = 30 instances (2%)

installation with interior = 10 instances (x %)
installation with exterior = 20 instances (y %)
installation with exterior and interior = 4 instances (z %)

is there a way of doing the inverse of this, i.e seeing which keywords never occur together?

---

Trying To Conceptualize Table Design For Stats
I have a stats page my users can go to so they can view how much traffic their listing has received.

Creating this for the total views is straight forward and I have that working. But I want to add two more features to this:

1. See the total views for just today
2. See a history for the listing over the duration of the post (ie. 30 days).

I am at a lost how I would create a table or several tables to accomplish this. I am looking for some guidance to get me thinking about the best way to do this.

---

Post Stats For Every File Looked Up?
I have 909,000 plus files in my database. Is it possible to post the amount of look ups , stats, for every file looked up?

Example:
People searching my database via my web site.

If 700 people looked at a file, while viewing that file, it also tells you 700 look ups as part of the results? Code:

---

Conceptualize Table Design For Stats
I have a stats page my users can go to so they can view how much traffic their listing has received.Creating this for the total view stat is straight forward and I have that working. But I want to also add two more features to this:

CODE1.    See the total views for just today
2.See a history for the listing over the duration of the post (ie. 30 days).

---

How To Setup A MySQL Table To Keep Track Of Stats?
I have a new system Im building that stores entries of what people are
searching for on my sites.

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

---

Table Design For Storing Item Stats
I've created quite the database for my app, and I'm familiar with normalization and general db design. However, I'm ready to design my tables for recording all sorts of stats for my db items.

I want to be able to view stats for each item, such as number of small views, number of large views, number of downloads, ratings and so on. I also want to be able to view these items by periods of time, such as by day, week, month, by multiple months, by year, etc. So, I'm trying to figure out what the most efficient way of recording this data, so that I can keep my tables as small as possible. I'm wondering if I will have to insert a row for every stat, or if I should enter a row for every day, or only a day if there is activity, etc.

---

Return Results Even If No Matching Row In Stats (was "Hmm, How To Get Around This?")
I have a MySQL query which works fine. This query references 3 tables, one of them (stats) may not have any entries for the particular user if they havent played any games. My issue is, if the user has no entry in the stats table the query doesnt work, and no results are returned. What would be the best way to get around this in MySQL so I get all the other information even if there is no entry for the stats table?

SELECT p.id
, p.player
, p.team
, p.mugshot
, sum(s.goals) as goals
, t.teamname
, t.logo
FROM league as p
JOIN (SELECT goals
, player_id
FROM stats) as s
on s.player_id = p.id
JOIN (SELECT id
,teamname
,logo
FROM teams) as t
on t.id = p.team
WHERE
p.player LIKE '$playerselect'
GROUP
BY p.id
, p.player;

---

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.

---

How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post):
*table person with fields:
-persid: autoincrement id
-name: name of the person
*table material with fields:
-materialid: autoincrement id
-material: name of the material eg "wood"
*table color with fields:
-colorid: autoincrement id
-color: name of the color eg "green"
*table persmaterial with fields:
-persmatid: autoincrement id
-persid: link to table person
-materialid: link to table material
*table perscolor with fields:
-perscolorid: autoincrement id
-persid: link to table person
-colorid: link to table color

In the webapplication the search can be entered by the users as a kind of pseudo query:
(color=red OR color=blue) AND color=green AND material=iron

My question is: how can I automatically transform this pseudo query into an efficient MySQL query?
I have tried out some different options:


Option 1:
(SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION
(SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1))
Remarks:
*I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query?


Option 2:
SELECT persid FROM person p WHERE
(EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid)
OR
EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid))
AND
EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid)
AND
EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid)
Remarks:
*very easy to get from pseudo query to MySQL query but what about performance?

Option 3:
SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE
p.persid=pc.persid
AND
(pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3)
AND p.persid=pm.persid
AND pm.materialid=2
GROUP BY p.persid HAVING
sum(case when pc.colorid in (&#391;',&#393;') then 1 else 0 end) >= 1
AND
sum(case when pc.colorid=&#392;' then 1 else 0 end)>=1
AND
sum(case when pm.materialid=&#392;' then 1 else 0 end)>=1
Remarks:
*this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form;




Option 4
SELECT DISTINCT pc1.persid FROM perscolor pc1
INNER JOIN perscolor pc2
ON pc1.persid=pc2.persid AND pc2.colorid=2
INNER JOIN persmaterial pm1
ON pc1.persid=pm1.persid AND pm1.materialid=2
LEFT OUTER JOIN perscolor pc3
ON pc1.persid=pc3.persid AND pc3.colorid=1
LEFT OUTER JOIN perscolor pc4
ON pc1.persid=pc4.persid AND pc4.colorid=3
WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL
Remarks:
*this option requires the pseudo query to be turned into a product of sums form

Option 5:
SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid
Remarks:
*very easy to get from pseudo query to MySQL query but what about performance?



-- phpMyAdmin SQL Dump
-- version 2.6.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 19, 2006 at 01:13 PM
-- Server version: 4.1.9
-- PHP Version: 4.3.10
--
-- Database: `aston`
--

-- --------------------------------------------------------

--
-- Table structure for table `color`
--

CREATE TABLE `color` (
`colorid` int(11) NOT NULL auto_increment,
`color` varchar(30) NOT NULL default '',
PRIMARY KEY (`colorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `color`
--

INSERT INTO `color` VALUES (1, 'red');
INSERT INTO `color` VALUES (2, 'green');
INSERT INTO `color` VALUES (3, 'blue');
INSERT INTO `color` VALUES (4, 'yellow');

-- --------------------------------------------------------

--
-- Table structure for table `material`
--

CREATE TABLE `material` (
`materialid` int(11) NOT NULL auto_increment,
`material` varchar(30) NOT NULL default '',
PRIMARY KEY (`materialid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `material`
--

INSERT INTO `material` VALUES (1, 'wood');
INSERT INTO `material` VALUES (2, 'iron');

-- --------------------------------------------------------

--
-- Table structure for table `perscolor`
--

CREATE TABLE `perscolor` (
`perscolorid` int(11) NOT NULL auto_increment,
`persid` int(11) NOT NULL default &#390;',
`colorid` int(11) NOT NULL default &#390;',
PRIMARY KEY (`perscolorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `perscolor`
--

INSERT INTO `perscolor` VALUES (1, 1, 1);
INSERT INTO `perscolor` VALUES (2, 1, 2);
INSERT INTO `perscolor` VALUES (3, 2, 1);
INSERT INTO `perscolor` VALUES (5, 3, 3);
INSERT INTO `perscolor` VALUES (6, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `persmaterial`
--

CREATE TABLE `persmaterial` (
`persmatid` int(11) NOT NULL auto_increment,
`persid` int(11) NOT NULL default &#390;',
`materialid` int(11) NOT NULL default &#390;',
PRIMARY KEY (`persmatid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `persmaterial`
--

INSERT INTO `persmaterial` VALUES (1, 1, 1);
INSERT INTO `persmaterial` VALUES (2, 1, 2);
INSERT INTO `persmaterial` VALUES (3, 2, 1);
INSERT INTO `persmaterial` VALUES (5, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

CREATE TABLE `person` (
`persid` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
PRIMARY KEY (`persid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `person`
--

INSERT INTO `person` VALUES (1, 'john');
INSERT INTO `person` VALUES (2, 'emily');
INSERT INTO `person` VALUES (3, 'liz');

---

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.

---

Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles.

based on this I now want to find all the individuals that are affiliated to those listed in the first query ....

---

Big Query - Query Not Completely Stored In Memory
I have this query and when executed in mySQL query window throws error that "Big Query - Query not completely stored in memory". Also executed thru app, the program justs hangs. I have checked all indexes, they all look good.....

---

Pagination W/1 Query + How To Use Query With Indexes
i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind.

i have mysql 4.1.10

1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes.

2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries?

---

Reusing A Query Output In The Same Query
I am guessing a basic question but not one I can find an obvious answer to.

If I create a calculated or modified column in a query (such as a modified text string), and then want to reuse that in the same query as I need to do three or four operations on it in sequence, how do I do it in mySQL 4.1?

Do I need to create a new column to store the interim result in an existing table (and then clear or alter it each time I run the query), or create a temporary table, or is there an easy way to reuse the query output in the same query (does the query have a name like a table name)?

If it requires a new column or table, are there particular disciplines to ensure it is robust and self maintaining?

---

Possible To Execute A Saved Query From Within Another Query?
Does MySQL provide a way to refer in a query to stored query instruction as if it were an existing table, in such a way that the stored query is exected when the query that refers to it is executed?

---

Select Query With Sub-query For Count And Max
I have a table "test"... as below... I want to have a result set of all the parents with the count of their children and the lastest created date of their child ...

---

Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop.

$sql = "SELECT * FROM mytable order by points desc limit 10";
$rec = mysql_query($sql) or die(mysql_error());
$datas = mysql_fetch_array($rec);

do{
$sq = "Select * from secondtable where linkid = '$datas[id]'";
$rst = mysql_query($sq) or die(mysql_error());
$rows = mysql_fetch_array($rst);
echo "$rows[somefield]";
}while($datas= mysql_fetch_array($rec));
This works perfectly but I want the second query to be out of the loop if there is a way and how.

---

Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database:
PHP

$get_stats_newthreads = $db->query_read("    SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible    FROM " . TABLE_PREFIX . "thread AS thread    WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10)    ORDER BY rand() DESC LIMIT 5");

and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid

---

Using A Query Result In Another Query
Can I use the results of a SELECT query as a "table" in another query?

I want to let my user pick a subset of the data, then refine it further. So ... do I have to repeat all the selection criteria at each step, or can I just do refer to the last query result?

If so, what's the PHP syntax for this?

---

Run A Query On The Results Of A Query?
Lets say I have a query that searches for people living in Colorado.
That results in a list on a "results.php" page. Now I want to query
that result and search further for people who use Linux.

I know I can do this from one query, but I would like to create
several checkboxes on my results.php page where I do a further
secondary query. Possible? I guess I want to query a query.

---

Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array).

With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...

---

Rewriting A Query Without A Sub Query
I've recently changed hosts and found that some of my code broke. The new host is using mysql version 4.0.25 which does not support sub-queries (and they won't upgrade). I'm trying to figure out how to rewrite the following query so it will work on 4.0.25 but not getting anywhere.....

---

Is It Possible To Run A Query On The Results Of A Query?
I have been trying to figure this out but no luck.

Lets say I have a query that searches for people living in Colorado.
That results in a list on a "results.php" page. Now I want to query
that result and search further for people who use Linux.

I know I can do this from one query, but I would like to create
several checkboxes on my results.php page where I do a further
secondary query. Possible? I guess I want to query a query.

---

Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order.

I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So:

SELECT * FROM order_log WHERE product's group_id = 1.

---

Sub Query And Count Query
i have a database with the following structure

id | MoveDate | ItemId | SiteID

(a new entry is entered when an item is moved from 1 site to another)

and i am trying to forumlate a query so that i can count how many days each item was at a specific location
so lets assumes i have the following data

1 | 01/01/2007| 1 | 1
2 | 03/01/2007| 1 | 2

how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5

---

Query From Query Results
I have a report I'm working on that is sort of like google adsense, where it tracks ad clicks and views.
I need to provide the option for the user to narrow down the results by date. I have my query worked out, but would it be better for me to run the query again and add the date information to the query, or should I cache the results and then query them?
If I were to cache the results and then run queries off that, what would be the advantages or disadvantages? I also have no idea how to go about doing that. Should I create temporary tables to hold the queried information or is there another way?

---

Mad Query
Hi every body I have this query in multi tables

select t.id, t2.id from table t, table2 t2 where t1.id = t2.id

the result is
t1, t2
1 3
1 6
1 8
2 4
3 5
3 8
3 3
...
...
..

I need to make it
t1 , t2
1 3,6,8
2 4
3 5,8,3
....
...

is there anyway to make this query?

---

Can This Be Done In One Query?
I have the following two tables:
article: article_id, subject_id_1, subject_id_2, subject_id_3
subject: subject_id, subject_text
subject_id_1, subject_id_2, subject_id_3 are the subject_id in subject table
for article_id 5, I want to get all the subjects, i can do this with 3 querys:
for subject 1:
select subject.subject_text from article, subject where subject.subject_id = artible.subject_id_1 and article_id = 5.
for subject 2:
select subject.subject_text from article, subject where subject.subject_id = artible.subject_id_2 and article_id = 5.
for subject 3:
select subject.subject_text from article, subject where subject.subject_id = artible.subject_id_3 and article_id = 5.
Is there a way to do the same thing with one query?

---

Query Help Please?
Completely puzzled by this one and wondered if someone could help

I have three tables:

A players table:

A appearances table:

A goals table:

Now here's what I want to:

- look at all records in the goals table
- Get the values from player_one_goals, player_two_goals etc...
- Match it against the appearances table ie: player_one=player_one_goals ie player with id= 2 scored 1 goal.
- Then find the player_name value of player_one by matching the value of the player against the player_id in the players_table

---

Need Help With A Query
Ok, I can't get this to work..
this is my database:

id nav1 nav2
1 company jobs
2 company this
3 company that
4 services foo
5 services bar
6 services blabla
now i want to select company, jobs and services, foo
so i want to select each nav1+nav2 pair with the lowest id (id1 and id 4 in this case)
makes sense?

---

Help With Query
So I have two integers, start and limit. I also have a table:

-----------------
days
------------------
id
sa01
sa02
sb01
sb02
sb03
sb04
sb05
sb06
sb07
sb08
sb09
sb10
sb11
sb12
sb13
sb14
sb15
sb16
sb17
sb18
The id column is a standard auto_increment primary key, whilst all the other columns are unsigned smallints that default to 0.
Basically, I want to select every s* column that is 0 (or any given number) from the rows with id's between the start and limit.

So kinda merging this query:


SELECT * FROM days WHERE id >= 2 AND id <= 200
and this pseudo-

PHP

row_as_array = db_resultforeach (row_as_array as key => value)   if (value == 0)      // action   else      // we didn't need this column

---

One Query From 2 DBs
I have a form that needs to input data into more than one table in my DB. I'm using this basic function for single table data entry:

PHP

function libraries_addTher($data) {

    $sql = "INSERT INTO ".TABLE_INSURANCE." SET";
    $sql .= " insCompanyName = '".$data["insCompanyName"]."'";
    $sql .= ", address1 = '".$data["address1"]."'";
    $sql .= ", address2 = '".$data["address2"]."'";
    $sql .= ", city = '".$data["city"]."'";

    my_query($sql);
    return true;
}//libraries_addTher

I also need to include a query like this with the same form submission:

PHP

$sql = "INSERT INTO ".TABLE_PROVIDER." SET";
    $sql .= " firstName= '".$data["firstName"]."'";
    $sql .= ", middleName= '".$data["middleName"]."'";
    $sql .= ", lastName= '".$data["lastName"]."'";

How do I concatenate those two queries into the $sql variable?

---

How Can I Fix This Query?
I need to select rows where country_code = "JE" and feature_code = "1" or "2". This doesn't work for me:

SELECT * FROM table WHERE feature_code = &#391;' OR feature_code = &#392;' AND country_code = 'JE'

I wonder how can I fix this query to select right rows?

---

Help With Sql Query
PHP

$q1 = "select * from re2_listings, re2_agents, re2_priority where re2_listings.AgentID = re2_agents.AgentID and re2_agents.PriorityLevel = re2_priority.PriorityLevel and re2_agents.AccountStatus = 'active' order by DateAdded desc limit 0,9 ";

what i want to do is add username in the query how would i do this if i put username='$username' i get a error Column 'username' in where clause is ambiguous
so how do i put username in the query without errors
thanks paul

---

Need Some Help With A Query
I'm not that bad at MySQL but I'm struggling with this one. I'm setting up a tutorial site and on the front page I'm trying to display the latest 5 tutorials. It was fine before but this time I've added categories. Every tutorial must be in a subcategory, a tutorial may NOT be in a parent category.

'tutorials' table has a field called 'cat' for the id of the category.
'categories' has cid ( for the id ), cat_name ( for the name )

I need thus to select all the tutorials then join it on to the categories table to get the subcategory. This is fine but my problem is that I need to also select the parent category of the subcategory. I'm also trying to do all this in 1 query rather than a new query for each tutorial.

---

Help Me With A SQL Query
I have two tables with following structure:

Table A:
int a_id

Table B:
int b_id
int a_id

I want to remove all records from B if their a_id don't exist in A. It would be good to use one SQL query to do this. As these two tables are very big, it's not workable to check each record in B with one query.

---

How To Log Every Query
I want like to log every query which I run on my database server. So for every insert,update and delete I would like to log. So how can I go about seting up this log.

---

How To Do This Query?
table 1: (ID, name)

1, joe
2, frank
3, mary


table 2 (ID, id_fk, color)

1, 2, green
2, 2, blue
3, 1, red
4, 3, orange
5, 3, gray

now, the result (in CSV format) needs to be:

joe, (red)
frank, (green, blue)
mary, (orange, gray)

of course its a 1 to many (table 2) and the many is where i am getting stump by grouping them.

using mysql 5.0

---

Query Is Off By One
I wrote a complex query that appears to be giving me HALF of what I'm looking for. Essentially, the query is returning rows with 1's in them when I know that they should be zero.

Basically, it's the table which handle a polling script for my site. The overall poll is in the 1st table, the choices are in the 2nd table, and the results are in the 3rd table.

When I run my query:

SELECT tbl_poll_options. * , COUNT( tbl_poll_options.option_ID ) AS option_total
FROM tbl_poll_options
LEFT JOIN tbl_poll_tab
USING ( option_ID )
WHERE tbl_poll_options.option_liveID =1
AND tbl_poll_options.option_pollID =1
GROUP BY (
option_ID
)
ORDER BY option_total DESC
LIMIT 0 , 30
I get the following results:



option_ID | option | option_total
--------------------------------
1 | opt -1 | 3
2 | opt -2 | 1
3 | opt -3 | 1 [* should be zero]
4 | opt -4 | 1
5 | opt -5 | 1 [* should be zero]
6 | opt -6 | 1
I've never been good at GROUP BY and never had a reason to use a LEFT JOIN - until now. Can anyone take a look at my query for me? I've attached the tables and the query in the attached text file.

---

Is This Query Right?
I am selecting each row based on the number of times it occurs in the table...
SELECT * FROM `searches` GROUP BY value ORDER BY value DESC LIMIT 10

It orders it by the highest occuring row.

---

How To Fix This Query
I ahve the following query:

SELECT facility_id, facility_name
ORDER BY facility_name asc
WHERE atm_facilities.facility_resort = null;

It doesn;t seemt o like the null part at the end. Basically I am just trying to return all the results where atm_facilities.facility_resort is blank.

---

New DB Or A Query?
Not sure if I need a new DB for this or if a specific query can do this? I have a DB called pcomments, it stores poem comments. On my page I want to show up to the last 5 commented poems. The DB stores the actual poem_id and the time of the comment. But I don't want to show the last 5 comments, I want to show the last five commented poems. Example, if a new poem gets two comments before another poem, I don't want the poem to be listed twice. Is there a way to do this in a query or should I set up a new DB with the poem_id and a newcom_date that gets overwrote each time a comment is submitted, then just get the 5 newest dates from that DB?

---

Query Help!
I need some help before my head implodes. I am building an application that manages student placements with families. I have a list of beds which can meet certain requirements such as access to broadband or access to a piano. I have created a table that list the beds and met requirements so that bed may appear multiple times in this table.

My problem when it comes to searching for beds that meet certain requirements how do I build the query to return a bed that met the requirements you are looking for, say return all the beds that have access to broadband and a piano.

If I use a WHERE statement such as WHERE requirement = requirement1 AND WHERE requirement = requirement2 it returns nothing and if I use and OR statements it returns beds that only meet one of the requirements.

---

Query
I have a query to search the lowest price of each hotel

PHP

SELECT a.hID,
SUBSTRING( MIN( CONCAT( LPAD( dbl, 8, &#390;' ) , mID, rmID ) ) , 9, 1 ) AS mID, SUBSTRING( MIN( CONCAT( LPAD( dbl, 8, &#390;' ) , mID, rmID ) ) , 10 ) AS rmID, 0.00 + LEFT( MIN( CONCAT( LPAD( dbl, 8, &#390;' ) , mID, rmID ) ) , 8 ) AS price FROM hotels a, hotel_rates r
WHERE country = '".$cn."'
AND cID = '".$city ."'
AND dbl IS NOT NULL
AND a.deleted = 0
AND r.deleted = 0
and r.hID = a.hID
AND stars >= ". $stars."
AND valid_from <= '".$out ."'
AND valid_to >= '".$out ."'
GROUP BY hID ORDER BY airport

What's the meaning of ' SUBSTRING( MIN( CONCAT( LPAD( dbl, 8, &#390;' ) , mID, rmID ) ), 9, 1)'?
I want to change this query to search all the price of each hotel for different room.(mID is meal ID and rmID is room ID) How to change the query above?

---

SQL: Query Help Plz
SELECT c.ID AS 'Course ID',
date_format(c.start_date,'%d/%m/%Y') AS 'Start Date',
date_format(c.end_date,'%d/%m/%Y') AS 'End Date' ,
t.description AS 'Course Description',

FROM media_courses AS c
INNER JOIN media_courses_type AS t
ON t.ID = c.media_courses_type_ID

WHERE c.start_date > NOW()
GROUP BY c.ID
ORDER BY c.start_date ASC

Q1. Briefly explain what the query above would return?

Q2. Re-write the query to retrieve all courses in the past that a name that starts with Cert

Q3. Re-write the query to return all courses that are more than 1 day in length

---

Help With Query
am having a problem selecting three fields from a colum. i hope someone
would help me.

here is it

table

num int
page varchar

SELECT * FROM counter WHERE num = ( SELECT MAX(num) FROM counter ) LIMIT 0, 3

am trying to select 1 or 2 or 3 records were the num is highest

example

page--------num

test1-------20
test2-------22
test3 ------9
test4-------30
test5-------10

am trying to select the highst three but display the pages
like this

test4
test2
test1

---