Query Across DBs 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 (Ƈ',Ɖ') then 1 else 0 end) >= 1
AND
sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1
AND
sum(case when pm.materialid=ƈ' 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 Ɔ',
`colorid` int(11) NOT NULL default Ɔ',
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 Ɔ',
`materialid` int(11) NOT NULL default Ɔ',
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');

---

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.

---

MySQL Error 2013: Lost Connection To MySQL Server During Query
I'm running sugarcrm on mysql and the following query is returning "MySQL error 2013: Lost connection to MySQL server during query":

---

Different Results In Mysql & Mysql Query Browser?
I've come across an extremely strange problem. The exact same query in
both mysql command line client, and mysql query browser gives entirely
different results. I was hoping someone out there could shed some
light.

Ok, the query (I've stripped it bare, the real query is a bit more
complex)...

Each person in the people table has an associated 'place', which is an
integer that maps onto a suburb in the suburbs table. The 'place' CAN
be NULL.

so, for all intensive purposes, the schema is roughly:

---

Help With Php/MySQL Query?
I'm trying to pull records from a MySQL database using a sql query - to pull records where the "exp_date" field is greater than or equal to today. How do I do this?
Here is my query:
PHP
$query = "SELECT v_title,link_id,v_descr,hw_added,hw_region_id,approved,user_approved,exp_date FROM ec3_ad WHERE hw_feat=1 AND approved=1 AND user_approved=1 AND hw_region_id=103 AND exp_date>=strftime('%d %b %Y %H:%M:%S') ORDER BY hw_added DESC LIMIT 20";
... but this is obviously not working because I don't know how to add today's date/time dynamically to the query.

---

Can Anyone Help With This Mysql Query?
I'm using the following query to join the 3 tables gallery_category, gallery_photos and spectacle:

PHP

SELECT gallery_category.id AS gcid, gallery_category.name AS gcname, private,
COUNT(photo_id) AS countim, spectacle.id AS sid, spectacle.name AS showname
FROM gallery_category
LEFT JOIN gallery_photos ON gallery_category.id=gallery_photos.photo_category
LEFT JOIN spectacle ON gallery_category.spectacle=spectacle.id
GROUP BY gallery_photos.photo_category
ORDER BY gallery_category.name ASC

It seems to work fine, but it will only return 3 rows. If I add a new entry to the gallery_category table, the result from the query doesn't return it. If I then add an entry to the gallery_photos table with the new gallery_category in the column gallery_photos.photo_category (i.e. I add a photo to the new category), then the new category will be returned in my result set. My first thought was that it was ignoring categories with no photos in, but this is not the case (one of the 3 rows returned has no photos - countim=0).

---

MySQl Query
SELECT *
FROM test_downloads, test_download_ride, atm_rides, atm_areas, atm_area_ride
WHERE atm_areas.area_id =1
AND atm_area_ride.area_id = atm_areas.area_id
AND atm_rides.ride_id = atm_area_ride.ride_id
AND test_download_ride.ride_id = atm_rides.ride_id
AND test_downloads.download_id = test_download_ride.ride_id

I would like to keep the "and" format for the time being instead of using joinsor any other method.

---

Mysql Query Help
I have 2 tables in my database that I want to link
in policy table I have an orgid and a policyid
in the policypermissions table I have the permissionid and policy id.

my problem is that I have a bunch of duplicate policiepermissions assigned to different policyid's and I want to delete a specific permission id

Quote:

select count(p.orgid), p.orgid, pp.policyid from policypermissions pp join policies p on
p.policyid=pp.policyid where permissionid=7 group by p.orgid Having (count(p.orgid) >1)

the count for the orgid is right but I need to see all of the policyids for each orgid. is there a query that I can run that will delete all but the first policyid?

---

MySQL Query, Is It Possible?
I have the following table-structure:

countID
countItemCode
countIPaddress
countWhat (in or outclick)
countReferer

Is it possible to make a mySQL query that outputs the following

#38AFG66
total clicks: 34
(in:12,out:20) referrer (site1:10,site2:12,site3:2)

#JHGS676
total clicks: 45
(in:31,out:14) referrer (site1:8,site2:7,site3:5,site4:25)

---

MySQL Query Help
I am sure you should be able to do this using Join.. but its a tad bit advanced for me

I need to get the value of admin and super_admin from the table groups selecting by the column called name. But to get the value to select by the column name you need to get the value of the column called group in the table users selecting by the column called username.

---

Fed Up With MySql, Can Someone Help Me Run This ****ing Query?
I come from MS SQL 2005 background and I cannot get this MySql query to run..

INSERT INTO [lookup] ([Type],[Key],[Value],Rank,Status,Created) VALUES (1,2,'a',1,1,CURRENT_DATE());

Why won't this run??? (I know type, key, value are MySql "keywords" BUT I use the column definitions []). In MS SQL 2005, this works fine perfectly.


Also, how the ***** do I run multiple sql statements in MySql query browser? Which ****** developer wrote it so you can only run one statement at a time? It's these little annoying ****** that make me switch back to MS SQL.


Also, how the ***** do you declare variables in MySQL WITHOUT using a ***** stored procedure?

Here's what I want to run IN MS SQL 2005:

DECLARE @Id INT; SET @ID = 0; DECLARE @ResultDate Date; SET @ResultDate = GETDATE();
Begin
IF @ID > 0
BEGIN
INSERT INTO User (Name, Created) VALUES ('test', @ResultDate); SELECT @ID = @@IDENTITY;
END
ELSE
BEGIN
UPDATE User SET Name = @Name, Modified = @ResultDate WHERE ID = @ID;
END

SELECT @ID as ID, @ResultDate as ResultDate

Try and Convert that into MS SQL (INLINE SQL...not a function, not a procedure, INLINE SQL).

Is this possible in MySql? If it isn't, I'm done with open source crapola. People keep talking how Microsoft sucks, but at the end of the day...Microsoft actually has products that get the job done.

---

MySQL Query
Can someone help me or point me in a way for help coding the mySQL query thing so I can run querys?

Do I run the querys on PHP stuff?

---

MySQL Query Help Please...
How do i select full names from a table which start from any number ?
I am having a table named users in which i am simply retriving records by alphabets for eg. All names starting with A or B or C...i am getting this done by using the following:

sql = "SELECT * FROM users WHERE fullname = 'A%'";

But i don't know how to retrieve all records which start from 1,2,3,4,5,6,7,8,9,0 all at once. I will all records to be retrieved at once which start from a number or who's first char. is a number.

---

Mysql Query Log
I have used mysql 5.0 alpha and php to build a shop cart system.I installed all of those in windows. Coz I would like to set up a common query log, so i use "mysqld --log=fileName" to generate a log file。The result is the log file has been successfully generated, but there is nothing inside .

For exampe, I run "mysqld --log=test.log" （test.log is the log name given by myself). In the data folder, there is an file called "test.log" has been generated. There is not any log information inside except those general information. Even I have done some searching SQL command not only in the shop cart system, but also in the MySQL command prompt, it seems like mysql never write anything into that log file. I don't know what is going on here or is there something I should do but I didn't do or something else. Please help me.

The following are the contents in my log file. That's all of them, no matter how long I wait.
-----------------------------------------------------------test.log
mysqld, Version: 5.0.0-alpha-max-debug-log, started with:
Tcp port: 3306 Unix socket: MySQL
Time Id Command Argument

---

MySQL Query
I have a query:

$query = "SELECT id, catid, title, description, date, updated, hits FROM #__weblinks "
. "WHERE published='1' AND checked_out='0' "
. "ORDER BY date DESC LIMIT 5";

In this record, there is a record creation date ("date") and a record update date ("updated"). At record creation, the current date and time is entered in the format "YYYY-MM-DD HH:MM:SS" in the "date" field, and the "updated" field is filled with "0000-00-00 00:00:00". When the record is updated, the "date" field remains the same, but the "updated" field is updated with the current date and time.

In my query, it will fetch the 5 most recently added records. I want the query to check if "updated" has a value other than "0000-00-00 00:00:00" and if so use that field to ORDER BY, otherwise use the "date" field to ORDER BY, again, limiting the results to the 5 most recently added OR updated records.

I originally tried using a UNION, but this would return duplicate records if a record was both new and updated within the timeframe that separated the top five results. Obviously I need to use an IF or CASE statement in the ORDER BY, but I could get anything but errors when trying. Any ideas?

---

MySQL Query Help
Anybody know how the following query could be re-written without using the union feature?

SELECT vechicle_plate, vechicle_state
FROM purchase_info
WHERE customer_id = '1'
UNION
SELECT vechicle_plate, vechicle_state
FROM purchase_info, associates
WHERE purchase_info.id = associates.purchase_id
AND associates.customer_id = '1'

---

MySQL Query
I'm working on little webapp where one field is set to MySQL DATE datatype. by default this will go to 0000-00-00 which is fine, so I'm wondering if it's possible to have a query that see if the value is the "0000-00-00" and return NULL.

select this, that, date FROM table .. if date = '0000-00-00' set date NULL ...

or something?

---

