PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
I am trying to populate an HTML table with data from 2 SQL tables, that reference each other (autorenewals_schedules & autorenewals_schedules_intervals)
Column | Type | Modifiers
-----------+---------+--------------------------------------------------------------------
id | integer | not null default nextval('autorenewal_schedules_id_seq'::regclass)
productid | integer | not null
effortid | integer | not null
Column | Type | Modifiers
------------+---------+------------------------------------------------------------------------------
scheduleid | integer | not null
interval | integer | not null
templateid | integer | not null
id | integer | not null default nextval('autorenewal_schedules_intervals_id_seq'::regclass)
How would a get the interval and templateid from autorenewals_schedules_intervals as a subquery for autorenewals_schedules?
ex: [my query so far..] viewtopic.php?f=1&t=118615
$query = "SELECT id,effortid,productid,
(SELECT interval FROM autorenewal_schedules_intervals WHERE scheduleid=autorenewal_schedules.id) AS interval,
(SELECT description FROM templates WHERE id=autorenewals_schedules_intervals.id) AS template
FROM autorenewal_schedules
WHERE productid={$_SESSION['renewals_pub']}";
SELECT description FROM templates WHERE id=autorenewal_schedules_intervals.templateid
SELECT interval FROM autorenewal_schedules_intervals WHERE scheduleid=autorenewal_schedules.id
$query = "SELECT asi.interval, t.description, as.id, as.effortid, as.productid
FROM autorenewal_schedules_intervals asi, templates t, autorenewal_schedules as
WHERE asi.scheduleid = as.id AND
t.id = asi.id AND
as.productid={$_SESSION['renewals_pub']}";
This probably wont work for exactly what you are trying to do... but maybe it will send you in the right direction...
$query = "SELECT asi.interval, t.description, as.id, as.effortid, as.productid
FROM autorenewal_schedules_intervals asi, templates t, autorenewal_schedules as
WHERE asi.scheduleid = as.id AND
t.id = asi.id AND
as.productid={$_SESSION['renewals_pub']}";
This probably wont work for exactly what you are trying to do... but maybe it will send you in the right direction...
sorry if this doesnt help at all!
This worked!! WEll slightly...
It works if there is only 1 record in autorenewal_schedules_intervals that reference to autorenewal_schedules.. But if there is 2, it doesnt work.
Examples:
well... its beyond my knowledge of SQL.. at this point I would cut my losses and opt for nested selects. not the most efficient way of doing it..
as you are looping through the results of your SELECT statement, check the autorenewal_schedules_intervals table to see if there are more entries matching the templateid in your primary results... if so then loop through them.
so in side your loop have something like:
SELECT * FROM autorenewal_schedules_intervals WHERE templateid = ".the templateid from previous select results." AND scheduleid != ".the scheduleid from previous select results."
the second WHERE statement "scheduleid !=" is just saying WHERE scheduleid does not equal the scheduleid I already have from my previous SELECT.
ok i got it to populate my table with the data from all the SQL tables and populates with correct data, couple changes i want made though...
- It populated the table if there is duplicate ID's from autorenewal_schedules, but what i would like it do to is only have 1 row for each ID, but if it has multiple intervals or templates referencing that ID to put them in the correct column
(picture to explain)..
so do you want it to return multiple interval columns with each interval value? or 1 interval column with all interval values? either way... its beyond be as to how thats possible using SQL statements.
buckit wrote:so do you want it to return multiple interval columns with each interval value? or 1 interval column with all interval values? either way... its beyond be as to how thats possible using SQL statements.
i want multiple intervals & templates per SCHEDULE id (the ID shown on the form in the picture)
buckit wrote:SELECT * FROM autorenewal_schedules_intervals WHERE templateid = ".the templateid from previous select results." AND scheduleid != ".the scheduleid from previous select results."
the second WHERE statement "scheduleid !=" is just saying WHERE scheduleid does not equal the scheduleid I already have from my previous SELECT.
do you have an example of a nested loop for SQL? in theory i dont think it will work