Solved

Query Syntax

Posted on 2013-06-10
8
251 Views
Last Modified: 2013-06-10
I am trying to find a way to use a variable within a query but grabbing the variable and using the variable from within the same query.  I have a DB that stores Event ID's and Event Dates in two different tables.  I need the Event ID to find the Event Date.  If I do two queries I cant order the queries by date because that query is not inside of the original WHILE statement.  I am trying to do something like this:
(The ID is the part I am trying to figure out.)

$q ="SELECT magentocatalog_category_entity.entity_id AS id,
magentocatalog_category_entity_datetime.value AS end_date
FROM magentocatalog_category_entity,
magentocatalog_category_entity_datetime
WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id='id'
ORDER BY end_date ASC";

Open in new window

0
Comment
Question by:rgranlund
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39235704
how looks your tables??? some data example...

is this code correct???
...WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id='id'...

Open in new window

or you mean :
...WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id=magentocatalog_category_entity.parent_id...

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39235740
Maybe something like this?

$id = "175";

$q =
"
SELECT 
   magentocatalog_category_entity.entity_id AS id,
   magentocatalog_category_entity_datetime.value AS end_date
FROM 
   magentocatalog_category_entity,
   magentocatalog_category_entity_datetime
WHERE 
   magentocatalog_category_entity.parent_id='$id'
AND 
   magentocatalog_category_entity_datetime.entity_id='$id'
ORDER BY 
   end_date ASC
"
;

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 39235746
I don't know if it is correct.  Well, I know it is not correct cause it does not work,  But what I am trying to write is closer to your second example:

WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id='magentocatalog_category_entity.entity_id'

Open in new window

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 4

Expert Comment

by:BAKADY
ID: 39235762
WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id=magentocatalog_category_entity.entity_id

Open in new window

maybe without quotes???
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39235765
It would be without the quotes if your intent is to match a numeric data element.
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 500 total points
ID: 39235789
It would be without the quotes if your intent is to match a numeric data element.

WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id=magentocatalog_category_entity.entity_id

Open in new window


Table A: magentocatalog_category_entity
Table B: magentocatalog_category_entity_datetime

if TableA.entity_id = TableB.entity_id not need to be numeric, maybe they need to be of the same datatype, this is field's values comparison. Maybe i'm wrong and the result isn't what he wants, but that is what i mean...
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39235791
some data from your tables will be helpfully, as a example...
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39235879
Line 2 of this snippet is the one that has the quotes out of place.

WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id='magentocatalog_category_entity.entity_id'

Open in new window

It is OK to quote number strings in a query.  It is not necessary to do so.  But if you put the quotes around the wrong things, it can be assured that failure is not left to chance!

;-)

~Ray
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question