Query In MySQL
I know how to query a query in MSAccess, but do not know how to do this with MySQL.

---

Mysql Query
Why MYSQL query return empty set ? When there are many rows in user_table where status not equal to 'Deleted' ?

$query=" Select * from user_table where status !='Deleted' "

However if I look for rows with status equal to "Deleted" MYSQL return correct result.

$query=" Select * from user_table where status='Deleted' "

---

MySQL Query Log
I have huge load on my site because of mysql database queries.
I did necessary indexes and make some optimisations on data values. Now I'm tryin "EXPLAIN SELECT" query to find slow queries.
But if I get a query log with query times that will be a shortcut to find the problem.
Has mysql got a query log and where can I find it?
Do you recommend a tool or script to log mysql queries?
If I can see on the server that would be so helpful. But I can try on my windows localhost too.

---

Mysql Query
I would like to select something from a mysql table which started with int (0-9). how ??
for letter I can use "select * from mysql_table where letter like "A%"; or somthing like this"

---

MySQL Query
I want to find record of all the people whose date of Birth falls in this week i.e 2006-11-27 and 2006-12-04. basically I want to find out Date of Birth falling in current week

---

Mysql Query
I have two tables: departments & employees. "departments" contains name of department and associated managers & "employees" contains employee name and associated department name. Now to get those departments which have atleast 20 employees, I do the following query:
SELECT department_name COUNT(*) as "Number of employees"
FROM employees
GROUP BY department_name
HAVING COUNT(*) > 20;
This gives me a list of departments with its number of employees. Can I do anything to fetch the department manager names also from the other table of all those departments returned by above query

---

Mysql Query.
i have a small staff auction for work.
here is the query:

SELECT * FROM listing l, bids b, users u
WHERE l.listingid=b.listingidAND u.username=b.buyer
AND biddate = (SELECT MAX(biddate) FROM bids bi WHERE bi.listingid= b.listingid)AND l.enddate < NOW() + INTERVAL 8 HOUR

this is good but some auctions may have many winners in buy now format. so therefore, because of the SELECT MAX(biddate)
part, this is just bringing the last bidder (winner) in all auctions. however, its also selecting only one buynow who is the winner too but there are other winners in buynow.
in this case, i added this to the query: OR b.format='buynow' Because i want it to select all the buynows as all buynows are winners but then only select the maxdate only for those in the bid format auctions.

---

MySQL Query
I had a program developed in SQL for a windows application that I want to run on a Linux server. besides the application not physically being able to run on Linux, could I have MySQL on the linux server and have the application write to the tables in linux? also the .sql document gives me errors when i try to use the control center. it has problems with some syntax. my guess is that mysql doesnt like the query or there are no dbo in Linux.

---

Mysql Query
im currently trying to fix up this query to be more work efffiecent
SELECT nextInvoiceDue FROM users_orders WHERE nextInvoiceDue>=NOW() AND currentinvoice IS NULL AND active='1'
if the date is before the next invoice duedue and there isn't already a invoice made for that order the row is selected.
then after that check if its 7 days or closer to the due date and if so it makes the invoice.
to save time i think it would be better to have WHERE nextInvoiceDue is within the next 7 days.
i can't figure out how to say that in a query though

---

MySQL Query
I have a table called "sales"with two fields that I want to use for a calculation and then sum in a MySQL Query.
One field is "price", the other is "exchange_rate". I want the sum of all "price/exchange_rate" in the table.
I have the following:

PHP Code:

SELECT
    SUM(sales.price/sales.exchange_rate) AS total
  FROM sales
  WHERE ....

What am I doing wrong?

---

Mysql Query
i have the following query:

Code:


SELECT SubT.id,SubT.MN,SubT.MX, (SubT.MX-SubT.MN)as diff from (SELECT id, MIN (grade) as MN ,MAX(grade)as MX,DATE FROM grades GROUP BY id) AS SubT WHERE SubT.MN<SubT.MX Order by DIFF DESC

Basiclly it should spit out the student and the min grade max grade and difference.
When i run it in phpmyadmin it works fine... when i run it in my php script i get the following error:
Error message = FUNCTION grade.MIN does not exist

---

MySQL Query
I have two tables. A 'users' table, which contains an id, name, and email address and a table called 'responses' which tracks responses from users which has an id, userid, response, emailid, and date field. The userid field in the responses table is the id from the records in the users field.
When ever I send an email to the users in the database, I insert a blank record into the database for each user I send an email to and I fill in the userid and emailid fields. When the user clicks on a link in the email, they are taken to a page where they type in a response and it is stored in the database.
In my admin area, I want to be able to view the responses. Is there a way for me to order the records first by the date in the responses table, but then by the name of the user from the users table?

