PHP MySQL syntax

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?
lvollmerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
lvollmerAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
lvollmerAuthor Commented:
Kent - thanks . I am getting this error:

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

I know ID exists as a field though.
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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

Also, are you connected to the correct database?


Kent
0
 
lvollmerAuthor Commented:
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
 
lvollmerAuthor Commented:
and wp_10_posts is the correct table
0
 
Kent OlsenData Warehouse Architect / DBACommented:
>> 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
 
lvollmerAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
lvollmerAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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

0
 
lvollmerAuthor Commented:
MySQL - 5.0.22

phpMyAdmin - 2.8.2.4
0
 
lvollmerAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
lvollmerAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
lvollmerAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
lvollmerAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
lvollmerAuthor Commented:
the easier the better...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.