How to Build a Complex Web-Page from Dataset?

captainccs · May 22, 2013, 2:23am

fretburner:

I don’t really have much to add at this point, but one thing you could do that would make the HTML a little bit cleaner would be to build your article urls when you loop over the results of $stmt2, like this:
while (mysqli_stmt_fetch($stmt2)){ 
    $featuredArticlesArray[] = array(
        'articleSubsectionSlug' => $articleSubsectionSlug
        'articleHeading' => $articleHeading, 
        'articleSummary' => $articleSummary, 
        'articleImage' => $articleImage
        'articleUrl' => "/{$sectionSlug}/{$articleSubsectionSlug}/{$articleSlug}"
    ); 
}
Then your HTML link is just:
<a href="{$articleArray['articleUrl']}">{$articleArray['articleHeading']}</a> 
It’s a bit easier to see what’s going on at a glance now.

An associative array is an excellent suggestion and Debbie gets to play with curly braces.

DoubleDee · May 22, 2013, 5:41pm

captainccs:

I don’t know, sounds complicated. Let’s go back my original suggestion:
<body>
<div><?php echo $div1; ?></div>
<div><?php echo $div2; ?></div>
<div><?php echo $div3; ?></div>
<div><?php echo $div4; ?></div>
</body>
There you have the variables ($div1, $div2, $div3, $div4) that you need to build in the data section of the script. What could be easier?

You code implies something that is STATIC.

My situation is DYNAMIC.

If this was just hard-coding 4 slots for Sub-Sections, I would have never posted here.

I don’t particularly want to read the code for several reasons:

1.- You haven’t disclosed the table structure making it impossible to evaluate the sql
2.- I haven’t yet got to “prepared” statements, I’m currently working on class architectue.
3.- If and when you reorganize your page the code is likely to be different.

How can you help if you refuse to look at my code?

I didn’t disclose my ERD and Table Structure because as I said before, it is immaterial.

As for your comments above…

1.) Conceptually all you need to know is that a Section has multiple SubSection which have multiple Articles. (I’m not trying be a jerk on this, but the ERD for what we are talking about is 7 tables, and I don’t want this thread veering off into a debate about my Data Model which is fine.)

2.) Prepared Statements are not what this thread is about, so no knowledge is really required.

3.) If I rearrange anything, my code will likely be different, so what is your point?!

You don’t need the $n=$n+1; The name C++ comes from the ++ operator. You could use

$featuredArticlesArray[$n++]

which would increment $n after it was used. ++$n would increment $n before it was used.

All well and good, but not what my OP is about.

Sincerely,

Debbie

Drummin · May 22, 2013, 5:48pm

I knew there had to be more to your sample layout than that single div.

<div class=‘boxSubSection’></div>

Probably the best way to make each section dynamic is replace what you’ve already made for your link list (assuming <ul><li></li></ul>) with the foreach loop from your array.

Drummin · May 22, 2013, 8:49pm

I would think you wouldn’t need that “WHILE” loop in the html.
Just add the $subsectionSlug as the primary key in your array building.

This would let you do away with lines like