---

Mysql Query
I know the following query selects records for all the active users and if they have photo1 or photo2. But I dont know how the if statement works here. What is 2>1, and 2<1???
Here is the query.


Select * from users,photo_approved where users.active="yes" and ( if(photo_approved.photo1 or photo_approved.photo2 ,2>1,2<1) )

---

MySQL Query
I am using MySQL 4.0. I've been working for days to make this query working but still no avail. This is how the intended result.

Name | ID | Car Type | Years |
----------------------
Jason| 12 | Honda | 5 |
----------| Chevy | 6 |
----------| BMW | 9 |
------------------| 20 | << -Total Years
---------------------------

But my query resulted below:

Name | ID | Car Type|
----------------------
Jason| 12 | Honda |
Jason| 12 | Chevy |
Jason| 12 | BMW |
-------------------------

---

Mysql Query
I have a column with type DATETIME (2004-11-23 21:19:2) and i want to do a query to get all rows='2004-11-23'. Can anyone help me with this problem?

---

Mysql Query
l use this query to select username and customerid to select from customer(table) users that have sponsors as 2 from the table.
The sponsor is done when a user want to register he or she needs a sponsor to before he or she can register and the sponsor id is the username of the person who referred him. To register you must insert a sponsor id which is the same with the username of the person who referred you. On insert, the sponsor id which is the username of your referrer would be inserted in the sponsor column of the new user's row as his or her sponsor. so below is the query that select from the customer table users who have 2 sponsors.

$sql = "SELECT sponsor,mstatus, count( username ) from customer Group by sponsor DESC Having count( username ) = 2

so l want to mass update users that have 2 sponsors or atleast a sponsor.

---

Mysql 5.0 Query
im new to mysql and trying to get to grips with it.I am a complete begineer with this.I have installed it how it said in the manual and everything. The question i want to know is how do you program mysql.I have the command line client and i found a tutorial to add new user with this but is this where you do all mysql or do you key it in notepad or like? If you do key it in command line client can you save the files.

---

Mysql Query
My table structure is as follows :

student table : student id , first name , last name
scienceTest : student id , test id , score, date
mathTest : student id , test id, score ,date
optionalTest: student id , test id, score, date

I need to write a query to retrive the following data in a single query:

student id , first name, last name, math total , science total , optional total.
The query should fetch all the student records even if they have not attended any tests.

---

Mysql Query
i am trying to build the form for a football league table, the idea is to get the last 3 results for the team, and then build a table of results.
the table for results is also the table for fixtures, the only difference is that the field isResult =0 for result and -1 for fixture

---

How To Query First 10 Result From Mysql
i am new to mysql/php , i want to query last 10 result from mysql database, can anyone help me with the command?

---

Mysql Select Query Help
i am working with a shop script that i have inherited and i want to add the ability to have products in multiple categories.

To do this i have created an extra field in the product table called cat_id_2.

Now on the list products page I have the follwing call at the moment, which pulls all of the products for the current category...

$sql = "SELECT pd_id, pd_name, pd_price, pd_author, pd_thumbnail, pd_qty, c.cat_id, c.cat_description, c.cat_name
FROM tbl_product pd, tbl_category c
WHERE pd.cat_id = c.cat_id AND pd.cat_id IN $children AND pd_status=1
ORDER BY pd_name";
I tried to change this myself and added the extra paret to the WHERE clause and came up with this...

$sql = "SELECT pd_id, pd_name, pd_price, pd_author, pd_thumbnail, pd_qty, c.cat_id, c.cat_description, c.cat_name
FROM tbl_product pd, tbl_category c
WHERE (pd.cat_id = c.cat_id OR pd.cat_id_2 = c.cat_id ) AND pd.cat_id IN $children AND pd_status=1
ORDER BY pd_name";
But for some reason, instead of putting a product in two categories it puts the same product in the category twice, - its wierd.

Can anyone see anything wrong with what i have tried?

---

MySQL Query - Need Clarification
I have to make a system for my final year uni project. To put it simply i have 3 tables.
1.) School
2.) Class
3.) Student

Obviously Schools have classes and each class has their own students. I can create classes in individual schools no problem, but i wanted to show a list which can show me all the classes and the students in that class. This works as shown below.

I the SQL that does this is as follows:

SELECT class.ClassID as ClassID, class.schID, class.ClassName as ClassName, class.TeacherName as TeacherName, students.studentName as studentName, students.studentAge as studentAge
FROM class, students
WHERE schID = 4
AND class.ClassID = students.classID

