PHP Two Table Query

[momo219]

I hope you guys can help me, basically...

I have 2 database tables (note: the U & B here are my particular fields of interest - the id and fk_id are the PK and FK)

(main)Table 1 gives the MAIN_TITLES (id, TITLE, positioning, created, modified)
(list)Table 2 (id, FK_ID(= T1 id), subTITLE, positioning, created, modified) is a list set, organised by a FK, and takes its main_title from Table 1

would look something like this

T1 - t2, t2, t2
T1 - t2, t2, t2, t2, t2
T1 - t2, t2, t2, t2
T1 - t2, t2, t2, t2, t2, t2, t2 t2, t2
T1 - t2, t2, t2, t2

Hopefully this is clear enough.... and so my code is:

$get_main_title = mysqli_query($mysqli, "SELECT * FROM table_1") or trigger_error (mysqli_error($mysqli));
while($row_main = mysqli_fetch_array($get_main_title)) {
	$main_id = $row_main['id'];
	$main_title = $row_main['title'];

	// Draw the Title and open div
	$display_block  = "<h1>".$main_title."</h1>";
	$display_block .= "<div..>";

		// Draw the Contents
		$get_list_title = mysqli_query($mysqli, "SELECT * FROM table_2 WHERE fk_id = $main_id") or trigger_error (mysqli_error($mysqli));
			while($row_list = mysqli_fetch_array($get_list_title)) {
				$list_title = $row_list['title'];
				$display_block .= "<p>".$list_title."</p>";
			}

	// Draw close div
	$display_block .= "</div>";
}

Pre-empting the what errors qs - there are none, but the main problem is that I am only getting the last row from table_1, and its associted table_2 rows from that table_1.

I'm thinking a foreach (row as key), but this is the help I need. (The above code is my entire code, so I really would appreciate a quick code fix, and then I'll kick myself good and hard), I'm sure it's a 3 or 4 line fix.

Thank you - mo!

[jmichae3]

no, this is not clear at all too little information and it makes no sense. what are all those t2's? tables? why are they arranged like that? you don't deal with tables that way normally without having some sort of metadata about t2. some data structure and data examples would have been helpful.
those are really bad table names.
call the table names what they are.
also, after finishing a query and dealing with its rows, you should do a mysql_free_result($resultname)
you want mysql_fetch_assoc, not mysql_fetch_array.
lots of people make that mistake.
you will find that mysqli is not as available as mysql. I don't know why, but it's not available in my local PHP for windows installation (or it just plain didn't work).
mysqli statements probably have to be prepared. you may have problems with your second query.
examine the API before you use it. I suggest that to do this, you get the documentation from php.net in the form of the enhanced .chm with notes. it is browseable, and there's lots of good stuff there.
you really should be usiong mysql instead of mysqli with this code.

[momo219]

@jmichae3 - Quite simply your arrogance is astounding… …not clear, too little information and makes no sense… are you serious?

If you can’t figure out that the table names are simplified for code snippet here and even simpler to understand is the T1 – t2, t2 etc refers to:

T1 (Main title) – t2 (subTitle), t2 (subTitle), t2 (subTitle) etc
(T2 foreign key is the same as T1 primary key)

Something you might understand:

(T1) Animals – (t2) Cow, (t2) Sheep, (t2) Pig
(T1) Insects – (t2) Ant, (t2) Flea, (t2) jmichae3

So I am trying to extract the:
first T1 and then the T2 associated with T1, then
second T1 and then the T2 associated with T1, and so on

I just can not understand how you think this is not clear from my first post.

Even simpler still– if you are having so much trouble understanding this post, why do you feel the need to reply and berate it.

[momo219]

Hilarious, 920,000+ members, so there must be a good handful of gurus running about the place and not one of them can endeavour to answer a simple little 2 Table Query question...

This place is so dead, it's a husk, and anyone involved in running it is pure idle... (I've posted here a few times in the past, rarely recieved any help)

I bet some of you admins still remember the days when you used to be able to knock out a little bit of code no bother - those days are long gone I bet.

[jmichae3]

I have actually tried a long time ago to use mysqli function api maybe once and it failed for me. maybe you will have more success with the class API. I guess there were bugs in the API back then circa 2009 that I just noticed in the new documentation. so dealing with PHP classes is new to me, but hey, I just learned something new. I think you can keep your connection persistent by not closing it. probably fixed by now. probably the only real difference that I made for this code is
- updating to get past deprecation (obsolesence)
- freeing up resultsets after you are finished with them. fixes a lot of weird results problems.

mysqli is now object oriented I just found out. they have deprecated the function API, so you should be using the class/object based interface.
http://us.php.net/manual/en/book.mysqli.php

one of the things you should ALWAYS do after you are finished with a result (such as the inner one and the outer one), is free up the result after you are done with that result using mysqli_result::free()
http://us.php.net/manual/en/mysqli-result.free.php

otherwise, you get problems with your resultsets, possibly like what you are describing (they are random problems, usually bad).

<?php

class foo_mysqli extends mysqli {
    public function __construct($host, $user, $pass, $db, $port) {
        parent::__construct($host, $user, $pass, $db, $port);

        if (mysqli_connect_error()) {
            die('Connect Error (' . mysqli_connect_errno() . ') '
                    . mysqli_connect_error());
        }
    }
}

$db = new foo_mysqli("localhost" , "someuser", "somepassword", "dbname" , "3306");

    $get_main_title = $db->query("SELECT * FROM table_1") or trigger_error($db->error);
    while($row_main = mysqli_fetch_array($get_main_title)) {
    $main_id = $row_main['id'];
    $main_title = $row_main['title'];
     
    // Draw the Title and open div
    $display_block = "<h1>".$main_title."</h1>";
    $display_block .= "<div..>";
     
    // Draw the Contents
    $get_list_title = $db->query($mysqli, "SELECT * FROM table_2 WHERE fk_id = '".$main_id."'") or trigger_error($db->error);
    while($row_list = $get_list_title->fetch_assoc()) {
		$list_title = $row_list['title'];
		$display_block .= "<p>".$list_title."</p>";
    }
	$get_list_title->free(); //free your inner result
    // Draw close div
    $display_block .= "</div>";
    }
	$get_main_title->free(); //free your main result


$db->close();	
?>

http://us.php.net/manual/en/mysqli-result.fetch-assoc.php
http://us.php.net/manual/en/mysqli-result.free.php

you want fetch_assoc instead of fetch_array.

momo, thanks for commenting. after a respite from the problem, I got a message in my spambox by daniweb, and I checked it out, and your post was among several I was retrieving, and I wouldn't have come back to this problem otherwise. I think this solution will solve the problem - now that I can look at it with a fresh mind.

[diafol]

Hilarious, 920,000+ members, so there must be a good handful of gurus running about the place and not one of them can endeavour to answer a simple little 2 Table Query question...

This place is so dead, it's a husk, and anyone involved in running it is pure idle... (I've posted here a few times in the past, rarely recieved any help)

I bet some of you admins still remember the days when you used to be able to knock out a little bit of code no bother - those days are long gone I bet.

Self fulfilling prophecy. I bet you've found yourself on a good few ignore lists with that post, but hey, no problem, you weren't expecting any posts anyway. :)

[jmichae3]

sigh - I need to get a reign in on my attitude. usually I am kind. I am sorry guys.