PHP MySQL SELECT woes

[blain]

I am trying to create a custom navigation system for my wordpress site.
I have created some parent pages and some sub-pages
I have the following pages
Home ID=1, post_parent=0
Page1 ID=2, post_parent=0
Page2 ID=3, post_parent=0

subpage1 ID=4, post_parent=1
subpage2 ID=5, post_parent=1
subpage3 ID=6, post_parent=2
subpage4 ID=7, post_parent=2
subpage5 ID=8, post_parent=3
subpage6 ID=9, post_parent=3

the field post_parent relates to the ID of the parent page.

I am trying to create two navigation lists.

1. Displays all pages where post_parent=0
2. Displays all pages where post_parent= ID of parent page

for example
if someone clicks on home (id=1) then the second navigation list will display all subpages where post_parent=1, if someone clicks on page 1 (id=2) then the second navigation will display all subpages where post_parent=2 etc. etc.

I have created the first navigation list ok, it is the second I am struggling with.
Can anyone give me any help?

The page ID is passed in the URL as ?page_id=

[cranial-bore]

Are you trying to get the content for the parent page, and the links to the subpages in a single query, or are you wanting to run a separate query to list all the subpages for the current page?

If it's the latter it's just a matter of using the current page id in the query (as a variable)

For example:

$id = (int)$id;  //current page ID
$query = "SELECT * FROM `wp_pages` WHERE post_parent=$id";

if($result = mysql_query($query)) while($r = mysql_fetch_assoc($result)) {
   echo "Visit subpage $r[ID]<br>";
} 

Change the * to select only the fields you need.

[blain]

Basically I need the second menu to do this.

1. get page_id from url
2. goto database and find post where field id = page_id
3. look at field post_parent
4. find all other posts with same post_parent
5. print a list of these pages using

printf("<li><a href=\"%s\">%s</a></li>\n",$row["guid"], $row["post_title"]);

for each entry

[r937]

this will return all siblings, including the $page_id you started with

select sibling.id
     , sibling.dateposted
     , sibling.body
  from yourtable as thispost
inner
  join yourtable as parent
    on parent.id = thispost.post_parent
inner
  join yourtable as sibling
    on sibling.post_parent = parent.id
 where thispost.id = $page_id

[blain]

Can anyone explain why this doesn't work
url of page ends ?page_id=4

$pid = $_GET['page_id'];
printf ("page id is %d ", $pid);
$ppid  = "SELECT post_parent FROM wp_posts WHERE id=$pid"; 
printf ("post_parent of this page is %d ", $ppid);

It is displaying the following

page id is 4 post_parent of this page is 0

it should be displaying

page id is 4 post_parent of this page is 1

[cranial-bore]

You're not executing the query. You've just created a string that happens to look like an SQL query, but MySQL doesn't know anything about it.

You'll need to connect (mysql_connect) and use the functions in my original code to actually SELECT anything.

[earl-grey]

Can anyone explain why this doesn't work
url of page ends ?page_id=4

$pid = $_GET['page_id'];
printf ("page id is %d ", $pid);
$ppid  = "SELECT post_parent FROM wp_posts WHERE id=$pid"; 
printf ("post_parent of this page is %d ", $ppid);

It is displaying the following

page id is 4 post_parent of this page is 0

it should be displaying

page id is 4 post_parent of this page is 1

Also, make sure to escape values that come from user input:

$value = mysql_real_escape_string($value, $conn_id); 

Or check if variables contain values of appropriate data type.