if ($articleArray[‘articleSubsectionSlug’] == $subsectionSlug){

Doing away with the $n keys would also be a good idea and use undefined keys (0,1,2 etc).
This would let you know that key[0] is your featured acticle and 1 through 5 (or how many you wish to show) would be article links.

It’s a little hard to put this together without being able to test it but your array building section might be something like this assuming you are still nesting these queries,

<?php
    $sectionSlug = $_GET['section'];
    $sectionName = getSectionName($dbc, $sectionSlug);
    $dimensionSlug = 'featured-' . $sectionSlug;

    // Build Featured Sub-Sections.
$featuredArticlesArray = array();
    // Build query.
    $q1 = "SELECT ss.slug, ss.name
            FROM section_dimension AS sd
            INNER JOIN dimension_subsection AS ds
            ON sd.dimension_slug = ds.dimension_slug
            INNER JOIN subsection AS ss
            ON ss.slug = ds.subsection_slug
            WHERE sd.section_slug = ?
            AND sd.dimension_slug = ?
            ORDER BY sd.section_slug, ss.sort";

    // Prepare statement.
    $stmt1 = mysqli_prepare($dbc, $q1);

    // Bind variable to query.
    mysqli_stmt_bind_param($stmt1, 'ss', $sectionSlug, $dimensionSlug);

    // Execute query.
    mysqli_stmt_execute($stmt1);

    // Store results.
    mysqli_stmt_store_result($stmt1);

    // Check # of Records Returned.
    if (mysqli_stmt_num_rows($stmt1)>0){
        // Featured Sub-Sections Found.

        // Bind result-set to variables.
        mysqli_stmt_bind_result($stmt1, $subsectionSlug, $subsectionName);

        // Fetch below in loop...

    }else{
        // Error-Handling
    } 
    // Build Featured Articles.

    // Build query.
    $q2 = "SELECT ap.ds_subsection_slug, a.slug, a.heading, a.summary, a.image
            FROM article AS a
            INNER JOIN article_placement AS ap
            ON a.slug = ap.article_slug
            WHERE ap.sd_section_slug = ?
            AND ap.ds_dimension_slug = ?
            ORDER BY ap.ds_subsection_slug, RAND()";

    // Prepare statement.
    $stmt2 = mysqli_prepare($dbc, $q2);

    // Bind variable to query.
    mysqli_stmt_bind_param($stmt2, 'ss', $sectionSlug, $dimensionSlug);

    // Execute query.
    mysqli_stmt_execute($stmt2);

    // Store results.
    mysqli_stmt_store_result($stmt2);

    // Check # of Records Returned.
    if (mysqli_stmt_num_rows($stmt2)>0){
        // Featured Sub-Sections Found.

        // Bind result-set to variables.
        mysqli_stmt_bind_result($stmt2, $articleSubsectionSlug, $articleSlug, $articleHeading,
                                $articleSummary, $articleImage);


        // Build Featured-Articles Array.
        $n=1;
        while (mysqli_stmt_fetch($stmt2)){			
			$featuredArticlesArray[$subsectionSlug]['subsectionName'][] = $subsectionName;
            $featuredArticlesArray[$subsectionSlug]['articleSlug'][] = $articleSlug;
            $featuredArticlesArray[$subsectionSlug]['articleHeading'][] = $articleHeading;
            $featuredArticlesArray[$subsectionSlug]['articleSummary'][] = $articleSummary;
            $featuredArticlesArray[$subsectionSlug]['articleImage'][] = $articleImage;
        }//End of BUILD FEATURED-ARTICLE ARRAY

        // Fetch below in loop...

    }else{
        // Featured Sub-Sections Not Found.
    }  
?>

The output section might be something like this

        <?php			
			// ********************************
            // Build Featured Sub-Sections.        *
            // ********************************
            
            foreach($featuredArticlesArray as $subsectionSlug => $articleArray){
                // Start of Sub-Section Box.
                echo "<div class='boxSubSection'>
                        <h3>{$featuredArticlesArray[$subsectionSlug]['subsectionName'][0]}</h3>";

                // ****************************
                // Build Featured Articles.        *
                // ****************************
                 
                            // Build Featured-Article.
                            echo "<div class='subFeatured'>
                                    <a href=''>
                                        {$featuredArticlesArray[$subsectionSlug]['articleImage'][0]}
                                        <h4>{$featuredArticlesArray[$subsectionSlug]['articleHeading'][0]}</h4>
                                    </a>
                                    {$featuredArticlesArray[$subsectionSlug]['articleSummary'][0]}
                                </div>";                

                    // ********************************
                    // Find Articles in Sub-Section.    *
                    // ********************************	
					
						// check IF there are subArticles
						if (array_key_exists(1,$featuredArticlesArray[$subsectionSlug]['articleSlug']){
						
							// Start of Featured-Links.
							echo "<div class='subArticles'>
                                        <ul>";
										
								//Set limit for number of links shown in Loop		
		                        for($a=1;$a<=5;$a++){
								
									//check for subArticle key and show if found
									if (array_key_exists($a,$featuredArticlesArray[$subsectionSlug]['articleSlug']){
										echo "<li>
											<a href='/{$featuredArticlesArray[$subsectionSlug]['articleSlug'][$a]}/{$featuredArticlesArray[$subsectionSlug]['articleSubsectionSlug'][$a]}/{$featuredArticlesArray[$subsectionSlug]['articleSlug'][$a]}'>{$featuredArticlesArray[$subsectionSlug]['articleHeading'][$a]}</a>
										</li>";
			                        }
									
								}
								
						     // End Featured-Links
	               			 echo "</ul>
								 </div>"; 
								 
						}//End check IF there are subArticles
						
                // End of Sub-Section Box.
                echo "</div>";

            }//End of BUILD FEATURED SUB-SECTIONS
        ?>

You may of course shorten the name of the array to anything you wish, e.g. $AA for Articles Array etc.

captainccs · May 22, 2013, 9:19pm

Not at all! Inside a div you can have a tree, a list or anything else you want but the place to build that content is not in the view section of the script. If a div happens to have subsections, you create those in the data part of the script. And inside those sub-sections you can have sub-sub sections and inside those, sub-sub-sub sections as deep as you want or need. The object is to separate data and view so as not to build a pasta factory, a.k.a. spaghetti code.

You can iterate as in foreach(), or you can use recursive functions, or you can use OOP to create the data content and when you get to the view part of the code you simply echo is as in

<div><?php echo $myData; ?></div>

3.) If I rearrange anything, my code will likely be different, so what is your point?!

My point is that you need to get the design right, the actual code, as long as it works, makes little difference. The important issue is the architecture. Once you get the structure right, everything tends to fall in place as if by magic. That’s my point.

Drummin · May 22, 2013, 10:40pm

Absolutely catianccs there are many ways to build this data for display.

Direct build without array might go something like this. (not tested of course)

 <?php
    $sectionSlug = $_GET['section'];
    $sectionName = getSectionName($dbc, $sectionSlug);
    $dimensionSlug = 'featured-' . $sectionSlug;

    // Build Featured Sub-Sections.
$myData = "";
    // Build query.
    $q1 = "SELECT ss.slug, ss.name
            FROM section_dimension AS sd
            INNER JOIN dimension_subsection AS ds
            ON sd.dimension_slug = ds.dimension_slug
            INNER JOIN subsection AS ss
            ON ss.slug = ds.subsection_slug
            WHERE sd.section_slug = ?
            AND sd.dimension_slug = ?
            ORDER BY sd.section_slug, ss.sort";

    // Prepare statement.
    $stmt1 = mysqli_prepare($dbc, $q1);

    // Bind variable to query.
    mysqli_stmt_bind_param($stmt1, 'ss', $sectionSlug, $dimensionSlug);

    // Execute query.
    mysqli_stmt_execute($stmt1);

    // Store results.
    mysqli_stmt_store_result($stmt1);

    // Check # of Records Returned.
    if (mysqli_stmt_num_rows($stmt1)>0){
        // Featured Sub-Sections Found.

        // Bind result-set to variables.
        mysqli_stmt_bind_result($stmt1, $subsectionSlug, $subsectionName);

        // Fetch below in loop...

    }else{
        // Error-Handling
    }
    // Build Featured Articles.

    // Build query.
    $q2 = "SELECT ap.ds_subsection_slug, a.slug, a.heading, a.summary, a.image
            FROM article AS a
            INNER JOIN article_placement AS ap
            ON a.slug = ap.article_slug
            WHERE ap.sd_section_slug = ?
            AND ap.ds_dimension_slug = ?
            ORDER BY ap.ds_subsection_slug, RAND()";

    // Prepare statement.
    $stmt2 = mysqli_prepare($dbc, $q2);

    // Bind variable to query.
    mysqli_stmt_bind_param($stmt2, 'ss', $sectionSlug, $dimensionSlug);

    // Execute query.
    mysqli_stmt_execute($stmt2);

    // Store results.
    mysqli_stmt_store_result($stmt2);

    // Check # of Records Returned.
    if (mysqli_stmt_num_rows($stmt2)>0){
        // Featured Sub-Sections Found.

        // Bind result-set to variables.
        mysqli_stmt_bind_result($stmt2, $articleSubsectionSlug, $articleSlug, $articleHeading,
                                $articleSummary, $articleImage);

		$number_of_articles = mysqli_stmt_num_rows($stmt2);
        // Build Featured-Articles Array.
        $n=1;
        while (mysqli_stmt_fetch($stmt2)){
            // ********************************
            // Build Featured Sub-Sections.        *
            // ********************************
                // Start of Sub-Section Box.
				if ($n==1){
                	$myData .= "<div class='boxSubSection'>
                        <h3>$subsectionName</h3>";

                // ****************************
                // Build Featured Articles.        *
                // ****************************

                            // Build Featured-Article.
                            $myData .= "<div class='subFeatured'>
                                    <a href=''>
                                        $articleImage
                                        <h4>$articleHeading</h4>
                                    </a>
                                    $articleSummary
                                </div>";
				}
                    // ********************************
                    // Find Articles in Sub-Section.    *
                    // ********************************

                        // check IF there are subArticles
						if ($number_of_articles>1){
	                        if ($n==2){
	                            // Start of Featured-Links.
	                            $myData .= "<div class='subArticles'>
	                                        <ul>";
	                        }
	                                //set range of links shown
	                                if ($n>=2 && $n<=6){
	                                        $myData .= "<li>
	                                            <a href='/$sectionSlug/$articleSubsectionSlug/$articleSlug'>$articleHeading</a>
	                                        </li>";
	                                }
	
	                             // End Featured-Links
	                                $myData .= "</ul>
	                                 </div>";
	                        }
                        }//End check IF there are subArticles

                // End of Sub-Section Box.
                $myData .= "</div>";
				$n++;
        }//End of query loop

    }else{	
        // Featured Sub-Sections Not Found.
		$myData .= "Featured Sub-Sections Not Found.";
    }
?>

Drummin · May 22, 2013, 11:18pm

Looks like I’m missing the first query loop, which I assume needs to wrap around second query. Again without being able to test this it’s hard to know if it’s right.

 <?php
    $sectionSlug = $_GET['section'];
    $sectionName = getSectionName($dbc, $sectionSlug);
    $dimensionSlug = 'featured-' . $sectionSlug;

    // Build Featured Sub-Sections.
$myData = "";
    // Build query.
    $q1 = "SELECT ss.slug, ss.name
            FROM section_dimension AS sd
            INNER JOIN dimension_subsection AS ds
            ON sd.dimension_slug = ds.dimension_slug
            INNER JOIN subsection AS ss
            ON ss.slug = ds.subsection_slug
            WHERE sd.section_slug = ?
            AND sd.dimension_slug = ?
            ORDER BY sd.section_slug, ss.sort";

    // Prepare statement.
    $stmt1 = mysqli_prepare($dbc, $q1);

    // Bind variable to query.
    mysqli_stmt_bind_param($stmt1, 'ss', $sectionSlug, $dimensionSlug);

    // Execute query.
    mysqli_stmt_execute($stmt1);

    // Store results.
    mysqli_stmt_store_result($stmt1);

    // Check # of Records Returned.
    if (mysqli_stmt_num_rows($stmt1)>0){
        // Featured Sub-Sections Found.

        // Bind result-set to variables.
        mysqli_stmt_bind_result($stmt1, $subsectionSlug, $subsectionName);

        // Fetch loop...
		while (mysqli_stmt_fetch($stmt1)){
		
		    // Build Featured Articles.
		
		    // Build query.
		    $q2 = "SELECT ap.ds_subsection_slug, a.slug, a.heading, a.summary, a.image
		            FROM article AS a
		            INNER JOIN article_placement AS ap
		            ON a.slug = ap.article_slug
		            WHERE ap.sd_section_slug = ?
		            AND ap.ds_dimension_slug = ?
		            ORDER BY ap.ds_subsection_slug, RAND()";
		
		    // Prepare statement.
		    $stmt2 = mysqli_prepare($dbc, $q2);
		
		    // Bind variable to query.
		    mysqli_stmt_bind_param($stmt2, 'ss', $sectionSlug, $dimensionSlug);
		
		    // Execute query.
		    mysqli_stmt_execute($stmt2);
		
		    // Store results.
		    mysqli_stmt_store_result($stmt2);
		
		    // Check # of Records Returned.
		    if (mysqli_stmt_num_rows($stmt2)>0){
		        // Featured Sub-Sections Found.
		
		        // Bind result-set to variables.
		        mysqli_stmt_bind_result($stmt2, $articleSubsectionSlug, $articleSlug, $articleHeading,
		                                $articleSummary, $articleImage);
		
				$number_of_articles = mysqli_stmt_num_rows($stmt2);
		        // Build Featured-Articles Array.
		        $n=1;
		        while (mysqli_stmt_fetch($stmt2)){
		            // ********************************
		            // Build Featured Sub-Sections.        *
		            // ********************************
		                // Start of Sub-Section Box.
						if ($n==1){
		                	$myData .= "<div class='boxSubSection'>
		                        <h3>$subsectionName</h3>";
		
		                // ****************************
		                // Build Featured Articles.        *
		                // ****************************
		
		                            // Build Featured-Article.
		                            $myData .= "<div class='subFeatured'>
		                                    <a href=''>
		                                        $articleImage
		                                        <h4>$articleHeading</h4>
		                                    </a>
		                                    $articleSummary
		                                </div>";
						}
		                    // ********************************
		                    // Find Articles in Sub-Section.    *
		                    // ********************************
		
		                        // check IF there are subArticles
								if ($number_of_articles>1){
			                        if ($n==2){
			                            // Start of Featured-Links.
			                            $myData .= "<div class='subArticles'>
			                                        <ul>";
			                        }
			                                //set range of links shown
			                                if ($n>=2 && $n<=6){
			                                        $myData .= "<li>
			                                            <a href='/$sectionSlug/$articleSubsectionSlug/$articleSlug'>$articleHeading</a>
			                                        </li>";
			                                }
			
			                             // End Featured-Links
			                                $myData .= "</ul>
			                                 </div>";
			                        }
		                        }//End check IF there are subArticles
		
		                // End of Sub-Section Box.
		                $myData .= "</div>";
						$n++;
		        }//End of query2 loop
		
		    }else{	
		        // Featured Sub-Sections Not Found.
				$myData .= "Featured Sub-Sections Not Found.";
		    }
		}//End of query1 loop
    }else{
        // Error-Handling
				$myData .= "Section Not Found.";
    }
