Re: [PHP-DB] query repeating results???
Try left joining your states table also. MySQL may be joining the property_photos and the states table first, which would screw up your left join. Haven't had my morning coffee yet so I can't say for sure. Put EXPLAIN in front of your query to see what MySQL is doing. SELECT ... FROM properties LEFT JOIN states ON properties.state_id=states.id LEFT JOIN property_photos ON property_photos.property_id=properties.id WHERE states.code='fl' On Mar 26, 2004, at 1:45 AM, Katie Dewees wrote: I am running the following query: SELECT ...stuff... FROM properties LEFT JOIN property_photos ON property_photos.property_id=properties.id, states WHERE states.code='fl' AND properties.state_id=states.id LIMIT 0, 10 -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Stumped with proper query display
Can someone please help me or direct me to some scripts that might get me unstuck, as
I've not done this kind of query before.
To simplify:
Table 1
id 1
Name John Doe
company IBM
Table 2
id1
choice choice #1
id1
choice choice #2
So I have 2 records in table 2 that I need to tie in with id # 1 in table 1.
What's the smartest way to do the query ? I have 5 different tables I need to query.
I guess I can get the data I want by selecting all from the 5 tables then parsing
through like:
while ($row = mysql_fetch_assoc($res)) {
foreach Table1.id {
if Table1.id == Table2.id{
echo tr
}}}
I could probably manage if I do 5 different queries with 5 different results sets, but
that is obviously inelegant and would create more overhead
I need to display as:
John Doe IBM Choice #1, Choice #2
Am I even close ? Need some help with the logic...
Thx for any guidance
mignon
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Stumped with proper query display
i dont understand this part:
What's the smartest way to do the query ? I have 5 different tables I need
to query. I guess I can get the data I want by selecting all from the 5
tables then parsing
Send your tables structure.
- Original Message -
From: Mignon Hunter [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, March 26, 2004 3:07 PM
Subject: Re: [PHP-DB] Stumped with proper query display
Can someone please help me or direct me to some scripts that might get me
unstuck, as I've not done this kind of query before.
To simplify:
Table 1
id 1
Name John Doe
company IBM
Table 2
id1
choice choice #1
id1
choice choice #2
So I have 2 records in table 2 that I need to tie in with id # 1 in table 1.
What's the smartest way to do the query ? I have 5 different tables I need
to query. I guess I can get the data I want by selecting all from the 5
tables then parsing through like:
while ($row = mysql_fetch_assoc($res)) {
foreach Table1.id {
if Table1.id == Table2.id{
echo tr
}}}
I could probably manage if I do 5 different queries with 5 different results
sets, but that is obviously inelegant and would create more overhead
I need to display as:
John Doe IBM Choice #1, Choice #2
Am I even close ? Need some help with the logic...
Thx for any guidance
mignon
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Stumped with proper query display
Ok
Lets say for simplicity sake that I have 3 tables:
table one: contact
id
choice
table two: cust
id
first
last
title
company
table three: industry
id
industry
industry_other
So --- for example in table contact I may have:
id - 1
choice - choice#1
id -1
choice -choice#6
in table cust I may have
id - 1
first - John
last - Doe
title - pres
company - ibm
in table industry I may have
id - 1
industry - computers
industry_other - null
So --- I guess I need to run a join query or 3 queries. I need to display as such:
id name industrycontact
1 John Doe computers choice#1,choice#6
As you can see, I will need to display more than one record for a given id from the
contact table.
Does this make more sense? I'm not sure how to do the query and display the result...
Any guidance is greatly appreciated...I'll name my first born after you ! Or my next
dog :)
mignon
Ricardo Lopes [EMAIL PROTECTED] 03/26/04 10:54AM
i dont understand this part:
What's the smartest way to do the query ? I have 5 different tables I need
to query. I guess I can get the data I want by selecting all from the 5
tables then parsing
Send your tables structure.
- Original Message -
From: Mignon Hunter [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, March 26, 2004 3:07 PM
Subject: Re: [PHP-DB] Stumped with proper query display
Can someone please help me or direct me to some scripts that might get me
unstuck, as I've not done this kind of query before.
To simplify:
Table 1
id 1
Name John Doe
company IBM
Table 2
id1
choice choice #1
id1
choice choice #2
So I have 2 records in table 2 that I need to tie in with id # 1 in table 1.
What's the smartest way to do the query ? I have 5 different tables I need
to query. I guess I can get the data I want by selecting all from the 5
tables then parsing through like:
while ($row = mysql_fetch_assoc($res)) {
foreach Table1.id {
if Table1.id == Table2.id{
echo tr
}}}
I could probably manage if I do 5 different queries with 5 different results
sets, but that is obviously inelegant and would create more overhead
I need to display as:
John Doe IBM Choice #1, Choice #2
Am I even close ? Need some help with the logic...
Thx for any guidance
mignon
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Stumped with proper query display
The way I handle queries like this is to use an associative array with
the ID as the named index key. You then loop through the data
consolidating the choices and linking the other data based on id. Since
you are using a named index, it doesn't matter how your data is sorted.
//Consolidate your choices into an associative array
foreach($choiceData as $choices) {
//Get Unique ID for consolodiation
$personID= $choices['id'];
//Save value of fields being consolidated
$choicesList[$personID]['choice'] .= $choices['choice'].', ';
//Add Names and Company
$choicesList[$personID]['Name'] = $names['Name'];
$choicesList[$personID]['company'] = $names['company'];
}
You should now have an array ($choicesList) containing your
consolidated data. Each array element will contain and array with three
items: choices, name, company. The 'choices' will have an extra ', ' at
the end, but that's easy to get rid of with rtrim().
On Mar 26, 2004, at 10:07 AM, Mignon Hunter wrote:
Can someone please help me or direct me to some scripts that might get
me unstuck, as I've not done this kind of query before.
To simplify:
Table 1
id 1
Name John Doe
company IBM
Table 2
id1
choice choice #1
id1
choice choice #2
So I have 2 records in table 2 that I need to tie in with id # 1 in
table 1.
What's the smartest way to do the query ? I have 5 different tables I
need to query. I guess I can get the data I want by selecting all
from the 5 tables then parsing through like:
while ($row = mysql_fetch_assoc($res)) {
foreach Table1.id {
if Table1.id == Table2.id{
echo tr
}}}
I could probably manage if I do 5 different queries with 5 different
results sets, but that is obviously inelegant and would create more
overhead
I need to display as:
John Doe IBM Choice #1, Choice #2
Am I even close ? Need some help with the logic...
Thx for any guidance
mignon
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Recursive photo gallery removal problem.
No solutions or feedback? :( -Tom Reed [EMAIL PROTECTED] Tom Reed [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I designed a photo gallery system, in which I allow users to create recursive photo folders. The trouble I am having is being able to delete a photo folder, and all folders/photos within it. Example structure: Main Folder - Folder 1 - Folder a - Folder b - Folder i - Folder ii - Folder aa -Folder bb - Folder 2 - Folder 2a ... I am having trouble creating code to delete all folders within the folder they choose to delete. Say I want to delete Folder b. In addition to deleting Folder b, the code also needs to delete folders Folder i, Folder ii, Folder aa, and Folder bb. Here's how I've setup the photo gallery table: CREATE TABLE `gallery` ( `uniqueid` int(11) unsigned default NULL, `gallery` int(11) default NULL, `image` varchar(255) default NULL, `height` int(11) default NULL, `width` int(11) default NULL, `thumbnail` varchar(255) default NULL, `title` varchar(150) default NULL, `description` text, `location` int(11) unsigned default NULL, `uploaded_by` int(11) default NULL ) TYPE=MyISAM; uniqueid is the id of the folder. gallery is the folder depth (0=photo, 0=main folder, 1=sub folder level 1, 2==sub folder level 2...) location is the folder this sub-folder is located in Seems this code may be fairly complex. I've made about three attempts, with no success and I figured this would be the best place to ask. Any help would be appreciated. -Tom Reed [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Recursive photo gallery removal problem.
No solutions or feedback? :( -Tom Reed [EMAIL PROTECTED] _ How do you select _all_ the images from Folder B? Can you show that statement? In every delete you're able to do against a database table, you should first be able to run a select and basically replace 'select [stuff]' with delete. Now this rule is a little shakey when it comes to deleting from multiple tables using joins, but similar logic applies. I'm a little confused by your structure, this looks to me that it may have been more appropriate to use multiple tables to build relationships. Thats not to say i'm right...cause i may not be. HTH Jeff Tom Reed [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I designed a photo gallery system, in which I allow users to create recursive photo folders. The trouble I am having is being able to delete a photo folder, and all folders/photos within it. Example structure: Main Folder - Folder 1 - Folder a - Folder b - Folder i - Folder ii - Folder aa -Folder bb - Folder 2 - Folder 2a ... I am having trouble creating code to delete all folders within the folder they choose to delete. Say I want to delete Folder b. In addition to deleting Folder b, the code also needs to delete folders Folder i, Folder ii, Folder aa, and Folder bb. Here's how I've setup the photo gallery table: CREATE TABLE `gallery` ( `uniqueid` int(11) unsigned default NULL, `gallery` int(11) default NULL, `image` varchar(255) default NULL, `height` int(11) default NULL, `width` int(11) default NULL, `thumbnail` varchar(255) default NULL, `title` varchar(150) default NULL, `description` text, `location` int(11) unsigned default NULL, `uploaded_by` int(11) default NULL ) TYPE=MyISAM; uniqueid is the id of the folder. gallery is the folder depth (0=photo, 0=main folder, 1=sub folder level 1, 2==sub folder level 2...) location is the folder this sub-folder is located in Seems this code may be fairly complex. I've made about three attempts, with no success and I figured this would be the best place to ask. Any help would be appreciated. -Tom Reed [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Recursive photo gallery removal problem
Tom, I understand you have a structure of folders, each with a single parent folder. You want to be able to delete one specific folder and then have all folders directly or indirectly under that folder removed at the same time. This is easy to do using a foreign key with an on delete clause. Declare a table like this: CREATE TABLE gallery ( gallery_idint, parent_gallery_id int, PRIMARY KEY ( gallery_id ), FOREIGN KEY ( parent_gallery_id ) REFERENCES gallery ( gallery_id ) ON UPDATE CASCADE ON DELETE CASCADE ); The foreign key clause establishes a relationship between two records in the gallery table, a reference from on gallery to its parent. The on delete cascade part says that when the gallery record referenced from this record (i.e. the parent) is deleted, this gallery record is to be deleted too. This will be done recursively by the database system backend. To delete a subtree of galleries, you simply delete the record for the topmost folder, the database backend will take care of the rest. I'm not sure how well foreign key and on delete is supported by MySQL, but this is something PostgreSQL has been doing well for a long time. --- Geir Pedersen http://www.activio.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Select Query--Need help urgently
Hi all, I am having having a slight problem with SELECT query statement. I have a table Class. The table are as follow: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | class_id | int(11) | | PRI | NULL| auto_increment | | class_code | varchar(255) | | | || | edu_level | varchar(16) | | | || | student_name | varchar(255) | | | || | tutor_name | varchar(255) | | | || | timetable_day | varchar(255) | | | || | timetable_time | varchar(255) | | | || ++--+--+-+-++ I am doing a SELECT query to retrieve some of the fields like timetable_time, tutor_name, class_code and edu_level from the table WHERE the timetable_day is equal to Monday . Meaning it will display fields only when the timetable_day value is Monday Below is a snip of my code: snip $timetable_time = $_GET[timetable_time]; $class_code = $_GET[class_code]; $edu_level = $_GET[edu_level]; $tutor_name = $_GET[tutor_name]; $sql = mysql_query(SELECT * FROM class where timetable_day='Monday'); $row = mysql_fetch_array($sql); $result = $db-query($sql); $numofrows = mysql_num_rows($sql); echo div align=\center\; echo div id=\pagecontent5\; echo table border=\0\ width=\700\ \n; echo tr class=\darker\; echo td align=\center\ colspan=\4\bMonday/b/td/tr; echo trtd align=\center\ class=\darkgreen\ width=\200\Time/td; echo td align=\center\ class=\lightgreen\ width=\150\Classcode/td; echo td align=\center\ class=\darkgreen\ width=\150\Level/td; echo td align=\center\ class=\lightgreen\ width=\200\Tutor/td/tr; echo /div; echo trtd align=\center\ class=\darker\ width=\200\.$row ['timetable_time']./td; echo td align=\center\ class=\lighter\ width=\150\.$row ['class_code']./td; echo td align=\center\ class=\darker\ width=\150\.$row ['edu_level']./td; echo td align=\center\ class=\lighter\ width=\200\.$row ['tutor_name']./td/tr; echo /div; /snip But I was unable to retrieve the values from database. Was it because I have written the SELECT query wrongly?? Realli need some help urgently..Hope to hear from all soon. Thanks in advance. Regards, Irin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
