Solved

PHP MySQL syntax

Posted on 2008-06-24
25
234 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
ID: 21858845

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
ID: 21858946
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
ID: 21859114
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
ID: 21859401
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
ID: 21859738

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

Also, are you connected to the correct database?


Kent
0
 

Author Comment

by:lvollmer
ID: 21860278
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
ID: 21860285
and wp_10_posts is the correct table
0
 
LVL 45

Expert Comment

by:Kdo
ID: 21860455
>> 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
ID: 21860686
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
ID: 21861005

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
ID: 21861336
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 45

Expert Comment

by:Kdo
ID: 21862117

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

0
 

Author Comment

by:lvollmer
ID: 21867113
MySQL - 5.0.22

phpMyAdmin - 2.8.2.4
0
 

Author Comment

by:lvollmer
ID: 21867590
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
ID: 21867738

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
ID: 21867836
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
ID: 21869135

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
ID: 21869326
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
ID: 21869836

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
ID: 21869949
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
ID: 21870107

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
ID: 21870216
the easier the better...
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 23116043
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to count occurrences of each item in an array.
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 …

920 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

18 Experts available now in Live!

Get 1:1 Help Now