?>

captainccs · May 23, 2013, 12:20am

Drummin:

If it were my design I would have one data routine for each section of the page. Some sections would have simple sql queries, some complex one and others might have multiple queries. It all depends on how the data is organized in the database and how it needs to be transformed for display. You can’t decide that until you know both structures, source and destination. But as I said, the actual code is secondary to the overall design. Maybe all these data routines need to be inside a general sql query.

What I would insist on is making the sql queries do as much of the work as possible to organize the data to simplify the php code that does the final transformation.

DoubleDee · May 23, 2013, 7:50pm

I’m not exactly getting the answers I was hoping for, so let me try again…

Currently, my second query finds all Articles for all Sub-Sections.

Next, I build an array.

Finally, in the HTML portion of my script, I loop through all Articles in the array, and just display those that apply to the current Sub-Section.

Is taking that approach right or wrong?

It seems to me, that I should already have the proper Articles for the current Sub-Section, and just loop through those and display them? :-/ (But like I have said throughout this thread, I don’t see how to do that.)

Sincerely,

Debbie

Drummin · May 23, 2013, 7:57pm

If you use the parent category as the primary key in your array building (like I showed in the example with array building) then when you loop under each category you would include this key so only those belonging to parent are shown.

I do see however that the first WHILE loop is missing from that example, which should wrap around the second query.
while (mysqli_stmt_fetch($stmt1)){

<?php
    $sectionSlug = $_GET['section'];
    $sectionName = getSectionName($dbc, $sectionSlug);
    $dimensionSlug = 'featured-' . $sectionSlug;

    // Build Featured Sub-Sections.
$featuredArticlesArray = array();
    // Build query.
    $q1 = "SELECT ss.slug, ss.name
            FROM section_dimension AS sd
            INNER JOIN dimension_subsection AS ds
            ON sd.dimension_slug = ds.dimension_slug
            INNER JOIN subsection AS ss
            ON ss.slug = ds.subsection_slug
            WHERE sd.section_slug = ?
            AND sd.dimension_slug = ?
            ORDER BY sd.section_slug, ss.sort";

    // Prepare statement.
    $stmt1 = mysqli_prepare($dbc, $q1);

    // Bind variable to query.
    mysqli_stmt_bind_param($stmt1, 'ss', $sectionSlug, $dimensionSlug);

    // Execute query.
    mysqli_stmt_execute($stmt1);

    // Store results.
    mysqli_stmt_store_result($stmt1);

    // Check # of Records Returned.
    if (mysqli_stmt_num_rows($stmt1)>0){
        // Featured Sub-Sections Found.

        // Bind result-set to variables.
        mysqli_stmt_bind_result($stmt1, $subsectionSlug, $subsectionName);

        // Fetch loop...
		 while (mysqli_stmt_fetch($stmt1)){ 
		
		    // Build Featured Articles.
		
		    // Build query.
		    $q2 = "SELECT ap.ds_subsection_slug, a.slug, a.heading, a.summary, a.image
		            FROM article AS a
		            INNER JOIN article_placement AS ap
		            ON a.slug = ap.article_slug
		            WHERE ap.sd_section_slug = ?
		            AND ap.ds_dimension_slug = ?
		            ORDER BY ap.ds_subsection_slug, RAND()";
		
		    // Prepare statement.
		    $stmt2 = mysqli_prepare($dbc, $q2);
		
		    // Bind variable to query.
		    mysqli_stmt_bind_param($stmt2, 'ss', $sectionSlug, $dimensionSlug);
		
		    // Execute query.
		    mysqli_stmt_execute($stmt2);
		
		    // Store results.
		    mysqli_stmt_store_result($stmt2);
		
		    // Check # of Records Returned.
		    if (mysqli_stmt_num_rows($stmt2)>0){
		        // Featured Sub-Sections Found.
		
		        // Bind result-set to variables.
		        mysqli_stmt_bind_result($stmt2, $articleSubsectionSlug, $articleSlug, $articleHeading,
		                                $articleSummary, $articleImage);		
		
		        // Build Featured-Articles Array.
		       
		        while (mysqli_stmt_fetch($stmt2)){            
		            $featuredArticlesArray[$subsectionSlug]['subsectionName'][] = $subsectionName;
		            $featuredArticlesArray[$subsectionSlug]['articleSlug'][] = $articleSlug;
		            $featuredArticlesArray[$subsectionSlug]['articleHeading'][] = $articleHeading;
		            $featuredArticlesArray[$subsectionSlug]['articleSummary'][] = $articleSummary;
		            $featuredArticlesArray[$subsectionSlug]['articleImage'][] = $articleImage;
                }//End of query2 loop
        
            }else{    
                // Featured Sub-Sections Not Found.
            }
        }//End of query1 loop
    }else{
        // Error-Handling
    }    
?>

Note: The second query should be grabbing articles related to first query.

DoubleDee · May 23, 2013, 8:28pm

Drummin,

I have no clue how to read this…


$featuredArticlesArray[$subsectionSlug]['subsectionName'][] = $subsectionName;

Debbie

fretburner · May 23, 2013, 8:35pm

Debbie,

Quick question for you… when you loop through the results of your 2nd query, if it’s the first time through you create your featured article for that particular subsection, right? So, is it correct to assume that for the rest of the results for that subsection, articleImage, articleHeading and articleSummary will be empty (or null)?

Drummin · May 23, 2013, 8:37pm

$featuredArticlesArray[$subsectionSlug][‘subsectionName’] = $subsectionName;

$featuredArticlesArray == array name. You can change this to any name you wish to shorten.
[$subsectionSlug] == SHOULD BE the primary key that’s defined from first query. All results from second query should be related to this key.
[‘subsectionName’] == subsectionName key
== open key. First value added under this will have a key of ZERO, second ONE etc.
$subsectionName; == The matching value you wish to place under the [‘subsectionName’] key.

Drummin · May 23, 2013, 8:42pm

It’s Debbie’s question to answer but I would assume that each article would have an image, heading and summary and though she may not wish to display these in this case, the values should be there.

DoubleDee · May 23, 2013, 8:45pm

Correct.

So, is it correct to assume that for the rest of the results for that subsection, articleImage, articleHeading and articleSummary will be empty (or null)?

No.

Each Article has a Slug, Heading, Summary, and Image (path).

To avoid yet another query, I decided to bring over all of that data - not a whole lot! - and then I’d have it for the 1st record, and could disregard it for the other records.

Make sense?

Debbie

Drummin · May 23, 2013, 8:58pm

The first thing of course is to check if the array is being built as expected with articles are falling under the correct primary key. Use print to view.

<?php
echo "<pre>";			
print_r($featuredArticlesArray);
echo "</pre>";
?>

Drummin · May 23, 2013, 9:04pm

The direct build of display #27 is still an option instead of this whole array building.

DoubleDee · May 23, 2013, 9:07pm

I appreciate you guys trying to help, but I really struggle with Arrays in general, and doing this online is even trickier. (We need a real-time white board, or you guys need to come over to my place!!)

[ot]The reason why Arrays are so hard for me to follow, is that I was raised in the Database World, were ONE RECORD can have MANY COLUMNS, and arrays throw that idea out the window!!

(It’s all that damn nesting that drives me crazy, even when I use var_dump to try and visualize things…)

[/ot]

What I do know, is that all Arrays have a KEY and a VALUE. Period.

A KEY must always be a Scalar.

A VALUE can be a Scalar or another Array.

Arrays can be “Indexed” and have Key-Value Pairs like…
0 => Debbie
1 => Drummin
2 => Fretburner

Arrays can also be “Associative” and have Key-Value Pairs like…
Debbie => Arizona
Drummin => Mars
Fretburner => The Moon

Here is how I set up my Array which is one Array nested inside another…


	// ********************************
	// Build Featured-Articles Array.	*
	// ********************************
	$n=1;
	while (mysqli_stmt_fetch($stmt2)){
		$featuredArticlesArray[$n] = array('articleSubsectionSlug' => $articleSubsectionSlug,
									'articleSlug' => $articleSlug,
									'articleHeading' => $articleHeading,
									'articleSummary' => $articleSummary,
									'articleImage' => $articleImage);
		$n=$n+1;
	}//End of BUILD FEATURED-ARTICLE ARRAY

And here is how I access values from it…


	// ****************************
	// Build Featured Articles.		*
	// ****************************
	$a = 1;
	foreach($featuredArticlesArray as $articleNo => $articleArray){

		// ********************************
		// Find Articles in Sub-Section.	*
		// ********************************
		if ($articleArray['articleSubsectionSlug'] == $subsectionSlug){
			// Match.
			if ($a == 1){
				// Build Featured-Article.
				echo "<div class='subFeatured'>
						<a href=''>
							{$articleArray['articleImage']}
							<h4>{$articleArray['articleHeading']}</h4>
						</a>
						{$articleArray['articleSummary']}
					</div>";

I realize you are trying to add another dimension to the Array, but it seems like you have one too many levels with this code…


              // Build Featured-Articles Array.
               
                while (mysqli_stmt_fetch($stmt2)){            
                    $featuredArticlesArray[$subsectionSlug]['subsectionName'][] = $subsectionName;
                    $featuredArticlesArray[$subsectionSlug]['articleSlug'][] = $articleSlug;
                    $featuredArticlesArray[$subsectionSlug]['articleHeading'][] = $articleHeading;
                    $featuredArticlesArray[$subsectionSlug]['articleSummary'][] = $articleSummary;
                    $featuredArticlesArray[$subsectionSlug]['articleImage'][] = $articleImage;
                }//End of query2 loop

What’s up with that last [ ]??

Follow me??

Debbie

fretburner · May 23, 2013, 9:12pm

OK, so I’ve re-read your previous posts and had a go at trying to answer these questions you had:

DoubleDee:

Questions/Concerns:
1.) I’m not sure if I needed to use an Array above.

2.) The way I have it now, I query all of the Articles for a given Section and Dimension. While this number would likey be under 50, it seems like it would make more sense to do a query for each Sub-Section… (This is really what started this thread. Part of me wants to keep my Outer While loop, but inside of it, run a query based on the current Sub-Section in the loop. The logic being that I am only grabbing the Articles that apply to the current Sub-Section. Because this is a summary page, I don’t plan on having more than 5 Articles per Sub-Section box, because there will be a link like this “<< View all MARKET articles>>” at the bottom of each Sub-Section box. Still, I feel like I’m being sloppy here?!)

3.) It seems a little flaky how I had to add IF-THEN’s to handle the start of my HTML…

4.) It seems like my Array Variable names are rather LARGE in my HTML code…

