PHP get all matching rows in relational mysql innodb database

Question

I have made 3 tables in a DB with a many to many relationship. for variable $refernece_keys in my DB should return all of the related fields in the DB - but it is only returning the first assigned and ignoring the others.

e.g. page1 should return keys_href: id's 1, 2, 3, 4, 8, 10, 15 - however it only returns id:1 page2 should return keys href: id's 3, 4, 5, 7, 8, 11 - however it only returns id:3

Is there something wrong with my ARRAY?

$SQL = 
   "SELECT  * FROM learn_more AS lm 

LEFT JOIN  learn_more_to_reference_key AS lmtrk 
        ON  lm.id = lmtrk.learn_more_id 

LEFT JOIN  reference_keys AS rk 
        ON  rk.keys_id = lmtrk.reference_key_id

     WHERE  lm.page_title = '$this_page'";

$result = mysql_query($SQL) or die(mysql_error()); 

 while ($db_field = mysql_fetch_array($result))
{   
        $id                =     $db_field['id'];
        $main_title        =     $db_field['main_title'];
        $main_content     =     $db_field['main_content'];
        $reference_keys   =     $db_field['keys_href']; 
        $sub_title        =     $db_field['sub_title'];
        $sub_content      =     $db_field['sub_content'];
}

------------------------------------------------>EDIT

My PHP view page is a template

<div id="content">  
<div class="section_frame">


<div class="section_title">
<?php echo $main_title; ?>
</div>

<div class="section_content">
<?php echo $main_content; ?>
</div>


<div class="section_content_ref">
<span class="hl_reference"><u>key references:</u></span> 
<?php echo $reference_keys; ?>
</div>

<div class="sub_section_title">
<?php echo $sub_title; ?>
</div>

<div class="sub_section_content">
<?php echo $sub_content; ?>
</div>

so basically i just want my values to echo out..

The sample code is incomplete without table schema and sample data, given as SQL statements. Read [Writing the perfect question](tinyurl.com/so-hints) for more guidelines. The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. — outis
– outis, Commented Jul 11, 2012 at 21:39
Don't use SELECT * unless you're writing a DB administration program; select only the columns you need. Note that or die(mysql_error()) should never appear in production code, as die breaks HTML output and database error messages should never be revealed to non-admin users as it discloses too much information. A better approach would be to properly implement error handling. — outis
– outis, Commented Jul 11, 2012 at 21:49

Kasapo · Accepted Answer · 2012-07-11 21:55:49Z

1

Ah, my first thought would be that since your while loop runs over all the records, after it is finished only the last record is available... except you are only seeing the first one. if you were to add echo $id inside the while loop, do all the IDs get printed? Also, if you have some more code to complete the example that would help.

$items = array();
while ($db_field = mysql_fetch_array($result))
{   
    $id                =     $db_field['id'];
    $main_title        =     $db_field['main_title'];
    $main_content     =     $db_field['main_content'];
    $reference_keys   =     $db_field['keys_href']; 
    $sub_title        =     $db_field['sub_title'];
    $sub_content      =     $db_field['sub_content'];
    $items[$id] = array($main_title, $main_content, $reference_keys, $sub_title, $sub_content);
}

The above would create an array where the id of each record points to the record information. Is that along the lines of what you are going for?

edited Jul 11, 2012 at 21:55

answered Jul 11, 2012 at 21:41

Kasapo

5,3611 gold badge22 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MrPizzaFace Over a year ago

forgive my ignorance because i just started to learn this past 30 days. on my page i'm just <?php echo $reference_keys; ?> in the while loop adding 'echo $id' does nothing as $id is not assigned to anything. I tried to do a 'foreach but ---> Warning: Invalid argument supplied for foreach() ' in my page code, but that returned an error.. what other code do i need? Maybe im missing something... db records are good though this time.. lol ty again

MrPizzaFace Over a year ago

i caught my mistake.. i echo $reference_keys; instead of echo $id and it shows all of the assigned keys.. but at the top of the page.. what am i doing wrong?

Kasapo Over a year ago

well, just an aside, as @Outis mentioned, the mysql_fetch_array function (and related ones) are headed towards deprecation. So you might want to see if mysqli is workable for you. See the message at the top of this link: php.net/manual/en/function.mysql-connect.php They recommend using the mysqli extension over mysql extension, meaning use mysqli_connect and mysqli_fetch_* functions instead of the mysql variants.

MrPizzaFace Over a year ago

Yeah im aware of the mysqli.. I tired that and PDO and I found it too confusing.. I will come back to it when i have a better grasp of the language

MrPizzaFace Over a year ago

Ok so i tried: <?php foreach($items[$id] as $value) { echo $value; } ?> Basically it gives me the same result as echoing each statement individually like i was... And still only showing the first related id..

|

Collectives™ on Stack Overflow

PHP get all matching rows in relational mysql innodb database

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related