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](http://sscce.org/) is incomplete without [table schema and sample data](http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html), 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, Jul 11 '12 at 21:39
Don't use [`SELECT *`](http://stackoverflow.com/q/321299/) 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`](http://www.phpfreaks.com/blog/or-die-must-die) breaks HTML output and database error messages should never be revealed to non-admin users as it [discloses too much information](http://msdn.microsoft.com/en-us/library/ms995351.aspx#securityerrormessages_topic2). A better approach would be to properly implement error handling. — outis, Jul 11 '12 at 21:49

Kasapo · Accepted Answer · 2012-07-11T21:55:49.243

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 '12 at 21:55

answered Jul 11 '12 at 21:41

Kasapo

5,294
1
19
21

forgive my ignorance because i just started to learn this past 30 days. on my page i'm just `` 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 Jul 11 '12 at 21:48
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? – MrPizzaFace Jul 11 '12 at 21:50
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: http://www.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. – Kasapo Jul 11 '12 at 21:55
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 Jul 11 '12 at 22:00
Ok so i tried: Basically it gives me the same result as echoing each statement individually like i was... And still only showing the first related id.. – MrPizzaFace Jul 11 '12 at 22:07
You should verify you are retrieving multiple records -- what does "mysql_num_results" report? – Kasapo Jul 11 '12 at 22:17
try: `` $value) { echo $value; } ?>`` -- although that should just print an array. But, if you rewrite the part that says ``$items[$id] = array(...)`` to use keys to point to each db field, then you could do: `` $property) { foreach ($property as $name => $value) { ... do_stuff(); } }`` – Kasapo Jul 11 '12 at 22:21
i should mention -- that loop (items as id => value) must go in the template and the loop braces must surround all the echo statements as the template will be executed only once and only after looping through the results, if I understand your code correctly. Are you Using a specific framework? Anyway, gots to go, but if this is still an issue tomorrow I'll start a chat with ya. – Kasapo Jul 11 '12 at 22:24
Im not using a framework - I just designed my own very simple version of a framework.. Ty for the help. I will try to get it working if i can't I will chat with you tomorrow for sure. Thanks again and have a great day.. btw im in queens so we're like neighbors.. – MrPizzaFace Jul 11 '12 at 22:48

PHP get all matching rows in relational mysql innodb database

1 Answers1

Linked