5.) In general, it just seems like my code could be streamlined more. (Maybe not?!)

Here’s what I’ve come up with:


$sectionSlug = $_GET['section']; 
$sectionName = getSectionName($dbc, $sectionSlug); 
$dimensionSlug = 'featured-' . $sectionSlug; 
$subsectionSlug = '';

// Queries 
$q1 = "SELECT ss.slug, ss.name 
        FROM section_dimension AS sd 
        INNER JOIN dimension_subsection AS ds 
        ON sd.dimension_slug = ds.dimension_slug 
        INNER JOIN subsection AS ss 
        ON ss.slug = ds.subsection_slug 
        WHERE sd.section_slug = ? 
        AND sd.dimension_slug = ? 
        ORDER BY sd.section_slug, ss.sort";
        
$q2 = "SELECT ap.ds_subsection_slug, a.slug, a.heading, a.summary, a.image 
        FROM article AS a 
        INNER JOIN article_placement AS ap 
        ON a.slug = ap.article_slug 
        WHERE ap.sd_section_slug = ? 
        AND ap.ds_dimension_slug = ? 
        AND ap.ds_subsection_slug = ?
        ORDER BY ap.ds_subsection_slug, RAND()";

// Prepare statement 1 
$stmt1 = mysqli_prepare($dbc, $q1); 
mysqli_stmt_bind_param($stmt1, 'ss', $sectionSlug, $dimensionSlug);  

