Solved

PHP MySQL syntax

Posted on 2008-06-24
25
231 Views
Last Modified: 2013-12-12
I have a table with the following columns:

ID
Content
post_parent

I don't know how to explain what I am trying to do, so I will provide an example.



ID - 200
Content - "this is some content . the content is usually long"
post_parent - 0



If the above record has an image associated to the content field, another record in the same table is written

id - 250 ( i don't think i need this)
content - null
post_title - filename.jpg (this is the file I need to grab)
post_parent - 200 - same as ID in first record.



I want to query the table and display the records, but if there is an associated record with an image , I need to display that image with the record.

Can anyone help me out with this?
0
Comment
Question by:lvollmer
  • 12
  • 10
25 Comments
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Hi lvollmer,

The SQL is pretty easy.  :)  If the links cascade (Row A points to Row B which points to Row C which has the graphic) you'll probably want recursive SQL.  If the number of links is at most 1, a simple join will do.

SELECT T0.id, T0.content, coalesce (t0.post_title, t1.post_title)
FROM sometable t0
LEFT JOIN sometable t1
  ON t0.post_parent = t1.ID;

You might want to filter some of the rows, as if Row A points to Row B, and Row B has the graphic, there will be a line returned for Row A and Row B, both pointing to the same graphic.


Good Luck,
Kent
0
 

Author Comment

by:lvollmer
Comment Utility
hmm this is a little above my head can you help me out? here are the exact names


ID
post_content (contains the text)
post_title (contains filename)
post_parent - matches the ID if a file exists

table name

wp_posts
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Sure.  :)

Try this:

  SELECT T0.id, T0.post_content, coalesce (t0.post_title, t1.post_title)
  FROM wp_posts t0
  LEFT JOIN wp_posts t1
    ON t0.post_parent = t1.ID;



Kent
0
 

Author Comment

by:lvollmer
Comment Utility
Kent - thanks . I am getting this error:

#1054 - Unknown column 'T0.ID' in 'field list'

I know ID exists as a field though.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

That's really, really bizarre.  How are you running this?

Also, are you connected to the correct database?


Kent
0
 

Author Comment

by:lvollmer
Comment Utility
yeah I am running the query in query analyzer and that is the error I get.

ID        bigint(20)               UNSIGNED        No               auto_increment

that is the field in wp_10_posts
0
 

Author Comment

by:lvollmer
Comment Utility
and wp_10_posts is the correct table
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
>> and wp_10_posts is the correct table

ahh...  :)

  SELECT T0.id, T0.post_content, coalesce (t0.post_title, t1.post_title)
  FROM wp_10_posts t0
  LEFT JOIN wp_10_posts t1
    ON t0.post_parent = t1.ID;
0
 

Author Comment

by:lvollmer
Comment Utility
I still get that error


SELECT T0.id, T0.post_content, coalesce( t0.post_title, t1.post_title )
FROM wp_10_posts t0
LEFT JOIN wp_10_posts t1 ON t0.post_parent = t1.ID
LIMIT 0 , 30;

MySQL said: Documentation
#1054 - Unknown column 'T0.id' in 'field list'
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Something else is going on here.  This SQL should work just fine.  There's an oddity with the MySQL parser where a function name must be followed immediately by a left parenthesis (no intervening space) but that generates an entirely different error.

Can you run a describe on the table?  I don't think that that will reveal anything, but let's check.

  describe wp_10_posts;


Kent
0
 

Author Comment