This works but the problem i have is that if there is a newly created class with no students currently in the class then this SQL does not display the Class and this is where i need some assistance to show this.

---

Using IF Statement In MySQL Query
I have the following code in one of my scripts.

PHP

$result = @mysql_query("SELECT * FROM Table WHERE id='$id'");
$row = mysql_fetch_array($result);
if ($row['name'] != "") {/* do nothing here */}
elseif ($row['email'] != "") {$row['name'] = $row['email'];}
else {$row['name'] = "&lt;anonymous&gt;";}

Like you can see I'm using an IF statement outside the MySQL query to define the variable $row['name']. But I know that I can use an IF statement inside the query instead of outside it.

---

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'

---

WHERE COUNT MySQL Query
PHP

$queryA = " SELECT soundcard_co, soundcard_model,  count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews GROUP BY soundcard_model ORDER BY value DESC LIMIT 10";


Okay, there's my MySQL query.

I have a crappy little review script that lets people review home recording sound cards. This script will display 10 sound cards with the highest average score for overall value.

As you can tell, this selects the the company, model, and average overall value rating of sound cards that have been reviewed on my site. It puts them in descending order and limits the results to the top 10.

I've found that these results are easily skewed when a particular sound card gets only one review. I don't want a single person to have the power to put a sound card on top of my ratings. So, I want to select sound cards from the database that have had at least 3 votes.

While I could come up with a php solution, I'd prefer to use MySQL.

I have tried this:

PHP

$queryA = " SELECT soundcard_co, soundcard_model,  count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews WHERE COUNT(soundcard_model)>2 GROUP BY soundcard_model ORDER BY value DESC   ";

But I keep getting a "Invalid use of group function" error.

---

Mysql Query Browser
I wander how can i connect to database by using mysql query browser using my cpanel account
remotemysqlhost,username, password

---

Should I Use MySQL Query Cache?
The majority of my website's bottlenecks occur due to the database overhead. This is because I run a user community website, where users can send messages, post comments, read/write forum topics, and view profiles.

I have been studying to solve how to speed up the http and the website, however I was also thinking about using caching techniques for the database as well (as opposed to only PHP and HTTP).

Therefore, since MySQL has it's own Query Cache system, would this be effective. I understand that it would work in a system that has a lot of SELECT queries with little UPDATE and INSERT procedures, but for a user community website which consists of lots of changing data, would it be effective?

When and where should Query Caching be used? My Server has alot of RAM and space, so what else should I look out for?

---

Query Fails In MySQL 5
I just migrated a Mambo CMS site from a server runing MySQL 4 something to one running MySQL 5.0 and am getting one problem (was expecting many more) with a query generated in the admin section:

SELECT c . * , g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name
FROM mos_content AS c, mos_categories AS cc, mos_sections AS s
LEFT JOIN mos_users AS u ON u.id = c.checked_out
LEFT JOIN mos_groups AS g ON g.id = c.access
LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id
WHERE c.state >=0
AND c.catid = cc.id
AND cc.section = s.id
AND s.scope = 'content'
AND c.sectionid = &#3914;'
ORDER BY cc.ordering, cc.title, c.ordering
LIMIT 0 , 10

MySQL said: Documentation
#1054 - Unknown column 'c.checked_out' in 'on clause'
The problem appears to be with the fact that the table mos_content is aliased as c and the the SELECT uses a wilcard to select all its columns. For some reason, when the ON clause test the value in any of the colums using the alias, it says they are onknown.

---

Yet Another Complex Php/mysql Query
im trying a select, compare, update sota query thingy 'm' jig..

what i need to do is:

i have a 2 rows in a table(table=users) called gcount and credits

both rows are numeric.

im trying to build a query that

a) updates gcount by gcount = gcount + 1
b) if gcount = 5 reset gcount to 0 update credits = credits+1

so basically what im after is a query that adds 1 to the g count every time but if the gcount reaches 5... it is reset to zero and credits is reset to + 1

is this too much to ask from one query?

am i totally bonkers and you have no idea what i mean?

---

MySQL Error In Query
Can anyone see why this doesn't work? Is it becuase I am joing the . with brackets?

PHP

WHERE tblmember.MONTH(DateOfBirth) = '{$theMonth}') AND `member`='J'

---

How To Make A Sub Query On Mysql 4.0 ?
I need to do something like this:

---

MySQL Variable Query
SET @tcslot = "my_column";
SELECT @tcslot FROM my_table WHERE id = 1;
The above code returns:

Quote:

@tcslot
my_column

It literally returns the value of the variable I set.

Is there any way to get it to parse the variable and then select the column contained in it? It's been driving me nuts for 3 days,

---