// Prepare statement 2 
$stmt2 = mysqli_prepare($dbc, $q2); 
mysqli_stmt_bind_param($stmt2, 'ss', $sectionSlug, $dimensionSlug, $subsectionSlug);

// Execute statement 1
mysqli_stmt_execute($stmt1); 
mysqli_stmt_store_result($stmt1);

// Check # of Records Returned. 
if (mysqli_stmt_num_rows($stmt1) > 0) {

    // Bind result-set to variables. 
    mysqli_stmt_bind_result($stmt1, $subsectionSlug, $subsectionName); 

    echo "<!-- FEATURED SUBSECTIONS --> "
    echo "<div id='boxFeaturedSubsections'> "
    echo "    <h2>Featured</h2>"

    // ******************************** 
    // Build Featured Sub-Sections.        * 
    // ******************************** 
    while (mysqli_stmt_fetch($stmt1)){ 
        // Start of Sub-Section Box. 
        echo "<div class='boxSubSection'> 
                <h3>$subsectionName</h3>"; 

        // Execute query. 
        mysqli_stmt_execute($stmt2); 
        mysqli_stmt_store_result($stmt2); 

        // Check # of Records Returned. 
        if (mysqli_stmt_num_rows($stmt2) > 0) {

            mysqli_stmt_bind_result($stmt2, $articleSubsectionSlug, $articleSlug, $articleHeading, 
                                    $articleSummary, $articleImage); 
    
            // Build Featured-Articles 
            while (mysqli_stmt_fetch($stmt2)) {                         
                $a = 1; 
                if ($a == 1) { 
                    // Build Featured-Article. 
                    echo "<div class='subFeatured'> 
                            <a href=''> 
                                $articleImage
                                <h4>$articleHeading</h4> 
                            </a> 
                            $articleSummary
                        </div>"; 
                } else { 
                    if ($a == 2){ 
                        // Start of Featured-Links. 
                        echo "<div class='subArticles'> 
                                <ul>"; 
                    } 
                    echo "          <li> 
                                        <a href='/$sectionSlug/$articleSubsectionSlug/$articleSlug'>$articleHeading</a> 
                                    </li>"; 
                }
                $a = $a + 1; 
            } 
            // End Featured-Links 
            echo "              </ul> 
                              </div>"; 
            // End of Sub-Section Box. 
            echo "</div>"; 
            
        }//End of BUILD FEATURED SUB-SECTIONS 
        
    } else { 
        // Featured Sub-Sections Not Found. 
    } 
    
} else { 
    // Error-Handling 
}