by:lvollmer
Comment Utility
Field          Type          Null          Key          Default          Extra
      Edit       Delete       ID       bigint(20) unsigned       NO       PRI       NULL       auto_increment
      Edit       Delete       post_author       bigint(20)       NO               0       
      Edit       Delete       post_date       datetime       NO               0000-00-00 00:00:00       
      Edit       Delete       post_date_gmt       datetime       NO               0000-00-00 00:00:00       
      Edit       Delete       post_content       longtext       NO                       
      Edit       Delete       post_title       text       NO                       
      Edit       Delete       post_category       int(4)       NO               0       
      Edit       Delete       post_excerpt       text       NO                       
      Edit             post_status       enum('publish','draft','private','static','object'...       NO               publish       
      Edit             comment_status       enum('open','closed','registered_only')       NO               open       
      Edit       Delete       ping_status       enum('open','closed')       NO               open       
      Edit       Delete       post_password       varchar(20)       NO                       
      Edit       Delete       post_name       varchar(200)       NO       MUL                
      Edit       Delete       to_ping       text       NO                       
      Edit       Delete       pinged       text       NO                       
      Edit       Delete       post_modified       datetime       NO               0000-00-00 00:00:00       
      Edit       Delete       post_modified_gmt       datetime       NO               0000-00-00 00:00:00       
      Edit       Delete       post_content_filtered       text       NO                       
      Edit       Delete       post_parent       bigint(20)       NO               0       
      Edit       Delete       guid       varchar(255)       NO                       
      Edit       Delete       menu_order       int(11)       NO               0       
      Edit       Delete       post_type       varchar(20)       NO       MUL       post       
      Edit       Delete       post_mime_type       varchar(100)       NO                       
      Edit       Delete       comment_count       bigint(20)       NO               0       
      Edit             button       varchar(255)       NO               
      Edit       Delete       domain       varchar(255)       NO               
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

Expert Comment

by:Kdo
Comment Utility

What are you using for a client?  And what version of MySQL are you running?

0
 

Author Comment

by:lvollmer
Comment Utility
MySQL - 5.0.22

phpMyAdmin - 2.8.2.4
0
 

Author Comment

by:lvollmer
Comment Utility
OK well i spotted the error, it was a case issue with the T's



SELECT T0.id, T0.post_content, coalesce( t0.post_title, t1.post_title )
FROM wp_10_posts t0
LEFT JOIN wp_10_posts t1 ON t0.post_parent = t1.ID


So I ran the query , but the results are not quite what I am looking for.

Example:

Record 2096:
post_content = Forgive me if I take a break from eating out for a...
coalesce( t0 . post_title , t1 . post_title ) = Restaurant Week: Crabtree's Kittle H...

coalesce should be library.jpg
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

I'm not aware of MySQL being that picky with case.  I don't know what's going on there....

For debugging, run the following query:

  SELECT T0.*, T1.*, coalesce( t0.post_title, t1.post_title )
  FROM wp_10_posts t0
  LEFT JOIN wp_10_posts t1 ON t0.post_parent = t1.ID


And post back the results.

Kent
0
 

Author Comment

by:lvollmer
Comment Utility
I had to change the case again for it to work, but I get the same results I would expect to get if I hit the "browse" link on wp_10_posts

I just realized there is a field called "post_type"

the ID 2096 has a post type of "publish"

the associated image where post_parent = id 2096 has a post_type of "attachment"

Does that help at all?
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Hi lvollmer,

That doesn't sound like the query from two posts back.  I just realized that your query isn't the entire table, so I need to make a small change to it.  Basically, I want the query to return the key column for each row, those same columns for the associated row at "post_parent", and the value that should be returned for post_title.

Try this one and post back a few lines.

Kent



--

--

  SELECT T0.id, T0.content, T0.post_parent, T1.id, T1.content, T1.post_parent,

    coalesce(T0.post_title, T1.post_title)

  FROM wp_10_posts T0

  LEFT JOIN wp_10_posts T1

    ON T0.post_parent = T1.ID;

Open in new window

0
 

Author Comment

by:lvollmer
Comment Utility
ID = 2096
post_content = Forgive me if I take a break from...
post_parent = 0
ID = null
post_content = null
post_parent = null
coalesce( T0 . post_title , T1 . post_title ) = Restaurant Week: Crabtree's Kittle H...
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Ok.  Based on what you've asked, that looks correct.  But I suspect that we're not done yet.  :)

What do we need to do next?


Kent
0
 

Author Comment

by:lvollmer
Comment Utility
OK -  looking down a little further on the result set, I see the type of row I would need to pull in.

here is an example:

ID = 2111
post_content = this is blank, not null
post_parent = 2096
id = 2096
post_content = Forgive me if I take a break from eating out for a...
post_parent = 0
coalesce( T0 . post_title , T1 . post_title ) = library-6648.jpg

This is the exact type of record I need, with the only addition being pulling in the post_title. Also, several records show for 2096 because several images are uploaded. I only need 1 record per ID. any record will do.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

We can certainly filter for names that end in .jpg.  That's not tough, but that could be limiting as there are a lot of different graphics file types.  Is there another indicator that the field post_title contains a file name?

Also, if you only need reference per image, we can do this the easy way.  We don't need to follow the post_parent value into another record because we'll have already picked up the secondary record is it will match our search criteria.


Kent
0
 

Author Comment

by:lvollmer
Comment Utility
the easier the better...
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now