Solved

Query Syntax

Posted on 2013-06-10
8
249 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
  • 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 109

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 109

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 109

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

825 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