So in answer to your questions:

I’ve removed the array to try and simplify things.
The way I’ve changed the code should query the DB for each subsection individually as it generates the HTML… I’ve had a go at altering your q2 query to do this, but as I don’t fully understand your data model you may have to tweak this yourself.
I couldn’t think of a different way to do it, so I think you’ll just have to live with a couple if/else’s.
Now that the array is gone, I’ve shortened the variable names, so they should be a little more readable.
I’ve removed most of the comments for brevity/clarity - and it should be a little easier to read now that the code has been reorganised a little.

Let me know if it works OK - I apologise for any bugs, but it’s hard to test this kind of thing on my own machine.

Drummin · May 23, 2013, 9:33pm

If you look at your example

Debbie => Arizona
Drummin => Mars
Fretburner => The Moon

Let’s say these keys are from your first query “Debbie”, “Drummin” and “Fretburner”. These are the primary keys in your array and the also the identifying variable in your second query. These primary keys are added to the array like this.

$featuredArticlesArray[‘Debbie’]
$featuredArticlesArray[‘Drummin’]
$featuredArticlesArray[‘Fretburner’]

So the first loop is going to find Debbie and second query will look for all articles from Debbie and will add each key => value par under Debbie.
For example sake, article names will be DOG, CAT and MOUSE. The Result will be

$featuredArticlesArray[‘Debbie’][‘subsectionName’][0]=>DOG
$featuredArticlesArray[‘Debbie’][‘subsectionName’][1]=>CAT
$featuredArticlesArray[‘Debbie’][‘subsectionName’][2]=>MOUSE

Once all articles from Debbie are found the second loop starts and finds all articles by Drummin and this process repeats.

Now for displaying these results the primary key would be the section “Debbie”.
The displayed article has the key of zero [0] and any other results [1],[2] etc are shown as links.

Topic		Replies	Views
Is this doable for a beginner? PHP	3	343	October 8, 2014
How can this be done? Databases	10	721	January 8, 2011
Create HTML table from PHP Multidimensional array PHP	2	16776	October 21, 2014
Can anyone help me? PHP	3	376	June 11, 2011
Need help with building URLs PHP	14	693	March 26, 2011

How to Build a Complex Web-Page from Dataset?

Related topics