This is a tough question to ask.
I need to pull up a report of all the orders in our e-commerce site,
that has more than one item attributed to it.
orders.ordernum , orderitems.item number orderitems.orde rnum is shared
with the same field in the orders table.
In PHP I could just do a querey of all ordernumbers and within the
WHILE do a querey of orderitems where the ordernum equals the original
querey, and then do what I need IF numrows > 1.
But surely there's a way to maybe do that in the mySQL querey? Some way
that might be more efficient than a querey of EVERY order?
If there's simply a name of some topic in mySQL that might apply, just
tell me to look that up. For example: "Take a look for 'distinct',
newbie." =)
Thanks for any help!
Liam 2 1872
On Wed, 13 Jul 2005 10:34:07 -0700, news wrote: This is a tough question to ask. I need to pull up a report of all the orders in our e-commerce site, that has more than one item attributed to it. orders.ordernum , orderitems.item number orderitems.orde rnum is shared with the same field in the orders table.
But surely there's a way to maybe do that in the mySQL querey? Some way that might be more efficient than a querey of EVERY order?
Don't know why that should be tough to ask. If I understand it correctly,
it's easy enough to answer. To get a simple list of order numbers that are
associated with more than 1 single item, I would suggest -
SELECT ordernum
FROM orderitems
WHERE COUNT(*) > 1
GROUP BY ordernum;
In PHP I could just do a querey of all ordernumbers and within the WHILE do a querey of orderitems where the ordernum equals the original querey, and then do what I need IF numrows > 1.
I'm sure that works fine. But I would hazard a beer bet that says the
MySQL engine, when queried as shown, will deliver the answer substantially
faster than your PHP program loop.
And
If MySQL is running on a separate server - using the PHP code you mention
requires that the server push all records down the network pipes just so
your PHP code can examine the data further and subselect the order numbers
you seek. Use of the SELECT query shown asks the server to deliver only
the data you want. IOW - putting the task to MySQL saves bandwith and is
more network friendly.
If there's simply a name of some topic in mySQL that might apply, just tell me to look that up. For example: "Take a look for 'distinct', newbie." =)
You would probably like to become more familiar with SQL "Aggregate
Functions". Of these, the function COUNT() is probably the most used and
useful.
Although you will doubtless discover more that will make you happy :-)
Thomas Bartkus
Thomas Bartkus wrote: On Wed, 13 Jul 2005 10:34:07 -0700, news wrote:
This is a tough question to ask. I need to pull up a report of all the orders in our e-commerce site, that has more than one item attributed to it. orders.ordernum , orderitems.item number orderitems.orde rnum is shared with the same field in the orders table.
But surely there's a way to maybe do that in the mySQL querey? Some way that might be more efficient than a querey of EVERY order?
Don't know why that should be tough to ask. If I understand it correctly, it's easy enough to answer. To get a simple list of order numbers that are associated with more than 1 single item, I would suggest -
SELECT ordernum FROM orderitems WHERE COUNT(*) > 1 GROUP BY ordernum;
--snip-- You would probably like to become more familiar with SQL "Aggregate Functions". Of these, the function COUNT() is probably the most used and useful.
Although you will doubtless discover more that will make you happy :-)
Thanks for the help!
That querey as written kept giving me a "invalid use of group function"
error, but I did research into COUNT and GROUP BY thanks to you, and
came up with:
SELECT ordernum, COUNT(ordernum)
FROM orderscart
GROUP BY ordernum
HAVING COUNT(ordernum) >1;
and that worked perfectly. I'm seeing a lot of cool and powerful things
I can do with queries like this. =)
Thanks again!!
Liam This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Gary |
last post by:
I haver a table of students - Say 100 students that I need to be able to
update/delete and amend. I know I can do this one student at a time which is
simple but lets say I want to see all the students on the screen at the same
time, modify some, mark some for deletion and even have blank fields at the
end to add a new record.
In HTML which is generated I label each row and input field with a
name/number combination i.e <input type=text...
|
by: jacob nikom |
last post by:
I would like to create data model for a group of stores.
All stores in this group are very similar to each other, so it is
natural
to allocate one MySQL database per store. Each database is going to
have very similar set of tables with content related to a particular
store.
Let's suppose each store has multiple departments which are also very
similar to each other. In this case I would like to model the
departments
|
by: Christian Höhener |
last post by:
Hi
I have a given attributvalue of a specific attribut (sys_obid=81653) which
occurs in multiple tables. Now, I should know the tablename of the table in
which this attribut occurs.
Which possiblities do I have to find it out?
-> "find tablename where sys_obid=81653 ....?"
|
by: Chris |
last post by:
Hi, I am currently designing a simple service orders database. I have played
around with MySQL a bit but this is the first time I'm using it in anger, I
have a few design queries to make sure I am implementing the database
correctly.
Currently I have a number of tables, the first being the package table
(lists the different service names - such as small, medium etc). Note in the
tables I have trimmed a lot of the unnecessary stuff to...
|
by: jlee |
last post by:
I'm pretty much a newbie on mysql, and I need some help.
I am running mysql Ver 12.22 Distrib 4.0.24, for portbld-freebsd5.4
(i386) on a server hosting an active website.
The site's developer uses his own php shopping cart to receive customer
orders.
The configuration was done via cPanel with no external modifications -
which produced no protests when built, ran and connected with no
| |
by: taras.di |
last post by:
I'm trying to understand why MySQL isn't using all parts of the primary
key when I add in an OR statement in the query.
I have a people database, and a distances database. The people database
lists a person's name and their location, the distances database
contains the distance between any two points in the country. There are
an roughly 15000^2 entries in the distances table, and currently about
100 entries in the people table. The...
|
by: michael.martinek |
last post by:
Greetings!
I've recently been trying to do something, which apparently looks like
it may be a little odd.. I'm not finding anything in the manuals or
anywhere on the web where something similiar is being done.. so it
looks like my two options now are to post for some assistance, or find
another way to accomplish my task.
Basically, what I'm trying to do is select the number of duplicate
entries from a table using three columns. There...
|
by: Federico |
last post by:
Hi everybody,
I'm evaluating the possibility of using MySQL 5.0 as a database backend
for an application we are doing and I'd like to have hardware
requirements rough estimates and/or real world experience from people
that are already running similar dimensioned database/application.
As a rought estimate the database has around 80 tables 4-5 of these are
supposed to grow up to 5.000.000 records another 30% will be in the
100.000 range...
|
by: dino d. |
last post by:
hi everyone- my subject pretty much says it all- is there a secure way
to do this? the non-secure ways are, as i understand it, to populate
a listbox with indices as names, or maybe use a hidden control. is the
only secure way with sessions? i was also thinking about perhaps
creating a meta table that says who is editing which record right
now. any advice?
thanks,
dino
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |