[SOLVED] PHP mysql results array in array?

[desoto0311]

I have two tables that I'm getting data from using join. After collecting this, I'm creating an array to use variables for php/smarty later. Everything works fine with this mySQL call, but I think I need to somehow put my 'filename' variables in another array inside my 'records' array. This is what I mean:

 

myfunction ($ArticleId ) {

 

//SQL statement:

$sql = "SELECT * FROM table1, table2 WHERE table1.id = table2.articleid AND table1.id = $ArticleId";// $ArticleID pulled from URL.

 

while ($a_row = sqlFetchArray($res)) {

$records[$c]["id"] = $a_row["id"];

 

$records[$c]["articleid"] = $a_row["articleid"];

$records[$c]["filename"] = $a_row["filename"];

}

return $records

} // Note this isn't the exact function; I copy/pasted only this for brevity.

 

In my two tables I have a bunch of data, but here are the important ones:

table 1: id, title, date, author, desc

table 2: id, articleid, filename

 

Currently when I use a for each loop (smarty template but basically php) if there's only one 'filename', then it's fine, but if a particular recordset has multiple filenames, it loops (displays) the same 'article' with each filename. (Which I understand is what I'm asking the SQL statement to do.) So since this function actually does a few other things, I'm hoping to not have to cut it up too bad (lol) but basically need a way to have my output $records loop display only the one record with $ArticleID (in this example there's only 1 record that should ever display, so I'm sure my mySQL statement is somewhat wasteful.) but have the $record.filename output give me the list of only table2.filename results.

 

I hope this makes sense.

 

[thepip3r]

while ($a_row = sqlFetchArray($res)) {
       $records[$c]["id"] = $a_row["id"];

       $records[$c]["articleid"] = $a_row["articleid"];
       $records[$c]["filename"][] = $a_row["filename"];
}

 

viola!  now your $records[$c]["filename"] is an array.  it may hold ONE entry or it may hold 1million!!  (/pinky-to-lip)  regardless, just use a foreach loop on $records[$c]["filename"] and it will always display the correct number of file names despite how many articles there are for it.

[desoto0311]

Sweet, I'll try it! BTW, do you answer ALL of the posts in this forum? lmao. When I go searching for answers around here I see your name come up quite a bit on the 'answer' side of things, lol.

[thepip3r]

i was once in your shoes my friend.  i come back often myself to post and while i'm waiting for assistance with my problems, i answer others to kind of, "give back to the community."  hopefully you'll do the same someday.  =P

[desoto0311]

I'm getting there, lol. At the moment I tend to be of more flash assistance than php/mysql, lol.

 

Okay, so I took a look at this again and it *appears* that I might actually be having problems with either the initial SQL statement or possible the 'while' statement.

The 'filename' records are in an array now, however when displaying (using : {foreach item="records" from=$records}) the loop, I still get a repeated record (same record from 'table1.id') times the number of filename entries. I thought something like that only happens if I do a LEFT or RIGHT join?

[thepip3r]

well the "repeatedness" depends on a lot of factors:  your table structure, your sql query, etc, etc.  i can tell you how to bandaid your problem but i'm still a little lost as to your exact issue since i don't know ur table struct or your resultset.

 

bandaid:  you can always use an if (!isset($records[$c]["articleid"])) {  -- that way if it's already set, it won't again but i again, that would be a bandaid to *what sounds like* a poor query. 

 

u can either play with the bandaid or give us a sample resultset of your query so we can examine it and get a better grasp at what data relationships look like.

[desoto0311]

Here are the results of the query. Looking at this, it appears that while the filename is an array, I don't have it setup properly to list all filenames in that array. (It's putting one name into each instance of the array.)

 

I have the DB structure posted above; it's a pretty simple one but should work.

 

Array
(
    [0] => Array
        (
            [id] => 2
            [catid] => 3
            [title] => images test
            [author] => author name
            [desc] => description here.
            [articleid] => 2
            [filename] => Array
                (
                    [0] => image0001.jpg
                )

        )

    [1] => Array
        (
            [id] => 3
            [catid] => 3
            [title] => images test
            [author] => author name
            [desc] => description here.
            [articleid] => 2
            [filename] => Array
                (
                    [0] => image5434.jpg
                )

        )

    [2] => Array
        (
            [id] => 4
            [catid] => 3
            [title] => images test
            [author] => author name
            [desc] => description here.
            [articleid] => 2
            [filename] => Array
                (
                    [0] => image5464.jpg
                )

        )

)

[thepip3r]

between your two tables.. where is table1 associated to table2?  what is the data relationship?  table1.id is a 1->Many with table2.articleid??  if so, try this query:

 

SELECT *
FROM table1 t1
JOIN table2 t2 ON (t2.articleid=t1.id)
WHERE t1.id='$articleid'

 

EDIT:  the reason your code is displaying in different numeric-index'd 0th arrays is because of your $c variable.  i'm not sure what that is or what number it's derived from but that's the reason, your data is showing up in different blocks at that lvl, $c is changing with each iteration of your while loop.

 

also, if you have PHPmyadmin... run the raw SQL query through it and give us a sample result set in the SQL itself, not the PHP-washed data.

[desoto0311]

I should have probably just done this from the get-go, lol. Here's the complete table structure and function:

Table Structure:

table:

mediaarticles (TABLE 1)

L id

L catid

L title

L author

L desc

 

mediafiles (TABLE 2)

L id

L articleid (This is 'id' from TABLE 1)

L filename

 

Function:

function getMediaPages($catID,$mediaArticle) {
$limit = 5;

if (isset($mediaArticle)) {
$sql = "SELECT * FROM mediaarticles, mediafiles WHERE mediaarticles.id = mediafiles.articleid AND mediaarticles.id = $mediaArticle";
} else {

if ($catID != "all" && $catID != 0) {
$sql = "SELECT * FROM mediaarticles WHERE catid = $catID";  
} else {
	$sql = "SELECT * FROM mediaarticles, mediafiles WHERE mediafiles.articleid = mediaarticles.id ORDER BY mediaarticles.id DESC LIMIT $limit";
}

}  // check : search by category or article END

$res = sqlQuery($sql); if(sqlErrorReturn()) sqlDebug(__FILE__,__LINE__,sqlErrorReturn());

$c=0;
while ($a_row = sqlFetchArray($res)) {
	 $records[$c]["id"] = $a_row["id"];
	 $records[$c]["catid"] = $a_row["catid"];
	 $records[$c]["title"] = $a_row["title"];
	 $records[$c]["author"] = $a_row["author"];
	 $records[$c]["desc"] = $a_row["desc"];

	 $records[$c]["articleid"] = $a_row["articleid"];
	 $records[$c]["filename"][] = $a_row["filename"];
++$c;
}
if (!empty($records)) {
    return $records;
}

}

 

In a nutshell, if "$ArticleId" var is there, it uses the first SQL query, and is *SUPPOSED* to return ONE record, from mediaarticles with the appropriate id ($ArticleId = mediaarticles.id) and then grab all mediafiles.filenames and place them into the filenames records array.

 

I then have :: (smarty template)

{if $mediaArticle}
{foreach item="records" from=$records}
Results Looped here. At this point I may need a second foreach(?) to pull from the filenames array?
{/foreach}
{/if}

Which displays the records results. Seemed like it should work (lol) until I ran into multiple filename records....  :/

 

EDIT: As far as getting a raw SQL dump; sorry, don't know how to pull that one off. I paste my sql query but the results are still html printed tabular data. (And it DOES loop through the same record, in this case mediarticle.id "2", but attaches the different filename to it each time.)

[thepip3r]

ok... so i think i get it looking back at your #6, it's the $c variable that's throwing both of us off (I think)...

 

change your array building loop to:

 

while ($a_row = sqlFetchArray($res)) {
	 $records[$a_row["id"]] = $a_row["id"];
	 $records[$a_row["id"]]["catid"] = $a_row["catid"];
	 $records[$a_row["id"]]["title"] = $a_row["title"];
	 $records[$a_row["id"]]["author"] = $a_row["author"];
	 $records[$a_row["id"]]["desc"] = $a_row["desc"];

	 $records[$a_row["id"]]["articleid"] = $a_row["articleid"];
	 $records[$a_row["id"]]["filename"][] = $a_row["filename"];
}

 

that way... duplicate record information (catid, title, author, desc) will just get overwritten and any new filenames will be appended into the filename array.

 

if this doesn't work, i still don't see the problem then because according to your temp resultset, your "id" field is unique so they *SHOULD* be different records w/different filenames.

[desoto0311]

Yep that did it. So the good news is no more looping of duplicate records. (Now I just get to figure out how to pull filenames from it's array, but that's not terrible.)

 

The BAD news is that function had a double-use, which was to check if looking for a single record or multiple records. Now my multiple record search results only pulls the last record.

 

Not a biggie though; I figure I'll just duplicate the entire "while" statement and use your modified version for my single result and the original for all other results. I think it's a 'band-aid', but WAY less band-aid-y than it was going to be originally, lmao.

 

Thanks a bunch!

[thepip3r]

to give you a hint desoto.. the thing that makes PHP absolutely beautiful for me is it's array abilities.  of all the languages, i've worked with (batch, vbscript, perl, kix, autoit, etc), all array handling was horribly painful (with the exception of perl) but PHP just eclipses them (IMO). 

 

so guessing your resultset now looks somethign like:

 

Array
(
    [0] => Array
        (
            [id] => 2
            [catid] => 3
            [title] => images test
            [author] => author name
            [desc] => description here.
            [articleid] => 2
            [filename] => Array
                (
                    [0] => image0001.jpg
                    [1] => image0002.jpg
                    [2] => image0003.jpg
                )

        )

    [1] => Array
        (
            [id] => 3
            [catid] => 3
            [title] => images test
            [author] => author name
            [desc] => description here.
            [articleid] => 2
            [filename] => Array
                (
                    [0] => image5434.jpg
                    [1] => image5435.jpg
                    [2] => image5436.jpg
                )

        )

 

the nested loop to see and use that data is as simple as:

 

foreach ($array[$id]['filename'] as $val) {
            echo "$val";  # $val will be the filename echo'd to the screen
}

 

..or you can do it iteratively:

foreach ($array as $key => $val) {
         if ($key == "filenames") {
                     foreach ($key as $file) {
                                echo "$file";  
                     }
         }
}

[desoto0311]

Cool thanks. Yeah, I'm *somewhat* versed in php arrays (I seem to use them more and more as time goes on) but I've got an existing site this is now going in that uses smarty templates. It almost seems harder sometimes as I've got to duplicate the results (ie setup the aforementioned foreach in php, then again later in smarty) although I understand the separation of code and content.

 

Anyway, this works for me so I'm stoked.

 

Thanks again,

-D